Home » SQL & PL/SQL » SQL & PL/SQL » Parsing comma separated values into variables. (merged 6) (Oracle 9i,Windows XP)
Parsing comma separated values into variables. (merged 6) [message #441752] Wed, 03 February 2010 00:30 Go to next message
anil029
Messages: 14
Registered: February 2010
Junior Member
Hi,
I am trying to parse a string which contains comma separated values.The string contains more than 100 values.I want to capture the values in PL/SQL variables so that I can insert the values in the columns of database table.I know it can be done by using SUBSTR and INSTR functions separately for each values.Can it be done in a loop to avoid writing the same piece of code again and again for each values.

Example:
Suppose the input string is as string :='val1,val2,val3,............................'.
The values needs to be stored in separate variables as
var1:=val1
var2:=val2
...........
...........

Please help to resolve this issue and let me know if further clarification is needed.


Thanks,
Anil
Re: Parsing comma separated values into variables. [message #441757 is a reply to message #441752] Wed, 03 February 2010 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/364816/102589/?#msg_364816
http://www.orafaq.com/forum/m/285231/102589/?#msg_285231

Regards
Michel

[Updated on: Wed, 03 February 2010 00:37]

Report message to a moderator

Re: Parsing comma separated values into variables. [message #441762 is a reply to message #441752] Wed, 03 February 2010 00:44 Go to previous messageGo to next message
mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india
Junior Member
try collections (nested tables)..

and for column data type, first create a type (create type ty_name.. statement) of same nested table type used in your pl/sql code.

hope it will be of some use to you

regards,
mohley
Re: Parsing comma separated values into variables. [message #441763 is a reply to message #441757] Wed, 03 February 2010 00:52 Go to previous messageGo to next message
anil029
Messages: 14
Registered: February 2010
Junior Member
Hi Michael,
The solution provided in the url is just displaying the values in the string.lastly,when the loop ends 's' contains last value in the string.I want to store each values in some predefined pl/sql variables so that i can insert them in a database table.

Thanks,
anil
Re: Parsing comma separated values into variables. [message #441764 is a reply to message #441763] Wed, 03 February 2010 00:56 Go to previous messageGo to next message
anil029
Messages: 14
Registered: February 2010
Junior Member
Hi Mohley,
Can you send me some sample code.I never used collections before.It will be of great help.

Thanks,
Anil
Re: Parsing comma separated values into variables. [message #441765 is a reply to message #441763] Wed, 03 February 2010 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anil029 wrote on Wed, 03 February 2010 07:52
Hi Michael,
The solution provided in the url is just displaying the values in the string.lastly,when the loop ends 's' contains last value in the string.I want to store each values in some predefined pl/sql variables so that i can insert them in a database table.

Thanks,
anil

I provided an example of a way to do it with INSTR and SUBSTR not a direct solution to YOUR issue, it is easy to adapt it to your case, just analyze how it works and you will find how to do it in PL/SQL.

The core is:
substr(:myString,
       instr('|'||:myString||'|', '|', 1, rn),
       instr('|'||:myString||'|', '|', 1, rn+1)
       - instr('|'||:myString||'|', '|', 1, rn)

where rn indicates the number of your variable: 1 for var1, 2 for var 2 and so on (and replacing | by , for your case).

Regards
Michel

[Updated on: Wed, 03 February 2010 00:59]

Report message to a moderator

Re: Parsing comma separated values into variables. [message #441767 is a reply to message #441764] Wed, 03 February 2010 01:04 Go to previous messageGo to next message
mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india
Junior Member
DECLARE
   CURSOR EMP_CUR IS SELECT A.EMPNO,A.ENAME,B.ENAME AS MANAGER, A.SAL 
   FROM EMP A JOIN EMP B ON (A.EMPNO = B.EMPNO)
   WHERE A.DEPTNO = 30;
   
   TYPE EMP_TAB_TYPE IS TABLE OF EMP_CUR%ROWTYPE; 
   EMP_TAB  EMP_TAB_TYPE;
BEGIN
   EMP_TAB := EMP_TAB_TYPE();   
   OPEN EMP_CUR;
   FETCH EMP_CUR BULK COLLECT INTO EMP_TAB;
   CLOSE EMP_CUR;
   
   IF EMP_TAB.FIRST IS NOT NULL THEN
     FOR I IN EMP_TAB.FIRST .. EMP_TAB.LAST
     LOOP
       dbms_output.put_line(EMP_TAB(I).EMPNO ||'    '||EMP_TAB(I).ENAME||'   '||
                            EMP_TAB(I).MANAGER||'     '||EMP_TAB(I).SAL);
     END LOOP;
  END IF;
     
END;

hope it will be of some help

regards,
mohley

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 03 February 2010 01:34] by Moderator

Report message to a moderator

Re: Parsing comma separated values into variables. (merged 6) [message #441794 is a reply to message #441752] Wed, 03 February 2010 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use DBMS_UTILITY.COMMA_TO_TABLE and load the whole string into a pl/sql table.
You might need to wrap the comma separated items in double quotes, but that's easy enough to do.
Re: Parsing comma separated values into variables. (merged 6) [message #441802 is a reply to message #441794] Wed, 03 February 2010 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I hope these solutions with collection will be helpful but the question is:
Quote:
The values needs to be stored in separate variables as
var1:=val1
var2:=val2

Regards
Michel
Re: Parsing comma separated values into variables. (merged 6) [message #442478 is a reply to message #441802] Mon, 08 February 2010 06:47 Go to previous messageGo to next message
anil029
Messages: 14
Registered: February 2010
Junior Member
Hi JRowbottom,
Can you please give me a example to demonstrate the use of DBMS_UTILITY.COMMA_TO_TABLE.That will be really helpful.

Thanks,
Anil
Re: Parsing comma separated values into variables. (merged 6) [message #442479 is a reply to message #442478] Mon, 08 February 2010 06:49 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?hl=en&source=hp&q=Oracle+COMMA_TO_TABLE+&aq=f&aqi=&oq=

Regards
Michel
Previous Topic: REF CURSORS
Next Topic: Object does not exist or marked for delete
Goto Forum:
  


Current Time: Tue Dec 06 12:24:52 CST 2016

Total time taken to generate the page: 0.09077 seconds