Re: Passing Table as Variable into Procedure

lydonbergin_at_gmail.com wrote:
> I'm new to PL/SQL from a .NET background. I'm working on a procedure
> to convert ISBN numbers to the new ISBN13 standard, and this needs to
> be done on several (hundreds) of our tables.
>
> I'm wondering if it's possible to setup a procedure that will accept a
> table name (or table object, however you want to look at it) and column
> name and will then take all of the values from table.column and run
> them through a conversion function. This way I will not have to create
> what's practically the same procedure hundreds of times.
>
> Is this even possible, and if so, can anyone shed some light on where
> to start?

You can use dynamic SQL.

CREATE OR REPLACE PROCEDURE ISBN_CONV (p_TABLE VARCHAR2, p_COLUMN VARCHAR2)
AS

BEGIN EXECUTE IMMEDIATE 'UPDATE '||p_TABLE||' SET '||p_COLUMN=||'your conversion fumction';
COMMIT; END; Received on Fri Aug 18 2006 - 16:12:09 CDT