Problem Executing Oracle Stored Procedure

[sidnooradarsh]

I have a Oracle Stored procedure with one input parameter and no out parametres.

The procedure contains a cursor which is fetching data obtained after joining 3 tables and i am inserting the data from cursor into some table. the stored procedure is not outputting anything.

When i execute the procedure in Query template like this

<b>execute myproc('xyz')</b>

it flashed the

following error

<b>"A SQL Error has occurred on query, ORA-00900: invalid SQL statement" </b>

as suggested in earlier thread i used FixedQueryWithOutput mode but the same error. I also used Command and Fixed modes but it flashed the same error.

But when i execute the procedure in SQL*Plus it works fine.

Somebody please help me out with this.

[Former Member · Accepted solution]

hi,

please call the procedure like this

CALL Stored_Proc_Name('[Param.1]','[Param.2]',:x)

here Param.1 and param.2 are input for the procedure and x is the output param

regards,

beevin

[sidnooradarsh · Accepted solution]

Beevin,

Thank you very much it worked when i replaced Execute by Call.

Could you also please tell me how to run a Oracle script file (*.sql file) from Query template.

In SQL*Plus we run a script file as @Script_file_name.sql

how do we do it in Query Template.

Regards,

Adarsh

[Former Member · Accepted solution]

hi adarsh,

i am not sure how to run a *.sql file from Query template

u can post it as a new thread. someone who have this knowledge will reply

regards,

beevin

[salvatore_castro]

Adarsh,

You may need to change the JDBC driver to one that complies with the Oracle system you are using. I believe that xMII 11.5 comes with the 9i driver but if you have a different version than you will probably have to load the new driver into xMII and then modify the Data Server to use the new classes. Hope this helps.

Sam

[sidnooradarsh]

Sam,

I am using xMII 11.5 and some simple select, inset, delete and update statments are working fine.

But i am facing problem to execute stored procedures and

also unable to run Oracle script files. Is there any special format or syntax for running the Oracle SP and script files.

[salvatore_castro]

Adarsh,

Just and FYI, just because the basic SQL statements work with the driver does not mean that it is the correct driver for the database. I strongly suggest using the driver for the specific version of oracle you are using or you will run into quirky issues down the line. Hope this helps.

Sam

[jcgood25]

Adarsh,

Whenever an Oracle cursor is involved you need to use FixedQueryWithOutput mode so the data query will not just send the query request, but it will go back to iterate through the cursor for the resultset you desire.

I've seen examples of this such as:

call myproc(, 'xyz')

exec myproc(, 'xyz')

Let me know how it works out.

Regards,

Jeremy

[sidnooradarsh]

Jeremy,

I did exactly the same way as you suggested. I used FixedQueryWithOutput

mode. And i did something like this shown below:

VAR C REFCURSOR

EXEC MYPROC('ABC', :C)

PRINT C

this is how we execute in SQL*Plus.

I typed this in Query template but again the same error. Is there any other syntax or way to execute a Oracle stored procedure in xmii query template.

Any different format.

I am using xmii 11.5 version does this version support the above syntax.

Do we have any help documentation for writing Oracle queries in Query Template.

[Former Member]

Hi,

Try without single codes xyz