| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: problem with execute immediate and function
sybrandb ha scritto:
> 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
>
>
 |
 |