How to dynamically create a variable with the data type of table?

Question

In the following query select_string can return any number of columns and rows with any possible data types.

Execute immediate 'select_string' into v_table_variable;

For example select_string could be 'select name, last name from student' or 'select date, subject, address , phone from booking' and so on.

Does anyone has any idea how I can define the v_table_variable so that the execute immediate is run as I want?! I intend to make a loop afterwards to read the values from this variable.

Many thanks!

You realistically probably can't. You might be able to dynamically build an entire PL/SQL block (including the subsequent loop) but resorting to dynamic PL/SQL is generally a quick way to drive yourself (and anyone that has to maintain your code) insane. It's possible that you want to use dbms_sql rather than execute immediate but it's pretty rare that you'd really want to write code that is that generic. What is the underlying problem that you are trying to solve? — Justin Cave
– Justin Cave, Commented Oct 10, 2014 at 18:07
Thanks for your quick response! I want to build a function to get the name of a table and a value which is one of its PK and then delete all corresponding rows in the child tables, and in the child of the child and so on. I am not allowed to alter my tables with the cascade . — Dax
– Dax, Commented Oct 10, 2014 at 18:15
Are you sure that you really need a completely generic function? Rather than, say, a delete_foo procedure that deletes a foo by first deleting data from all the tables that are children of foo? You could build a single procedure that read from a bunch of data dictionary tables and created a bunch of dynamic SQL. But that's an order of magnitude more complex than a simple, procedural option. Are you sure that the additional complexity is really worth it? — Justin Cave
– Justin Cave, Commented Oct 10, 2014 at 18:40
So you have some tables with foreign key constraints but without ON DELETE CASCADE and you want to "simulate" the cascade in PL/SQL? — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 10, 2014 at 21:00

Wernfried Domscheit · Accepted Answer · 2014-10-10 19:23:37Z

2

The "full blown" dynmamic version would be this one (not tested):

DECLARE  
  v_stmt_str       VARCHAR2(200);
  v_cur            INTEGER;
  v_rows_processed INTEGER;

  col_cnt     INTEGER;
  rec_tab     DBMS_SQL.DESC_TAB;
  rec         DBMS_SQL.DESC_REC;

  num_var NUMBER;
  string_var VARCHAR2(4000);
  date_var DATE;
  -- .. some more variables if needed 
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR; -- open cursor 
  v_stmt_str := 'SELECT whatever from ....';
  DBMS_SQL.PARSE(v_cur, v_stmt_str, DBMS_SQL.NATIVE); 
  DBMS_SQL.DESCRIBE_COLUMNS(v_cur, col_cnt, rec_tab);  

  FOR c in 1..col_cnt LOOP
     rec := rec_tab(c);
     IF rec.col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
         DBMS_SQL.DEFINE_COLUMN(v_cur, c, num_var); 
     ELSIF rec.col_type = DBMS_TYPES.TYPECODE_VARCHAR2 THEN
         DBMS_SQL.DEFINE_COLUMN(v_cur, c, string_var, rec.col_max_len); 
     ELSIF rec.col_type = DBMS_TYPES.TYPECODE_DATE THEN
         DBMS_SQL.DEFINE_COLUMN(v_cur, c, date_var); 
     -- .. some more data types if needed
     END IF;
  END LOOP;

  -- Execute
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur);  
  LOOP 
    -- Fetch a row 
    IF DBMS_SQL.FETCH_ROWS(v_cur) > 0 THEN 
      FOR c in 1..col_cnt LOOP
        rec := rec_tab(c);
        IF rec.col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
            DBMS_SQL.COLUMN_VALUE(v_cur, c, num_var); 
        ELSIF rec.col_type = DBMS_TYPES.TYPECODE_VARCHAR2 THEN
            DBMS_SQL.COLUMN_VALUE(v_cur, c, string_var); 
        ELSIF rec.col_type = DBMS_TYPES.TYPECODE_DATE THEN
            DBMS_SQL.COLUMN_VALUE(v_cur, c, date_var); 
        -- .. some more data types if needed
        END IF;
      END LOOP;
      -- Process: do something with num_var or string_var or date_var values
    ELSE
      EXIT; 
    END IF; 
  END LOOP; 
  DBMS_SQL.CLOSE_CURSOR(v_cur); -- close cursor
END;
/

But as already stated in comments, check your requirements if you really need it such dynamic - probably not.

Check Oracle documentation Coding Dynamic SQL and DBMS_SQL for other examples. There are various methods providing different "levels" of dynamic.

answered Oct 10, 2014 at 19:23

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dax Over a year ago

Thank you for your great tip :-). I was not familiar with DBMS_SQL. Just one thing, shouldn't "do something with num_var or ..." Be before the latest end loop ?! If our query returns for example 4 columns with data type number, then I guess, in this way we would have just the last value in num_var, right?! Just want to make sure I have got it right. This was a great help to me, thank you so much :-)

Wernfried Domscheit Over a year ago

YEs, if your table contains several NUMBER type column you have to process them inside the loop. Or you can put them into a nested table and process the nested table after the loop.

Collectives™ on Stack Overflow

How to dynamically create a variable with the data type of table?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related