Home » SQL & PL/SQL » SQL & PL/SQL » FORALL statement not recognized in sqlplus
FORALL statement not recognized in sqlplus [message #299296] Mon, 11 February 2008 05:05 Go to next message
kavyak@techmahindra.com
Messages: 3
Registered: October 2007
Junior Member
Hi Experts,

when i was exploring FORALL in oracle i got the following error
I executed a sample block as follows.

BEGIN
FORALL i IN 1 ..10
dbms_output.put_line('hi');
END;

ORA-06550: line 3, column 2:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the following:

* & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
The symbol "*" was substituted for "DBMS_OUTPUT" to continue.
ORA-06550: line 3, column 28:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( * % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute save merge
Re: FORALL statement not recognized in sqlplus [message #299299 is a reply to message #299296] Mon, 11 February 2008 05:21 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
FORALL is used in conjunction with DML.
Re: FORALL statement not recognized in sqlplus [message #299307 is a reply to message #299296] Mon, 11 February 2008 06:27 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
As Pablolee said,

the best use of all is

One e.g.,

if u want to insert into multiple tables then "ALL" will be useful.

insert ALL
followed by the statements...
Re: FORALL statement not recognized in sqlplus [message #299309 is a reply to message #299307] Mon, 11 February 2008 06:33 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
The op is talking about FORALL (i.e. in the context of BULK processing in PL/SQL) not INSERT ALL (in the context of multi table inserts in SQL)
Re: FORALL statement not recognized in sqlplus [message #299328 is a reply to message #299307] Mon, 11 February 2008 08:02 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ramanajv1968 wrote on Mon, 11 February 2008 07:27

One e.g.,

if u want to insert into multiple tables then "ALL" will be useful.

insert ALL
followed by the statements...



I am waiting for an answer from you that is appropriate for the question or does not just repeat what others have already said.
Re: FORALL statement not recognized in sqlplus [message #299331 is a reply to message #299296] Mon, 11 February 2008 08:28 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
@joy_division,

Yes,my answer was absolutely out of context .Instead of reading the actual post and giving the answer, I commented on Pablolee comment and write down some thing irrelevant to the situation.

That's were I went wrong Embarassed

Re: FORALL statement not recognized in sqlplus [message #299334 is a reply to message #299331] Mon, 11 February 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As for this one: http://www.orafaq.com/forum/mv/msg/97048/299317/102589/#msg_299317 and many others of your posts.
Please think about what you post BEFORE posting.
Also read the whole topic and don't (badly) repeat the same answer.

Regards
Michel
Re: FORALL statement not recognized in sqlplus [message #299336 is a reply to message #299296] Mon, 11 February 2008 08:47 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
hmm, less than 8 times repeatation taken place.
Actually,by the time I am ready with the solution some one else will post the answer (always little late on those repeated answer).But, I had put my efforts and I posted them.

will try to over come in the next post

[Updated on: Mon, 11 February 2008 08:49]

Report message to a moderator

Re: FORALL statement not recognized in sqlplus [message #299422 is a reply to message #299299] Tue, 12 February 2008 00:37 Go to previous messageGo to next message
kavyak@techmahindra.com
Messages: 3
Registered: October 2007
Junior Member
Thanks for your excellet reply.

One more question,
1. can i execute statements other than DML ? if not any other alternative
2.In my insert statement i am using sequence for primary key column value simultaenoulsy i have to store its value in an varray and need to increment the counter variable ,how will i achieve the same ?

My problem is ........ for 301000 insert statement it is taking 1o hours.have improve performance .

Regards,
Kavya

Re: FORALL statement not recognized in sqlplus [message #299426 is a reply to message #299422] Tue, 12 February 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. No. Use FOR ... LOOP and not FORALL
2. Use RETURNING INTO clause of insert statement.

Regards
Michel
Re: FORALL statement not recognized in sqlplus [message #299448 is a reply to message #299422] Tue, 12 February 2008 01:37 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
2. What Insert statement. Your code shows no insert statement
Re: FORALL statement not recognized in sqlplus [message #299451 is a reply to message #299448] Tue, 12 February 2008 01:57 Go to previous messageGo to next message
kavyak@techmahindra.com
Messages: 3
Registered: October 2007
Junior Member
Hi,

My procedure is as follows,in one procedure call the cursor may fetch more than 2000 records and altogether at times 3 lakh insert may happen.
PROCEDURE proc_orders(p_id NUMBER, id NUMBER, va_new_person_id type_va_numbers,
                      va_new_meeting_id type_va_numbers, va_new_orders_id OUT type_va_numbers)
 IS
  CURSOR cur_orders IS SELECT * FROM orders_test WHERE meeting_id = id;
  CURSOR cur_orders_misc IS SELECT * FROM orders_test WHERE meeting_id = 0 AND person_id = p_id;-- orders_misc
  n_ord_id NUMBER;
  n_ord_seq NUMBER;
  n_ord_cntr NUMBER := 1;
  v_code VARCHAR2(10);
  v_errm VARCHAR2(100);
  type ty_orders is table of orders_test%ro
 BEGIN
  

  IF n_order_flag = 0 THEN
   FOR k IN cur_orders_misc 
   LOOP
    va_new_orders_id := type_va_numbers();
    -- loop iterates for new person_id times to populate records into orders table
	FOR n_ord_misc_lcntr IN 1..va_new_person_id.COUNT
    LOOP
     SELECT seq_orders.NEXTVAL INTO n_ord_seq FROM dual;
     va_new_orders_id.extend;
     va_new_orders_id(n_ord_cntr) := n_ord_seq;
     n_ord_cntr := n_ord_cntr+1;
     n_ord_id := k.order_id;
     INSERT INTO orders_test VALUES(    
     n_ord_seq, k.order_info,k.order_description);
    END LOOP;
    n_order_flag := 1; 
    proc_log('orders',n_ord_id,'nil', n_ord_cntr-1);
    proc_order_detail(n_ord_id, va_new_orders_id);
    n_ord_cntr := 1;
   END LOOP;
  END IF;
  FOR i IN cur_orders
  LOOP 
   va_new_orders_id := type_va_numbers();
   -- loop iterates for new encountere_id times to populate records into orders table
   FOR n_ord_lcntr IN 1..va_new_meeting_id.COUNT
   LOOP
    SELECT seq_orders.NEXTVAL INTO n_ord_seq FROM dual;
    va_new_orders_id.extend;
    va_new_orders_id(n_ord_cntr) := n_ord_seq;
    n_ord_cntr := n_ord_cntr+1;
    n_ord_id := i.order_id;
    INSERT INTO orders_test VALUES(    
     n_ord_seq, i.order_info,i.order_description);   
   END LOOP;
   proc_log('orders',n_ord_id,'nil', n_ord_cntr-1);
   proc_order_detail(n_ord_id, va_new_orders_id);
   n_ord_cntr := 1;
  END LOOP;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
   v_code := SQLCODE;
   v_errm := SQLERRM;
   proc_errlog('orders',n_ord_id,v_code,v_errm);
  WHEN OTHERS THEN
   v_code := SQLCODE;
   v_errm := SQLERRM;
   proc_errlog('orders',n_ord_id,v_code,v_errm);
 END proc_orders;

Regards,

Kavya

[EDITED by LF: added [code] tags to improve readability]

[Updated on: Tue, 12 February 2008 02:28] by Moderator

Report message to a moderator

Re: FORALL statement not recognized in sqlplus [message #299452 is a reply to message #299422] Tue, 12 February 2008 02:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
kavyak@techmahindra.com wrote on Tue, 12 February 2008 07:37
1. can i execute statements other than DML
EXECUTE IMMEDIATE is allowed too, but I don't encourage its use. Here's a small example:
DECLARE
   TYPE t_tab IS TABLE OF NUMBER;

   v_tab   t_tab;
BEGIN
   -- fill my array
   SELECT     LEVEL
   BULK COLLECT INTO v_tab
   FROM       DUAL
   CONNECT BY LEVEL < 11;
   
   -- use a bulk loop:
   FORALL i IN 1..v_tab.COUNT
     EXECUTE IMMEDIATE 'Begin dbms_output.put_line(''Hi ''||:a);End;' Using v_tab(i);
END;
Michel has already hinted about fetching the values from the insert.

MHE
Re: FORALL statement not recognized in sqlplus [message #299453 is a reply to message #299451] Tue, 12 February 2008 02:06 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
This bears no resemblance to the original question. Please restate EXACTLY what your question is. But before you do, please read and follow The posting guidelins especially the formatting bit.
Cheers
Previous Topic: Subscript beyond count
Next Topic: Running Total
Goto Forum:
  


Current Time: Thu Dec 08 10:47:31 CST 2016

Total time taken to generate the page: 0.27359 seconds