SQL & PL/SQL

Hi,

I do not have 10g to test the following snippet, which tries to use sqlerrm in an insert statement:

-- start testcase
drop table tbl;
drop table err;
 
create table tbl (x number);
create table err (a number, b varchar2(4000));
 
declare
  n_tmp   tbl.x%type;
begin
  select x into n_tmp from tbl;
exception
  when others then
    -- the line below works in 9i
    -- dbms_output.put_line('Sqlerrm: '||sqlerrm);
    -- but the insert statement doesn't work in 9i, how about 10g ?
    insert into err(a,b) values (1,sqlerrm);
    commit;
end;
/
 
--
declare
  n_tmp   tbl.x%type;
begin
  select x into n_tmp from tbl;
exception
  when others then
    -- the line below works in 9i
    -- dbms_output.put_line('Sqlerrm: '||sqlerrm);
    -- the insert statement works in 9iR2 but the error message is
    -- not the one sought, how about 10g ?
    insert into err(a,b) values (1,sys.standard.sqlerrm);
    commit;
end;
/

Can someone test it on any release/edition of 10g please ?

The tests on 9iR2 below behave predictably:

SQL>
SQL> @ver

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL>
SQL>
SQL> -- start testcase
SQL> drop table tbl;

Table dropped.

SQL> drop table err;

Table dropped.

SQL>
SQL> create table tbl (x number);

Table created.

SQL> create table err (a number, b varchar2(4000));

Table created.

SQL>
SQL> declare
  2    n_tmp   tbl.x%type;
  3  begin
  4    select x into n_tmp from tbl;
  5  exception
  6    when others then
  7      -- the line below works in 9i
  8      -- dbms_output.put_line('Sqlerrm: '||sqlerrm);
  9      -- but the insert statement doesn't work in 9i, how about 10g ?
 10      insert into err(a,b) values (1,sqlerrm);
 11      commit;
 12  end;
 13  /
    insert into err(a,b) values (1,sqlerrm);
                                   *
ERROR at line 10:
ORA-06550: line 10, column 36:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 10, column 5:
PL/SQL: SQL Statement ignored


SQL>
SQL> --
SQL> declare
  2    n_tmp   tbl.x%type;
  3  begin
  4    select x into n_tmp from tbl;
  5  exception
  6    when others then
  7      -- the line below works in 9i
  8      -- dbms_output.put_line('Sqlerrm: '||sqlerrm);
  9      -- the insert statement works in 9iR2 but the error message is
 10      -- not the one sought, how about 10g ?
 11      insert into err(a,b) values (1,sys.standard.sqlerrm);
 12      commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from err;

         A B
---------- --------------------------------------------------
         1 ORA-0000: normal, successful completion

SQL>
SQL>

The behavior of the second pl/sql block was mentioned by Nicolas:

1476653

Is there any change in 10g ?

Thanks in advance,
pratz

(Sorry, the hyperlink is getting modified automatically; remove the backslash ("\") immediately before the ampersand ("&") character.)

Message was edited by:
pratz