Long Raw Column Problem?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Long Raw Column Problem?

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    Long Raw Column Problem?

    Hi all,

    I am new to PL/SQL programming, i was writing this basic block:

    SQL> DECLARE
    2 X NUMBER;
    3 BEGIN
    4 SELECT COUNT(*) INTO X FROM overviewimage;
    5 dbms_output.put_line(X);
    6 END;
    7 /

    And it threw up the following error:

    ERROR at line 4:
    ORA-06550: line 4, column 30:
    PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-320: the declaration of the type of this expression is
    incomplete or malformed
    ORA-06550: line 4, column 1:
    PL/SQL: SQL Statement ignored

    Now i couldnt see anything wrong it so i tried on another table and it worked fine. The only difference between the 2 tables is that the one generating the error has LONG RAW datatypes. Can what i am doing not be done on tables with such datatypes? Is there a way around this?

    Thanks in advance,
    Chucks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    What happens if you open a SQLPLUS session and do...

    1- select count(*) from overviewimage;
    2- select count(column_name) from overviewimage;
    where column_name is both NOT your LONG RAW column and, is a NOT NULL column on overviewimage table.
    Last edited by PAVB; 08-09-2007 at 09:48 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Hi,

    SQL> select count(capturekey) from overviewimage;

    COUNT(CAPTUREKEY)
    -----------------
    175

    SQL> select count(*) from overviewimage;

    COUNT(*)
    ----------
    175

    Got the same numbers. What does this signify?

    Thanks in advance,
    Chucks

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    They should return the same number of rows, that's correct.
    Just wanted to be sure it worked fine on sqlplus.

    Would you mind in compiling and testing the procedure below?

    Code:
    CREATE OR REPLACE PROCEDURE COUNTLONGRAW 
    IS
    X NUMBER;
    BEGIN
    -- first try
    SELECT COUNT(capturekey) INTO X FROM overviewimage;
    dbms_output.put_line(X);
    -- second try
    SELECT COUNT(*) INTO X FROM overviewimage;
    dbms_output.put_line(X);
    END;
    /
    If it fails, would you mind in checking the size of your LONG RAW columns?
    For CLOB columns -which is the datatype that has replaced LONG RAW- it should be something like...

    Code:
    select  capturekey,
            dbms_lob.getlength(your-long-raw-column)    "SIZE CLOB CONTENT" 
    from    overviewimage
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    Hi PAV,

    Thanks for your help.

    The first block you gave the same error as before:

    Code:
    SQL> show errors
    Errors for PROCEDURE COUNTLONGRAW:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/1      PL/SQL: SQL Statement ignored
    6/38     PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
             ORA-06553: PLS-320: the declaration of the type of this
             expression is incomplete or malformed
    
    9/1      PL/SQL: SQL Statement ignored
    9/29     PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
             ORA-06553: PLS-320: the declaration of the type of this
             expression is incomplete or malformed
    The second gave this:

    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    Its strange that i can do a count(*) in sqlplus and not via pl/sql

    Thanks again,
    Chucks

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Well... sqlplus and pl/sql are not exactly the same animal, something might be bothering pl/sql.

    What's your Oracle version?...

    I just compiled COUNTLONGRAW storedproc with no problems in 9i. Actually, it executed just fine.

    What I do not have is really big data into long raw columns and per previous experience I can tell it makes a lot of difference if you go beyond the 4K or so threshold in these datatypes; that's why I wanted to check the size of your long raw data.

    In regards to the second block, I didn't expected for this one to run as it is. That piece of code is certified for CLOB; some research and work is needed to have the same thing working for LONG RAW.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width