12c 12cR2

Restricted sqlplus and sql_script

Yesterday I wrote about execute sql script from plsql. Connor then posted a 12c new feature that I overviewed

If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus

If you run

sqlplus -L -s -R 3 scott/tiger@db01

lot’s of OS-command are disabled

SQL> get /etc/passwd
SP2-0738: Restricted command "get" not available
SQL> spool /etc/oratab
SP2-0738: Restricted command "spool" not available
SQL> host reboot
SP2-0738: Restricted command "host" not available

There is also a Product User Profile. I never saw any customer using this. I doubt it is very safe.

You could disable some commands

SQL> insert into system.PRODUCT_USER_PROFILE

Which prevents SCOTT from doing rollabck

SQL> rollback;
SP2-0544: Command "rollback" disabled in Product User Profile

but it doesn’t resist Rock & Roll

SQL> roll & rock
Rollback complete.

If you have 12c, go have a look at the doc for SQL_SCRIPT jobs

dba plsql sqlplus

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 😉


ANNOUNCEMENT: 12cR2 documentation is available

The doc is available. Check new features and stay tuned.

Okay, just 12 for today : HIGH compressed index, case insensitive database with bound collation, partitioned external tables, AL32UTF8 default for new database, listagg overflow, VALIDATE_CONVERSION, approx_percentile, json_exists, flashback pluggable database, the SHARING clause, RAC streched clusters site definition and INACTIVE_ACCOUNT_TIME in profile.

Some I like more than others 🙂

Okay, one more : SQLPLUS HISTORY !