-
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
-
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.
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|