Long Raw Column Problem?

[Chucks_k]

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

[PAVB]

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.

[Chucks_k]

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

[PAVB]

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?

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...

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

[Chucks_k]

Hi PAV,

Thanks for your help.

The first block you gave the same error as before:

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

[PAVB]

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.