Home » SQL & PL/SQL » SQL & PL/SQL » dynamic column_name in cursor (pl sql, oracle 9.1)
dynamic column_name in cursor [message #402635] Mon, 11 May 2009 13:06 Go to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Hello,
Oracle 9.1, win XP.

I am trying to get data from cursor using loop with changing the column_name dynamiclly.
I use array for colum_name for my table and loop it with cursor.
When i use fix colum_name (rec_cur_new.id), it's work fine, when i placed the array value column_name, i got error.
I tried many way, nothing work for me Sad


-- Create test table
CREATE TABLE test_1(id NUMBER, name VARCHAR2(25), hier_date DATE);
INSERT INTO test_1 VALUES(10,'SAM',sysdate);
COMMIT;


-- Here my example test procedure
CREATE OR REPLACE procedure record_data1 AS
	-- Cursor
	CURSOR cur_new IS
	SELECT * FROM test_1;

	-- Array for column table
	arr_record_string VARCHAR2(4000);
	arr_uncl dbms_utility.uncl_array;  
	l_tablen NUMBER;
	
	v_col_name user_tab_cols.column_name%type;
BEGIN
	-- array
	SELECT column_name BULK COLLECT INTO arr_uncl
	FROM user_tab_cols
	WHERE table_name = 'TEST_1';
	
	dbms_utility.table_to_comma(arr_uncl, l_tablen, arr_record_string);
	dbms_output.put_line(' ===== '||arr_uncl.COUNT);
	
	FOR i IN 1..arr_uncl.COUNT LOOP
		FOR rec_cur_new IN cur_new LOOP
			--v_col_name:=arr_uncl(i);
			--dbms_output.put_line(' ---> '||rec_cur_new.arr_uncl(i)); ----> not working !			
			dbms_output.put_line(' ---> '||rec_cur_new.id); -- i need to put here dynamic column_name --> v_col_name
		END LOOP;
	END LOOP;
END;
/
sho err
exec record_data1



Is there any way to keep my way i choose to slove this problem?

Thanks guys
Re: dynamic column_name in cursor [message #402642 is a reply to message #402635] Mon, 11 May 2009 13:42 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want to do (and I don't want to reverse engineer your code) but you have to use dynamic sql, dbms_sql for instance.

By the way, you don't to indent your code with 7 characters each level, 2 or 3 are sufficient and then lines can fit small windows.

Regards
Michel
Previous Topic: How does Java fit into PL/SQL-Oracle?
Next Topic: Receiving Email Messages through Oracle procedure.
Goto Forum:
  


Current Time: Sun Dec 04 08:30:23 CST 2016

Total time taken to generate the page: 0.19806 seconds