Home » SQL & PL/SQL » SQL & PL/SQL » insert with mutilple parameter (9i windows)
insert with mutilple parameter [message #332731] Wed, 09 July 2008 08:52 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i have searched the web but unable to get the result/solution.
pls any one look into this

i need to insert t1 from t2 using a procedure
create or replace procedure testinsert 
(i_set in t2.set%type) 
is 
begin 
insert into t1 
(id 
,name 
,set_name) 
select idseq.nextval 
,name 
,set_name 
from t2 where t1set in i_set ; 
commit; 
end; 
/


here user will pass N number parameter (input value from t2 t1set column)

can use type function etc..

execute testinsert ('set1','set2','set3') 


Re: insert with mutilple parameter [message #332736 is a reply to message #332731] Wed, 09 July 2008 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "varying in-list".

Regards
Michel
Re: insert with mutilple parameter [message #332740 is a reply to message #332731] Wed, 09 July 2008 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
How many inconsistancies in a single post?
>insert with mutilple parameter
but only single parameter specified "(i_set in t2.set%type)"

What is t2.set & where is it defined?

What happens when testinsert called from T3 or some other procedure?

>i need to insert t1 from t2 using a procedure
but example from sqlplus "execute testinsert ('set1','set2','set3')"
Re: insert with mutilple parameter [message #332744 is a reply to message #332731] Wed, 09 July 2008 09:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As you did not post your table description and data (what is t2.set%type?), I will just provide you this demonstration example:
SQL> create table tt1( id integer, name varchar2(10), set_name varchar2(10) );

Table created.

SQL> create table tt2( id integer, name varchar2(10), set_name varchar2(10) );

Table created.

SQL> create type t_var_ty is table of varchar2(10);
  2  /

Type created.

SQL> insert into tt2( id, name, set_name )
  2  select level, 'name'||to_char(level), 'set'||to_char(level)
  3  from dual
  4  connect by level <= 5;

5 rows created.

SQL> select * from tt2;

        ID NAME       SET_NAME
---------- ---------- ----------
         1 name1      set1
         2 name2      set2
         3 name3      set3
         4 name4      set4
         5 name5      set5

SQL> insert into tt1( id, name, set_name )
  2  select id, name, set_name
  3  from tt2
  4  where set_name in (select column_value
  5                     from table( t_var_ty( 'set1', 'set2', 'set3' ) ) );

3 rows created.

SQL> select * from tt1;

        ID NAME       SET_NAME
---------- ---------- ----------
         1 name1      set1
         2 name2      set2
         3 name3      set3

Note, you have to create a SQL collection type for that 'multiple parameter'.
Maybe you will need to use CAST function in the SQL when passing its value as a procedure parameter.

P.S. "varying in-list" is great for dynamic SQL, however I do not think it is appropriate here (static SQL).
Re: insert with mutilple parameter [message #332766 is a reply to message #332744] Wed, 09 July 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
P.S. "varying in-list" is great for dynamic SQL, however I do not think it is appropriate here (static SQL).

I don't know what you meant with this. I never use varying in-list for dynamic SQL but for static one.
What you posted is a varying in-list technic.
For me, "varying in-list" is a short way to say "... IN (SELECT ... FROM TABLE(...))".

Regards
Michel
Re: insert with mutilple parameter [message #332768 is a reply to message #332766] Wed, 09 July 2008 10:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Michel Cadot wrote on Wed, 09 July 2008 17:40
I don't know what you meant with this. I never use varying in-list for dynamic SQL but for static one.
What you posted is a varying in-list technic.
For me, "varying in-list" is a short way to say "... IN (SELECT ... FROM TABLE(...))".

You are right, "varying in-list" is supposed to be static.
I do not know, what reminded me of dynamic SQL Embarassed
Re: insert with mutilple parameter [message #332923 is a reply to message #332768] Thu, 10 July 2008 02:24 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
still looking for solution

i will have one in put parmeter

in my insert statement i will use select ...... where in (parameter) clause where i will be passing n number of values
pls any look into it
Re: insert with mutilple parameter [message #332924 is a reply to message #332923] Thu, 10 July 2008 02:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why doesn't flyboy's solution work for you?
Re: insert with mutilple parameter [message #332925 is a reply to message #332923] Thu, 10 July 2008 02:28 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
still looking for solution

Just read what has been posted.

Regards
Michel
Previous Topic: any method or package in UTL_SMTP to verfiy email address
Next Topic: Sequencial Incremental Rows result
Goto Forum:
  


Current Time: Wed Dec 07 13:01:37 CST 2016

Total time taken to generate the page: 0.09558 seconds