Re: PL/SQL differences between Solaris & Linux
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-lReceived on Thu Jul 24 2008 - 09:44:06 CDT