ORA-00932 on long column

From: Dave Hausladen <david_hausladen_at_mail.amsinc.com>
Date: 1997/01/22
Message-ID: <01bc089d$119eaab0$5fa046a2@haus>#1/1

I am having a problem converting a C program with embedded SQL from Sybase to Oracle. The history is that the column I am trying to retrieve was previously a Sybase Text field, now is an Oracle Long field.

Below are the DDL for the table and the embedded SQL that is failing in the C code. When the system reaches the point (at runtime) where it is trying to execute the SQL command 'SELECT DOCUMENT_CONTENT INTO :lcp_document_content WHERE DOCUMENT_ID = :doc_id,' the system logs the error: 'Unable to read document content (DocId = (203), sql msg = (ORA-00932: inconsistent datatypes.'

Relevant facts:

1) DOCUMENT_CONTENT is datatype LONG.
2) The system selects it into a character pointer host variable.
3) The space for the pointer is malloc'd, then memset'd to spaces, then

null terminated (when char pointers are used as host variables, according to the Programmer's Guide to the Oracle Pro*C/C++ Precompiler, the precompiler calls strlen() to determine the size of the output buffer; the buffer should not contain any embedded nulls) prior to the SELECT INTO statement.
4) The explanation for the error message in the Oracle7 Server Messages, Release 7.3, page 2-58 reads:

ORA-00932	inconsistent datatypes
	Cause	Either

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field. An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
An attempt was made to use an undocumented view.

        Action If the cause is
different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields. an object not intended for normal use, then do not access the restricted object.

Is there a problem with using the long datatype in embedded SQL statements in C? Did I miss something else?

The table:

CREATE TABLE CACSG."DG_DOCUMENT_ARCHIVE"

       ("DOCUMENT_ID" NUMBER(9,0) NOT NULL,


       "TEMPLATE_ID" NUMBER(8,0),

       "DOCUMENT_GROUP_IND" CHAR(1) DEFAULT ' ' ,

       "DOCUMENT_STATUS" CHAR(1) DEFAULT ' ' ,

       "DOCUMENT_PRINTER" NUMBER(2,0) DEFAULT 0 ,

       "DOCUMENT_COPIES" NUMBER(4,0) DEFAULT 0 ,

       "DOCUMENT_CONTENT" LONG,

       "BATCH_PRINT_DATE" DATE,

       "REQUEST_USER" CHAR(8) DEFAULT ' ' ,

       "REQUEST_STATE" CHAR(4) DEFAULT ' ' ,

       "CONTENT_LENGTH" NUMBER(10,0) DEFAULT 0 );

 
 

The code:

	EXEC SQL
		SELECT 
			DOCUMENT_CONTENT
		INTO
			:lcp_doc_content 
		FROM
			DG_DOCUMENT_ARCHIVE
		WHERE
			DOCUMENT_ID = :av_long_docid ;

Thanks,
Dave Received on Wed Jan 22 1997 - 00:00:00 CST