dynamic sql and cursors..

From: yyib <yyib_at_hotmail.com>
Date: 20 Oct 2004 06:31:58 -0700
Message-ID: <8c455290.0410200531.138f49d4@posting.google.com>

Hi,

I'm trying to swap the following 3 update statements with something a bit more dynamic using execute immediate with a string i'm building.

Now I know why this fails, however I need to get an understanding of how I go about modifying the code to make it work correctly.

I've read something about SQLDA, which perhaps is the solution here, however I am producing an oracle package only, not pro*C etc.

Please see the sample code beneath, which hopefully may explain what I am saying.

prob:-
curs_rec2.position_'||curs_rec3.column_name||'

Any help/advice would be greatly appreciated.

Rgds.

... -- shall never actually know the column names to be specified in this cursor declaration..

CURSOR curs_get_positions IS
SELECT * FROM map_040
WHERE file_name = v_file_name;
...

OPEN curs_file_name FOR 'SELECT file_name FROM ' || v_map_table;                 

 LOOP
  FETCH curs_file_name INTO v_file_name;

  EXIT WHEN curs_file_name%NOTFOUND;

  FOR curs_rec2 IN curs_get_positions LOOP

     /* 
     Try and replace three update blocks with a dynamic piece of
code...


     */

				
     v_output_write := '';
     FOR curs_rec3 IN curs_populate_temp_map LOOP
         v_output_write := 'UPDATE temp_table_map SET field_position =

curs_rec2.position_'||curs_rec3.column_name||' WHERE field_name = '''||curs_rec3.column_name||'''';

         
         EXECUTE IMMEDIATE v_output_write;
	 --DBMS_OUTPUT.PUT_LINE(v_output_write);
	
         COMMIT;
				
     END LOOP;
				


     */

	
     UPDATE temp_table_map SET 
     field_position = curs_rec2.position_row_id
     WHERE field_name = 'ROW_ID';
				
     UPDATE temp_table_map SET 
     field_position = curs_rec2.position_employee
     WHERE field_name = 'EMPLOYEE';
				
     UPDATE temp_table_map SET 
     field_position = curs_rec2.position_sal
     WHERE field_name = 'SAL';
			
			

    ...ETC Received on Wed Oct 20 2004 - 08:31:58 CDT