Mega Code Archive

 

Categories / Oracle PLSQL / System Packages

 

Executing Queries and use DBMS_SQL COLUMN_VALUE to map value

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.