DSXchange

Hi,

I am trying to call SP from SQL --> Before tab by using the following command {call SP_CID_EXTRACT_CUST_DATA_FIX1()} but it gives the following error.

ORA-00911: invalid character

It is working fine If i call the same SP through script / sql prompt / toad. Can anyone help me in resolving the issue.

You don't have a semi-colon at the end, do you?

Yeap.I have at the end. Sorry, forgot to copy that. Please see the below statement from Before SQL tab.

{call SP_CID_EXTRACT_CUST_DATA_FIX1()};

Remove it. And silly question - are the curly braces actually there as well?

Try just this:

call SP_CID_EXTRACT_CUST_DATA_FIX1()

Might be silly to you. I tried it and got the following error.

ORA-06576: not a valid function or procedure name

So now it is being called but cannot be found. Does the user you are connecting as own the stored procedure? Have execute permissions on it? You may need to change who you are connecting as, or specify the schema owner in the call statement, or setup a synonym.

Assuming the name as typed actually exists.

It's available, I could able to execute thorugh shell script for the same user.

Is it the ONLY command in the BEFORE SQL tab?

Yes.that's only that command..

I too have seen this funny thing especially with the CALL. I dont exactly remember what I did to overcome that.
Just Try call SP_CID_EXTRACT_CUST_DATA_FIX1.

When you call it from TOAD or any other sql tool within the instance, you dont need to prefix the stored proc. with the schema name. But within DataStage you do need to fully qualify the name by prefixing the user to the stored proc.

Not really a mandate option. If you are opting for that, make sure the schema name should be parameterized.

DSguru2B wrote:When you call it from TOAD or any other sql tool within the instance, you dont need to prefix the stored proc. with the schema name. But within DataStage you do need to fully qualify the name by prefixing the user to the stored proc.

No, you don't. The rules how Oracle derives ownership don't suddenly change in DataStage.

They dont... Huh !
Well I guess I have always fully qualified it and hence my rather wrong understanding. But it does'nt hurt to fully qualify the name. The OP should try it.

I tried as Kumar mention, still having the same issue.