> Hi  Kaplan,
> I am sorry to disturb you. But I am not able to solve the problem below
> with PL/SQL. If you could guide me.
> I have list of record in a variable say : xyz is of varchar2 and the
> value contained in xyz is :
> XYZ|PQR|OPQ|http://www.xyz.com|this is a test description|Description
> ABC|AAA|BBBAA|http://www.abc.com|This is test too|jhjjjjjjjj
> KKK|KKKK|JJJJ|http://www.opq.com|Anything|jksdkjskd
> These are three records in a variable xyz which are seperated by a new
> line character and each field is seperated by a pipe symbol. This is an
> input to a procedure  as:
> create or replace procedure add_site( xyz IN varchar2)
> IS
> END;
> Is it possible that I can break the xyz variable's data  first for each
> row record and then for each and every column (which is delimited by a
> pipe '|' symbol) ? Is there any inbuilt function to find out the char
> '\n' as in 'C'? SUBSTR function does not work because a row record or
> column record can of variable length.
> The above input I am getting from the client browser which I have to
> break and put in a table after validation check for each and every
> record.
> Thanks and Regards,
> Vipin

"INSTR" will give the position of a string within another string.
You can also use CHAR for special characters. I believe 10 or 13 is a newline.

Using the above two (with SUBSTR) you can break it out. Say for instance that
CHAR(15) as the | separator (it probably isn't but I can't look it up now).
To get the first column, do

SQL> SELECT SUBSTR(the_line,1,INSTR(the_line,CHAR(15))) FROM the_table;


3 rows selected.


I hope this helps!

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 160+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page 18"> Back to Ari Kaplan's Home Page