FORALL statement not recognized in sqlplus [message #299296] |
Mon, 11 February 2008 05:05 |
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 #299328 is a reply to message #299307] |
Mon, 11 February 2008 08:02 |
joy_division
Messages: 4963 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 #299422 is a reply to message #299299] |
Tue, 12 February 2008 00:37 |
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 #299451 is a reply to message #299448] |
Tue, 12 February 2008 01:57 |
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
|
|
|
|
|