Re: Passing lists to a procedure

On Jan 30, 6:25 am, paulwragg2..._at_hotmail.com wrote:
> 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

Actually you have three problems, the one you asked about, the symptom you hinted at(ie the "it does not work" portion of your posting), and the real problem you want to solve. I am going to tease you a bit and only discuss the first two.

First, I cannot read your mind so I have no idea what "it does not work" means in your case. Did it return an error? Did it return no results? did it return too many results? Did it enter a black hole? I can only guess.

Second, if you want to pass a list of tables, the brute force way is to put them all in one string. Okay, so you did that, but why so heavily quoted? Just pass in 'TABLE1, TABLE2'. Quoting is not your real problem. (Here's a hint: is what you are really trying to do easy in SQL/Plus? Keep in mind that SQL/Plus and PL/SQL are not the same thing.)

None of that is going to solve your real problem, but it should lead you to asking the right question. And asking the right question is the best way to solve a problem (with or without our help)

Ed Received on Tue Jan 30 2007 - 06:57:03 CST