TEXT and IMAGE datatypes as parameters and local variables

[prandip]

Hi all,
having difficulties in using TEXT and IMAGE datatypes as stored procedure parameters or as local variables.
e.g.
CREATE PROCEDURE myproc
@a INT,
@b TEXT NULL
AS
BEGIN
--code for the procedure
END

Gives error as
'TEXT and IMAGE datatypes are invalid for parameters and local variables'

Please advice. Thanks

[Indira Aramandla]

Hi Prandip,
This may be of some help.
Error with LONG RAW / IMAGE data types :-

An assign statement such as: myvariable := :longrawcol;

or

if :image_item is null then
some logic;
end if;

produces compilation error:

PL/SQL ERROR 49 BAD BIND VARIABLE 'VARNAME'
or
ORA-00932 : inconsistent datatypes

Problem Explanation:
====================
A table with a long raw column exists on the database. The value of the long raw item cannot be used in an assign statement. You also cannot see if the image item is null with the if statement.

Solution Description:
=====================
PL/SQL v1 does not support long raw / image datatypes, this means it is not possible to perform any assignment through PL/SQL with this datatype. This is a limitation of PL/SQL.

Solution Explanation:
=====================
If you need logic in Oracle Forms to see if the long raw column is null then the workaround is:

a. Alter the database table and add a flag column.
b. Whenever you populate the record with an image, also populate the flag column with 'Y'.
c. Then in the form do the following if statement:
if :flag_column is null then
some login;
end if;

Within SQL*Plus, the long raw column cannot be copied with a create table command, insert statement or copy command.

SQL> create table table2 as select * from table1;

ERROR:
ORA-00932: inconsistent datatype

SQL> copy from user/pwd@db to user/pwd@db create table2 using select * from table1 ;

ERROR:
ORA-0932: inconsistent datatype

The COPY command can be used as a workaround for dealing with tables with long columns but not long raw. Using the copy command with long raw columns will result in ORA-932: inconsistent datatypes as illustrated above.


Solution References:
====================

Enhancement Requests
--------------------
153963
Abstract: SELECT LONG RAW GIVES INCONSISTENT DATATYPE
To retrieve long raw, use OCI or pre-compiler and do defines with the
appropriate datatype.

162373
Abstract: COPY A TABLE WITH "LONG RAW" GIVES ORA-932

293729
Abstract: OLE. PLS-49 OR ORA-932 WHEN ASSIGNING LONG RAW TO OLE-CONTAINER IN PL