Compiling PLSQL from command prompt (not inside sqlplus)

Question

Hi all,

is there any way compiling plsql from command prompt not by opening sqlplus and writing the command or @filename?

We want to import the output to a file and parse it for a code review tool we are working on

Thanks...

Answer 1

Dont think so, see this presentation by Pete Finnigan for some compiler internals. It lists an ANTLR grammar file that you could use.

I have also seen mentions about Scala combinator parser implementations for PL/SQL.

Answer 2

Not sure I quite understand what you mean, it sounds like you just want to capture the output of the execution, but can't tell if you actually mean that you want to avoid SQL*Plus completely. From my first reading this is as simple as:

sqlplus -s user/password @filename > outputfile

... but that makes me think I've missed something important.

Answer 3

If you just need to output the source - USER_SOURCE.

I do a lot of dynamic generation of packages, and I've written code that extracts, adds additional code, and recompiles the package.

At it's simplest you just need to wrap up your CREATE PACKAGE command as dynamic SQL.

EXECUTE IMMEDIATE 
    'CREATE OR REPLACE PACKAGE myPackageName AS '||pPackageSource;

However, that means your source is just one string. I use the older DBMS_SQL.PARSE method that can accept an array of VARCHAR2 lines (dbms_sql.varchar2s).

Example - code to pull the source from user_source into a varchar 2s, then recompile the same package via dbms_sql.

DECLARE
    lCid INTEGER;
    lError INTEGER;
    lSource dbms_sql.varchar2s;

    FUNCTION fSource(
        pName IN VARCHAR2,
        pType IN VARCHAR2)
    RETURN dbms_sql.varchar2s
    IS
        CURSOR cSource IS
        SELECT  RTRIM(text,CHR(10))
        FROM    user_source
        WHERE   name = pName
        AND     type = pType
        ORDER BY line;
        lSource pp_type.gtyp_ArrayOfSource;
    BEGIN
       OPEN cSource;
       FETCH cSourcee BULK COLLECT INTO lSource;
       CLOSE cSource;
       RETURN lSource;
     END fSource;
BEGIN
    lSource := fSource(pName => 'myPackageName',pType => 'PACKAGE');
    /* Add CREATE or REPLACE to the start of the source */
    lSource(1) := 'CREATE OR REPLACE '||lSource(1);
-- Cannot use EXECUTE IMMEDIATE as this is an ARRAY
    lCid := dbms_sql.OPEN_CURSOR;
    --
    dbms_sql.parse(
       c    => lCid ,
       statement => lSource,
       lb   => 1,
       ub   => p_source.count,
       lfflg    => true,
       language_flag => dbms_sql.v7
   );
   dbms_sql.close_cursor (lCid);
END;

This could easily be changed to extract the source from a file, HTTP service, etc - anything that the pl/sql code / database can reach.

The actual code includes validation, checks the result is valid, emails any errors (from user_errors) matched against the lines in user_source, etc.

Security

The code needs to be running with privileges to install a package - this may mean installing the package against a user with higher privileges than the executing user.

If you are doing this, you need to be very careful about what interfaces are exposed to 'normal' DB users - i.e. you do not want to create anything that lets a normal user extract or modify the package source or privileges.

My approach was creating a low-level package (hidden from normal users) for all the service functions (get source, install, dynamic execute, etc) and then a higher level package that exposed a limited set of specific actions to regular users - in your case this would be something like exposing a 'reviewCode' procedure.