LONGs:Is there a App.Dev.Guide-Fundamentals(8.1.7)

From: Mike Jay <mikejay_at_mitre.org>
Date: Wed, 01 May 2002 13:40:07 -0400
Message-ID: <3CD02877.7F0B6AB8@mitre.org>

Howdy,

One for the masters, my questions center on the LONG datatype with details for:

1. Documents -- any for 8.1.7
2. Code -- why did it work for 8.1.6 but not 8.1.7
3. Version/parameters -- any known gotchas for 8.1.7 vs 8.1.6 wrt parameters

The questions follow:

1. Documents ******************

I have looked for the 8.1.7 version of the:

Oracle8i Application Developer's Guide - Fundamentals Release 2 (8.1.6)
Part Number A76939-01

but could only find "New Features" type docs and Java stuff on OTN.

Is there a new document for 8.1.7 or is the 8.1.6 still in effect.

Specifically, have any release notes impacting the "Using the LONG Datatype" paragraph been issued that would supersede or break the statement:

"You can use columns defined as LONG in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements. LONG columns have many of the characteristics of VARCHAR2 columns."

2. Code **********************

Here are the details of why:

• storage clauses removed

CREATE TABLE CLOB_TEMP (
  ID NUMBER NOT NULL,   CLOB_PREPEND CLOB,
  CLOB_APPEND CLOB,
  CONSTRAINT PK_SUMMARY_CLOB_TEMP
  PRIMARY KEY ( ID )
);

• this PL/SQL approach worked
• on an 8.1.6 system on Solaris 7
• but not on an 8.1.7 system on Solaris 8

declare

	v_CLOB_prepend CLOB; -- locator
	v_CLOB_append CLOB; -- locator
	cursor cur_STUFF is
	select *
	from mySchema.STUFF;
begin
	 for rec_STUFF in cur_STUFF loop
	 insert into mySchema.CLOB_TEMP 
	 (id, CLOB_prepend, CLOB_append)
	 values (rec_STUFF.id

, 'ID=' || rec_STUFF.ID || ' lots of concat decodes were here ' || 'THE_LONG_FIELD* ' , rec_STUFF.THE_LONG_FIELD)
returning CLOB_prepend, CLOB_append into v_CLOB_prepend, v_CLOB_append;

         end loop;
exception

		 when others then
		 rollback;

end;
/

The 8.1.7 issued the PLS-00382 and the ORA-06550 error, that is, it did not like the datatype in the above line:

, rec_STUFF.THE_LONG_FIELD)

However, the 8.1.6 worked fine.

3. Version ***********************

Any reason why an 8.1.6. would behave differently than an 8.1.7

Also, what parameters (as in sqlplus "show parameters") should I look for that might affect LONG datatype behavior.

Regarding CLOB datatypes:
Don't I wish; I can't use the CLOB datatype because lots of application code will have to be scrapped so I have to use LONG since it uses value not locator syntax.

Thanks,
mikejay Received on Wed May 01 2002 - 12:40:07 CDT