hi,
the oracle version I am working on is
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Nov 24 12:06:01 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
The code I am trying to run is
DECLARE
TYPE RecTabTyp IS TABLE OF tab1%ROWTYPE
INDEX BY BINARY_INTEGER;
TYPE NumTabTyp IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE CharTabTyp IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT col1, col2 FROM tab2;
rec_tab RecTabTyp;
num_tab NumTabTyp := NumTabTyp(2,5,8,9);
char_tab CharTabTyp := CharTabTyp('Tim', 'Jon', 'Beth', 'Jenny');
BEGIN
FORALL i IN 1..4
INSERT INTO tab1 VALUES(num_tab(i), char_tab(i));
SELECT col1, col2 BULK COLLECT INTO rec_tab FROM tab1
WHERE col1 < 9;
FORALL i IN rec_tab.FIRST..rec_tab.LAST
INSERT INTO tab2 VALUES rec_tab(i);
FOR i IN rec_tab.FIRST..rec_tab.LAST LOOP
rec_tab(i).col1 := rec_tab(i).col1 + 100;
END LOOP;
FORALL i IN rec_tab.FIRST..rec_tab.LAST
UPDATE tab1 SET (col1, col2) = rec_tab(i) WHERE col1 < 8;
OPEN c1;
FETCH c1 BULK COLLECT INTO rec_tab;
CLOSE c1;
END;
Its complaining about the following lines
-----------------------------------------------------------
UPDATE tab1 SET (col1, col2) = rec_tab(i) WHERE col1 < 8;
ERROR at line 4:
ORA-06550: line 27, column 38:
PL/SQL: ORA-01767: UPDATE ... SET expression must be a subquery
ORA-06550: line 27, column 7:
PL/SQL: SQL Statement ignored
------------------------------------------------------------
num_tab NumTabTyp := NumTabTyp(2,5,8,9);
char_tab CharTabTyp := CharTabTyp('Tim', 'Jon', 'Beth', 'Jenny');
num_tab NumTabTyp := NumTabTyp(2,5,8,9);
*
ERROR at line 10:
ORA-06550: line 10, column 26:
PLS-00222: no function with name 'NUMTABTYP' exists in this scope
ORA-06550: line 10, column 13:
PL/SQL: Item ignored
ORA-06550: line 11, column 27:
PLS-00222: no function with name 'CHARTABTYP' exists in this scope
ORA-06550: line 11, column 13:
PL/SQL: Item ignored
ORA-06550: line 14, column 31:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 14, column 31:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 14, column 7:
PL/SQL: SQL Statement ignored
---------------------------------------------------------------
the other thing is its not letting me initialize in the declaration
section
ex: rec_tab RecTabTyp := RecTabTyp();
rec_tab RecTabTyp := RecTabTyp();
*
ERROR at line 9:
ORA-06550: line 9, column 26:
PLS-00222: no function with name 'RECTABTYP' exists in this scope
ORA-06550: line 9, column 13:
PL/SQL: Item ignored
-----------------------------------------------------------------
What am I missing .. is it the version ???
Can somebody Please enlighten me as why the problems are occuring
and how do I fix them ?
thanks a lot for your help ,
Harish
