Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    VA
    Posts
    35

    Unanswered: Error while selecting data into a variable

    I am running Oracle 10.2.0.3. When I select a "long raw" column into a "long raw" variable, I'm getting an error. When I select the data without placing it into a variable, the statement succeeds. (Please see below.) Any ideas?

    Thanks,

    J

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

    Code:
    SQL> desc sc_image_displays
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SC_CHART_ID                               NOT NULL NUMBER
     MOD_USER_NAME                             NOT NULL VARCHAR2(30)
     MOD_DATE                                  NOT NULL DATE
     HEIGHT                                             NUMBER(10)
     WIDTH                                              NUMBER(10)
     FILE_SIZE                                          NUMBER(10)
     IMAGE                                              LONG RAW
    
    SQL> declare
      2     temp_image              long raw;
      3  
      4  begin
      5     select image into temp_image from aimdba.sc_image_displays where sc_chart_id = 20;
      6  end;
      7  /
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 5
    
    SQL> select image from aimdba.sc_image_displays where sc_chart_id = 20;
    
    I
    -
    8

  2. #2
    Join Date
    Dec 2004
    Location
    VA
    Posts
    35
    A coworked ran across a reference on asktom.com. Apparently, PL/SQL has a 32K limit when working with "long raw" data. Longer data is not supported. Wow...

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also, the use of the LONG variable is strongly advised against, it has too many limitations. Use CLOB or BLOB instead.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2004
    Location
    VA
    Posts
    35
    Quote Originally Posted by beilstwh
    Also, the use of the LONG variable is strongly advised against, it has too many limitations. Use CLOB or BLOB instead.
    Yup. I agree. And that is exactly what we did when we migrated from Oracle 9i to 10g. Then we asked the Developers to test result with the applications, and we thought they tested the applications. Now we learn that maybe testing wasn't as thorough as once believed... One of the major apps is choking on a converted BLOB, and we need to revert back to the old data type to avoid re-writing boat loads of code....

    Ahhhhhhhhhhhhh, progress..........

    [grumble]Yes, this digs a hole for us.[/grumble]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •