Home » SQL & PL/SQL » SQL & PL/SQL » insert select in PLSQL (Oracle 9i)
insert select in PLSQL [message #595160] Sun, 08 September 2013 05:56 Go to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
I have two tables, table A with column a,b,c and table B with column a,b,c,d
In PLSQL I have im my procedure a dynamic veriable.
I like to insert into B the values of table A + the content of the dynamic veriable.
Column d is varchar2.
write - insert into B select a,b,c,d from A:
in sqlplus when putting the value in 'XXX' it works
question: procedure failed because d is not a column in table A and d should be inserted as the veriable value as varchar2.
If anyone have an idea will be nice......
Re: insert select in PLSQL [message #595161 is a reply to message #595160] Sun, 08 September 2013 06:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2135
Registered: May 2013
Location: World Wide on the Web
Senior Member
doron.simchoni wrote on Sun, 08 September 2013 16:26
insert into B select a,b,c,d from A


Above statement will definitely fail since "D" is not a column of table A.

Copy paste the SQL*Plus session of what you tried. And please use code tags when you post that, it will be easier to read.

Re: insert select in PLSQL [message #595162 is a reply to message #595160] Sun, 08 September 2013 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58903
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Sat, 31 August 2013 14:08
...
For your questions, Use SQL*Plus and copy and paste your session, the WHOLE session showing what you say.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: insert select in PLSQL [message #595545 is a reply to message #595162] Wed, 11 September 2013 10:52 Go to previous messageGo to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

I have not tested this. But something like this can work.

DECLARE
	DYNAMICVAL TABLE1.COL4%TYPE;
BEGIN
	SELECT DBMS_RANDOM.VALUE INTO DYNAMICVAL FROM DUAL;

	INSERT INTO
		  TABLE1 ( COL1,
			   COL2,
			   COL3,
			   COL4 )
		( SELECT
			   COL1,
			   COL2,
			   COL3,
			   DYNAMICVAL
		 FROM
			   TABLE2 );

	COMMIT;
END;
/
Re: insert select in PLSQL [message #595547 is a reply to message #595162] Wed, 11 September 2013 12:22 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
Thanks to all of you trying to find solution for this issue.
Originally I used ref cursor with insert select and for some reason it didn't work.
I change to dynamic plsql using execute immediate and it works fine.
Issue closed
Re: insert select in PLSQL [message #595548 is a reply to message #595547] Wed, 11 September 2013 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 22704
Registered: January 2009
Senior Member
please post your working code here
Re: insert select in PLSQL [message #595551 is a reply to message #595548] Wed, 11 September 2013 12:53 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
sorry but I'm not allow putting company code on the internet, company rules.
However the idea is having variable name stmt contain the insert select sql command with two parameter who are entered dynamically every time it needed.
After having the stmt done with the parameters doing execute immediate stmt;
Re: insert select in PLSQL [message #595553 is a reply to message #595551] Wed, 11 September 2013 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58903
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You don't need to post the actual code but a code that shows the problem and how it solves it
2/ "fter having the stmt done with the parameters doing execute immediate stmt;" does this mean you concatenate the parameter values in the statement? If so, it is very very bad.

Regards
Michel
Re: insert select in PLSQL [message #595556 is a reply to message #595553] Wed, 11 September 2013 13:55 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
No, I do not agree with it.
there are two (or more....) options:
first is using sql_block using bind variables and execute immediate using the sql_block.
Second, create a stmt with the changeable variables concatenate to it and execute immediate stmt.
Checked both with explain plan and find out the second one is better so took it.
Most probable that in other cases it can be the first
Re: insert select in PLSQL [message #595559 is a reply to message #595556] Wed, 11 September 2013 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 22704
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


blah, blah blah, blah
Post code so we can see the same as you since we don't know to what you refer.

Re: insert select in PLSQL [message #595561 is a reply to message #595556] Wed, 11 September 2013 14:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2135
Registered: May 2013
Location: World Wide on the Web
Senior Member
Doron -

When you use so many EXECUTE IMMEDIATE statements in your code, it means you have ended up at a situation where you are left with no better option. You might have just ignored or overlooked some much better ways.

And the thing with explain plan(while you have bind variables) is that, especially in 11g, you might face performance issues if you blindly trust on it. Only a tracing event will be a good proof of what exactly happens.
Re: insert select in PLSQL [message #595565 is a reply to message #595559] Wed, 11 September 2013 14:35 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
this my last mail regarding this never ending case.
The answer of who ever it is called black swan is discusting and humiliating and make me and other people to get as far far as possible from people like you.
What is this blah, blah................please behave yourself
Please don't answer just think..........
Re: insert select in PLSQL [message #595568 is a reply to message #595556] Wed, 11 September 2013 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58903
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
doron.simchoni wrote on Wed, 11 September 2013 20:55
No, I do not agree with it.
there are two (or more....) options:
first is using sql_block using bind variables and execute immediate using the sql_block.
Second, create a stmt with the changeable variables concatenate to it and execute immediate stmt.
Checked both with explain plan and find out the second one is better so took it.
Most probable that in other cases it can be the first


As BlackSwan said, it is just blablabla, we don't know what you are talking about.
Performances of YOUR statement are not the only point, overall performances is a must important point. Without speaking about security.
Post code as I asked you and we can talk about some real thing not on a void.

Regards
Michel

Re: insert select in PLSQL [message #595589 is a reply to message #595556] Thu, 12 September 2013 02:04 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
doron.simchoni wrote on Wed, 11 September 2013 20:55
No, I do not agree with it.
there are two (or more....) options:
first is using sql_block using bind variables and execute immediate using the sql_block.
Second, create a stmt with the changeable variables concatenate to it and execute immediate stmt.
Checked both with explain plan and find out the second one is better so took it.
Most probable that in other cases it can be the first

Although you do not care for any help, I would add the third option: use static SQL including the name of "dynamic" variable (the same name as in binding). If it has the same name as any column in table A (or any other table/view involved in the query), rename it.

As you did not post any relevant clues which could approve/disprove my assumptions (it is just some kind of gobbledygook; we do speak SQL, is it so hard to create dummy table to demonstrate your issue? you already used table/column names A/B/C/D), this is all I can say.

People here tend to give precise answers, however they are limited to the detailness of questions. As your question is very vague and you refuse to precise it, all you will get are vague answers.

You seem to have found some (not optimal for performance) solution. Good luck, enjoy it.
Previous Topic: Next multiple of 10 value for the given integer
Next Topic: dbms_lob.substr
Goto Forum:
  


Current Time: Wed Aug 27 08:39:45 CDT 2014

Total time taken to generate the page: 0.09013 seconds