RE: Problems with CharArrayType after upgrade from 7.3.4 to 8.1.7

From: PHU,JULIA (HP-Sunnyvale,ex2) <julia_phu_at_hp.com>
Date: Wed, 30 May 2001 11:56:58 -0700
Message-ID: <F001.003143CD.20010530120824@fatcity.com>

VARCHAR is the older version. VARCHAR2 is for 8.0 and above. Changing from VARCHAR to VARCHAR2 for 8.1.7 will solve the problem.

-----Original Message-----

Sent: Wednesday, May 30, 2001 11:47 AM
To: Multiple recipients of list ORACLE-L 8.1.7.1

Cherie, I have no idea if this could be the case, but I noticed that the type is based on the datatype VARCHAR, not VARCHAR2. According to the docs, this shouldn't make a difference, but they've been warning since the introduction of VARCHAR2 that they may do something different with VARCHAR at some point in the future, so it's best not to use it. From the PL/SQL manual:

VARCHAR2 Subtypes

The VARCHAR2 subtypes below have the same range of values as their base type. For example, VARCHAR is just another name for VARCHAR2.

     STRING
     VARCHAR

You can use these subtypes for compatibility with ANSI/ISO and IBM types.

Note: Currently, VARCHAR is synonymous with VARCHAR2. However, in future releases of PL/SQL, to accommodate emerging SQL standards, VARCHAR might become a separate datatype with different comparison semantics. So, it is a good idea to use VARCHAR2 rather than VARCHAR.

Again, I don't see how that could affect the code...just a shot in the dark.

Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: Diana_Duncan_at_ttpartners.com

                    Cherie_Machler

                    @gelco.com            To:     Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    Sent by:              cc:

                    root_at_fatcity.c        Fax to:

                    om                    Subject:     Problems with
CharArrayType after upgrade from 7.3.4 to      
                                          8.1.7.1

 

                    05/30/2001

                    01:40 PM

                    Please respond

                    to ORACLE-L

After upgrading from Enterprise Edition 7.3.4 to 8.1.7.1 on Sun Solaris 2.6, we are
having problems executing a procedure with CharArrayType as shown below. We don't get any error message. The package simply does not return any rows
when there are rows out there that should be returned.

The developer is trying to turn on some debug but I thought I would also ask if anyone sees anything that is a no-no now under 8.1.7.1. Is there something
that I need to do in order to migrate this code that wasn't done automatically?

CREATE OR REPLACE PACKAGE CCFILECD AS TYPE NumArrayType IS TABLE OF NUMBER

INDEX BY BINARY_INTEGER; TYPE CharArrayType IS TABLE OF VARCHAR(31)

INDEX BY BINARY_INTEGER; PROCEDURE GETFILECODES(file_code OUT CharArrayType,

               file_prefix    OUT CharArrayType,
               start_pos      OUT NumArrayType,
               length         OUT NumArrayType,
               num_lines_to_skip OUT NumArrayType,
               record_length  OUT NumArrayType,
               cntry_cd_proc  OUT NumArrayType,
               curr_cd_proc   OUT NumArrayType,
               count          OUT NUMBER);

END CCFILECD; Thanks for any advice you can give,

Cherie Machler
Oracle DBA
Gelco Information Network

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Diana_Duncan_at_ttpartners.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: PHU,JULIA (HP-Sunnyvale,ex2)
INET: julia_phu_at_hp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message