Calling Stored procedure DRS Stage SQL Before tab

[news78]

I am trying to call a oracle stored proc say
TRUNC_MYTABLE(tablename varchar in, msg varchar out)
I want to execute this proc before loading data via DRS stage, hence am using the SQL > Before tab.

After going through the posts I tried various combinations of the following in the DRS Stage > SQL > Before Tab:
=======================================
CALL myschema.TRUNC_MYTABLE('MYTABLENAME',variable vmsg varchar(22))

[gives error > ORA-00907: missing right parenthesis]
=======================================
variable vmsg varchar(22);
CALL myschema.TRUNC_MYTABLE('MYTABLENAME',:vmsg);

[gives error > Incorrect sql statement variable vmsg varchar(22);]

Any suggestions?

[DSguru2B]

I highly doubt you can get a OUT parameter from the before/after sql tab. For that you need to run it via STP stage.

[news78]

I highly doubt you can get a OUT parameter from the before/after sql tab. For that you need to run it via STP stage. ...

If my design is as follows:

[Transformer] > [DRS Stage]

Where do i put in the [STP] stage such that it is invoked once before the data is laoded via the DRS stage? I do not see any Before Stage option in DRS Stage.

[news78]

I can think of a few options:
1. Create a shell script which invokes the stored proc and execute it as a Before Job sub routine.
2. Create a separate job which has a Stored Proc stage and run this job before the data load job.

(The stored proc truncates a given table and returns a success/eror message)

Any other options possible?

[narasimha]

If you are not very keen in capturing the success/error message.
You can put the truncate stagement directly in the before tab of the DRS stage.

[pradkumar]

You cannot get any out parameters if you c=use stored procedure in before/after tab of DRS.

The best option would be to use a STP stage, call the procedure there and then run the job.

Youc an create a sequence:

STP job Activity----->Main JOB

[news78]

If you are not very keen in capturing the success/error message.
You can put the truncate stagement directly in the before tab of the DRS stage.

Thanks Narasihma. I tried to do this(see my first post), but was not able to, it kept on throwing errors. Any idea whats wrong?
I also tried:
CALL myschema.TRUNC_MYTABLE('MYTABLENAME')

Oracle Error> Incorrect number or type of arguments.

Is it the case that if a procedure has a OUT parameter, we cant call it through the Before SQL tab, even if we don't intend to use that OUT return value?

[narasimha]

I was talking in terms of executing the truncate statement directly in the before tab of the DRS stage, rather an wrapping it into a procedure. If you are ok with it, enter it directly. You can also parameterize the table name.

Code: Select all

truncate table <TABLE_NAME>

[us1aslam1us]

Why not just using the in-built update action in the DRS stage to truncate the table and then insert the rows. Check the DRS stage " Update action" drop down menu.

[chulett]

The reason one typically uses a 'sproc' for that is because the user doesn't have the permissions needed, so I would guess that isn't an option.

And AFAIK you cannot call one before/after that has OUT parameters. Makes sense as there's no place for them to go, but not positive. For grins, have you tried?

CALL myschema.TRUNC_MYTABLE('MYTABLENAME','')