Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: bat file to run sql script

    I have a bat file with code like below:

    sqlplus -S <user>/<password>@<connect_string> @SQLScripts1.sql

    I have to execute some procedures in database from above SQLScripts1.sql. I am able to execute only the procedures with IN parameters or without parameters by following way in SQLScripts1.sql.

    exec mypackage.myprocedure1; -- for procedure without parameters
    exec mypackage.myprocedure2(sysdate); -- for procedure with OUT parameter

    I am facing issues with executing procedures (with OUT parameters). I am unable to pass NULL or 0 or values to OUT parameters of procedures inside SQLScripts1.sql.

    Please help.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But, OUT parameters return a value from a procedure, you don't use them to send a value into it.

    BTW, I guess you meant to say "for procedure with IN parameter" (not OUT):
    exec mypackage.myprocedure2(sysdate); -- for procedure with OUT parameter
    How to fix a problem? Declare a variable which will get the OUT value, and then call the procedure. Something like this: imagine that there's a procedure which looks like
    Code:
    PROCEDURE myprocedure3 (par_date IN DATE, par_out OUT some_datatype);
    Then you'd do it like this:
    Code:
    DECLARE
      l_out some_datatype;
    BEGIN
      mypackage.myprocedure3(sysdate, l_out);
      -- do something (or do not) with 'l_out'
    END;

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    Thanks Littlefoot for the reply.

    You are right. I want to say at below statement is
    exec mypackage.myprocedure2(sysdate); -- for procedure with IN parameter

    But actual my goal is execute the sql script file from bat file. If I declare script file like sql block as you mantioned below, the bat file is not executing the script file. Even bat file execution never ending.

    I think script file shouldn't have block of code with DECLARE, BEGIN, and END; it should have only statements/commands which can execute by bat file. For Example, statement to execute procedure with IN parameter is:

    exec mypackage.myprocedure2(sysdate); -- for procedure with IN parameter

    What is the statement or way to execute procedure with OUT parameter?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by rajkumarboga
    I think script file shouldn't have block of code with DECLARE, BEGIN, and END; it should have only statements/commands which can execute by bat file.
    A batch file cannot execute SQL statements.
    The batch file calls SQL Plus and that executes SQL statements.
    And SQL plus is very well able to run the anonymous PL/SQL block show by littlefoot.
    Without the block you won't be able to deal with OUT parameters.
    What is the statement or way to execute procedure with OUT parameter?
    Littlefoot has shown you how to do that.
    You need to put his example into your file SQLScript1.sql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •