Oracle DBMS_SQLPLUS_SCRIPT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Undocumented: This package is dropped in 12.2.0.1 and will be dropped from the library with the next major release.
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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved