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.
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.