how to create a batch file to invoke sqlplus and run procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: how to create a batch file to invoke sqlplus and run procedure

  1. #1
    Join Date
    Apr 2001
    Posts
    21

    Exclamation

    i want to create a batch file in Win NT which invokes a sqlplus and executes a procedure. where can i find information regarding this.
    Thanks a lot

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    Hi Joe,

    You have to write two file one is the batch file and the other is the sql file.I will write the steps below I have also attached an example here

    1) create a batch file on NT orclrun.bat as follows

    set ORACLE_SID=orcl
    c:\oracle\ora81\bin\sqlplus system/manager @c:\first.sql

    2) create a first.sql file as follows

    spool c:\result.txt
    select * from cat;
    select user from dual;
    exec procedurename;
    spool off;
    exit;

    Iam creating a batch file in the first step and Iam invoking sqlplus over there and directly in turn executing first.sql from there and all the commands I have stored in first.sql.you can store all ur sql commands over there and I have inserted the spool command over there so that you can verify the result, and offcourse experiment on the batch file like passing parameters to the batch file for username,password that is batch files have parameters from %1 to %8 for example the sqlplus Invoking will look like this

    sqlplus %1/%2

    so when you execute the batch file you have to pass parameters like this from the command prompt

    orclrun.bat system manager

    so

    system will be %1

    manager will be %2

    Regards



    Santosh

  3. #3
    Join Date
    Dec 2008
    Posts
    6

    It works for

    I am not the person who posted the above question, but I used it to work on similar problem and it work just fine.
    I created two scripts as you said and everything ran just great.

    --File1:
    set ORACLE_SID=mysid
    D:\oracle\product\10.2.0\db_1\BIN\sqlplus.exe username/password@mspdev
    @d:\schedules\archive_data1.sql

    --File2:
    create or replace procedure update_archive is
    begin
    insert into archive_data2 select * from archive_data1 where seq_guid in
    (select seq_guid from archive_data1 where upper(SUBSTR(controlnumber, -4,4))='001W');
    delete from archive_data1 where rownum > 0;
    end update_archive;
    /
    exec update_archive;
    exit;


    --Thank you, Albert Zaza

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Wink Echo..

    Try this:
    Code:
    set ORACLE_SID=orcl
    ( 
      echo exec MyProcedure;
      echo exit
    )|sqlplus system/manager >MyProcedure.log
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Nov 2011
    Posts
    4

    Oracle_sid

    Hi,
    Thanks for posting this link. Its very useful and works fine for me.
    What does ORACLE_SID = orcl do?

    Abhijat.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Abhijat View Post
    What does ORACLE_SID = orcl do?
    As you may remember now, it sets the instance to which you are trying to connect - in that particular case the name of the instance was "orcl"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2011
    Posts
    4
    thanks a lot.

  8. #8
    Join Date
    Dec 2011
    Posts
    1
    Quote Originally Posted by PAVB View Post
    As you may remember now, it sets the instance to which you are trying to connect - in that particular case the name of the instance was "orcl"
    Thank you for this one of the first few threads i looked at had the question that i needed answered

  9. #9
    Join Date
    Mar 2014
    Posts
    1
    Quote Originally Posted by LKBrwn_DBA View Post
    Try this:
    Code:
    set ORACLE_SID=orcl
    ( 
      echo exec MyProcedure;
      echo exit
    )|sqlplus system/manager >MyProcedure.log
    Just an FYI: If you're trying to run sql commands that contain a closing bracket, you'll need to triple delimit them with ^

    [PRE]
    set ORACLE_SID=%2

    REM DATETIME1 is in the format MM/DD/YYYY HH24:MI:ss
    SET DATETIME1=%date:~-10% %time:~0,8%

    (
    echo insert into db_bu_status values ('%ORACLE_SID%', to_date('%DATETIME1%','mm/dd/yyyy hh24:mi:ss'^^^),'Running'^^^);
    echo commit;
    echo exit;
    ) | sqlplus system/manager@database > sqlplus_log.txt
    [/PRE]

Posting Permissions

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



Click Here to Expand Forum to Full Width