PL/SQL Ref cursors problem

[xrodge]

Hi all,

I am having a little problem with ref cursors that I hope someone can help me with.

I am trying to use them to generically audit any table on the system.
That means, the procedure will never know what the table structure or columns involved are until it is called.
This is my code portions with notes as to what I am trying to do with the problem noted below.

      type field_type is table of varchar2(32)
      index by binary_integer;

      audit_field field_type;

      idx binary_integer := 0;
      iterator binary_integer := 0;

      type rc is REF CURSOR;

      select_field_curs rc;

...


      OPEN data_child_curs;

      idx := 0;

-- This builds up an array of column names that I would like to audit.

      LOOP

          fetch data_child_curs into data_child_rec;
          exit when data_child_curs%NOTFOUND;

          idx := idx + 1;

          audit_field(idx) := data_child_rec.audited_field;

      END LOOP;

--This value is the number of fields that will be audited.

      no_of_fields := idx;

      col := ' ';

      idx := 1;

-- This loop builds up a string of all of the columns I would like to select
-- ie if 3 fields, say  'FIELD1, FIELD2, FIELD3'

      LOOP

          EXIT when idx > no_of_fields;

          t_field := audit_field(idx);

          IF idx = 1 THEN
          
              col := t_field;
              
          ELSE
              
              col := col || ', ' || t_field;
          
          END IF;
          
          idx := idx + 1;

      END LOOP;

-- This is where I use this newly built up list of column names to construct my select statement

      open select_field_curs for 
          'SELECT '             || 
          col                   ||
          ' FROM '              || 
          t_tablename           || 
          ' WHERE projectid = ' ||
          p_projectid;

-- Now is where I have my problem.
-- I need to select the columns into fields, so that I can use them.
-- This example shows how much code there is for 4 fields, there can a lot more potentially.

          IF no_of_fields = 1 
          THEN
          
              fetch select_field_curs 
              INTO t_field_1;
              EXIT when select_field_curs%NOTFOUND;             
          
          ELSE 
            IF no_of_fields = 2
            THEN
          
                fetch select_field_curs 
                INTO t_field_1, t_field_2;
                EXIT when select_field_curs%NOTFOUND;
              
            ELSE 
              IF no_of_fields = 3
              THEN
          
                  fetch select_field_curs 
                  INTO t_field_1, t_field_2, t_field_3;
                  EXIT when select_field_curs%NOTFOUND;
              
              ELSE 
                IF no_of_fields = 4
                THEN
          
                    fetch select_field_curs 
                    INTO t_field_1, t_field_2, t_field_3, t_field_4;
                    EXIT when select_field_curs%NOTFOUND;
                    
                END IF;
                
              END IF;
              
            END IF;
          
          END IF;

I tried declaring a rowtype on the ref cursor, as below, but sql didn't like it:

      type rc is REF CURSOR;

      select_field_curs rc;
      select_field_rec select_field_curs%ROWTYPE;

I know the syntax is supposed to be something like:

      type rc is ref cursor return EMPLOYEES%rowtype;

but I want a generic procedure that will not need EMPLOYEES hardcoded.
I need to either be able to use a dynamic %ROWTYPE with my ref cursor or find some way of modifying my the INTO portion of my fetch statement so it can cope with any number of fields.

Any help will be much appreciated!

 

[Chao]

I don't quite understand what you're trying to do. However, if what you want is to parse through all of the tables in your database and for each table in the database extract the column names in each table, then you are better off using the dictionary. Hope this information is helpful.

i.e. Select * from user_tables;
--> list tables in the given user database

Select column_name
from user_tab_columns
where table_name = v_table_name;
--> list the column names defined in the table

NOTE: the data in the dictionary are all in upper case, so remember to provide upper case in your criteria.

 

[sem]

Ref cursor without return clause has no rowtype, it's just a handler, suitable for any kind of cursor. So, without knowlege of the returned result structure you can not fetch it. Though, you may use dynamic sql to CREATE the code to be excuted. In this code SELECT statement will be fixed and cursor will have a type.

code2execute:='declare cursor c is '||select_statement||'; r c%rowtype; begin open c; fetch c into r; while c%found loop ...

execute immediate code2execute;

If you do know what you need, and the work you're willing to do does no depend on the real table (select) structure, this may work. But do you REALLY need such kind of flexibility ?

 

[jee]

Here is a suggestion that might work for you (or might not). It is really an elaboration on CHAO's response. For my projects, I always write a SQL*PLUS script file that will create/compile audit triggers on the fly for the project tables. It extracts information from the dictionary views as to what tables/columns are defined for my project schema and creates a standard audit trigger for each one. Then any time I add a new table or alter the column structure of an existing table, I simply run this script to create/recreate all of the audit triggers.

Sometimes I want to do variable stuff - for example sometimes I want to exclude certain tables and/or certain columns from the audit. I use the table and/or column comment fields to encode flags that my script can read and make decisions on. For instance, if I want to exclude a table from audit, I might start the comment field for that table with '<NOAUDIT>' or something like that. My script will look at all table comments and will not generate an audit trigger for any table that has this type of comment.

This method is not quite as flexible as what you are trying to do, but it gets the job done.