Dynamic query in Cursor for %rowtype

Question

I need to create a record type to contain the result set of the above query of

V_SQL VARCHAR2(200) := 'SELECT T1.'||record_id||',T1.'||card_no||',T2.TEST_CARD_NO, T1.'||type_cc||' FROM '|| table_name||' T1 
                INNER JOIN TEST T2 ON 
                T2.ID = T1.'||record_id;

So that I can use t1 C_BRNGB_EXTRACT%ROWTYPE (plus another column for t2.col4%TYPE inside l_BRNGB_EXTRACT) inside loop execute statement

CREATE OR REPLACE PROCEDURE "GCCPMAINT"."FUNCTION_CURSOR"(table_name VARCHAR2,record_id VARCHAR2,card_no VARCHAR2,type_cc VARCHAR2)
AS
limit_in NUMBER:=100;
V_SQL VARCHAR2(200) := 'SELECT T1.'||record_id||',T1.'||card_no||',T2.TEST_CARD_NO, T1.'||type_cc||' FROM '|| table_name||' T1 
            INNER JOIN TEST T2 ON 
            T2.ID = T1.'||record_id;

  TYPE BRNGB_EXTRACT IS REF CURSOR;
  C_BRNGB_EXTRACT BRNGB_EXTRACT;
  TYPE BRNGB_EXTRACT1 IS TABLE OF C_BRNGB_EXTRACT%ROWTYPE;
  l_BRNGB_EXTRACT BRNGB_EXTRACT1;    

BEGIN

OPEN C_BRNGB_EXTRACT FOR V_SQL;

   LOOP
       FETCH C_BRNGB_EXTRACT BULK COLLECT INTO l_BRNGB_EXTRACT LIMIT limit_in;
               EXIT WHEN l_BRNGB_EXTRACT.COUNT = 0;
                           FORALL indx IN 1 .. l_BRNGB_EXTRACT.COUNT
        EXECUTE IMMEDIATE 'UPDATE table_name SET CARD_NO=:1 WHERE CARD_NO=:2 AND RECORD_ID=:3' USING l_BRNGB_EXTRACT(indx).test_card_no,l_BRNGB_EXTRACT(indx).CARD_NO,l_BRNGB_EXTRACT(indx).RECORD_ID;
END LOOP;
CLOSE C_BRNGB_EXTRACT;
COMMIT;
END;

For a above stored procedure I'm getting the following error

PLS-00320: the declaration of the type of this expression is incomplete or malformed PL/SQL: Item ignored PLS-00597: expression 'L_BRNGB_EXTRACT' in the INTO list is of wrong type PL/SQL: SQL Statement ignored PLS-00487: Invalid reference to variable 'C_BRNGB_EXTRACT%ROWTYPE'

Please help me to solve this.

You can't... you need to declare the entire block dynamically if you do it this way; if you really need to do this then you're going to have to use DBMS_SQL. I'd advise against your current model if you're calling the procedure any significant volume of times as you're going to have to hard-parse the statement every time it's called, which could significantly slow down what you're doing. — Ben
The solution was (is probably) use normal SQL if you can, not dynamic. If you must use dynamic SQL you're going to have to use the DBMS_SQL package. I just didn't have the time 9 hours ago to go into that solution and I don't at the moment either I'm afraid, which is why I commented rather than writing out a full answer. — Ben

Sathish Sivanandam Sathish Sivanandam · Accepted Answer · 2015-02-06T12:36:25

i got the solution

 TYPE BRNGB_EXTRACT IS REF CURSOR;
  C_BRNGB_EXTRACT BRNGB_EXTRACT;
 -- TYPE BRNGB_EXTRACT1 IS TABLE OF C_BRNGB_EXTRACT%ROWTYPE;

  TYPE BRNGB_EXTRACT1 IS RECORD  (
  record_id varchar(30),
  card_no varchar(30),
  TEST_CARD_NO varchar(30)
  );
  type test_rec_arr is table of BRNGB_EXTRACT1 index by pls_integer;
  l_BRNGB_EXTRACT test_rec_arr;

Dynamic query in Cursor for %rowtype

1 Answers