Two questions about Oracle stored procedure.

[merlini]

1.How could I get the return value from a Oracle Stored Procedure with output parameter via operationId? Datasource Code: Call ABC(1); Procedure Code: create or replace procedure ABC (aa out int) as begin aa:=2; end; JSP Code: isc.DataSource.get("Student").fetchData({},'',{operationId:'new'}) Imformation in Console: output parameter not a bind variable parameter for a function or a procedure so I tried that: Datasource Code: Call ABC(MyPackage.aaa); Package Code: create or replace package MyPackage IS aaa int; End MyPackage; create or replace Package Body MyPackage AS End MyPackage; Imformation in console: ‘AAA’ is not a procedure or is undefined Then How to transfer output variables to the stored procedure ‘ABC’? 2.How could I call an Oracle Stored Procedure without output parameter? Datasource Code: Call ABC(); Procedure Code: create or replace procedure ABC as a int; begin a:=1; end; JSP Code: isc.DataSource.get("Student").fetchData({},'',{operationId:'new'}) Imformation in Console： Unable to execute extracting on PLSQL statement

[Blama]

Hi merlini,

please have a look at this thread, perhaps it does help you.
Also, your post is really hard to read with no formatting at all.

Best regards
Blama

[Blama]

This thread contains ideas (no POC) for OUT-parameter handling.

[merlini]

This thread contains ideas (no POC) for OUT-parameter handling.

Thx for ur replying my questions Blama and now I have posted a same topic with formatting here: http://forums.smartclient.com/forum/...ored-procedure

But then, to be honest,I have searched and read these two topics before I post this topic.I have tried ‘SELECT ABC() FROM DUAL ‘ (procedure ABC don’t have any output/input parameters) as ur suggestion and then the console prints the imformation :”invalid identifier ABC”

[Blama]

Hi,

this will only work if ABC is a function, not a procedure. That code should also fail from SQL Developer.
I changed my program design so I don't need to call stored procedures / functions anymore from SmartGWT, but in a nutshell I think these are the options:

1. SP with no out value: Use CALL sp / EXEC sp and correct sqlType-attribute for the custom-operationBinding
2. SP with one out value: Use wrapper function that calls the prodecure, then SELECT func() from DUAL.
3. SP with more than one out value: Use wrapper table function that calls the prodecure, then <tableClause>tablefunction</tableClause> (did not try that)

Trying the different options is easy, just change the .ds.xml, no recompile needed.

Besides that, you can also use a DMI, discard the normal operation and call the SP yourself via JDBC (did not try that).

Best regards
Blama

[merlini]

Hi,

this will only work if ABC is a function, not a procedure. That code should also fail from SQL Developer.
I changed my program design so I don't need to call stored procedures / functions anymore from SmartGWT, but in a nutshell I think these are the options:

1. SP with no out value: Use CALL sp / EXEC sp and correct sqlType-attribute for the custom-operationBinding
2. SP with one out value: Use wrapper function that calls the prodecure, then SELECT func() from DUAL.
3. SP with more than one out value: Use wrapper table function that calls the prodecure, then <tableClause>tablefunction</tableClause> (did not try that)

Trying the different options is easy, just change the .ds.xml, no recompile needed.

Besides that, you can also use a DMI, discard the normal operation and call the SP yourself via JDBC (did not try that).

Best regards
Blama

It works.Thx a lot!
I get the resultset through creating a pipelined function ABC and then call it via 'Select * From Table(ABC)'