RE: PL/SQL differences between Solaris & Linux

Bill,

I'm not sure, how/why it worked for you before. But, this is from Oracle docs:

"You cannot use SQLERRM directly in a SQL statement. Assign the value of SQLERRM to a local variable first:

my_sqlerrm := SQLERRM;
...

INSERT INTO errors VALUES (my_sqlerrm, ...);"

Igor  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman Sent: Wednesday, July 23, 2008 2:29 PM
To: oracle-l
Subject: PL/SQL differences between Solaris & Linux

Greetings,

We are in the process of moving our 9.2.0.8.0 EE database from Solaris SunOS 5.8 Generic_117350-54 sun4u sparc SUNW,Sun-Fire-480R to Linux 2.6.9-67.0.15.ELsmp #1 SMP Tue Apr 22 13:58:43 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux and have found an issue with some PL/SQL code. The following PL/SQL code segment (if the entire procedure would help, it's only 100 lines, let me know) compiled successfully on Solaris...

EXCEPTION
   WHEN OTHERS
   THEN

      SELECT ucd.log_seq.NEXTVAL
        INTO v_error_row
        FROM DUAL;

      INSERT INTO ucd.LOG
           VALUES (v_error_row, 'APS', 'CARD_SERVICE_FIX', 'PROCEDURE',
                   'ERROR', SQLCODE || ': ' || SQLERRM || '.', SYSDATE);

This code on linux generated the error
PL/SQL: ORA-00984: column not allowed here Which is to be expected (I guess) as I this is apparently not supported with SQLCODE and SQLERRM and I had to make the following changes...

   v_error_string VARCHAR2 (4000);
.
.

EXCEPTION
   WHEN OTHERS
   THEN

      v_error_string := SQLCODE || ': ' || SQLERRM || '.';
      SELECT ucd.log_seq.NEXTVAL
        INTO v_error_row
        FROM DUAL;

      INSERT INTO ucd.LOG
           VALUES (v_error_row, 'APS', 'CARD_SERVICE_FIX', 'PROCEDURE',
                   'ERROR', v_error_string, SYSDATE);

Can anyone explain why this worked on Solaris but not on linux (disclaimer: I didn't write the code). Is there something else I may not be aware of going on, is it an artifact of something else, are there differences between solaris and linux in this regard or are the gods just angry at me for other transgressions?

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l