Question Re: Calling store procedure using class cl_sql_statement not running in Technology Q&A

Calling store procedure using class cl_sql_statement not running

Former Member — Thu, 20 May 2010 16:13:28 GMT

Hello together

i want to call a stored procedure that has an input and an output parameter but when i using my coding i m getting the following error

ORA-06550: line 1, column 7:#PLS-00201: identifier 'STORED_PROC_NAME' must be declared#ORA-06550: line 1, column 7:#PL/SQL: Statement ignored


    GET REFERENCE OF lv_input  IN lr_dref."in

    lr_cl_sql_statement->set_param( data_ref = lr_dref
                        inout    = cl_sql_statement=>C_PARAM_IN ).
   GET REFERENCE OF lv_out INTO lr_dref. "out
    lr_cl_sql_statement->set_param( data_ref = lr_dref
                        inout    = cl_sql_statement=>C_PARAM_OUT ).

    TRY.
      data lv_ROWS_PROCESSED type i.

    CALL METHOD LR_CL_SQL_STATEMENT->EXECUTE_PROCEDURE
      EXPORTING
        PROC_NAME      ='Stored_Proc_Name'
      RECEIVING
        ROWS_PROCESSED = lv_ROWS_PROCESSED

i my oppinion there could be an error in setting the parameters. Has anyone an running solution for calling a stored procedure with in and out parameter. I already tested the ADBC Programs and even had a sight in the class documentation but there is no example with in and output parameter.

Thank your for your help!

Re: Calling store procedure using class cl_sql_statement not running

volker_borowski2 — Thu, 20 May 2010 16:29:54 GMT

Well,

do not know if it helps, but there is a function module DB_EXECUTE_SQL or similar (do not have system

available right now). Works i.e. to do "exec DBMS_STATS...." calls.

It takes SQL as a string, but is not able to return output.

But may be you can peek into the code to learn something.

Volker

Re: Calling store procedure using class cl_sql_statement not running

Former Member — Thu, 20 May 2010 22:52:43 GMT

Not sure as the exact solution , but you can try the following :

You are executing the "stored procedure" as which user , is it under your schema and do you have execute priveleges on it.

Please see below links , might be helpful to you :

http://forums.devshed.com/java-help-9/call-stored-procedure-337312.html

http://bytes.com/topic/oracle/answers/643380-pls-00201-identifier-user-procedure-name-must-declared

Thanks

Rishi

Re: Calling store procedure using class cl_sql_statement not running

Former Member — Fri, 21 May 2010 06:47:24 GMT

Hello,

the user im using has the privileges to execute stored procedure. Thanks for the Links i checking it now.

Re: Calling store procedure using class cl_sql_statement not running

Former Member — Thu, 27 May 2010 09:06:16 GMT

Thanks for the hyperlinks but they weren 't working. We still have the problem. A piece of simple code would be nice.

Re: Calling store procedure using class cl_sql_statement not running

Former Member — Wed, 02 Jun 2010 13:27:46 GMT

Hello,

there was a solution for my problem. i had to add a special connection string i get from the collegues of the external database. But they told me that the method i want to call up is not a stored procedure but a function. how is it possible to call a function?

Re: Calling store procedure using class cl_sql_statement not running

Former Member — Mon, 07 Jun 2010 19:06:29 GMT

Hello I found a possible solution:

To call a stored function you have to create an instance of cl_sql_statment and create a string with the form

'SELECT <STORED_FUNCTION_NAME>(<PARAMETER_1>, ...<Parameter_n) from dual' like any other statement. Its necessary to make the select from the dual table a special oracle table