Re: Passing lists to a procedure

From: <paulwragg2323_at_hotmail.com>
Date: 30 Jan 2007 08:45:02 -0800
Message-ID: <1170175502.761132.119880@a34g2000cwb.googlegroups.com>

Hi Maxim,

Thanks for your help with this. I have the following:

CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000); CREATE OR REPLACE FUNCTION GetTableList
RETURN VARCHAR2_TT
AS

strTables CONSTANT VARCHAR2_TT := VARCHAR2_TT('TABLE1', 'TABLE2', 'TABLE3'); BEGIN RETURN strTables;
END;
/

--Cursor to find all table/column definitions CREATE OR REPLACE PROCEDURE DropAllDefaults(strTables IN VARCHAR2_TT) 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);

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;
/

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE DROPALLDEFAULTS:

LINE/COL ERROR

---

---

5/2 PL/SQL: SQL Statement ignored
10/22 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got

         I4410ADM.VARCHAR2_TT

24/3     PL/SQL: Statement ignored
24/19    PLS-00364: loop index variable 'COLRECORD' use is invalid
25/3     PL/SQL: Statement ignored
25/16    PLS-00364: loop index variable 'COLRECORD' use is invalid
26/3     PL/SQL: Statement ignored
26/17    PLS-00364: loop index variable 'COLRECORD' use is invalid

So from what I can figure, the TABLE_NAME IN clause is expecting NUMBERS rather than my newly created data type VARCHAR2_TT.

I think I may be getting somewhere now though. Having looked through the example again, I can use strTables(1), but obviously this will only use the first table in the list of tables (or the second depending on the array element numbers).

So do I need to use some form of function within the IN clause to expand this as required?

Thanks,

Paul Received on Tue Jan 30 2007 - 10:45:02 CST