execute sql script from pl/sql

When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of bugs…

Another approach is to use sqlplus.

Wait! calling sqlplus from plsql? Well… why not. For instance via external job

  1. create a table with sql scripts
  2. create table t(id number primary key, text clob);
    insert into t(id, text) values (
      42, 'update emp set sal=sal*2 /* XMas gift */;'
  3. create a shell script, /tmp/ddl, that spool and executes T.text content
  4. #!/bin/ksh
    export ORACLE_HOME
    $ORACLE_HOME/bin/sqlplus -s -L scott/tiger <
  5. create a UNIX credential
  6. begin 
  7. create and run an external job
  8. begin
        job_name             => 'J',
        job_type             => 'EXECUTABLE',
        number_of_arguments  => 1,
        job_action           => '/tmp/ddl',
        credential_name      => 'C');
        'J', 1, '42');

CREATE EXTERNAL JOB privilege is powerfull and your DBA won't like it 😉

1 thought on “execute sql script from pl/sql

  1. Pingback: Restricted sqlplus and sql_script – Laurent Schneider

Comments are closed.