Oracle DBMS_SQLPLUS_SCRIPT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Undocumented
AUTHID CURRENT_USER
Dependencies
DBMS_SQLPLUS_SCRIPT_LIB    
Documented No
First Available 11.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtsms.plb
 
PARSE
Undocumented

With thanks to John Awald at Alstom
for writing the loops and display
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;
/

Related Topics
DBMS_SQL
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved