Home » SQL & PL/SQL » SQL & PL/SQL » How to insert dynamically through a variable in PL/SQL?? (Oracle 9i)
How to insert dynamically through a variable in PL/SQL?? [message #394780] Mon, 30 March 2009 05:50 Go to next message
abhradwip
Messages: 19
Registered: March 2009
Location: INDIA
Junior Member
create table test ( id number, name varchar2(20) );

Now during insert, i wanna hold the data into variable first & then dynamically pass the variable into the VALUES clause.

declare
v_data varchar2(50);
begin
v_data:='1,sunny';
execute immediate 'insert into test values(v_data)';
commit;
end;

But its showing some errors(Not enough values)...... please help how to achieve this??
Re: How to insert dynamically through a variable in PL/SQL?? [message #394784 is a reply to message #394780] Mon, 30 March 2009 06:00 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

execute immediate 'insert into test values(v_data)';



Build your Insert Statement dynamically, if u just provide v_Data in your statement, oracle doesn't understand that its a variable, it will treat as the normal string.

Execute Immediate 'Insert into test values (' ||your_variable|| ')';


Better construct your Insert Statment in a seperate variable and use it in Execute Immediate
Re: How to insert dynamically through a variable in PL/SQL?? [message #394785 is a reply to message #394780] Mon, 30 March 2009 06:02 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
Dynamic sql is scoped differently to static sql. It can't see variables declared in the pl/sql block.
To do what you want you'd have to concatenate v_data into the string before executing it.

However I really don't recommend doing this since
1) It's about the slowest way of doing an insert you can find - you should be using bind variables if you really need to do dynamic sql.
2) It's vulnerable to sql injection.
Re: How to insert dynamically through a variable in PL/SQL?? [message #394786 is a reply to message #394780] Mon, 30 March 2009 06:03 Go to previous messageGo to next message
abhradwip
Messages: 19
Registered: March 2009
Location: INDIA
Junior Member
declare
v_data varchar2(50);
v_sql varchar2(500);
begin
v_data:='1,sunny';
v_sql:='insert into test values('||v_data||')';
execute immediate (v_sql);
commit;
end;

I have implemented this.... but still its showing some error..

ORA-00984: column not allowed here
Re: How to insert dynamically through a variable in PL/SQL?? [message #394787 is a reply to message #394780] Mon, 30 March 2009 06:04 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

'1,sunny'


This cannot be in one variable, or when u are inserting you should make sure that you will seperate it and then insert.

Or
Quote:

v_data := '1,''sunny''';


It should be like above

Re: How to insert dynamically through a variable in PL/SQL?? [message #394788 is a reply to message #394780] Mon, 30 March 2009 06:07 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

First try to print your query, to see whether the syntax is proper or not use dbms_output.put_line, if thats correct then only put the same in Execute Immediate.
Re: How to insert dynamically through a variable in PL/SQL?? [message #394789 is a reply to message #394780] Mon, 30 March 2009 06:09 Go to previous messageGo to next message
abhradwip
Messages: 19
Registered: March 2009
Location: INDIA
Junior Member
Thanx a lot buddy........ Smile
Re: How to insert dynamically through a variable in PL/SQL?? [message #394790 is a reply to message #394780] Mon, 30 March 2009 06:11 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

That means that did it solved your requirement ?? If so, then post whatever you tried so far, may be its useful for somebody else out here.

But i also suggest that its not a right way of doing and it may lead to SQL Injection.

Re: How to insert dynamically through a variable in PL/SQL?? [message #394791 is a reply to message #394780] Mon, 30 March 2009 06:18 Go to previous messageGo to next message
abhradwip
Messages: 19
Registered: March 2009
Location: INDIA
Junior Member
No buddy its done.... thanx again actually my requirement is that i will get index name & value comma separated as input like...

'BSE,ICICI,98.4#NSE,HDFC,96.5'

now i have to find the position of '#' & split the data & insert into respective table....

Re: How to insert dynamically through a variable in PL/SQL?? [message #394830 is a reply to message #394780] Mon, 30 March 2009 08:12 Go to previous message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
INSTR

Rajy
Previous Topic: Optimization CASE STATEMENT (2 threads merged by bb)
Next Topic: Number data type issue
Goto Forum:
  


Current Time: Tue Dec 06 04:51:22 CST 2016

Total time taken to generate the page: 0.29267 seconds