Re: problem with execute immediate and function

From: sybrandb <sybrandb_at_gmail.com>
Date: 14 Nov 2006 11:51:55 -0800
Message-ID: <1163533915.560363.26360@e3g2000cwe.googlegroups.com>

mariano.calandra_at_gmail.com wrote:
> I have make the follow function:
> ______________________________________________
> create or replace function replacedott2
> return varchar2 as
> csql varchar2(2000);
> cursor cur_table is
> select table_name, column_name from all_tab_columns where owner =
> 'MARIANO' and data_type like '%CHAR%' AND table_name <>
> 'BIN$FTrKVuKiSsaycOCI4t/SPg==$0';
> val_table cur_table%ROWTYPE;
> begin
> open cur_table;
> loop
> fetch cur_table into val_table;
> exit when cur_table%NOTFOUND;
> csql := 'UPDATE '||val_table.table_name||' '
> || 'SET '||val_table.column_name||' '
> || '= REPLACE ('||val_table.column_name||', ''Dr'',
> ''Dott'');';

>


>             execute immediate (csql);

>      end loop;

> end;
> ______________________________________________

>
>


> ______________________________________________

>

> select replacedott2() from DUAL;
> ______________________________________________

>
>

> The previous command gave me error ORA-00911: invalid character. As
> debugging I've try to remove the 'execute immediate (csql)' and I've
> inserted 'return csql', the output string is:

>


> ______________________________________________

>

> UPDATE ARTICOLO SET DESCRIZIONE = REPLACE (DESCRIZIONE, 'Dr', 'Dott');
> ______________________________________________

>
>

> If I use only this command at prompt, it will work properly (no error
> and replace is ok), why don't work if I use as the input of execute
> immediate command?
> Thank you, g'bye

Because of the redundant ';'

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Tue Nov 14 2006 - 13:51:55 CST