>
> 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
> BEGIN
>
> 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;
XYZ
ABC
KKK
3 rows selected.
SQL>
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