Re: PL/SQL differences between Solaris & Linux

From: Bradd Piontek <piontekdd_at_gmail.com>
Date: Thu, 24 Jul 2008 09:44:06 -0500
Message-ID: <e9569ef30807240744n4f7b44cfn1f07508ec26caff0@mail.gmail.com>


I performed a test in a couple environments with this test case and can't reproduce the issues.
drop table log;
create table log (

        log_entry VARCHAR2(4000)
);

CREATE OR REPLACE PROCEDURE test AS

        lv_date DATE;
BEGIN
        SELECT sysdate INTO lv_date FROM dual;

EXCEPTION
  WHEN OTHERS
  THEN

     INSERT INTO LOG
                  VALUES (SQLCODE || ': ' || SQLERRM);
END;
/
show errors

results:
9.2.0.8 EE with Apr2007CPU
SunOS dbs003 5.8 Generic_117350-51 sun4u sparc SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jul 24 09:39:58 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning option
JServer Release 9.2.0.8.0 - Production

Table dropped.

Table created.

Warning: Procedure created with compilation errors.

Errors for PROCEDURE TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------
9/6      PL/SQL: SQL Statement ignored

10/46 PL/SQL: ORA-00984: column not allowed here

9.2.0.8 EE Linux Redhat 4
2.6.9-42.0.2.ELsmp #1 SMP Thu Aug 17 17:57:31 EDT 2006 x86_64 x86_64 x86_64 GNU/Linux
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning option
JServer Release 9.2.0.8.0 - Production

Table created.

Warning: Procedure created with compilation errors.

Errors for PROCEDURE TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------
9/6      PL/SQL: SQL Statement ignored

10/46 PL/SQL: ORA-00984: column not allowed here

For fun , here are the results from 8.1.7.4 (same Solaris platform, I don't have an 8i Linux env ;) )

Table created.

Warning: Procedure created with compilation errors.

Errors for PROCEDURE TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------
9/6      PL/SQL: SQL Statement ignored

10/27 PLS-00231: function 'SQLCODE' may not be used in SQL

Bradd Piontek
Oracle Blog: http://piontekdd.blogspot.com Linked In: http://www.linkedin.com/in/piontekdd

On Wed, Jul 23, 2008 at 1:29 PM, William Wagman <wjwagman_at_ucdavis.edu> wrote:

> 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
Received on Thu Jul 24 2008 - 09:44:06 CDT

Original text of this message