Calling Stored Procedure

Mun · Post by **Mun** » Fri May 12, 2006 7:42 am

I am trying to call and execute a stored procedure in After SQL.

Oracle_OCI_9i_6: Executing AfterSQL
Oracle_OCI_9i_6: CALL BI_APP.MUNS_PROC
Oracle_OCI_9i_6: ORA-06576: not a valid function or procedure name

BI_APP is the database schema and MUNS_PROC is the name of my stored procedure.

I've tried using EXEC instead of CALL too but it gave me the same message.

chulett · Post by **chulett** » Fri May 12, 2006 7:55 am

To me, that would imply that the user you are connected as doesn't have the grants they need to execute the procedure.

Mun · Post by **Mun** » Fri May 12, 2006 8:04 am

Thanks! I've thought about that initially. When I copy and paste that command in TOAD and run it, it ran successfully......

ray.wurlod · Post by **ray.wurlod** » Fri May 12, 2006 3:22 pm

Does your DataStage job connect to Oracle using the same login/password that you used in TOAD?

Mun · Post by **Mun** » Fri May 12, 2006 3:42 pm

Yes, it is the same user id and password.

chulett · Post by **chulett** » Fri May 12, 2006 5:03 pm

Ok then... what exactly does your stored procedure do?

Mun · Post by **Mun** » Fri May 12, 2006 5:09 pm

The stored procedure inserts some rows into the table. It does not return any values. Just plain insert statements.

I also forgot to mention that the stored procedure is in BI_APP schema and the stored procedure insert the rows into another schema (let's say BI_APP_TEST). I couldn't create the stored procedure in the BI_APP_TEST schema due to insufficient privilege with the userid.

Could that be the issue?

DSguru2B · Post by **DSguru2B** » Sat May 13, 2006 10:01 pm

Oh yea definetly I beleive, its the reason. YOu dont have privelages to insert into, or execute a procedure in the other schema.