Is it possible to run a sqlplus file inside a stored procedure?

Question

Hi is it possible to run and execute a sqlplus file from inside a stored procedure ?

I have tried the following so far:

CREATE OR REPLACE PROCEDURE SCRIPTRUN
(

    p_orgid     IN VARCHAR2,
    p_axtype    IN VARCHAR2,
    P_option    IN VARCHAR2

) AS 

 runn VARCHAR2(200) := '@C:\Scripts\delete_ax\delete-ORG.sql '||  p_orgid ||' '||  p_axtype||' '|| P_option ; 


BEGIN

 execute IMMEDIATE runn;


END SCRIPTRUN;

Error Messages I'm getting :

ORA-00900: invalid SQL statement ORA-06512: at "DWOLFE.SCRIPTRUN", line 17 ORA-06512: at line 10

No - SQL*Plus is a client, and @ is a client command. You could possibly read and parse the file, and run each statement separately; but parsing isn't simple. You could possibly use a Java stored procedure to make a system call to run SQL*Plus but that isn't easy (or necessarily safe) either. (And the file has to be on the DB server, not a client machine.) How do you plan to call the procedure, and where from; and where will the arguments come from? There may be other approaches. One is to put whatever is in the script into a procedure; is there a reason you need an external file at all? — Alex Poole
@Alex exactly. Way to much trouble to try to make this call when you can easily put the same SQL plus logic into the procedure. Why do you think you need to call to sqlplus? — alexherm
@AlexPoole I created a SQL*PLUS file 'delete-ORG.sql' for spooling the output, ACCEPT prompts and some error handling which then call a stored procedure which performs the delete. The delete-org.sql also takes in positional parameters that get passed into the stored procedure that will be ultimately executed. — Amd Deeb
The developers where I work want to upload it to the production server but my local script will not run there using @C:\Scripts\delete_ax\delete-ORG.sql which is how I ran it on oracle developer — Amd Deeb
You certainly can't have user interaction through it. You really need to rethink your approach... — Alex Poole

Dmitry Demin Dmitry Demin · Accepted Answer · 2019-08-30T08:42:35

1.Create the directory EXT_TAB_DATA /opt/oracle/DEV/SAMPLE

 select * from dba_directories
    where directory_name='EXT_TAB_DATA'
    ==>
    OWNER          DIRECTORY_NAME          DIRECTORY_PATH                                                                                       
    --------- ------------------------ ----------------------------------- 
    SYS           EXT_TAB_DATA                /opt/oracle/DEV/SAMPLE

2.Create an external table sqlplus2 with sqlplus preprocessor.

DROP TABLE ext_sqlplus
/
CREATE TABLE ext_sqlplus
    (stdout                         VARCHAR2(256 CHAR))
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY  EXT_TAB_DATA
    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
   PREPROCESSOR EXT_TAB_DATA:'sqlplus2'
   BADFILE 'EXT_SQLPLUS_%a_%p.bad'
   NOLOGFILE
   SKIP 1
   FIELDS TERMINATED BY '\n' 
   MISSING FIELD VALUES ARE NULL
 )
   LOCATION (
    EXT_TAB_DATA:'..'
   )
  )
   REJECT LIMIT UNLIMITED
  NOPARALLEL
/

3. Create the shell sqlplus2 with a sqlplus call and sql file /opt/oracle/DEV/SAMPLE/select_sysdate_from_dual.sql.

 oracle@esmd:~/DEV/SAMPLE> more sqlplus2 
/oracle/product/11.2.0.3/db/bin/sqlplus /nolog  @/opt/oracle/DEV/SAMPLE/select_sysdate_from_dual.sql

4.Сreate a procedure write_sqlfile that writes queries to the sql file select_sysdate_from_dual.sql.

CREATE OR REPLACE 
PROCEDURE write_sqlfile (SQL_STRING IN VARCHAR2) IS
 OutFile  utl_file.file_type;


connect_string VARCHAR2(256):='connect system/manageresmd';
exit_string VARCHAR2(25):='exit';
file_name  VARCHAR2(256):='select_sysdate_from_dual.sql';
file_dir   VARCHAR2(25):='EXT_TAB_DATA'; 

BEGIN

  OutFile := utl_file.fopen(file_dir, file_name, 'w');
  utl_file.put_line(OutFile, connect_string, FALSE);
  utl_file.put_line(OutFile, SQL_STRING, FALSE);
  utl_file.put_line(OutFile, exit_string, FALSE);
  utl_file.fflush(OutFile);
  utl_file.fclose(OutFile);

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END write_sqlfile;
/

Example 1 Run the procedure write_sqlfile to write sql to the script.

begin
write_sqlfile(
'
select ''1-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''2-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''3-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''4-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''5-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
');

end;

Check the contents of the file in the file system.

oracle@esmd:~/DEV/SAMPLE> more select_sysdate_from_dual.sql
connect system/manageresmd

select '1-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '2-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '3-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '4-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '5-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;

exit
oracle@esmd:~/DEV/SAMPLE>

Execute a query to the external table and see the sqlplus output

SELECT *  FROM ext_sqlplus

Output

    SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 30 12:49:41 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.

'1-'||TO_CHAR(SYSDA
-------------------
1-30-AUG-2019 12:49


'2-'||TO_CHAR(SYSDA
-------------------
2-30-AUG-2019 12:49


'3-'||TO_CHAR(SYSDA
-------------------
3-30-AUG-2019 12:49


'4-'||TO_CHAR(SYSDA
-------------------
4-30-AUG-2019 12:49


'5-'||TO_CHAR(SYSDA
-------------------
5-30-AUG-2019 12:49

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Example 2 Run the procedure write_sqlfile to write host command OS to the script.

 begin
    write_sqlfile(
    '
    host  cd /opt ; /bin/ls -l
    ');


    end;

Execute a query to the external table and see the sqlplus output

SELECT *  FROM ext_sqlplus;

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 30 14:21:27 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
total 20
drwxr-xr-x 3 root   root     4096 2012-12-12 10:13 app
drwxr-xr-x 3 root   root     4096 2014-11-10 11:04 IBM
drwxr-xr-x 3 root   root     4096 2012-04-24 09:58 kde3
drwxr-xr-x 8 oracle oinstall 4096 2019-08-13 09:47 oracle
drwxr-xr-x 3 root   root     4096 2012-04-25 11:41 ORCLfmap

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Is it possible to run a sqlplus file inside a stored procedure?

1 Answers