Oracle batch script issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle batch script issue

  1. #1
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Try this...
    Code:
    @echo on
    FOR /f "tokens=1" %%i in (C:\Jc\joblist.txt) do (
    echo %%i
    sqlplus -s w951gxd@DWTST01/mcgi05 <<-ENDSQL 
    SET heading off
    SET pagesize 80
    SET MARKUP HTML ON SPOOL ON HEAD " - STYLE TYPE=’TEXT/CSS’>"
    SET ECHO OFF
    SPOOL "C:\Jc\Reports\%%i.htm"
    select REPORT_TEXT from TB_JOB_REPORT where jobname = '%%i' ;
    SPOOL OFF
    SET MARKUP HTML OFF
    SET ECHO ON
    ENDSQL
    )
    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.

  2. #2
    Join Date
    Oct 2010
    Posts
    2

    Oracle batch script issue

    Hi,

    i created a one batch script , inside it calls some SQL functions but when i execute that script am not able to connect sqlplus , SQl*Plus commands are not working ..

    please look my below script and give some suggestions???

    @echo on
    FOR /f "tokens=1" %%i in (C:\Jc\joblist.txt) do (
    echo %%i
    sqlplus connect w951gxd@DWTST01/mcgi05
    SET heading off
    SET pagesize 80
    SET MARKUP HTML ON SPOOL ON HEAD "Job Comparison Report - STYLE TYPE=’TEXT/CSS’>"
    SET ECHO OFF
    SPOOL "C:\Jc\Reports\%%i.htm"
    select REPORT_TEXT from TB_JOB_REPORT where jobname = '%%i' ;
    SPOOL OFF
    SET MARKUP HTML OFF
    SET ECHO ON

    )

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

    Thumbs down WinDoze stuff?

    Try this:
    Code:
    @echo on
    FOR /f "tokens=1" %%i in (C:\Jc\joblist.txt) do (
      echo %%i
      set jobid=%%i
      call :sqlplus
    )
    :sqlplus
    (
    echo set head off pages 80 echo off
    echo SET MARKUP HTML ON SPOOL ON HEAD " - STYLE TYPE=’TEXT/CSS’>"
    echo SPOOL C:\Jc\Reports\%jobid%.htm"
    echo SELECT report_text FROM tb_job_report WHERE jobname = '%jobid%' ;
    echo SPOOL OFF
    echo SET MARKUP HTML OFF
    echo exit
    ) | sqlplus w951gxd@DWTST01/mcgi05
    :endproc
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Oct 2010
    Posts
    2
    Hi LKBrwn_DBA,

    i tried with that code, its running fine but i cant see any reports in the directory C:\Jc\Reports\

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

    Talking Typo...

    Try removing the trailing quote (") from:
    Code:
    ...etc...
    echo SPOOL C:\Jc\Reports\%jobid%.htm"
    ...etc...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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