| General Information |
| Purpose |
Undocumented |
| Source |
$ORACLE_HOME/rdbms/admin/prvtscrp.plb |
| Data Types |
DBMS_SQLPLUS_SCRIPT.STMT_RECORD
DBMS_SQLPLUS_SCRIPT.BIND_RECORD
DBMS_SQLPLUS_SCRIPT.STMT_TABLE
DBMS_SQLPLUS_SCRIPT.BIND_TABLE |
| Dependencies |
|
| Security Model |
Owned by SYS with no granted privileges |
| |
| 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;
/ |
|
