Forum Stats

  • 3,734,702 Users
  • 2,247,028 Discussions
  • 7,857,459 Comments

Discussions

passing parameters to js script?

anilech_nts
anilech_nts Member Posts: 10
edited Nov 23, 2018 4:03AM in SQLcl

Hi! Simple question: how can i pass parameter to js script from sqlcl command line? for me args.length value is always 0.

contents of test.js:

scriptprint("\nargs.length: "+args.length);for(var i=1;i<args.length;i++){  print("arg["+i+"]:"+args[i]);}/exit

C:\oracle>sql /nolog @test.jsSQLcl: Release 18.3 Production on Thu Nov 15 10:55:43 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.args.length: 0C:\oracle>sql /nolog @test.js par1 par2 par3SQLcl: Release 18.3 Production on Thu Nov 15 10:55:43 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved. args.length: 0 <- i expect 3 here

thanks!

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Nov 15, 2018 8:00AM

    slight simplification to your test.js:

    F:\Oracle>type test.jsprint("\nargs.length: "+args.length);for (i = 1; i < args.length; i++) {   print("arg[" + i +"]: " + args[i]);}F:\Oracle>

    In sqlcl:

    F:\Oracle>sql /nologSQLcl: Release 18.1 Production on Thu Nov 15 22:32:45 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.SQL> script test.js 1 2 3 4args.length: 5arg[1]: 1arg[2]: 2arg[3]: 3arg[4]: 4SQL>

    ...so pipe the script command into sqlcl ala:

    F:\Oracle>echo script test.js 1 2 3 4 | sql /nolog -sargs.length: 5arg[1]: 1arg[2]: 2arg[3]: 3arg[4]: 4F:\Oracle>

    Note that arg.length: 5 due to the fact that array indexes start with 0. Script was passed 4 arguments + arg[0] (which holds filename) == 5.

  • anilech_nts
    anilech_nts Member Posts: 10
    edited Nov 21, 2018 8:53AM

    Hi !

    Solved it with good old fashioned &1 &2 ... and a solution from here.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Nov 21, 2018 9:13AM

    It would be nice if you posted a simple working example, that may aid others.

  • anilech_nts
    anilech_nts Member Posts: 10
    edited Nov 23, 2018 4:03AM

    Hi. It is a little bit ugly

    scriptif ("&_connect_identifier"=="") {  print("ERROR: not connected to Oracle!");  sqlcl.setStmt("exit"); sqlcl.run();}/-- <trick url="https://vbegun.blogspot.com/2008/04/on-sqlplus-defines.html">-- here we expect max 4 command line arguments, just add another columns if you need more.-- works only if connected to the database :(set ver off serverout on size unl feedback offcol 1 new_value 1col 2 new_value 2col 3 new_value 3col 4 new_value 4select '' "1",'' "2",'' "3",'' "4"  from dual where rownum = 0/-- </trick>scriptfunction main() {  var myArgs=[];  function pArg(a) {    if (a=="") return;    myArgs.push(a);    /* other argument's processing could be here */  }  pArg('&1'); pArg('&2'); pArg('&3'); pArg('&4');  print("\nnumber of arguments: "+myArgs.length);  for (var i = 0; i < myArgs.length; i++) {    print("arg["+i+"]="+myArgs[i]);  }}main();/exit

    sql -s -l  user/[email protected] @test.js p1 "p 2" par3number of arguments: 3arg[0]=p1arg[1]=p 2arg[2]=par3
Sign In or Register to comment.