OraFAQ Forum: SQL & PL/SQL » Procedure with cursor and variables

Home » SQL & PL/SQL » SQL & PL/SQL » Procedure with cursor and variables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Procedure with cursor and variables [message #4584]

Fri, 27 December 2002 13:18

Rusty
Messages: 2
Registered: December 2002

Junior Member

Hi,

I need to create a Procedure that will accept two variables, the table name and a specific column. I am trying to have the procedure copy from the variable column in the variable table, to a consistent column.

The purpose is to modify a number of tables which already contain a new column, and copy from an existing column into the new column for each of these tables.

Here's what I have, which doesn't work. Can someone tell me how to do this correctly?

Thanks,
Rusty

COMMON_IDENTIFIER is the name of the new column exisitng in all the tables.

CREATE PROCEDURE FILL_CI_COLUMN (INPUT_TABLE VARCHAR2, COLUMN_NAME VARCHAR2)IS
CURSOR FILL_CURSOR (INPUT_TABLE VARCHAR2, COLUMN_NAME VARCHAR2) IS
Select COLUMN_NAME from INPUT_TABLE FOR UPDATE OF INPUT_TABLE.COMMON_IDENTIFIER;
BEGIN
FOR CURRENT_ROW IN FILL_CURSOR LOOP
COPY_FROM := CURRENT_ROW.COLUMN_NAME;

UPDATE INPUT_TABLE
SET INPUT_TABLE.COMMON_IDENTIFIER = COPY_FROM
WHERE CURRENT OF FILL_CURSOR;
END LOOP;
END FILL_CI_COLUMN;

Report message to a moderator

Re: Procedure with cursor and variables [message #4586 is a reply to message #4584]

Fri, 27 December 2002 22:12

Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA

Senior Member

CREATE OR REPLACE PROCEDURE fill_ci_column 
  (p_input_table IN VARCHAR2, 
   p_column_name IN VARCHAR2)
IS
BEGIN
  EXECUTE IMMEDIATE
      'UPDATE ' || p_input_table 
  || ' SET common_identifier = ' || p_column_name;
END fill_ci_column; 
/

Report message to a moderator

Re: Procedure with cursor and variables [message #4600 is a reply to message #4586]

Mon, 30 December 2002 08:32

Rusty
Messages: 2
Registered: December 2002

Junior Member

Thanks!

It looks like I was trying to make it more difficult than it needed to be.

--Rusty

Report message to a moderator

Previous Topic:	Regarding OCP Exam
Next Topic:	Triggers refrencing Multiple Tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 14:09:35 CDT 2024