OraFAQ Forum: SQL & PL/SQL » DBMS

Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_LOB.SUBSTR

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_LOB.SUBSTR [message #186389]

Mon, 07 August 2006 15:05

spuskas
Messages: 4
Registered: November 2005

Junior Member

Greetings,

I wanted to inquire as to whether anyone has experience with CLOBS and converting them to a SUBSTR within a view using:

DBMS_LOB.SUBSTR(x, y, z)

Currently, we need to convert a CLOB field to consecutive VARCHAR2 (which has a limit of 4000 characters) fields to create a view for the table. When using the following command:

DBMS_LOB.SUBSTR(field_name1, 4000, 1)
DBMS_LOB.SUBSTR(field_name2, 4000, 4001)
DBMS_LOB.SUBSTR(field_name3, 4000, 8001)
…. and so on…..

The following happens:

The view compiles; however, when the field_name1, field_name2, etc. displays, it is omitting/duplicating characters in randomly where field_name1 ends and field_name2 begins. Does anyone have detailed knowledge on this subject or can offer any advice? Any help is welcome. Thanks,

THE IMPORTANT THING ABOUT THIS ISSUE, is that it is a VIEW we have to convert the table too.

Scott

[Updated on: Mon, 07 August 2006 15:08]

Report message to a moderator

Re: DBMS_LOB.SUBSTR [message #186487 is a reply to message #186389]

Tue, 08 August 2006 02:39

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

It's a safe bet that it's not doing anything randomly.

If you are trying to convert a single CLOB (say field_1) into consecutive Varchar2s, wouldn't the following code make more sense:

DBMS_LOB.SUBSTR(field_1, 4000, 1)
DBMS_LOB.SUBSTR(field_1, 4000, 4001)
DBMS_LOB.SUBSTR(field_1, 4000, 8001)

Rather than getting the first 4000 chrs from field_name1, then the second 4000 chrs from field_name2 and the 3rd 4000 from field_name3.

Also are you sure you need to do this? What are you going to be doing with this data that requires it to be split down into Varchar2? Can you not simply split the CLOB down when you need to extract the data?

Report message to a moderator

Re: DBMS_LOB.SUBSTR [message #186790 is a reply to message #186389]

Wed, 09 August 2006 09:59

spuskas
Messages: 4
Registered: November 2005

Junior Member

I actually changed my code to your suggested code and the results consisted of only the first 4000 characters, nothing more, which was unexpected given that the parameters seem correct. The problem is that we need to create a view for the user to see the data (they are not allowed direct access to tables). The table contains the CLOB field, but the view cannot have a CLOB field and therefore must be changed to a VARCHAR2.

i used the following:

DBMS_LOB.SUBSTR(table_field, 4000, 1) field1,
DBMS_LOB.SUBSTR(table_field, 4000, 4001) field1,
DBMS_LOB.SUBSTR(table_field, 4000, 8001) field1,
DBMS_LOB.SUBSTR(table_field, 4000, 12001) field1,
DBMS_LOB.SUBSTR(table_field, 4000, 16001) field1,
DBMS_LOB.SUBSTR(table_field, 4000, 20001) field1

[Updated on: Wed, 09 August 2006 10:00]

Report message to a moderator

Re: DBMS_LOB.SUBSTR [message #186810 is a reply to message #186790]

Wed, 09 August 2006 12:33

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You seem to be using the same column name (field1) for all your columns - could this be your problem?

This little test case works for me:
I've not shown all the output data, but you can see that the 3 lines are different.

create table clob_test (col_1  varchar2(10), col_2 clob);

  1  declare
  2    v_data  varchar2(32767);
  3  begin
  4  delete clob_test;
  5    for i in 1..1260 loop
  6      v_data := v_data||'abcdefghijklmnopqrstuvwxyz';
  7    end loop;
  8  insert into clob_test values ('A',v_data);
  9  commit;
 10* end;
SQL> /

PL/SQL procedure successfully completed.
SQL> create or replace view clob_test_vw
  2  as select col_1
  3  ,dbms_lob.substr(col_2,4000,1) col_2_1
  4  ,dbms_lob.substr(col_2,4000,4001) col_2_2
  5  ,dbms_lob.substr(col_2,4000,8001) col_2_3
  6  from clob_test
  7  /

View created.

SQL> set linesize 4000
SQL> select * from clob_test_vw;

COL_1
----------
COL_2_1
---------------------------------------------------------------------------------
COL_2_2
---------------------------------------------------------------------------------
COL_2_3
---------------------------------------------------------------------------------
A
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabc
wxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy
stuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstu

Report message to a moderator

Previous Topic:	ORA-06500: PL/SQL: storage error
Next Topic:	repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repeat

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 10:54:24 CDT 2024