long raw

[joeythelips]

Hi,

i have a field in a table which for some reason is a long raw datatype.

I need to update the value in the field (it holds printer names and addresses but our server names are being changed)

is it possible to do this using sql or will i have to manually update each printer through the application?

Joe

 

[SantaMufasa]

Joey,

I don't want to suggest anything that will introduce risk into your data, so please confirm for me (us) answers to the the following questions:
1) Are the contents of your "LONG RAW" column (which, for the sake of discussion, let's call "X") all characters (...since, as you say, "it holds printer names and addresses")?
2) What is the maximum length in characters of any "X"?
3) Why did someone create this column as "LONG RAW" in the first place?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:55 (11Mar04) UTC (aka "GMT" and "Zulu"), 10:55 (11Mar04) Mountain Time)

 

[joeythelips]

Dave,

1. there are numeric and text values in the field.
2. its not specified. I did a desc table_name and all that was returned was LONG RAW as the datatype.
3. I don't know why the field was created as LONG RAW in the first place.

Joe

 

[SantaMufasa]

Joey,

First, because LONG columns represent such a pragmatic nightmare, avoid their use unless you have no alternative. (Fellow Tek-Tipster, Carp, and I have been collaborating on your issue this morning and resolve that we shall NEVER use LONG RAWs. We have found, for example, that we cannot use Oracle LONG-RAW-handling utilities from SQL directly; If we wish to use SQL for LONG-RAW access, we must contrive a PL/SQL user-defined function.

Second, Oracle supports LONG RAW mainly for backward compatibility to earlier versions (such as Oracle7). Conventional wisdom suggests your using BLOB or CLOB instead of LONG RAW, and again, only when some form of LONG is absolutely necessary.

Now to your case, If you are satified that there is no good reason for your column to remain "LONG RAW", then I would certainly change it to become VARCHAR2. I would, however, get the backing/blessing of your colleagues. To do this, I would "get my ducks in a row" by confirming the following:

1) What was the original reason was for the column to be LONG RAW? If the original designer is still accessible, contact her/him for the rationale.
2) Does the original rationale still exist? If so, can the rationale be "redesigned"?
3) What is the current maximum length of your LONG RAW column?
4) Is there any negative impact on software infrastructure to implement this change?

Once you gain the advocacy on this matter from your IT and applications colleagues, I would convert the column. The following sections in my post are: Section 1 -- Simulating your situation, Section 2 -- Creating a function to access LONG RAW data as VARCHAR2, Section 3: Converting your LONG RAW column to VARCHAR2. (This code is similar to the code I posted yesterday to resolve Bookouri's thread759-796135, but LONG RAW requires an even more intricate resolution than Bookouri's.)

Section 1 -- Simulating your situation:

create table longthang (x long raw, id number);
desc longthang
insert into longthang values (utl_raw.cast_to_raw('hello'), 1);

Section 2 -- Creating a function to access LONG RAW data as VARCHAR2:

create or replace function get_long (row in rowid) return varchar2 is
	longhold	varchar2(4000);
begin
	for r in (select x from longthang where rowid = row) loop
		longhold := utl_raw.cast_to_varchar2(r.x);
	end loop;
	return longhold;
end;
/

Section 3: Converting your LONG RAW column to VARCHAR2:

create table long2 (id number, x varchar2(4000));
insert into long2 select id, get_long(rowid) from longthang;
update longthang set x = null;
alter table longthang modify x varchar2(4000);
update longthang set x = (select x from long2 where id = longthang.id);
desc longthang
col x format a20
select * from longthang;

Again, many thanks to my distinguish colleague, Carp, whose collaboration was vital to producing this result.

Let us know how it worked for you (if you decide to convert).

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:31 (11Mar04) UTC (aka "GMT" and "Zulu"), 11:31 (11Mar04) Mountain Time)

 

[carp]

If you gain permission to get rid of the long raw column, I might take a slightly different approach (again assuming your long raw column is named "x"):

ALTER TABLE my_table ADD printer VARCHAR2(4000); 

Then
DECLARE
   CURSOR c IS SELECT * FROM my_table FOR UPDATE;
BEGIN
   FOR i IN c LOOP
      UPDATE my_table
         SET printer = utl_raw.cast_to_varchar2(i.x)
       WHERE CURRENT OF c;
   END LOOP;
   COMMIT;
END;

After this has completed, if there is no reason to keep the long raw column, get rid of it. If you can't get rid of it, at least you can now update the varchar2 column in a human-readable format, then shove it all back into the long_raw column:

DECLARE
   CURSOR c IS SELECT * FROM my_table FOR UPDATE;
BEGIN
   FOR i IN c LOOP
      UPDATE my_table
         SET x = utl_raw.cast_to_raw(i.printer)
       WHERE CURRENT OF c;
   END LOOP;
   COMMIT;
END;

Elbert, CO
1201 MST

 

[NateUNI]

Ok, im running a similar statment. I want to append onto a string that is stored in a blob. This is what I currently have

DECLARE
   v_QUERY CHAR;
   CURSOR c IS SELECT QUERY FROM MESSAGECONFIG where campaignid = 1902 FOR UPDATE;
BEGIN
   FOR i IN c LOOP
      v_QUERY:= utl_raw.cast_to_varchar2(i.QUERY);
      v_QUERY:= v_QUERY + ' AND PROSPECT_CONTACT.last_call_date > (SYSDATE-14)';
      UPDATE MESSAGECONFIG
         SET QUERY = utl_raw.cast_to_raw(v_QUERY)
         where campaignid = 1902
       WHERE CURRENT OF c;
   END LOOP;
   COMMIT;
END;

I'm getting errors where i try to to the cast_to_varchar. I probably have tons of other errors to. Any Help? Thanks!

 

[rao1soft]

Dave,

I tried your solution, sections 2 and 3, but get this error while trying to insert into long2 from longthang:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MYDB.GET_LONG", line 4

What am I doing wrong?
TIA for your help.

--Rao.

 

[SantaMufasa]

Rao,

You wondered, "What am I doing wrong?" With kindness and respect, I suggest that the first thing you are doing wrong is posting a request for help without posting at least the code with which you are having trouble. It might also be helpful to see the table formats and sample data involved in your code processing. Once we have that, then we can better assist you.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:50 (04Apr04) UTC (aka "GMT" and "Zulu"), 18:50 (03Apr04) Mountain Time)

 

[rao1soft]

Dave,

Excuse me for my sloppy post earlier. Following is the code I used. Thanks again for your help.

--existing table in the database

desc tab_raw

raw_ID NOT NULL NUMBER
raw_data NOT NULL LONG RAW

--new table

desc long2;

ID NUMBER
X VARCHAR2(4000)

--function

create or replace function get_long (row in rowid) return varchar2 is
longhold varchar2(4000);
begin
for r in (select raw_data x from tab_raw where rowid = row) loop
longhold := utl_raw.cast_to_varchar2(r.x);
end loop;
return longhold;
end;
/

--conversion

insert into long2 select raw_id, get_long(rowid) from tab_raw;

The following error has occurred:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MYDB.GET_LONG", line 4

 

[markrem]

SQL*Plus variables can hold up to 32K of data, whereas an application (using PL/SQL, Java, or any other language) can write unlimited volumes of data by breaking the stream into chunks.

If you want to do a test of this in SQL*Plus, first issue the command "SET LONG N" where N is an integer upto 2000. Otherwise, you won't see much of your data when running queries / testing your work.

9i OCP