Oracle PL/SQL/System Packages/dbms sql
Содержание
- 1 Call dbms_sql.describe_columns2 to get info for a column
- 2 Calling a function which uses dynamic SQL from within an SQL statement.
- 3 DBMS_SQL.BIND_VARIABLE_CHAR
- 4 DBMS_SQL.NATIVE;
- 5 DBMS_SQL package: dynamic SQL
- 6 DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
- 7 dbms_sql.varchar2_table type variable
- 8 drop the supplied table using dynamic SQL.
- 9 Dynamic select statement and row fetch
- 10 Dynamic sql statement with variable binding
- 11 Execute PL/SQL block
- 12 Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
- 13 Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
- 14 First DBMS_SQL Example
- 15 illustrates a DDL statement which is built dynamically from the procedure parameters.
- 16 Illustrates the interaction of roles and dynamic SQL.
- 17 illustrate the importance of setting out_value_size.
- 18 Non-Query DML and DDL Statements
- 19 Pass a query statement to a stored procedure
- 20 Pass where clause to a stored procedure
- 21 Use dbms_sql.bind_array to bind array to a cursor
- 22 Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
- 23 Use dbms_sql.bind_variable to bind variable
- 24 Use dbms_sql.open_cursor create a cursor
- 25 Use dbms_sql.parse to bind a select statement to a cursor
- 26 Use dbms_sql to process query, cursor and value
- 27 Use DBMS_SQL with the RETURNING clause.
Call dbms_sql.describe_columns2 to get info for a column
<source lang="sql">
SQL> SQL> declare
2 l_cursor number := dbms_sql.open_cursor; 3 l_ignore number; 4 l_desc dbms_sql.desc_tab2; 5 l_cnt number; 6 begin 7 dbms_sql.parse( l_cursor, 8 "select to_char( sysdate, ""DD-MON-YYYY"" ) || " || 9 "to_char( sysdate, ""HH24:MI:SS"" ) " || 10 "from dual", 11 dbms_sql.native ); 12 13 dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc ); 14 15 for i in 1 .. l_cnt loop 16 dbms_output.put_line( "Column " || i || " is "" || l_desc(i).col_name || """ ); 17 end loop; 18 19 dbms_sql.close_cursor( l_cursor ); 20 end; 21 /
Column 1 is "TO_CHAR(SYSDATE,"DD-MON-YYYY")||TO_CHAR(SYSDATE,"HH24:MI:SS")" PL/SQL procedure successfully completed. SQL> --
</source>
Calling a function which uses dynamic SQL from within an SQL statement.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION DynamicSysdate
2 RETURN DATE AS 3 4 v_SQLStatement VARCHAR2(100) := "SELECT SYSDATE FROM dual"; 5 v_CursorID INTEGER; 6 v_ReturnVal DATE; 7 v_Dummy INTEGER; 8 BEGIN 9 v_CursorID := DBMS_SQL.OPEN_CURSOR; 10 DBMS_SQL.PARSE(v_CursorID, v_SQLStatement, DBMS_SQL.NATIVE); 11 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_ReturnVal); 12 v_Dummy := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID); 13 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_ReturnVal); 14 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 15 RETURN v_ReturnVal; 16 EXCEPTION 17 WHEN OTHERS THEN 18 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 19 RAISE; 20 END DynamicSysdate; 21 /
Function created. SQL> show errors No errors. SQL> SQL> SELECT DynamicSysdate FROM dual; DYNAMICSYSDATE
Jun 19, 2008 20:39:02 1 row selected. SQL> SQL>
</source>
DBMS_SQL.BIND_VARIABLE_CHAR
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE updateSession(
2 p_Department IN session.department%TYPE, 3 p_NewCredits IN session.num_credits%TYPE, 4 p_RowsUpdated OUT INTEGER) AS 5 6 v_CursorID INTEGER; 7 v_UpdateStmt VARCHAR2(100); 8 BEGIN 9 v_CursorID := DBMS_SQL.OPEN_CURSOR; 10 11 v_UpdateStmt := 12 "UPDATE session 13 SET num_credits = :nc 14 WHERE department = :dept"; 15 16 DBMS_SQL.PARSE(v_CursorID, v_UpdateStmt, DBMS_SQL.NATIVE); 17 18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":nc", p_NewCredits); 19 20 DBMS_SQL.BIND_VARIABLE_CHAR(v_CursorID, ":dept", p_Department); 21 22 p_RowsUpdated := DBMS_SQL.EXECUTE(v_CursorID); 23 24 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 25 EXCEPTION 26 WHEN OTHERS THEN 27 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 28 RAISE; 29 END updateSession; 30 /
Procedure created. SQL> SQL> drop table session; Table dropped. SQL>
</source>
DBMS_SQL.NATIVE;
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE CreateDynamicTables(
2 p_Method IN VARCHAR2) 3 AUTHID CURRENT_USER AS 4 v_CreateString1 VARCHAR2(100) := 5 "CREATE TABLE dbms_sql_table (f1 NUMBER)"; 6 v_CreateString2 VARCHAR2(100) := 7 "CREATE TABLE native_table (f1 NUMBER)"; 8 v_Dummy INTEGER; 9 v_CursorID INTEGER; 10 BEGIN 11 IF p_Method = "DBMS_SQL" THEN 12 v_CursorID := DBMS_SQL.OPEN_CURSOR; 13 DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE); 14 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 15 ELSE 16 EXECUTE IMMEDIATE v_CreateString2; 17 END IF; 18 END CreateDynamicTables; 19 /
Procedure created. SQL> SQL> exec CreateDynamicTables("DBMS_SQL") PL/SQL procedure successfully completed. SQL> exec CreateDynamicTables("native") PL/SQL procedure successfully completed. SQL> SQL> desc dbms_sql_table
Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------ F1 NUMBER
SQL> desc native_table
Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------ F1 NUMBER
SQL> SQL> DROP TABLE dbms_sql_table; Table dropped. SQL> DROP TABLE native_table; Table dropped. SQL>
</source>
DBMS_SQL package: dynamic SQL
<source lang="sql">
SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE updateSession(
2 p_Department IN session.department%TYPE, 3 p_NewCredits IN session.num_credits%TYPE, 4 p_RowsUpdated OUT INTEGER) AS 5 6 v_CursorID INTEGER; 7 v_UpdateStmt VARCHAR2(100); 8 BEGIN 9 v_CursorID := DBMS_SQL.OPEN_CURSOR; 10 11 v_UpdateStmt := 12 "UPDATE session 13 SET num_credits = :nc 14 WHERE department = :dept"; 15 16 DBMS_SQL.PARSE(v_CursorID, v_UpdateStmt, DBMS_SQL.NATIVE); 17 18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":nc", p_NewCredits); 19 20 DBMS_SQL.BIND_VARIABLE_CHAR(v_CursorID, ":dept", p_Department); 21 22 p_RowsUpdated := DBMS_SQL.EXECUTE(v_CursorID); 23 24 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 25 EXCEPTION 26 WHEN OTHERS THEN 27 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 28 RAISE; 29 END updateSession; 30 /
Procedure created. SQL> SQL> drop table session; Table dropped. SQL>
</source>
DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 v_CursorID NUMBER;
3 v_CreateTableString VARCHAR2(500);
4 v_NUMRows INTEGER;
5
6 BEGIN
7 v_CursorID := DBMS_SQL.OPEN_CURSOR;
8 v_CreateTableString := "CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50))";
9
10 DBMS_SQL.PARSE(v_CursorID,v_CreateTableString,DBMS_SQL.V7);
11 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
12
13 EXCEPTION
14 WHEN OTHERS THEN
15 IF SQLCODE != -955 THEN -- 955 is error that table exists
16 RAISE;
17 ELSE
18 DBMS_OUTPUT.PUT_LINE("Table Already Exists!");
19 END IF;
20 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
21 END;
22 /
PL/SQL procedure successfully completed. SQL> SQL> drop table mytable; Table dropped. SQL> SQL> --
</source>
dbms_sql.varchar2_table type variable
<source lang="sql">
SQL> SQL> SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
Table created. SQL> SQL> declare
2 l_owner dbms_sql.varchar2_table; 3 l_object_name dbms_sql.varchar2_table; 4 l_object_type dbms_sql.varchar2_table; 5 l_created dbms_sql.varchar2_table; 6 7 cursor c is 8 select owner, object_name, object_type, created 9 from myTable 10 order by created DESC; 11 begin 12 select owner, object_name, object_type, created 13 bulk collect into l_owner, l_object_name, l_object_type, l_created from ( select owner, object_name, object_type, created from myTable order by created DESC ) 14 where ROWNUM <= 10; 15 16 open c; 17 fetch c bulk collect 18 into l_owner, l_object_name, l_object_type, l_created 19 limit 10; 20 close c; 21 end; 22 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
drop the supplied table using dynamic SQL.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
2 sqlString VARCHAR2(100); 3 v_Cursor BINARY_INTEGER; 4 v_ReturnCode BINARY_INTEGER; 5 BEGIN 6 sqlString := "DROP TABLE " || p_Table; 7 8 v_Cursor := DBMS_SQL.OPEN_CURSOR; 9 10 DBMS_SQL.PARSE(v_Cursor, sqlString, DBMS_SQL.NATIVE); 11 v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor); 12 13 DBMS_SQL.CLOSE_CURSOR(v_Cursor); 14 END DropTable; 15 /
Procedure created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
2 sqlString VARCHAR2(100); 3 BEGIN 4 sqlString := "DROP TABLE " || p_Table; 5 6 EXECUTE IMMEDIATE sqlString; 7 END DropTable; 8 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL>
</source>
Dynamic select statement and row fetch
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50)); Table created. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 v_CursorID NUMBER; 3 v_SelectRecords VARCHAR2(500); 4 v_NUMRows INTEGER; 5 v_MyNum INTEGER; 6 v_MyText VARCHAR2(50); 7 8 BEGIN 9 v_CursorID := DBMS_SQL.OPEN_CURSOR; 10 v_SelectRecords := "SELECT * from MyTable"; 11 DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7); 12 DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum); 13 DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50); 14 15 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID); 16 LOOP 17 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN 18 EXIT; 19 END IF; 20 21 DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum); 22 DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText); 23 24 DBMS_OUTPUT.PUT_LINE(v_MyNum || " " || v_MyText); 25 26 END LOOP; 27 28 EXCEPTION 29 WHEN OTHERS THEN 30 RAISE; 31 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 32 END; 33 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table mytable; Table dropped. SQL> --
</source>
Dynamic sql statement with variable binding
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50)); Table created. SQL> SQL> DECLARE
2 v_CursorID NUMBER;
3 v_InsertRecords VARCHAR2(500);
4 v_NUMRows INTEGER;
5
6 BEGIN
7 v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
8 v_InsertRecords := "INSERT INTO MyTable(MyRow,MyDesc)VALUES (:mynum,:mytext)"; -- Write SQL to insert records
9
10 DBMS_SQL.PARSE(v_CursorID,v_InsertRecords,DBMS_SQL.V7);
11 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",1);
12 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","One");
13 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
14 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: " || v_NUMRows);
15
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",2);
17 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","Two");
18 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
19 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
20
21 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",3);
22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","Three");
23 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
24 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
25
26 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",4);
27 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","Four");
28 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
29 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
30
31 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
32 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 RAISE;
37
38 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
39 COMMIT;
40 END;
41 /
The number of records just processed is: 1 The number of records just processed is: 1 The number of records just processed is: 1 The number of records just processed is: 1 The number of records just processed is: 1 PL/SQL procedure successfully completed. SQL> SQL> drop table mytable; Table dropped. SQL> SQL> SQL> --
</source>
Execute PL/SQL block
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50)); Table created. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 v_CursorID NUMBER; 3 v_MatchRecord VARCHAR2(500); 4 v_NUMRows INTEGER; 5 v_MyNum INTEGER; 6 v_MyText VARCHAR2(50); 7 8 BEGIN 9 v_CursorID := DBMS_SQL.OPEN_CURSOR; 10 v_MatchRecord := "BEGIN 11 SELECT MyRow,MyDesc 12 INTO :MyRow, :MyText FROM MyTable 13 WHERE MyRow = 2; 14 END;"; 15 16 DBMS_SQL.PARSE(v_CursorID,v_MatchRecord,DBMS_SQL.V7); 17 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":MyRow",v_MyNum); 18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":MyText",v_MyText,50); 19 20 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID); 21 DBMS_SQL.VARIABLE_VALUE(v_CursorId,":MyRow",v_MyNum); 22 DBMS_SQL.VARIABLE_VALUE(v_CursorId,":MyText",v_MyText); 23 DBMS_OUTPUT.PUT_LINE(v_MyNum || " " || v_MyText); 24 EXCEPTION 25 WHEN OTHERS THEN 26 RAISE; 27 28 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 29 END; 30 /
DECLARE
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 26
SQL> SQL> SQL> drop table mytable; Table dropped. SQL> --
</source>
Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE DynamicPLSQL (
2 p_ID IN lecturer.ID%TYPE) IS 3 4 v_CursorID INTEGER; 5 v_BlockStr VARCHAR2(500); 6 myFirstName lecturer.first_name%TYPE; 7 v_LastName lecturer.last_name%TYPE; 8 v_Dummy INTEGER; 9 10 BEGIN 11 v_CursorID := DBMS_SQL.OPEN_CURSOR; 12 13 v_BlockStr := 14 "BEGIN 15 SELECT first_name, last_name 16 INTO :first_name, :last_name 17 FROM lecturer 18 WHERE ID = :ID; 19 END;"; 20 21 DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7); 22 23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":first_name", myFirstName, 30); 24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":last_name", v_LastName, 30); 25 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":ID", p_ID); 26 27 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); 28 29 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":first_name", myFirstName); 30 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":last_name", v_LastName); 31 32 INSERT INTO MyTable (num_col, char_col) 33 VALUES (p_ID, myFirstName || " " || v_LastName); 34 35 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 36 37 COMMIT; 38 EXCEPTION 39 WHEN OTHERS THEN 40 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 41 RAISE; 42 END DynamicPLSQL; 43 /
Procedure created. SQL> SQL> drop table mytable; Table dropped. SQL> SQL> drop table lecturer; Table dropped.
</source>
Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
<source lang="sql">
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE DynamicQuery (
2 p_Major1 IN lecturer.major%TYPE DEFAULT NULL, 3 p_Major2 IN lecturer.major%TYPE DEFAULT NULL) AS 4 5 v_CursorID INTEGER; 6 v_SelectStmt VARCHAR2(500); 7 myFirstName lecturer.first_name%TYPE; 8 v_LastName lecturer.last_name%TYPE; 9 v_Major lecturer.major%TYPE; 10 v_Dummy INTEGER; 11 12 BEGIN 13 v_CursorID := DBMS_SQL.OPEN_CURSOR; 14 15 v_SelectStmt := "SELECT first_name, last_name, major 16 FROM lecturer 17 WHERE major IN (:m1, :m2) 18 ORDER BY major, last_name"; 19 20 DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7); 21 22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":m1", p_Major1); 23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":m2", p_Major2); 24 25 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, myFirstName, 20); 26 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20); 27 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Major, 30); 28 29 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); 30 31 LOOP 32 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN 33 EXIT; 34 END IF; 35 36 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, myFirstName); 37 DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName); 38 DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Major); 39 40 INSERT INTO MyTable (char_col)VALUES (myFirstName || " " || v_LastName || " is a " ||v_Major || " major."); 41 END LOOP; 42 43 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 44 45 COMMIT; 46 EXCEPTION 47 WHEN OTHERS THEN 48 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 49 RAISE; 50 END DynamicQuery; 51 /
Procedure created. SQL> SQL> show error No errors. SQL> SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> drop table mytable; Table dropped.
</source>
First DBMS_SQL Example
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
2 p_Description IN VARCHAR2) IS 3 4 v_Cursor NUMBER; 5 v_CreateString VARCHAR2(100); 6 v_DropString VARCHAR2(100); 7 v_NumRows INTEGER; 8 BEGIN 9 v_Cursor := DBMS_SQL.OPEN_CURSOR; 10 11 v_DropString := "DROP TABLE MyTable"; 12 13 BEGIN 14 DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.V7); 15 v_NumRows := DBMS_SQL.EXECUTE(v_Cursor); 16 EXCEPTION 17 WHEN OTHERS THEN 18 IF SQLCODE != -942 THEN 19 RAISE; 20 END IF; 21 END; 22 v_CreateString := "CREATE TABLE MyTable " || p_Description; 23 DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.V7); 24 v_NumRows := DBMS_SQL.EXECUTE(v_Cursor); 25 26 DBMS_SQL.CLOSE_CURSOR(v_Cursor); 27 EXCEPTION 28 WHEN OTHERS THEN 29 DBMS_SQL.CLOSE_CURSOR(v_Cursor); 30 RAISE; 31 END RecreateTempTable; 32 /
Procedure created. SQL>
</source>
illustrates a DDL statement which is built dynamically from the procedure parameters.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE DynamicSequence(
2 p_SequenceName IN VARCHAR2, 3 p_IncrementBy IN NUMBER := NULL, 4 p_StartWith IN NUMBER := NULL, 5 p_MaxValue IN NUMBER := NULL, 6 p_MinValue IN NUMBER := NULL, 7 p_Cycle IN VARCHAR2 := NULL, 8 p_Cache IN NUMBER := NULL) AS 9 10 v_CreateStmt VARCHAR2(200); 11 v_CursorID INTEGER; 12 BEGIN 13 v_CreateStmt := "CREATE SEQUENCE " || p_SequenceName; 14 15 IF p_IncrementBy IS NOT NULL THEN 16 v_CreateStmt := 17 v_CreateStmt || " INCREMENT BY " || p_IncrementBy; 18 END IF; 19 20 IF p_StartWith IS NOT NULL THEN 21 v_CreateStmt := 22 v_CreateStmt || " START WITH " || p_StartWith; 23 END IF; 24 25 IF p_MaxValue IS NOT NULL THEN 26 v_CreateStmt := 27 v_CreateStmt || " MAXVALUE " || p_MaxValue; 28 END IF; 29 30 IF p_MinValue IS NOT NULL THEN 31 v_CreateStmt := 32 v_CreateStmt || " MINVALUE " || p_MinValue; 33 END IF; 34 35 IF p_Cycle IS NOT NULL THEN 36 v_CreateStmt := 37 v_CreateStmt || " " || p_Cycle || " "; 38 END IF; 39 40 IF p_Cache IS NOT NULL THEN 41 v_CreateStmt := 42 v_CreateStmt || " CACHE " || p_Cache; 43 END IF; 44 45 -- And now execute it. 46 v_CursorID := DBMS_SQL.OPEN_CURSOR; 47 DBMS_SQL.PARSE(v_CursorID, v_CreateStmt, DBMS_SQL.NATIVE); 48 END DynamicSequence; 49 /
Procedure created. SQL> SQL> SQL>
</source>
Illustrates the interaction of roles and dynamic SQL.
<source lang="sql">
SQL> SQL> DECLARE
2 v_CreateString1 VARCHAR2(100) := 3 "CREATE TABLE dbms_sql_table (f1 NUMBER)"; 4 v_CreateString2 VARCHAR2(100) := 5 "CREATE TABLE native_table (f1 NUMBER)"; 6 v_Dummy INTEGER; 7 v_CursorID INTEGER; 8 BEGIN 9 v_CursorID := DBMS_SQL.OPEN_CURSOR; 10 DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE); 11 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 12 13 EXECUTE IMMEDIATE v_CreateString2; 14 END; 15 /
PL/SQL procedure successfully completed. SQL> SQL> desc dbms_sql_table
Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------ F1 NUMBER
SQL> desc native_table
Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------ F1 NUMBER
SQL> SQL> DROP TABLE dbms_sql_table; Table dropped. SQL> DROP TABLE native_table; Table dropped. SQL>
</source>
illustrate the importance of setting out_value_size.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE DynamicPLSQL (
2 p_ID IN lecturer.ID%TYPE) IS
3
4 v_CursorID INTEGER;
5 v_BlockStr VARCHAR2(500);
6 myFirstName lecturer.first_name%TYPE;
7 v_LastName lecturer.last_name%TYPE;
8 v_Dummy INTEGER;
9
10 BEGIN
11 v_CursorID := DBMS_SQL.OPEN_CURSOR;
12
13 v_BlockStr :=
14 "BEGIN
15 SELECT first_name, last_name
16 INTO :first_name, :last_name
17 FROM lecturer
18 WHERE ID = :ID;
19 END;";
20
21 DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.NATIVE);
22
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":first_name", myFirstName);
24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":last_name", v_LastName);
25 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":ID", p_ID);
26
27 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
28
29 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":first_name", myFirstName);
30 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":last_name", v_LastName);
31
32 DBMS_OUTPUT.PUT("ID: " || p_ID || " ");
33 DBMS_OUTPUT.PUT_LINE(myFirstName || " " || v_LastName);
34
35 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
36 EXCEPTION
37 WHEN OTHERS THEN
38 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
39 RAISE;
40 END DynamicPLSQL;
41 /
Procedure created. SQL> SQL> REM This will raise ORA-6502. SQL> exec DynamicPLSQL(10010) BEGIN DynamicPLSQL(10010); END;
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "sqle.DYNAMICPLSQL", line 39 ORA-06512: at line 1
SQL> SQL> drop table lecturer; Table dropped.
</source>
Non-Query DML and DDL Statements
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE DeleteMajor(
2 p_Major IN lecturer.major%TYPE, 3 p_RowsDeleted OUT INTEGER) AS 4 5 v_CursorID INTEGER; 6 v_DeleteStmt VARCHAR2(100); 7 BEGIN 8 v_CursorID := DBMS_SQL.OPEN_CURSOR; 9 10 v_DeleteStmt := "DELETE FROM lecturer WHERE major = :m"; 11 12 DBMS_SQL.PARSE(v_CursorID, v_DeleteStmt, DBMS_SQL.V7); 13 14 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":m", p_Major); 15 16 p_RowsDeleted := DBMS_SQL.EXECUTE(v_CursorID); 17 18 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 19 EXCEPTION 20 WHEN OTHERS THEN 21 DBMS_SQL.CLOSE_CURSOR(v_CursorID); 22 RAISE; 23 END DeleteMajor; 24 /
Procedure created. SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
Pass a query statement to a stored procedure
<source lang="sql">
SQL> SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace
2 procedure print_table( p_query in varchar2 ) 3 AUTHID CURRENT_USER is 4 l_theCursor integer default dbms_sql.open_cursor; 5 l_columnValue varchar2(4000); 6 l_status integer; 7 l_descTbl dbms_sql.desc_tab; 8 l_colCnt number; 9 begin 10 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); 11 dbms_sql.describe_columns 12 ( l_theCursor, l_colCnt, l_descTbl ); 13 14 for i in 1 .. l_colCnt loop 15 dbms_sql.define_column 16 (l_theCursor, i, l_columnValue, 4000); 17 end loop; 18 19 l_status := dbms_sql.execute(l_theCursor); 20 21 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop 22 for i in 1 .. l_colCnt loop 23 dbms_sql.column_value 24 ( l_theCursor, i, l_columnValue ); 25 dbms_output.put_line 26 ( rpad( l_descTbl(i).col_name, 30 ) 27 || ": " || 28 substr( l_columnValue, 1, 200 ) ); 29 end loop; 30 end loop; 31 32 end print_table; 33 /
Procedure created. SQL> SQL> exec print_table( "select * from emp where empno like ""778%""" ); EMPNO : 7782 ENAME : CLARK JOB : MANAGER MGR : 7839 HIREDATE : 09-JUN-1981 00:00:00 SAL : 2450 COMM : DEPTNO : 10 EMPNO : 7788 ENAME : SCOTT JOB : ANALYST MGR : 7566 HIREDATE : 09-DEC-1982 00:00:00 SAL : 3000 COMM : DEPTNO : 20 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> --
</source>
Pass where clause to a stored procedure
<source lang="sql">
SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE showemps (
2 where_in IN VARCHAR2 := NULL 3 ) 4 IS 5 cur INTEGER := DBMS_SQL.OPEN_CURSOR; 6 fdbk INTEGER; 7 8 last_name_tab DBMS_SQL.VARCHAR2_TABLE; 9 hire_date_tab DBMS_SQL.DATE_TABLE; 10 BEGIN 11 DBMS_SQL.PARSE (cur, 12 "SELECT last_name, hire_date FROM employees 13 WHERE " || NVL (where_in, "1=1") || 14 "ORDER BY hire_date", 15 DBMS_SQL.NATIVE); 16 17 DBMS_SQL.DEFINE_ARRAY (cur, 1, last_name_tab, 100, 1); 18 DBMS_SQL.DEFINE_ARRAY (cur, 2, hire_date_tab, 100, 1); 19 20 fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur); 21 22 DBMS_SQL.COLUMN_VALUE (cur, 1, last_name_tab); 23 DBMS_SQL.COLUMN_VALUE (cur, 2, hire_date_tab); 24 25 FOR rowind IN last_name_tab.FIRST .. last_name_tab.LAST 26 LOOP 27 DBMS_OUTPUT.PUT_LINE ( 28 last_name_tab(rowind) || 29 " was hired on " || 30 hire_date_tab(rowind)); 31 END LOOP; 32 33 DBMS_SQL.CLOSE_CURSOR (cur); 34 END; 35 /
Procedure created. SQL> SQL> drop table employees; Table dropped. SQL> SQL>
</source>
Use dbms_sql.bind_array to bind array to a cursor
<source lang="sql">
SQL> SQL> SQL> create table foo (
2 a integer, 3 b dec( 9, 2 ), 4 c character varying( 30 ), 5 d national char( 3 ) 6 ) 7 /
Table created. SQL> declare
2 l_cursor number := dbms_sql.open_cursor; 3 l_ignore number; 4 l_num dbms_sql.number_table; 5 l_var dbms_sql.varchar2_table; 6 begin 7 dbms_sql.parse( l_cursor,"insert into foo(b,c) values ( :n, :c )",dbms_sql.native ); 8 l_num(1) := 3; 9 l_num(2) := 4; 10 l_var(1) := "Tom"; 11 l_var(2) := "Joel"; 12 dbms_sql.bind_array( l_cursor, ":N", l_num ); 13 dbms_sql.bind_array( l_cursor, ":C", l_var ); 14 l_ignore := dbms_sql.execute( l_cursor ); 15 dbms_sql.close_cursor( l_cursor ); 16 end; 17 /
PL/SQL procedure successfully completed. SQL> select * from foo;
A B C D
---------- ------------------------------ ---
3 Tom
4 Joel
2 rows selected. SQL> SQL> drop table foo; Table dropped. SQL> SQL> --
</source>
Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
<source lang="sql">
SQL> SQL> SQL> create table foo (
2 a integer, 3 b dec( 9, 2 ), 4 c character varying( 30 ), 5 d national char( 3 ) 6 ) 7 /
SQL> SQL> declare
2 l_cursor number := dbms_sql.open_cursor; 3 l_ignore number; 4 begin 5 dbms_sql.parse( l_cursor,"insert into foo values ( :n, :c )",dbms_sql.native ); 6 dbms_sql.bind_variable( l_cursor, ":N", 1 ); 7 dbms_sql.bind_variable( l_cursor, ":C", "Chris" ); 8 l_ignore := dbms_sql.execute( l_cursor ); 9 dbms_sql.bind_variable( l_cursor, ":N", 2 ); 10 dbms_sql.bind_variable( l_cursor, ":C", "Sean" ); 11 l_ignore := dbms_sql.execute( l_cursor ); 12 dbms_sql.close_cursor( l_cursor ); 13 end; 14 /
PL/SQL procedure successfully completed. SQL> select * from foo;
N
V
1
Chris
2
Sean
2 rows selected. SQL> SQL> drop table foo; Table dropped. SQL> --
</source>
Use dbms_sql.bind_variable to bind variable
<source lang="sql">
SQL> SQL> create table myTable( pid primary key )
2 organization index 3 as select rownum from all_objects 4 where rownum <=0;
Table created. SQL> SQL> SQL> SQL> SQL> SQL> SQL> create or replace procedure binding is
2 cursorValue number; 3 4 returnValue number; 5 returnValue1 number; 6 begin 7 cursorValue := dbms_sql.open_cursor; 8 dbms_sql.parse(cursorValue, "select pid from myTable "|| "where pid = :b1", dbms_sql.native); 9 for i in 1 ..0 loop 10 dbms_sql.bind_variable(cursorValue,":b1",i); 11 returnValue := dbms_sql.execute(cursorValue); 12 returnValue1 := dbms_sql.fetch_rows(cursorValue); 13 end loop; 14 dbms_sql.close_cursor(cursorValue); 15 end; 16 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> drop table myTable; Table dropped. SQL> SQL> SQL>
</source>
Use dbms_sql.open_cursor create a cursor
<source lang="sql">
SQL> SQL> declare
2 l_cursor number := dbms_sql.open_cursor; 3 l_stmt dbms_sql.varchar2s; 4 begin 5 l_stmt(3) := "junk"; 6 l_stmt(4) := "create table foo"; 7 l_stmt(5) := "( n numb"; 8 l_stmt(6) := "er, v varchar2(100)"; 9 l_stmt(7) := ")"; 10 l_stmt(8) := "more junk"; 11 dbms_sql.parse( l_cursor, 12 l_stmt, 13 4, 14 7, 15 FALSE, 16 dbms_sql.native ); 17 dbms_sql.close_cursor( l_cursor ); 18 end; 19 /
PL/SQL procedure successfully completed. SQL> --
</source>
Use dbms_sql.parse to bind a select statement to a cursor
<source lang="sql">
SQL> SQL> SQL> create table foo (
2 a integer, 3 b dec( 9, 2 ), 4 c character varying( 30 ), 5 d national char( 3 ) 6 ) 7 /
Table created. SQL> declare
2 l_cursor number := dbms_sql.open_cursor; 3 l_num dbms_sql.number_table; 4 l_var dbms_sql.varchar2_table; 5 l_ignore number; 6 l_cnt number; 7 begin 8 dbms_sql.parse( l_cursor,"select b, c from foo",dbms_sql.native ); 9 dbms_sql.define_array( l_cursor, 1, l_num, 100, 1 ); 10 dbms_sql.define_array( l_cursor, 2, l_var, 100, 1 ); 11 12 l_ignore := dbms_sql.execute( l_cursor ); 13 loop 14 dbms_output.put_line( "About to attempt a fetch of 100 rows." ); 15 l_cnt := dbms_sql.fetch_rows( l_cursor ); 16 dbms_output.put_line( "Fetched " || l_cnt || " rows." ); 17 18 exit when l_cnt = 0; 19 20 dbms_sql.column_value( l_cursor, 1, l_num ); 21 dbms_sql.column_value( l_cursor, 2, l_var ); 22 23 for i in 1 .. l_num.count loop 24 dbms_output.put_line( "N = " || l_num(i) || " V = " || l_var(i) ); 25 end loop; 26 27 exit when l_cnt < 100; 28 29 end loop; 30 dbms_sql.close_cursor( l_cursor ); 31 end; 32 /
About to attempt a fetch of 100 rows. Fetched 0 rows. PL/SQL procedure successfully completed. SQL> SQL> drop table foo; Table dropped. SQL> --
</source>
Use dbms_sql to process query, cursor and value
<source lang="sql">
SQL> create table myTable ( x number ) pctfree 0; Table created. SQL> SQL> insert into myTable select rownum from all_objects where rownum < 100;
SQL> SQL> create or replace procedure ARRAY_PROCESS is
2 s integer := dbms_sql.open_cursor; 3 n1 dbms_sql.number_table; 4 d number; 5 c number; 6 BEGIN 7 dbms_sql.parse(s,"select * from myTable", DBMS_SQL.native); 8 dbms_sql.define_array(s,1,n1,500,1); 9 d := dbms_sql.execute(s); 10 loop 11 c := DBMS_SQL.FETCH_ROWS(s); 12 DBMS_SQL.COLUMN_VALUE(s, 1, n1); 13 exit when c < 500; 14 end loop; 15 16 DBMS_SQL.CLOSE_CURSOR(s); 17 END; 18 /
Procedure created. SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Use DBMS_SQL with the RETURNING clause.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> set serveroutput on SQL> DECLARE
2 v_RowidString VARCHAR2(100);
3 v_NumVal NUMBER := 1;
4 v_StringVal VARCHAR2(20) := "rowid";
5 BEGIN
6 INSERT INTO MyTable VALUES (v_NumVal, v_StringVal)
7 RETURNING ROWID INTO v_RowidString;
8 DBMS_OUTPUT.PUT_LINE("ROWID of new row is: " || v_RowidString);
9 END;
10 /
ROWID of new row is: AAADlMAABAAAKV6AAA PL/SQL procedure successfully completed. SQL> SQL> DECLARE
2 v_RowidString VARCHAR2(100);
3 v_CursorID INTEGER;
4 v_NumVal NUMBER := 1;
5 v_StringVal VARCHAR2(20) := "rowid";
6 sqlString VARCHAR2(100);
7 v_ReturnCode NUMBER;
8 BEGIN
9 sqlString :=
10 "INSERT INTO MyTable VALUES (:n, :c) " ||
11 "RETURNING ROWID INTO :r";
12 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13 DBMS_SQL.PARSE(v_CursorID, sqlString, DBMS_SQL.NATIVE);
14 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":n", v_NumVal);
15 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":c", v_StringVal);
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":r", v_RowidString, 100);
17 v_ReturnCode := DBMS_SQL.EXECUTE(v_CursorID);
18 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":r", v_RowidString);
19 DBMS_OUTPUT.PUT_LINE("ROWID of new row is: "|| v_RowidString);
20 END;
21 /
ROWID of new row is: AAADlMAABAAAKV6AAB PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
