Prev: APEX in Oracle XE stopped working
Next: missing header creating XML doc from XMLDOM
From: Martin T. on 15 Nov 2006 05:38
mariano.calandra(a)gmail.com wrote:
> 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?

This one should've been real easy to google ...

Instead of: select replacedott2() from DUAL;
Do:
declare
vv varchar2(2000);
begin
vv := replacedott2();
end;

From: "Jim Kennedy" jim dot scuba dot kennedy at gee male dot on 15 Nov 2006 09:28

<mariano.calandra(a)gmail.com> wrote in message
news:1163583208.913609.246800(a)h54g2000cwb.googlegroups.com...
>
> 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?
>
Don't do it in a function. Do it in a procedure.
Jim


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

Jim Kennedy ha scritto:

> <mariano.calandra(a)gmail.com> wrote in message
> news:1163583208.913609.246800(a)h54g2000cwb.googlegroups.com...
> >
> > 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?
> >
> Don't do it in a function. Do it in a procedure.
> Jim
yes, it was originally a procedure, I will transform it in a function
for debugging purpose.

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