How can I dynamically recover columnName_N from a cursor, N being a number incremented in a LOOP?

Question

thanks for taking time to read and maybe answer my question! Note that I am a beginner and should not be considered a pro but i did search for the answer without finding it , maybe due to my uncommon problem and/or lack of knowledge about it.

I have the following problem at work, i know it is not really supposed to happen, but here it is, on my desk... :

I have a table (conv_temp1) with the following columns:

ID No Sigle Info COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4 COLUMN_5 .. COLUMN_50

I have this cursor:

 CURSOR c_minis IS
        SELECT *
        FROM conv_temp1;

I am trying to do something as the following:

FOR v_rsrec IN c_minis LOOP
   l_column_i := 1;
   dbms_output.put_line('----- Beginning - v_rsrec.id ----');
     FOR boucle IN REVERSE 1.. 50 LOOP

       --this is my problem, i am trying to acces a cursor column "dynamically"
       EXECUTE IMMEDIATE 'v_declared_varchar2 := COLUMN_'|| l_column_i ||';';  

       IF v_declared_varchar2 IS NOT NULL THEN
         dbms_output.put_line('I am doing something with the information!');
         --(Dont worry, YES I am re-structuring it in a new table...)


       END IF;
       l_column_i := l_column_i + 1;
     END LOOP;
   dbms_output.put_line('-----c end - v_rsrec.id ----');
END LOOP;

Is there a way to perform such a thing as accessing a different column (only the number changes in the name of those) depending on where i am in my iterations? ex, if I have already done 10 iterations, i will recover information from COLUMN_11 in my cursor.

It might be feasible with `dbms_sql`, but if you're looking for the 'first' not-null column, wouldn't it be simpler to include them all as named columns in a `coalesce()` call? Or maybe unpivot, if you need all the not-null values? What is your actual goal? — Alex Poole, Jan 06 '15 at 20:08
Yes, you can access specific columns. Look at DBMS_SQL package. — OldProgrammer, Jan 06 '15 at 20:09
My goal is to create another table with one row per COLUMN_N when COLUMN_N is not null, note that I have multiple other informations going with COLUMN_N , But my main question here is: How to recover information from a cursor by using concatenation in the call, such as: 'Select cursor.column_'|| var_containing_a_number || '' into another_variable and then be able to use another_variable to do some treatment — Raphaël, Jan 06 '15 at 20:14
You can't do that because of the scope - the cursor isn't visible to the dynamic SQL. The `dbms_sql` package is the only route for this, as far as I'm aware. But converting columns to rows is unpivoting, so why reinvent the wheel? — Alex Poole, Jan 06 '15 at 20:18
@AlexPoole Another way to achieve what I want would be something like: `code` IF cur.column_1 is not null do something with column_1 end if; IF cur.column_2 is not null do something with column_2 end if; IF cur.column_3 is not null do something with column_3 end if; `code` Which could be acceptable if I only had 3 columns, but I have many and want to acces them in a loop, note that i have many columns like that: name_1 somethingelse_1 otherthing_1 name_2 somethingelse_2 otherthing_2 ... etc — Raphaël, Jan 06 '15 at 20:25
Ill look into the package, ill tell you if I find soemthing, thanks — Raphaël, Jan 06 '15 at 20:31
True, I meant the only route to do it dynamically. Otherwise you're hard-coding the column names in some form (whether in an unpivot, coalesce, or if conditions). You could generate the code from `user_tab_columns` once, as a kind of middle ground, I suppose. Less typing anyway, if that's the issue. — Alex Poole, Jan 06 '15 at 20:43

score 1 · Accepted Answer · answered Jan 06 '15 at 22:48

A better solution would be to normalize the table. Break it into two tables as:

CREATE TABLE CONV_TEMP_HEADER
  (ID_NO        NUMBER
     CONSTRAINT PK_CONV_TEMP_HEADER
       PRIMARY KEY
       USING INDEX,
   SIGLE_INFO   VARCHAR2(100));  -- or whatever

CREATE TABLE CONV_TEMP_DETAIL
  (ID_DETAIL    NUMBER,
   ID_NO        NUMBER
     CONSTRAINT CONV_TEMP_DETAIL_FK1
       REFERENCES CONV_TEMP_HEADER(ID_NO)
         ON DELETE CASCADE,
   IDX          NUMBER,
   COLUMN_VALUE VARCHAR2(100)
   CONSTRAINT CONV_TEMP_DETAIL_UQ1
     UNIQUE(ID_NO, IDX));

This way, instead of having to generate column names dynamically and figure out how to use DBMS_SQL, you can get your data using a simple join:

SELECT h.*, d.*
  FROM CONV_TEMP_HEADER h
  LEFT OUTER JOIN CONV_TEMP_DETAIL d
    ON d.ID_NO = h.ID_NO;

Share and enjoy.

That could be a solution, thanks for answering!, but I will create myself a view containing that exact cursor, that way I will be able to use it from my EXECUTE IMMEDIATE with an index in that view, which will correspond where I am in my FOR loop. Note that this does not mean the previous answer is not better than what I do, I just find my solution more appropriate for my knowledge and needs — Raphaël, Jan 07 '15 at 16:33

score 0 · Answer 2 · answered Jan 07 '15 at 16:41

For other people with basic knowledge and the same needs, here is my solution:

CREATE OR REPLACE VIEW temp_view_name AS
SELECT ROWNUM AS ind, t.* FROM (
   SELECT DISTINCT m.*
   FROM conv_temp1 m ) t ;

This should be accessible from a EXECUTE IMMEDIATE query while a cursor isnt.

How can I dynamically recover columnName_N from a cursor, N being a number incremented in a LOOP?

2 Answers2