Execute procedure from Windows batch file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Execute procedure from Windows batch file

Hybrid View

  1. #1
    Join Date
    Jan 2006
    Posts
    39

    Execute procedure from Windows batch file

    I tried to call a procedure (my_procedure) from windows batch file. It is doable. The code in batch file is like this:

    sqlldr user/passwd@dbname.com control=C:\load\load.ctl

    set oracle_sid=dbname.com

    set MyDir=C:\load

    sqlplus -s user/passwd@dbname.com @%MyDir%\my_procedure.sql

    exit

    Then I saved file as finename.bat or filename.cmd. I click it to run, it will run sqlloader first to load flat file into table. Then it will loginto
    SQLPLUS envirnment with pointed database, then it just hang on there. I know SQLPLUS needs adding ' / ' at the end or underneath to make procedure run. I just type '/' at prompt and hit enter key, the procedure will be executed successfully.

    The problem is: if I add ';' at the end of my_procedure.sql or add ' / ' underneath into the batch file. It will result in Windows system can not open my_procedure.sql file with SP2-0310 error. If don't have ';' or ' / ', SQLPLUS will not execute my_procedure. So please help me to solve this. How can I add either ';' or ' / ' to the code in batch file and let Windows system can transport into SQLPLUS envirnment for the execution. Thanks a lot.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    do you have a / at the bottom of my_procedure.sql

  3. #3
    Join Date
    Jan 2006
    Posts
    39
    Please read my post. I said if I put "/" at the bottom of my_procedure.sql, the windows will treat it as part of the file path, the file can not be identified and compile into SQLPLUS envirnment for execution. If don't have "/" at the bottom of my_procedure.sql, the script will not be executed. This is my problem and the reason I post here for help.

    At current batch file and sqlplus envirnment, I manually type "/" at the bottom, my_procedure.sql will be run successfully. Any idea?

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

    Talking The end.

    Quote Originally Posted by traveller
    Please read my post. I said if I put "/" at the bottom of my_procedure.sql, the windows will treat it as part of the file path, ...
    Why would WinDoze care about the contents of your file or even interpret them?

    You need to put the '/' after an SQL statement, but if you have a procedure (like exec MyProc() you need the ';' after the procedure.

    Also, you need to add 'exit' at the end of your script.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Jan 2006
    Posts
    39
    Thanks for your input. It is right and will work.
    Last edited by traveller; 12-15-2008 at 06:10 PM.

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