?
Solved

UTL_RAW/ RETRIEVE LONGRAW DATA

Posted on 2003-03-19
4
Medium Priority
?
2,278 Views
Last Modified: 2007-12-19
Hi All
There is a table in Oracle 9i Releaase 2
with a LONGRAW column
SQL> DESC NARRATIVE
Name Null? Type
------------------------------- -------- ----
NARRATIVE_ID NOT NULL NUMBER(15)
PARENT_TABLE_ID NOT NULL NUMBER(15)
NARRATIVE_TYPE NOT NULL VARCHAR2(1)
NARRATIVE LONG RAW

I need to read the data in the LONGRAW column. I tried using the
UTL_RAW.CAST_TO_VARCHAR2 package and tried to dump the LONG
RAW data to a LONG COLUMN.

declare
l_text long;
l_raw long raw;
l_text_length number;
begin
for x in ( select rowid r, t.* from narrative t )
loop
l_text := utl_raw.cast_to_varchar2(x.narrative);
l_text_length := length(l_text);
dbms_output.put_line( 'it is ' || length(l_text) || ' bytes
longs');
insert into narrative_long_tbl values (x.narrative_id, x.parent_table_id,
x.narrative_type, l_text);
commit;
end loop;
end;

When i execute this in SQL*PLUS this is the result
SQL> @retrieve_test_raw.sql
21 /
it is 23040 bytes
longs
it is 29184 bytes
longs
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6

Is this error because the data is > 32000 bytes? The data stored in
NARRATIVE
column is actually a word document per row.

How can i retrieve LONG RAW data? Please help.

Thanks,
Kaveena.
0
Comment
Question by:kaveena
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 1

Accepted Solution

by:
indypung earned 300 total points
ID: 8169264
here is how i've done it.  i use the insert statement below to insert the long raw column into a temporary table as a blob. if it's long raw, you have to use blob, not clob. then i used the opsblob_proc below to read the blob in to a blob variable, then use the dbms_lob and utl_raw packages to get the data line by line to an output file.  this may not be the most eloquent way to do this, but it works. let me know if you have any specific questions on how this works.


INSERT INTO ems_owner.report_requests_tmp
   (SELECT reqid, reqrepid, to_lob (reqresult)
      FROM ems_owner.report_requests
     WHERE reqcompdt >= (  sysdate
                         - 1
                        )
       AND (   reqcompflag = 'C'
            OR reqcompflag = 'I'
           )
       AND reqtype = 'S');



CREATE OR REPLACE PROCEDURE opsblob_proc (
   p_reqid_in                 VARCHAR2,
   c_extract_output_name_in   VARCHAR2
)
IS
   l_xml_text        BLOB;
   r_xml_line        RAW (32767);
   n_substr_amount   INTEGER;
   n_instr_offset    INTEGER                := 1;
   v_in_handle       SYS.UTL_FILE.file_type;
   i                 VARCHAR2 (32767);
BEGIN
   SELECT reqresult_blob
     INTO l_xml_text
     FROM ems_owner.report_requests_tmp
    WHERE reqid = p_reqid_in;

   --opens input file for reading
   SYS.DBMS_LOB.OPEN (l_xml_text, SYS.DBMS_LOB.lob_readonly);
   v_in_handle :=
      SYS.UTL_FILE.fopen ('/home/ehsitdev/EIMS/xml',
                          c_extract_output_name_in,
                          'W',
                          32767
                         );
   --search for carriage return starting at 1
   n_substr_amount :=
                (SYS.DBMS_LOB.INSTR (l_xml_text, '0D', n_instr_offset, 1) - 1
                );
   --get the line from the start to the carriage return
   r_xml_line :=
             SYS.DBMS_LOB.SUBSTR (l_xml_text, n_substr_amount, n_instr_offset);
--send line to file
   SYS.UTL_FILE.put_line (v_in_handle,
                          SYS.UTL_RAW.cast_to_varchar2 (r_xml_line)
                         );
   --set new starting point to beginning of next line
   n_instr_offset := n_instr_offset + n_substr_amount + 2;

   WHILE r_xml_line IS NOT NULL
   LOOP
      --this is the same block of code as above with the exception of the second line.
      --substr counts from the beginning of the file to instr, so in order to get the amount
      --of the current line, you have to take total amount minus all the previous lines

      n_substr_amount :=
               (SYS.DBMS_LOB.INSTR (l_xml_text, '0D', n_instr_offset, 1) - 1
               );
      n_substr_amount := n_substr_amount - n_instr_offset + 1;
      r_xml_line :=
             SYS.DBMS_LOB.SUBSTR (l_xml_text, n_substr_amount, n_instr_offset);
      SYS.UTL_FILE.put_line (v_in_handle,
                             SYS.UTL_RAW.cast_to_varchar2 (r_xml_line)
                            );
      n_instr_offset := n_instr_offset + n_substr_amount + 2;
   END LOOP;

   --manually output closing xml tag because of format of rover report
   SYS.UTL_FILE.put_line (v_in_handle, '</xml>');
   SYS.DBMS_LOB.CLOSE (l_xml_text);
   SYS.UTL_FILE.fclose (v_in_handle);
END;
/
0
 

Author Comment

by:kaveena
ID: 8173143
thank u. shall try this and let u know how it goes.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question