Home » SQL & PL/SQL » SQL & PL/SQL » help string to table (this is not yrgent at all, so please take your time)
help string to table (this is not yrgent at all, so please take your time) [message #263496] Thu, 30 August 2007 03:35 Go to next message
rishi1979
Messages: 36
Registered: November 2006
Member
Let me first explain u the situation .

I had a table say test(a number
b varchar2(10)
c varchar2(10))

I need to create a routine where i have to pass three parameters say
procedure insert_routine (in_id number,in_type varchar2,in_code varchar2)

i would be passing these three parameters from outside one would in id of a person which i will pass in in_id and the other two would be string say (p1,p2,p3,p4) which would be passed in in_type and (w1,w2,w3,w4) which would passed in in_code.so the three parameters would be

procedure insert_routine(98765,('p1,p2,p3,p4'),('w1,w2,w3,w4')) now i want to insert them into the table test so that it would look

a b c
98765 p1 w1
98765 p2, w2
98765 p3 w3
98765 p4 w4
so basically this is stirng to table

i had tried this by making a pl/sql table .but it works for one column but hoe to insert in two column at a time.If there is any other way of doing this




PACKAGE test_for_string IS

TYPE mail_rectype IS RECORD (val1 varchar2(10), val2 VARCHAR2(10));

TYPE emp_table IS TABLE OF mail_rectype INDEX BY BINARY_INTEGER;
--TYPE emp_table IS TABLE OF varchar2(10) INDEX BY BINARY_INTEGER;

mail_table emp_table;

TYPE t_mail IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

p_mail t_mail;


procedure values_test2 (in_id in varchar2,in_type in varchar2,in_code in varchar2);

END;


PACKAGE BODY test_for_string IS


FUNCTION string_to_table ( p_string IN VARCHAR2,
p_seprator IN VARCHAR2 DEFAULT ',')
RETURN t_mail AS

p_mail t_mail;

l_string LONG DEFAULT p_string || p_seprator;
l_n NUMBER;
BEGIN
LOOP
EXIT WHEN l_string IS NULL;
l_n := INSTR( l_string,p_seprator);
IF l_n > 1 THEN
p_mail(p_mail.count+1) := LTRIM(RTRIM(SUBSTR(l_string,1,l_n-1)));
END IF;
l_string := substr( l_string,l_n+1 );
END LOOP;
RETURN p_mail ;
END string_to_rishi;

procedure values_test2 (in_id in varchar2,in_type in varchar2,in_code in varchar2) is

i number;


begin


mail_table(1).val1 := string_to_rishi('p,p,p,p',',');
mail_table(1).val2 := string_to_rishi('p,p,p,p',',');----This is where it give me error
--mail_table(mail_table.count+1).org_code := string_to_rishi(in_code,';');

for i in mail_table.first..mail_table.last loop

insert into table_test values (in_id,mail_table(i).val1,mail_table(i).val2);

end loop;


end;


END;

[Updated on: Thu, 30 August 2007 08:08] by Moderator

Report message to a moderator

Re: urgent help string to table [message #263519 is a reply to message #263496] Thu, 30 August 2007 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> var in_type varchar2(20);
SQL> exec :in_type := 'p1,p2,p3,p4'

PL/SQL procedure successfully completed.

SQL> with 
  2    data as ( select ','||:in_type||',' in_type from dual ),
  3    lines as (
  4      select level line 
  5      from data
  6      connect by level < length(in_type)-length(replace(in_type,',',''))
  7    )
  8  select substr(in_type, 
  9                instr(in_type, ',', 1, line) + 1,
 10                instr(in_type, ',', 1, line+1) - instr(in_type, ',', 1, line) - 1
 11               ) in_type
 12  from data, lines
 13  /
IN_TYPE
----------------------------------
p1
p2
p3
p4

4 rows selected.

Regards
Michel
Re: urgent help string to table [message #263535 is a reply to message #263519] Thu, 30 August 2007 04:44 Go to previous messageGo to next message
rishi1979
Messages: 36
Registered: November 2006
Member
Hi Thanks for this but the problem here is that i have to pass two strings at a time and then insert them into the table at the same time for one string i can do that but how to do for two strings at a same time and then insert them into table
like at one go.The table has three columns.so it should insert for string one (p1,p2,p3) and sting two(w1,w2,w3) like

A B C
1 p1 w1
1 p2 w2
1 p3 w3
Re: urgent help string to table [message #263539 is a reply to message #263535] Thu, 30 August 2007 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One step further:
SQL> var in_id number;
SQL> var in_type varchar2(20);
SQL> var in_code varchar2(20);
SQL> exec :in_id := 98765; :in_type := 'p1,p2,p3,p4'; :in_code := 'w1,w2,w3,w4'

PL/SQL procedure successfully completed.

SQL> with 
  2    data as ( 
  3      select ','||:in_type||',' in_type,
  4             ','||:in_code||',' in_code
  5      from dual
  6    ),
  7    lines as (
  8      select level line 
  9      from data
 10      connect by level < length(in_type)-length(replace(in_type,',',''))
 11    )
 12  select :in_id a,
 13         substr(in_type, 
 14                instr(in_type, ',', 1, line) + 1,
 15                instr(in_type, ',', 1, line+1) - instr(in_type, ',', 1, line) - 1
 16               ) b,
 17         substr(in_code, 
 18                instr(in_code, ',', 1, line) + 1,
 19                instr(in_code, ',', 1, line+1) - instr(in_code, ',', 1, line) - 1
 20               ) c
 21  from data, lines
 22  /
         A B                                  C
---------- ---------------------------------- ----------------------------------
     98765 p1                                 w1
     98765 p2                                 w2
     98765 p3                                 w3
     98765 p4                                 w4

4 rows selected.

Regards
Michel
Re: urgent help string to table [message #263547 is a reply to message #263539] Thu, 30 August 2007 05:08 Go to previous messageGo to next message
rishi1979
Messages: 36
Registered: November 2006
Member
thank u very much for this
Re: urgent help string to table [message #263577 is a reply to message #263547] Thu, 30 August 2007 07:55 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of using strings with comma delimited values, it should be better to use array of values.
Something like:
SQL> create or replace type my_array is table of varchar2(20);
  2  /

Type created.

SQL> create or replace procedure p (in_id integer, in_type my_array, in_code my_array)
  2  is
  3  begin
  4    dbms_output.put_line('    A  B  C');
  5    for i in in_type.first..in_type.last loop
  6      dbms_output.put_line(in_id||' '||in_type(i)||' '||in_code(i));
  7    end loop;
  8  end;
  9  /

Procedure created.

SQL> exec p(98765, my_array('p1','p2','p3','p4'), my_array('w1','w2','w3','w4'));
    A  B  C
98765 p1 w1
98765 p2 w2
98765 p3 w3
98765 p4 w4

PL/SQL procedure successfully completed.

Regards
Michel
Previous Topic: list of characters
Next Topic: oracle database 10g express edition installation - error
Goto Forum:
  


Current Time: Sat Dec 03 18:21:05 CST 2016

Total time taken to generate the page: 0.10877 seconds