?
Solved

UTL_RAW/ RETRIEVE LONGRAW DATA

Posted on 2003-03-19
4
Medium Priority
?
2,286 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
2 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

571 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