Forum Stats

  • 3,752,279 Users
  • 2,250,483 Discussions
  • 7,867,775 Comments

Discussions

In sqlcl scripting how to Call a package function with a in-out parameter

2800686
2800686 Member Posts: 2
edited Feb 6, 2018 12:14PM in SQLcl

I have plsql function which return a value and also have in-out parameter, and i am trying to call this from java script using SQLcl and trying to use the util global provided by sqlcl.

Here is an example

FUNCTION myfunction(plog IN OUT VARCHAR2) RETURN NUMBER

I would like to get the return code and also the IN-OUT variable value post the function execution.

I tried using the util.execute  and util.executeReturnList, the plsql function execution succeeds but the return value or the out variable value is not updated.

var binds = {};binds.retvalue=1;binds.plog='';util.execute("begin  :retvalue := my_PKG.myfunction(:plog); end;",binds); util.executeReturnOneCol("begin  :retvalue := my_PKG.myfunction(:plog); end;",binds); 

is there any other way to achieve this?

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Feb 5, 2018 6:31PM

    Here's a nother way:

    SQL> l  1  create or replace function myfunction(plog in out varchar2) return number as  2  begin  3    plog := 'Function with OUT param? Why?,...';  4    return(999);  5* end;SQL> /Function MYFUNCTION compiledSQL> var retval NUMBERSQL> var outval VARCHAR2(128)SQL> exec :retval := myfunction(:outval);PL/SQL procedure successfully completed.SQL> print retval outval    RETVAL----------      999OUTVAL--------------------------------------------------------------------------------Function with OUT param? Why?,...SQL> show versionOracle SQLDeveloper Command-Line (SQLcl) version: 17.4.0.354.2224SQL>
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Feb 5, 2018 7:39PM

    Here's a nother:

    SQL> l  1  script  2     sqlcl.setStmt("var retval NUMBER\n" +  3                   "var outval VARCHAR2(60)"  4     );  5     sqlcl.run();  6     sqlcl.setStmt("exec :retval := 1; :outval := '???';\n" +  7                   "exec :retval := myfunction(:outval);"  8     );  9     sqlcl.run(); 10     sqlcl.setStmt("print retval outval"); 11*    sqlcl.run()SQL> /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.    RETVAL----------       999OUTVAL--------------------------------------------------------------------------------Function with OUT param? Why?,...
    SQL>
  • 2800686
    2800686 Member Posts: 2
    edited Feb 6, 2018 12:14PM

    Hi Gaz,

     

    Thank for taking time to respond. Why function with OUT variables,? Well I am trying to reuse some of the old functions.

     

    I am familiar with both the solution you proposed, as I mentioned in my post I am trying to use java script and trying to use the global’s provided by SQLcl

    https://github.com/oracle/oracle-db-tools/tree/master/sqlcl  which return the values back into java script variables. Example : https://github.com/oracle/oracle-db-tools/blob/master/sqlcl/examples/sql.js .

     

    So my requirement is getting the function return value and the out variable back into java script variables, and I am using SQLCL jars inside my application and using java nashorn to call java script functions.

     

    I can definitely try doing something like this

    ·         Re write the PLSQL function returning multiple values using Object Type or write a wrapper function to myFunction that return Object type

    ·         Write a Java class which uses the sqlcl connection to execute the jdbc preparedstatement returning both function return values and Out variables values and declare it as global which can used in my java script .

     

    So my question was does something already exist in the SQLcl which can help achieve which I might be overlooking before I try one of the above mentioned ways.

     

    Regards,

    Prakash