Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Two questions about Oracle stored procedure.

    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

    #2
    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

    Comment


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

      Comment


        #4
        Originally posted by Blama View Post
        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”

        Comment


          #5
          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

          Comment


            #6
            Originally posted by Blama View Post
            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)'

            Comment

            Working...
            X