How to Read Single Line From Clob in Oracle

Question on your solution

Nirav, Oct 24, 2012 - half dozen:40 am UTC

Howdy Tom,
I am not much on pl/sql and trying to sympathize your solution.

What does this part of the code do or why is it there:

l_clob   clob := translate( p_clob, chr(13)|| chr(ten) || chr(9), '    ' ) || ' ';

Thanks
Nirav

October 25, 2012 - 8:52 am UTC

thirteen,10,ix are the ascii codes for carriage return, linefeed and tab. I'm turning them into a space so that when something is at the end of a line or separated from the side by side word by a tab - we just have to wait for a infinite

putting a infinite on the cease makes it so that if the last word in the text is SKnnnn - we can still easily parse it out.

it shouldn't be a "I'm not much on plsql" - the code should be readable by whatever programmer pretty easily.

shirish katti, October 25, 2012 - 12:44 am UTC

Thanks for the respond... I worked on it a bit and found other solution to information technology some what similar to yours.. Please find the logic below only in example if y'all are interested

declare
v_str varchar2(500) := 'THIS SK123 IS THE NEXT SK2345 OF THE Cord SK34567 THE END';
v_pos NUMBER := 0;
brainstorm
v_pos := INSTR(v_str, ' SK', v_pos+ane)+1;
while SUBSTR(v_str, v_pos, ii) = 'SK'
loop
dbms_output.put_line(SUBSTR(v_str, v_pos, INSTR(v_str, ' ', v_pos+ane)-v_pos));
v_pos := INSTR(v_str, ' SK', v_pos+1)+one;
end loop;
stop;

October 25, 2012 - 9:06 am UTC

and what if the string starts with SK? y'all won't notice ' SK'

and what if the string has SKnnn\north as this one does - instr( v_str, ' ' ) won't work.

Barry Chase, October 25, 2012 - nine:50 am UTC

l_clob clob := translate( p_clob, chr(13)|| chr(10) || chr(ix), '
' ) || ' ';

translate CARRIAGERETURN||LINEFEED||TAB to block of spaces

From what I tin can tell it is putting everything on a single line that is within that CLOB so that the parsing is not performed across carriage returns, line feeds, or tabs. If none of those occur, then at a minimum you take a space from one clob to another if processing multiple clobs.

October 25, 2012 - 9:53 am UTC

the end space is to work with the condition that SKnnnn might exist the last bit of text.

Also, i only noticed a possible issue, I should put a space on the front of the cord too and look for

              ' SK'            

to find "words that commencement with" SK.

Parsing Text of Package Specs

Vinayak Awasthi, October xxx, 2012 - 3:10 am UTC

Hi Tom,

I take a similar requirement. I have a bundle specs where nosotros holds all the record types used in the application. All the same, now nosotros have a new requirement where we take to extract text from this specs and compare record columns to a 3rd party file.

Can you suggest some means of performing such extraction. Here is my sample specs and required outpu:

Create or replace bundle test_pk_specs
IS
Every bit
/******************************************************* *
* this is a smaple specs comment line 1
* this is a smaple specs comment line two
* this is a smaple specs comment line three
****************************************************/

-- Record type for Emp Code
Type rcd_emp_code IS Record (
id_type VARCHAR2 (30)
,emp_code VARCHAR2 (100)
,id_version NUMBER (38)
,create_date DATE);

-- Ref cursor for Emp Code
TYPE rc_emp_code IS REF CURSOR
Return rcd_emp_code;

-- Record type for Dept Code
TYPE rcd_dept_code IS RECORD (
id_type VARCHAR2 (30)
,dept_code VARCHAR2 (100)
--, dept_name VARCHAR2(m) This is not required
,dept_city VARCHAR2(100));

Type rc_dept_code IS REF CURSOR
RETURN rcd_dept_code;

END test_pk_specs;
/

I problem I meet is the style we tin can insert inline comments in the specs. The output I want is:

Record_Name Columns
rcd_emp_code id_type
rcd_emp_code emp_code
rcd_emp_code id_version
rcd_emp_code create_date

rcd_dept_code id_type
rcd_dept_code dept_code
rcd_dept_code dept_city

See in the dept record type, I practice not see dept_name as that is commented out.

Can you delight enlighten some arroyo here.

Oct 30, 2012 - seven:01 pm UTC

well, if your coding standards are like that (nice - very uniform), and so

ops$tkyte%ORA11GR2> select rec_name, field_name   2    from (   3  select text,   4         line,   5         rec_name,   half dozen         example when row_number() over (partition by max_tag order by line) > ane   vii              so substr( ltrim( ltrim( text, ' ' ), ',' ), 1, instr( ltrim(ltrim(text, ' ' ), ','), ' ')-1 )   8          finish field_name,   9         max_tag,  10         lv_keep_flag,  11         row_number() over (partition by max_tag guild by line) rn  12    from (  13  select text,  14         line,  15         max(tag) over (order by line) max_tag,  16         last_value(record_name ignore nulls) over (order by line) rec_name,  17         last_value(keep_flag ignore nulls) over (order past line) lv_keep_flag  18    from (  19  select line,  20         text ,  21         case when text like '%TYPE % IS %RECORD %(%'  22              and so substr( text, instr(text,'Type ')+5, instr( text, ' IS ')-instr( text, 'Type ')-5 )  23          stop record_name,  24         case when text like '%TYPE % IS %Tape %(%'  25              then row_number() over (order by line)  26              when lag(text) over (order by line) like '%);%'  27              then row_number() over (club past line)  28          end tag,  29         case when text similar '%Blazon % IS %Record %(%'  thirty              so one  31              when lag(text) over (society by line) like '%);%'  32              and so 0  33          stop keep_flag  34    from user_source  35   where proper noun = 'TEST_PK_SPECS'  36     and type = 'Bundle'  37     and replace( text, ' ', '' ) Not similar '--%'  38         )  39         )  40   where lv_keep_flag = 1  41         )  42   where rn > 1  43   order past line;  REC_NAME        FIELD_NAME --------------- --------------- rcd_emp_code    id_type rcd_emp_code    emp_code rcd_emp_code    id_version rcd_emp_code    create_date rcd_dept_code   id_type rcd_dept_code   dept_code rcd_dept_code   dept_city  7 rows selected.            

Thanks a lot !!!

Vinayak, November fifteen, 2012 - iv:38 am UTC

Your idea worked like charm....

Though , unfortunately, our code standards were followed as required and there were many deviations from the code I posted simply I managed to get around them afterward getting direction from your approach.

SQL is overwhelmingly powerful...
Analytics rolls and rocks as usual....

Many thank you Tom

How to Read Single Line From Clob in Oracle

Source: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5585557600346118363

Share this post