Hi Guru,

Is it required that you have to use only PL/SQL tables? Just try this.
If you have any comments let me know.

procedure name_pro(name_lst varchar2)
is
v_name varchar2(100):=name_lst;
v_name1 varchar2(100):=name_lst;
--type name_list is table of varchar2(100) index by binary_integer;
--n_tab name_list;
begin
--dbms_output.put_line(v_name);
--dbms_output.put_line(length(name_lst)-length(replace(name_lst,',',null)));
for i in 1 .. length(name_lst)-length(replace(name_lst,',',null))
loop
v_name :=substr(v_name1,1,instr(v_name1,',')-1);
insert into emp(name) values(v_name);
v_name1 :=substr(v_name1,instr(v_name1,',')+1);
dbms_output.put_line(i);
--dbms_output.put_line(n_tab(i));
end loop;
end;

Regards,
Prakash.



"Gunasekaran Srinivasan "
<oracledba-ezmlmshield-x95778961.[Email address protected]
29/11/2005 14:13

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Urgent help on Table() with PL/SQL Type.






Hi all,



My requirement is - I'll be getting comma separated string as parameter
in my procedure and I need to

insert each word (delimited by comma) as a row into a table.



I'm trying to achieve it by using PL/SQL Table function with a PL/SQL
Table type.



Here is my Procedure.



Procedure prc_ins

is

array_roll tname := tname ('ABCD', 'XYZ'); -- tname is the type
created in database.

begin



insert into tmp select * from TABLE( cast ( array_roll as tname ) );

end;



the above procedure is working fine. But I would like to replace the
('ABCD', 'XYZ') values by a parameter

and declaring the type TNAME in the package header without creating it
in Database like below.



Procedure prc_ins (str)

is

array_roll tname := tname str; -- tname is the type created in
database.

begin



insert into tmp select * from TABLE( cast ( array_roll as tname ) );

end;



Please let me know if there is a way to achieve this



Thanks/rgds

Guna




______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html


ForwardSourceID:NT000117A2


Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you

Oracle LazyDBA home page