Oracle DBMS_SQLPLUS_SCRIPT Demos

Oracle DBMS_SQLPLUS_SCRIPT Version 11.2.0.3

General Information

AUTHID

CURRENT_USER

Dependencies

DBMS_SQLPLUS_SCRIPT_LIB

First Available

11.1

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/prvtsms.plb

PARSE

Undocumented

dbms_sqlplus_script.parse(src IN CLOB, stmts OUT stmt_table);

set serveroutput on

DECLARE
srcclob CLOB;
stmt_lst dbms_sqlplus_script.stmt_table;
BEGIN
srcclob := CAST('BEGIN
INSERT INTO WORKSTATIONS VALUES (11,3,20037,'''',''Y'',61.137,-149.9395,''10.128.16.105'');
INSERT INTO WORKSTATIONS VALUES (11,5,20044,'''',''Y'',61.137,-149.9395,''10.128.32.129'');
COMMIT;
INSERT INTO WORKSTATIONS VALUES (11,9,20048,'''',''Y'',61.137,-149.9395,''10.128.32.169'');
INSERT INTO WORKSTATIONS VALUES (12,1,20051,''15706'',''Y'',32.75604,-117.1201,'''');
INSERT INTO WORKSTATIONS VALUES (12,102,20057,'''',''Y'',32.75677,117.1241,''10.129.112.25'');
INSERT INTO WORKSTATIONS VALUE (12,103,20058,'''',''Y'',32.75662,117.124,''10.129.112.33'');
COMMIT;
END;' AS CLOB);

dbms_sqlplus_script.parse(srcclob, stmts=>stmt_lst);
dbms_output.put_line(stmt_lst.COUNT);

IF stmt_lst.EXISTS(stmt_lst.FIRST) THEN
    dbms_output.put_line(RPAD('OFFSET',10) || ' ' ||
                         RPAD('LENGTH',10) || ' ' ||
                         RPAD('TYPE',10)   || ' ' ||
                         RPAD('STMTID',10) || ' ' ||
                         RPAD('BIND OFFSET', 12) || ' ' ||
                         RPAD('BIND LEN', 10));

    dbms_output.put_line(RPAD('-', 10, '-') || ' ' ||
                         RPAD('-', 10, '-') || ' ' ||
                         RPAD('-', 10, '-') || ' ' ||
                         RPAD('-', 10, '-') || ' ' ||
                         RPAD('-', 12, '-') || ' ' ||
                         RPAD('-', 10, '-'));

    FOR i IN stmt_lst.FIRST .. stmt_lst.LAST LOOP
      dbms_output.put_line(LPAD(stmt_lst(i).offset,10) || ' ' ||
                           LPAD(stmt_lst(i).length,10) || ' ' ||
                           LPAD(stmt_lst(i).type,10)   || ' ' ||
                           LPAD(stmt_lst(i).stmtId,10));

      IF stmt_lst(i).binds.EXISTS(stmt_lst(i).binds.FIRST) THEN
        FOR j IN stmt_lst(i).binds.FIRST .. stmt_lst(i).binds.LAST LOOP
          dbms_output.put_line(LPAD('.',10, '.') || ' ' ||
                               LPAD('.',10, '.') || ' ' ||
                               LPAD('.',10, '.') || ' ' ||
                               LPAD('.',10, '.') || ' ' ||
                               LPAD(stmt_lst(i).binds(j).offset,12) || ' ' ||
                               LPAD(stmt_lst(i).binds(j).length,10));
        END LOOP; -- binds LOOP
      END IF;     -- binds exist
    END LOOP;     -- stmt_lst
END IF;         -- stmt_lst has elements
END;
/

set serveroutput on

VAR sql_stmt_clob CLOB

BEGIN
-- assign SQL statement to CLOB
:sql_stmt_clob := CAST('SELECT * FROM airplanes WHERE program_id BETWEEN :B AND :CD' AS CLOB);
END;
/

DECLARE
stmt_lst dbms_sqlplus_script.stmt_table;
BEGIN
dbms_sqlplus_script.parse(src => :sql_stmt_clob, stmts => stmt_lst);

IF stmt_lst.EXISTS(stmt_lst.FIRST) THEN
    dbms_output.put_line(RPAD('OFFSET',10)||' '||
                         RPAD('LENGTH',10)||' '||
                         RPAD('TYPE',10)||' '||
                         RPAD('STMTID',10)||' '||
                         RPAD('BIND OFFSET', 12)||' '||
                         RPAD('BIND LEN', 10));
                         dbms_output.put_line(RPAD('-', 10, '-')||' '||
                         RPAD('-', 10, '-')||' '||
                         RPAD('-', 10, '-')||' '||
                         RPAD('-', 10, '-')||' '||
                         RPAD('-', 12, '-')||' '||
                         RPAD('-', 10, '-'));

    FOR i IN stmt_lst.FIRST .. stmt_lst.LAST LOOP
      dbms_output.put_line(LPAD(stmt_lst(i).offset,10)||' '||
                           LPAD(stmt_lst(i).length,10)||' '||
                           LPAD(stmt_lst(i).type,10)||' '||
                           LPAD(stmt_lst(i).stmtId,10));

      IF stmt_lst(i).binds.EXISTS(stmt_lst(i).binds.FIRST) THEN
        FOR j IN stmt_lst(i).binds.FIRST .. stmt_lst(i).binds.LAST LOOP
          dbms_output.put_line(LPAD('.',10, '.')||' '||
                               LPAD('.',10, '.')||' '||
                               LPAD('.',10, '.')||' '||
                               LPAD('.',10, '.')||' '||
                               LPAD(stmt_lst(i).binds(j).offset,12)||' '||
                               LPAD(stmt_lst(i).binds(j).length,10));

        END LOOP; -- binds LOOP
      END IF;     -- binds exist
    END LOOP;     -- stmt_lst
END IF;         -- stmt_lst has elements
END;
/

Citation

With thanks to John Awald at Areva T&D
for writing the loops and display

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: