Home » SQL & PL/SQL » SQL & PL/SQL » using forall with rowtype in collection
using forall with rowtype in collection [message #189005] Tue, 22 August 2006 14:31 Go to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

My code is having some error can any one help me.

Thanks

Employees table is table as in HR user
employees2 is duplicated table of employees with no data


SQL> CREATE OR REPLACE PROCEDURE fast_way IS
2
3 TYPE myarray IS TABLE OF EMPLOYEES%ROWTYPE;
4 l_data myarray;
5
6 CURSOR r IS
7 SELECT EMPLOYEE_ID,
8 FIRST_NAME ,
9 LAST_NAME ,
10 EMAIL ,
11 PHONE_NUMBER ,
12 HIRE_DATE ,
13 JOB_ID ,
14 SALARY ,
15 COMMISSION_PCT,
16 MANAGER_ID ,
17 DEPARTMENT_ID FROM EMPLOYEES;
18
19 BEGIN
20 OPEN r;
21 LOOP
22 FETCH r BULK COLLECT INTO l_data LIMIT 1000;
23
24 FORALL i IN 1..l_data.COUNT
25 INSERT INTO EMPLOYEES2 VALUES l_data(i);
26
27 EXIT WHEN r%NOTFOUND;
28 END LOOP;
29 COMMIT;
30 CLOSE r;
31 END fast_way;
32 /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.00
SQL> SHOW ERROR
Errors for PROCEDURE FAST_WAY:

LINE/COL|ERROR
--------|-----------------------------------------------------------------
25/5 |PL/SQL: SQL Statement ignored
25/35 |PL/SQL: ORA-00906: missing left parenthesis
SQL>
Re: using forall with rowtype in collection [message #189015 is a reply to message #189005] Tue, 22 August 2006 15:19 Go to previous messageGo to next message
tongucy
Messages: 8
Registered: August 2006
Junior Member
what is your database version?
Re: using forall with rowtype in collection [message #189117 is a reply to message #189015] Wed, 23 August 2006 04:50 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

9.0.1
Re: using forall with rowtype in collection [message #189129 is a reply to message #189117] Wed, 23 August 2006 05:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that this is version related.
I'm on 9.2.0.7 and it works just fine for me.
Re: using forall with rowtype in collection [message #189132 is a reply to message #189129] Wed, 23 August 2006 05:31 Go to previous message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,
Thanks
It Does not work with 9.0.1
but fine with 9.2

Thanx again
Previous Topic: how to create database in sql. and how to create tables under different database
Next Topic: SQL QUERY USING DECODE FUNCTION
Goto Forum:
  


Current Time: Sat Dec 03 01:01:17 CST 2016

Total time taken to generate the page: 0.20909 seconds