Re: PL/SQL question

On Mon, 17 Dec 2001 21:47:43 +0100, "Thierry" <thierry.constant2_at_wanadoo.fr> wrote:

>Hi,
>
>I try to find a string TOTO in any table belonging to a user JOHN
>I write the following in PL/SQL:
>
>
>===============================================================
>declare
>cursor c1 is
> select table_name,column_name from dba_tab_columns where
>owner='JOHN' and data_type like '%CHAR%';
>nom_table varchar2(30) ;
>nom_col varchar2(30) ;
>begin
>open c1;
>loop
> fetch c1 into nom_table,nom_col;
> exit when c1%notfound;
> select * from nom_table where nom_col like '%TOTO%';
>
>>>>there is something wrong using the variables nom_table and nom_col
>>>>but I don't know what , any help ????
>
>end loop;
>close c1;
>end;
>/
>====================================================================
>

SIGH, No version again! What's so difficult about including it? The answer might be more accurate and it doesn't force the person responding to include resolutions for all different versions.

You need to use dynamic sql.
In 7.3 and 8.0 you need to use the dbms_sql package. In 8i and higher you can use the open <cursorvariable> for '<statement>' using .... construct (Hint: you *should* use bind variables)

Further details in your pl/sql manual.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Dec 17 2001 - 15:09:27 CST