Home » SQL & PL/SQL » SQL & PL/SQL » can we place insert statement in loop inside anonymous block? (Oracle PL/SQL)
can we place insert statement in loop inside anonymous block? [message #571380] Sun, 25 November 2012 04:27 Go to next message
BlackHorse
Messages: 2
Registered: November 2012
Location: Pune
Junior Member
can we place insert statement in loop inside anonymous block?

CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10));

DECLARE

I NUMBER(5);

BEGIN

  I := 0;

LOOP

  INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID);

  I := I+1;

  EXIT WHEN I = 5;
  
  END LOOP;
  
  END;

[Updated on: Sun, 25 November 2012 04:39]

Report message to a moderator

Re: can we place insert statement in loop inside anonymous block? [message #571382 is a reply to message #571380] Sun, 25 November 2012 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
Why don't you just try your code?

Regards
Michel

[Updated on: Sun, 25 November 2012 04:58]

Report message to a moderator

Re: can we place insert statement in loop inside anonymous block? [message #571384 is a reply to message #571380] Sun, 25 November 2012 06:04 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
BlackHorse wrote on Sun, 25 November 2012 11:27
can we place insert statement in loop inside anonymous block?

INSERT within a LOOP, yes, it is possible.
But what is not possible is the way you try to prompt the user to enter corresponding values within the loop (PL/SQL is executed at server side).
All you need to do is running your code and see the result.


Regards,
Dariyoosh

[Updated on: Sun, 25 November 2012 06:14]

Report message to a moderator

Re: can we place insert statement in loop inside anonymous block? [message #571387 is a reply to message #571380] Sun, 25 November 2012 06:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
Yes, but you will not get what you are hoping for. Substitution variables (&DEPTID, &DNAME and &LOCID in your case) are SQL*Plus, not PL/SQL variables and are substituted once only before compiling PL/SQL code. So if you expect you will be prompted for &DEPTID, &DNAME and &LOCID 5 times, it will not happen. You will be prompted once only and your code will insert 5 rows with same DEPTID, DNAME, LOCID values.

SY.

[Updated on: Sun, 25 November 2012 06:58]

Report message to a moderator

Re: can we place insert statement in loop inside anonymous block? [message #571398 is a reply to message #571387] Sun, 25 November 2012 09:48 Go to previous messageGo to next message
BlackHorse
Messages: 2
Registered: November 2012
Location: Pune
Junior Member
Thanks all for replies and valuable feedback.

However, I found Solomon's reply more precise and informative. Thanks Solomon.

When I ran above code, I got below error:


[EDITED by LF: removed image from the message body as it is too wide for what it is supposed to display (a tiny error message box)]
  • Attachment: Untitled.jpg
    (Size: 36.43KB, Downloaded 69 times)

[Updated on: Sun, 25 November 2012 12:46] by Moderator

Report message to a moderator

Re: can we place insert statement in loop inside anonymous block? [message #571399 is a reply to message #571398] Sun, 25 November 2012 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
duplicates values result in ORA-00001 error

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: can we place insert statement in loop inside anonymous block? [message #571400 is a reply to message #571398] Sun, 25 November 2012 10:30 Go to previous message
Michel Cadot
Messages: 59154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I found Solomon's reply more precise and informative


But Solomon answered a question you did not ask.
And from Solomon's answer you should now know why you get this error.

Regards
Michel
Previous Topic: Reversing the column name
Next Topic: how to fetch two times the same explicit cursor:first make a total,second divisions by total
Goto Forum:
  


Current Time: Mon Sep 22 06:09:28 CDT 2014

Total time taken to generate the page: 0.13786 seconds