Home » SQL & PL/SQL » SQL & PL/SQL » ora_sql_txt
ora_sql_txt [message #190295] Wed, 30 August 2006 01:58 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
im trying to get the DML statement executed on a table, but im getting this erro =)



SQL> desc gender
Name Type        Nullable Default Comments 
---- ----------- -------- ------- -------- 
CODE INTEGER     Y                         
G    VARCHAR2(1) Y           


SQL> create or replace trigger gender_aui
  2  after insert or update on gender
  3  declare
  4   li ora_name_list_t;
  5   n  number;
  6   stmt varchar2(32767);
  7  begin
  8   n := ora_sql_txt(li);
  9   for i in 1..n loop
 10      stmt := stmt || li(i);
 11   end loop;
 12   dbms_output.put_line(stmt);
 13  end;
 14  /

Trigger created

SQL> insert into gender values (9, 'f');

insert into gender values (9, 'f')

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "RHANI.GENDER_AUI", line 7
ORA-04088: error during execution of trigger 'RHANI.GENDER_AUI'


Re: ora_sql_txt [message #190298 is a reply to message #190295] Wed, 30 August 2006 02:12 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
I have Oracle 9.2.0.1.0 and it works fine - tested both in SQL*plus and PL/SQL Developer.
What's your DB version?

G.

create table GENDER
(
  CODE INTEGER,
  G    VARCHAR2(1)
);

create or replace trigger gender_aui
  after insert or update on gender  
DECLARE
  li   ora_name_list_t;
  n    NUMBER;
  stmt VARCHAR2(32767);
BEGIN
  n := ora_sql_txt(li);
  FOR i IN 1 .. n LOOP
    stmt := stmt || li(i);
  END LOOP;
  dbms_output.put_line(stmt);
END;
/

SQL> set serveroutput on
SQL> INSERT INTO GENDER VALUES(9, 'f');
INSERT INTO GENDER VALUES(9, 'f')

Creata 1 riga.

SQL> commit;

Commit completato.

SQL> 
Re: ora_sql_txt [message #190300 is a reply to message #190298] Wed, 30 August 2006 02:16 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the reply, im using Oracle 10g XE 10.2.0.1.0. It;s working on 9.2.0.4.0, what could be wrong?

[Updated on: Wed, 30 August 2006 02:20]

Report message to a moderator

Re: ora_sql_txt [message #190302 is a reply to message #190298] Wed, 30 August 2006 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
n can be null, making the loop condition illegal

Change it to
for i in 1 .. nvl(n, 0)
loop
...


By the way, dbms_output.put_line cannot handle unlimited linesizes.
Re: ora_sql_txt [message #190303 is a reply to message #190302] Wed, 30 August 2006 02:26 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks also for the reply, what do you mean sir by

"dbms_output.put_line cannot handle unlimited linesizes"

what else should i use? also tried the nvl, and it seems ora_sql_txt doesnt return a statement


SQL> set serveroutput on;
SQL> create or replace trigger gender_aui
  2  before insert or update on gender
  3  declare
  4  li ora_name_list_t;
  5  n  number;
  6  stmt varchar2(32767);
  7  begin
  8  n := ora_sql_txt(li);
  9  for i in 1..nvl(n, 0) loop
 10  stmt := stmt || li(i);
 11  end loop;
 12  dbms_output.put_line(stmt);
 13  end;
 14  /

Trigger created

SQL> insert into gender values (3, 'f');



1 row inserted

SQL> 
SQL> create or replace trigger gender_aui
  2  after insert or update on gender
  3  declare
  4  li ora_name_list_t;
  5  n  number;
  6  stmt varchar2(32767);
  7  begin
  8  n := ora_sql_txt(li);
  9  for i in 1..nvl(n, 0) loop
 10  stmt := stmt || li(i);
 11  end loop;
 12  dbms_output.put_line(stmt);
 13  end;
 14  /

Trigger created

SQL> insert into gender values (4, 'm');



1 row inserted




thanks again sir =)
Re: ora_sql_txt [message #190313 is a reply to message #190303] Wed, 30 August 2006 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

dbms_output.put_line cannot handle unlimited linesizes


Here's the documentation for dbms_output.

You can only pass a maximum of 255bytes to Dbms_output.put_line in a single call.
Re: ora_sql_txt [message #190320 is a reply to message #190313] Wed, 30 August 2006 03:46 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the reply, still cant figure out why ora_sql_txt doesnt have a value on 10g XE.
Re: ora_sql_txt [message #190342 is a reply to message #190295] Wed, 30 August 2006 05:26 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Quote:

You can only pass a maximum of 255bytes to Dbms_output.put_line in a single call.

To qualify that, this limit is true for 10gR1 (10.1) and before. 10gR2 (10.2), the maximum line length for dbms_output.put_line is 32767 bytes.

Re: ora_sql_txt [message #190349 is a reply to message #190295] Wed, 30 August 2006 05:50 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Found this problem listed on Metalink as a bug (#4171597) for 10.1.0.3.

This link might be relevant for you.

HTH

Re: ora_sql_txt [message #190350 is a reply to message #190342] Wed, 30 August 2006 05:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, pardon me for using old technology....

Cool
Re: ora_sql_txt [message #190357 is a reply to message #190295] Wed, 30 August 2006 06:09 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Have spent years splitting strings into 255byte-chunks to "put_line" them, so the relaxed line length limit brings much joy to me. Smile


Re: ora_sql_txt [message #190459 is a reply to message #190357] Wed, 30 August 2006 20:08 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for all the replies, yep ive seen also that tom kyte link, another user also encountered this bug in 10g, thanks again sir's.
Previous Topic: how to import/copy the tables of one user to another user.
Next Topic: date datatype
Goto Forum:
  


Current Time: Fri Dec 09 06:03:59 CST 2016

Total time taken to generate the page: 0.17019 seconds