How to write a PL/SQL script that can run as Script in Oracle's SQLPlus as well as in SQLDeveloper?

user_423411 0
Viewed 317

I have developed a script using SQLDeveloper.

It's basic structure is:

File pl_sql.sql:

-- setting some options here, e.g. set linesize 32767 / set serveroutput on / etc.
declare
-- some variables declared here
begin
-- some SQL statements and PL/SQL code here
end;

This runs fine in SQLDeveloper. To run this in SQLPlus I had to write a wrapper like so:

File wrapper.sql:

@pl_sql.sql
/
quit

Without that '/' the script isn't executed but I get a prompt only. However, when I call this I always get an error at the first variable declaration after the declare. As I found out - using a lot of trial and error - I apparently can't have the options preceeding the "declare" in the called pl_sql.sql file. So I moved the options to the wrapper.sql like so:

File wrapper2.sql:

-- setting some options here, e.g. set linesize 32767 / set serveroutput on / etc.
@pl_sql2.sql
/
quit

and the script is exactly the same as the first but without the leading options:

File pl_sql2.sql:

declare
-- some variables declared here
begin
-- some SQL statements and PL/SQL code here
end;

But that variant of the PL/SQL script now of course doesn't work in SQLDeveloper any more. Or more precise: it works but doesn't generate any output (because the "set serveroutput on" and other options are missing now).

Is it really not possible to include these options somehow into the inner file and have the wrapper really be just be:

File wrapper.sql:

@pl_sql2.sql
/
quit

i.e. just the call of the file and the trailing '/' + quit?

Or even better: could one not call the inner .sql file WITH the options AND the code from slqplus directly and have it execute that script without requiring that stupid wrapper just to append that '/'?

Hope I could make myself clear...

sqlplus oracle-sqldeveloper
1 Answers

From what i understand, you have an sql file that you would like to run this in SQLPlus. While running the block in SqlPlus, it always needs the forward slash /, where by you are telling Sqlplus, i've finished my proc definition, now run it (As in the proc each line is terminated by a semi-colon, sqlplus has no way to identify when it should execute the statement ). Its perfrectly fine to have the set commands in ur plsql.sql file. I suspect you are getting errors because of any non printable characters in the sql file.

Below is a copy of my script, and it works fine.

$cat slqtest.sh

#!/bin/sh
sqlplus -s ${DBUSER}/${DBUSERPASS}@//${DBHOST}:${DBPORT}/${DBSERVICENAME}<<EOSQL
@sqltest.sql
/
EOSQL

$ cat sqltest.sql
set linesize 32767
set serveroutput on
declare
        l_string varchar2(100);
begin
        select 'This works' into l_string from dual;
        dbms_output.put_line('l_string '||l_string);
end;

$ ./slqtest.sh
l_string This works


PL/SQL procedure successfully completed.
user_13006830
Related