Error while Calling SP from OCI Stage

oacvb · Post by **oacvb** » Thu Mar 08, 2007 11:11 pm

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.

chulett · Post by **chulett** » Thu Mar 08, 2007 11:14 pm

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

oacvb · Post by **oacvb** » Thu Mar 08, 2007 11:28 pm

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()};

chulett · Post by **chulett** » Thu Mar 08, 2007 11:35 pm

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

Try just this:

call SP_CID_EXTRACT_CUST_DATA_FIX1()

oacvb · Post by **oacvb** » Thu Mar 08, 2007 11:49 pm

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

ORA-06576: not a valid function or procedure name

chulett · Post by **chulett** » Thu Mar 08, 2007 11:53 pm

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.

oacvb · Post by **oacvb** » Fri Mar 09, 2007 12:16 am

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

rleishman · Post by **rleishman** » Fri Mar 09, 2007 12:25 am

Is it the ONLY command in the BEFORE SQL tab?

oacvb · Post by **oacvb** » Fri Mar 09, 2007 12:27 am

Yes.that's only that command..

kumar_s · Post by **kumar_s** » Fri Mar 09, 2007 3:03 am

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.

DSguru2B · Post by **DSguru2B** » Fri Mar 09, 2007 7:46 am

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.

kumar_s · Post by **kumar_s** » Fri Mar 09, 2007 7:50 am

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

chulett · Post by **chulett** » Fri Mar 09, 2007 8:25 am

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.

DSguru2B · Post by **DSguru2B** » Fri Mar 09, 2007 8:29 am

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.

oacvb · Post by **oacvb** » Sun Mar 11, 2007 5:14 pm

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