CODEWITHSUNDEEP
oracle-databaseplsql
JochenDB
JochenDB

Reputation: 587

Dynamically build record type to fetch into

I'd like to collect rows from a dynamic sql query into a pl/sql row. The problem is the query is built dynamically and I'm not sure what all columns will be there. The result set is based on a table in my DB (t1 in the example) + one additional column (sid in the example). It seems impossible to create this record type dynamically and fetch the records from the dynamic sql into it.

PLS-00597: expression 't_row' in the INTO list is of wrong type

Is it possible to do this in any other way?

Please note the code has been simplified for convenient reading:

DECLARE
  v_query_string VARCHAR2(32767 BYTE);

  TYPE ref_cursor IS REF CURSOR;
  cur ref_cursor; 

  TYPE rec_type IS RECORD (
    sid number,  
    struct t1%ROWTYPE);

  t_row rec_type;

BEGIN

  --build v_query_string -> select sid,t1.a,t1.b,t1.c,t1.some_column_from_t1 from t1

  OPEN cur FOR v_query_string;
  LOOP
    FETCH cur INTO t_row;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(t_row.some_column_from_t1);
  END LOOP;
  CLOSE cur;

END;

Upvotes: 0

Views: 2595

Answers (3)

furman87
furman87

Reputation: 968

You can always use a dynamic cursor, which is very easy to read and doesn't require extra data types or variables declared. In your sample SQL, you could do this:

BEGIN
  FOR cv IN (select sid, t1.a, t1.b, t1.c, t1.some_column_from_t1 from t1)
  LOOP
    DBMS_OUTPUT.PUT_LINE(cv.some_column_from_t1);
  END LOOP;
END;

Note that this does not work with SQL built on the fly and stored in a variable, but you would be able to access any column in the table with a simple SELECT * FROM t1;

Upvotes: 0

Frank
Frank

Reputation: 881

 TYPE rec_type IS RECORD (
    sid number,  
    somefield t1.somefield%TYPE
    ...);

  t_row rec_type;

and you can access to data in this way :

t_row.sid etc...

Upvotes: 0

fg78nc
fg78nc

Reputation: 5232

You can use DMBS_SQL.DESCRIBE_COLUMNS function.

Example

   DECLARE
       cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
       cols DBMS_SQL.DESC_TAB;
       col_num PLS_INTEGER;
    BEGIN
       DBMS_SQL.PARSE
          (cur, 'SELECT * FROM table', DBMS_SQL.NATIVE);
       // Retrieving column information
       DBMS_SQL.DESCRIBE_COLUMNS (cur, col_num, cols);
      ...
    END;

You can get more infor from this link

Upvotes: 1

Related Questions