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
    ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
    export ORACLE_HOME
    $ORACLE_HOME/bin/sqlplus -s -L scott/tiger <<EOF
      set hea off lin 2000 pages 0 trims on
      spo /tmp/$1.sql
      select text from t where id=$1;
      spo off
      @/tmp/$1
    EOF
    

  5. create a UNIX credential
  6. begin 
      dbms_scheduler.create_credential(
        'C','USER01','PW');
    end;
    /
    

  7. create and run an external job
  8. begin
      DBMS_SCHEDULER.CREATE_JOB(
        job_name             => 'J',
        job_type             => 'EXECUTABLE',
        number_of_arguments  => 1,
        job_action           => '/tmp/ddl',
        credential_name      => 'C');
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        'J', 1, '42');
      DBMS_SCHEDULER.run_job('J');
    end;
    /
    

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

One Reply to “execute sql script from pl/sql”

Leave a Reply