Re: Passing lists to a procedure

paulwragg2323_at_hotmail.com schrieb:
> Hi,
>
> I have the following procedure:
>
> CREATE OR REPLACE PROCEDURE DropAllDefaults(strTables IN VARCHAR2)
> AS
>
> CURSOR ColumnsCursor IS
> SELECT TABLE_NAME, COLUMN_NAME, DATA_DEFAULT
> FROM USER_TAB_COLS
> WHERE DATA_DEFAULT IS NOT NULL
> AND UPPER(TABLE_NAME) NOT LIKE 'BIN%'
> AND UPPER(COLUMN_NAME) NOT LIKE 'SYS_%'
> AND TABLE_NAME IN ( strTables )
> ORDER BY 1, 2;
>
> strDefault VARCHAR2(4000);
> strTableName VARCHAR2(250);
> strColumn VARCHAR2(250);
> strTablesToPass VARCHAR2(4000);
>
>
> BEGIN
>
> --For each column, reset the definition to NULL
>
>
> FOR ColRecord IN ColumnsCursor LOOP
>
> strTableName := ColRecord.TABLE_NAME;
> strColumn := ColRecord.COLUMN_NAME;
> strDefault := ColRecord.DATA_DEFAULT;
>
>
> IF UPPER(strDefault) IS NOT NULL THEN
>
> EXECUTE IMMEDIATE ('ALTER TABLE ' || strTableName || ' MODIFY (' ||
> strColumn || ' DEFAULT NULL)');
>
> END IF;
>
>
> END LOOP;
>
> END;
> /
>
> As you can see, this is a procedure to loop through and for the passed
> in list of tables in will remove any default definitions on the
> database. However, this procedure only works when passed one table at
> a time i.e.:
>
> EXEC DropAllDefaults('TABLE1');
>
> When I attempt to pass it a list of tables it does not work as below:
>
> EXEC DropAllDefaults('''TABLE1'', TABLE2''');
>
> I expect this is to do with the way it is passed - hopefully it is
> clear what I am trying to do. Does anybody have any suggestions as to
> how I can do this? I need to be able to pass a comma separated list of
> an indeterminate number of tables.
>
> Thanks in advance,
>
> Paul
>

See this example of passing comma separated list http://www.williamrobertson.net/feed/2005/10/comma-separated-input.html

Best regards

Maxim Received on Tue Jan 30 2007 - 07:42:48 CST