Passing lists to a procedure

From: <paulwragg2323_at_hotmail.com>
Date: 30 Jan 2007 03:25:26 -0800
Message-ID: <1170156326.832778.237150@m58g2000cwm.googlegroups.com>

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 Received on Tue Jan 30 2007 - 05:25:26 CST