Hi All,
My task is to form a string using 2 tables and I am not sure with the definition of these tables ( table 1 might have 20 columns or 30 columns & table 2 might have 20 rows or 30 columns ). So I was using below logic for the same and i am stuck in a small issue where I am not sure how to use it :-
DECLARE
TYPE ref_cursor IS REF CURSOR;
rc_ ref_cursor;
c_ NUMBER;
i_ NUMBER;
col_count_ NUMBER;
counter INTEGER := 0;
counter1 INTEGER := 0;
desc_tab_ DBMS_SQL.DESC_TAB;
TYPE c_array IS TABLE OF VARCHAR2 (40);
strv VARCHAR2 (20000);
v_set NUMBER := 123456789;
v_rule NUMBER := 987654321;
counts c_array := c_array ();
counts1 c_array := c_array ();
names c_array
:= c_array ('EFFECTIVE_FROM', 'EFFECTIVE_TO'');
BEGIN
OPEN rc_ FOR 'select * from table1';
c_ := DBMS_SQL.to_cursor_number (rc_);
DBMS_SQL.DESCRIBE_COLUMNS (c_, col_count_, desc_tab_);
FOR i_ IN 1 .. col_count_
LOOP
counter := counter + 1;
counts.EXTEND;
counts (counter) := desc_tab_ (i_).col_name;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (c_);
counts := counts MULTISET EXCEPT names;
FOR i
IN ( SELECT id
FROM table2
)
LOOP
counter1 := counter1 + 1;
counts1.EXTEND;
counts1 (counter1) := i.id;
END LOOP;
FOR k IN (SELECT *
FROM table1 )
LOOP
strv :=
'^^^p_id=>'
|| v_set
|| ',NULL,'
|| k.effective_from
|| ','
|| k.effective_to;
FOR p IN 1 .. counts.COUNT
LOOP
strv := strv || '~~~' || counts1 (p) || ',' || k.counts (p); --k.counts(p) is not allowed and I would like to fetch the value of column1, column2.. column N ( based on the iteration of p )
END LOOP;
strv := strv || '`~`' || v_rule || '```';
DBMS_OUTPUT.put_line (strv);
END LOOP;
END;
Can someone expert in collections help me in this.
Thanks in advance
Punit Soneji