Home » SQL & PL/SQL » SQL & PL/SQL » executing another sql script from within a script
executing another sql script from within a script [message #8323] Fri, 08 August 2003 15:41 Go to next message
Dan Wood
Messages: 11
Registered: January 2003
Junior Member
My first script reads in a dat file and uses the multiple parameters to select records into a table against which I then wish to generate a report. I was unable to succesfully incorporate the report generation within the first script. I then thought it would be easy enough to execute the report script from within the first script.
Here are the statements I am trying to use:

l_markup := '"'||'html on'||'"';
execute immediate
'!sqlplus -s -m '||l_markup||' id/pswd @adhoc8.sql > /u/netcam/stp_reports/work/DASpec_LINKSET.html';

I get ORA-00900: invalid SQL statement on almost every variation I've tried.

I've checked the substitutions. I can run the command at the unix prompt and generate the desired html report. What simple thing am I missing?
Re: executing another sql script from within a script [message #8325 is a reply to message #8323] Fri, 08 August 2003 17:07 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Is your first script a .sql script? If so, then you are using the wrong commands. You would not need to re-start SQL*Plus or re-connect. You would just need:

SET MARKUP HTML ON
@adhoc8.sql -- followed by whatever parameters

Execute immediate is intended for executing sql commands dynamically from a pl/sql block, but not for executing a .sql script. The HOST command is used for executing operating system commands from SQL*Plus. You can also use java to execute an external procedure from pl/sql.
Previous Topic: DBMS_OUTPUT in a for update cursor..
Next Topic: How to Insert,update and select values on a nested Table
Goto Forum:
  


Current Time: Fri Mar 29 03:08:34 CDT 2024