Prev: APEX in Oracle XE stopped working
Next: missing header creating XML doc from XMLDOM
From: mariano.calandra on 14 Nov 2006 13:44
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

From: sybrandb on 14 Nov 2006 14:51

mariano.calandra(a)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

From: mariano.calandra on 15 Nov 2006 03:12

sybrandb ha scritto:

> mariano.calandra(a)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
so how I have to write the sql?

From: Martin T. on 15 Nov 2006 03:19
mariano.calandra(a)gmail.com wrote:
> sybrandb ha scritto:
>
> > mariano.calandra(a)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
> so how I have to write the sql?

yours: ''Dott'');';

corrrect: ''Dott'')';

execute immediate statements must not have a ; at the end of the string
to be executed iirc.

cheers,
Martin

From: mariano.calandra on 15 Nov 2006 04:33

Martin T. ha scritto:

> mariano.calandra(a)gmail.com wrote:
> > sybrandb ha scritto:
> >
> > > mariano.calandra(a)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
> > so how I have to write the sql?
>
> yours: ''Dott'');';
>
> corrrect: ''Dott'')';
>
> execute immediate statements must not have a ; at the end of the string
> to be executed iirc.
>
> cheers,
> Martin

thank you, now the new error is:
ORA-14551 cannot perform a DML operation inside a query

How can I resolve it?

 |  Next  |  Last
Pages: 1 2
Prev: APEX in Oracle XE stopped working
Next: missing header creating XML doc from XMLDOM