Home » SQL & PL/SQL » SQL & PL/SQL » Need help
Need help [message #358019] Fri, 07 November 2008 14:40 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

I have a requirement like at the time of submition of a program user enters parameter value like this n1@yahoo.com.n2>@yahoo.co.in,n3@yahoo.com,n4@yahoo.com this is a dynamical value and it can be 1 or more.

I need to split these values into variables with out the comma like
v1 := n1@123.com
v2 := n1@123.com
v3 := n1@123.com
v4 := n1@123.com


Could you help me how can i do this by using pl/sql.

Thanks in advance
Re: Need help [message #358021 is a reply to message #358019] Fri, 07 November 2008 14:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at DBMS_UTILITY.COMMA_TO_TABLE()

Ross Leishman
Re: Need help [message #358069 is a reply to message #358021] Sat, 08 November 2008 03:21 Go to previous messageGo to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

Thanks it is working fine. But I have one problem like

When I execute following code it is giving the error when I am giving '@' symbol in the Email address.
DECLARE
  l_mail     VARCHAR2(550) := 'n1@123.com.com,n2@123.com';
  l_number   BINARY_INTEGER;
  l_array     DBMS_UTILITY.uncl_array;
BEGIN
  DBMS_UTILITY.comma_to_table (
     list   => l_mail,
     tablen => l_number,
     tab    => l_array);
  FOR i IN 1 .. l_number LOOP
    DBMS_OUTPUT.put_line(i || ' : ' || l_array(i));
  END LOOP;
END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-02084: database name is missing a component
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_UTILITY", line 160
ORA-06512: at "SYS.DBMS_UTILITY", line 202
ORA-06512: at line 6

When I remove the '@' code is wroking fine

  1  DECLARE
  2    l_mail     VARCHAR2(550) := 'n1123.com.com,n2123.com';
  3    l_number   BINARY_INTEGER;
  4    l_array     DBMS_UTILITY.uncl_array;
  5  BEGIN
  6    DBMS_UTILITY.comma_to_table (
  7       list   => l_mail,
  8       tablen => l_number,
  9       tab    => l_array);
 10    FOR i IN 1 .. l_number LOOP
 11      DBMS_OUTPUT.put_line(i || ' : ' || l_array(i));
 12    END LOOP;
 13* END;
SQL> /
1 : n1123.com.com
2 : n2123.com

Please help me.

Thanks in advance
Re: Need help [message #358100 is a reply to message #358069] Sat, 08 November 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "varying in-list", "in-list string", "list in string" or the like there are many answers on this here.

Regards
Michel
Re: Need help [message #358156 is a reply to message #358019] Sun, 09 November 2008 14:34 Go to previous messageGo to next message
samtajain
Messages: 7
Registered: November 2008
Junior Member
Here is something which should work:

SELECT LEVEL AS SNO,SUBSTR(str,INSTR(str,',',1,LEVEL)+1,INSTR(str,',',1,LEVEL+1)-INSTR(str,',',1,LEVEL)-1) AS LIST_OF_VALUES
FROM (select ','|| 'n1@123.com.com,n2@123.com'|| ',' as str from dual)CONNECT BY LEVEL<= LENGTH(str) - LENGTH(REPLACE(str,',',''))-1;


Re: Need help [message #358157 is a reply to message #358156] Sun, 09 November 2008 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@samtajain


Please read OraFAQ Forum Guide:

1/ "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

2/ Don't put solution only hint or clue as requested in "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel

Re: Need help [message #358217 is a reply to message #358157] Mon, 10 November 2008 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
An easiear approach than using substr and instr is to use REGEXP_SUBSTR:
select regexp_substr('n1@yahoo.com,n2@yahoo.co.in,n3@yahoo.com,n4@yahoo.com','[^,]+',1,3) from dual;


You'll have to read up on Oracle's Regular Expressions, but they repay the effort.
Re: Need help [message #358367 is a reply to message #358217] Mon, 10 November 2008 23:07 Go to previous messageGo to next message
samtajain
Messages: 7
Registered: November 2008
Junior Member
Can you please confirm if it works for 9i as well. I have Oracle 9 and its not working.
Re: Need help [message #358373 is a reply to message #358019] Tue, 11 November 2008 00:34 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Regular Expressions are introduced in Oracle 10g . It wont work in 9i.

Smile
Rajuvan.
Previous Topic: ERROR - Buffer overflow, limit of 1000000 bytes
Next Topic: table with subtypes, inserting problem
Goto Forum:
  


Current Time: Fri Dec 09 11:55:42 CST 2016

Total time taken to generate the page: 0.08137 seconds