Skip to Main Content
  • Questions
  • FORALL insert giving PLS-00382: expression is of wrong type

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Anirban.

Asked: February 03, 2005 - 1:30 pm UTC

Last updated: December 25, 2005 - 2:39 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

I am trying a simple FORALL Insert using this piece
of code. I am getting a PL/SQL Error

PLS-00382: expression is of wrong type while doing the insert.
Can't figure out why?

Can you help?



declare
cursor c is
select * from t1;

type t_type is table of t1%ROWTYPE;
l_t_type t_type;
begin

for i in 1..1000
loop
l_t_type.extend;
l_t_type(l_t_type.last).f1 := 1;
l_t_type(l_t_type.last).f2 := 'AB';
end loop;

forall i in l_t_type.first..l_t_type.last
insert into t1
values (l_t_type(i));
end;
/


and Tom said...

You are just ahead of your time is all. In 9i:

ops$tkyte@ORA9IR2> declare
2 cursor c is
3 select * from t1;
4
5 type t_type is table of t1%ROWTYPE;
6 l_t_type t_type := t_type();
7 begin
8
9 for i in 1..1000
10 loop
11 l_t_type.extend;
12 l_t_type(l_t_type.last).f1 := 1;
13 l_t_type(l_t_type.last).f2 := 'AB';
14 end loop;
15
16 forall i in l_t_type.first..l_t_type.last
17 insert into t1
18 values l_t_type(i);
19 end;
20 /

PL/SQL procedure successfully completed.


that works.. however in 8i, insert using a RECORD (and update using a RECORD) is a feature that did not exist.


You can use a record of ARRAYS, but not an ARRAY of records in 8i.





Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ANYDATA

Yuan, March 04, 2005 - 3:45 pm UTC

I'm trying to store different record types into one column on a table. I made the column ANYDATA type. I made a procedure that will populate this column accepting a paramter of the column type. The problem arises when I call this procedure, passing my record types. I get:

PLS-00306: wrong number or types of arguments in call to 'LOG_BAD_REC'

I tried doing:

CAST(pirDlvrRec AS ANYDATA)

and get:

PLS-00382: expression is of wrong type

instead.

What am I doing wrong? Sorry for not giving more info and sample code. As I mentioned in another post, I'm under the gun right now.

Tom Kyte
March 04, 2005 - 6:15 pm UTC

You would need to use anytype, anydata is for scalar types.

I cannot fathom using it though.

under the gun? you are in design phase here... but anyway.

AnyData Continued

Yuan, March 04, 2005 - 9:38 pm UTC

Thanks! I'll try it on Monday.

As for being under the gun, I got pulled onto this project Monday/Tuesday of this week. Project plan says goes to QA this coming Tuesday. My design phase is my development phase, unfortunately.

Tried ANYTYPE

Yuan, March 07, 2005 - 8:33 am UTC

Getting this compilation error: PLS-00382: expression is of wrong type

Here is where the code:
...
PROCEDURE Proc_Orig_Rec(pivLeadID ESL_Lead.Lead_ID%TYPE,
pirOrigRec gr_ORIG_REC_TYP,
pidOrigFailDt Dlvr_Fail_Rec.Orig_Fail_Dt%TYPE := NULL) IS

lnStsID ESL_Lead_Status.Status_ID%TYPE;

PROCEDURE Log_Bad_Orig_Rec(pivDescr Dlvr_Fail_Rec.Descr%TYPE) IS
BEGIN
Log_Bad_Rec(pivLeadID, CAST(pirOrigRec AS ANYTYPE), pidOrigFailDt, pivDescr); -- ERROR ON THIS LINE
END Log_Bad_Orig_Rec;
...

gr_ORIG_REC_TYP is one of 3 (currently) types I want to insert into the same column. They are declared in the package spec that this proc resides in:
...
TYPE gr_ORIG_REC_TYP IS RECORD (TrkNbr ESL_Sales.Shipment_Tracking_Number%TYPE);
TYPE gr_DLVR_REC_TYP IS RECORD (Pay ESL_Sales.Payment_Made%TYPE,
DlvrDt ESL_Sales.Delivery_Date%TYPE);
TYPE gr_EXCP_REC_TYP IS RECORD (Descr ESL_Sales.Agent_Comments%TYPE,
Resolution ESL_Sales.Agent_Comments%TYPE,
Dt DATE);
...

Here is Log_Bad_Rec, which is also in the same package:
...
PROCEDURE Log_Bad_Rec(pivLeadID ESL_Lead.Lead_ID%TYPE,
pirRec Dlvr_Fail_Rec.Rec%TYPE,
pidOrigFailDt Dlvr_Fail_Rec.Orig_Fail_Dt%TYPE := NULL,
pivDescr Dlvr_Fail_Rec.Descr%TYPE) IS

BEGIN
INSERT INTO Dlvr_Fail_Rec (Lead_ID, Orig_Fail_Dt, Rec, Descr)
VALUES (pivLeadID, pidOrigFailDt, pirRec, pivDescr);
EXCEPTION
WHEN OTHERS THEN
gnDummy := LD.Capture_Error(SQLCODE, SQLERRM, 'ESL_Info.Set_Sts');
RAISE;
END Log_Bad_Rec;
...

And finally, here is the table I want the records inserted into:
CREATE TABLE Dlvr_Fail_Rec (Fail_Rec_ID INTEGER,
Lead_ID VARCHAR2(10) NOT NULL,
Cre_Dt DATE DEFAULT SYSDATE NOT NULL,
Orig_Fail_Dt DATE NOT NULL,
Rec ANYTYPE NOT NULL,
Descr VARCHAR2(1000))

Not sure you need all this to fix the problem, as it seems to be a matter of changing my CAST statement somehow, but if you can see a better way to accomplish what I want, please feel free. Thanks in advance!

Tom Kyte
March 07, 2005 - 2:59 pm UTC

it is not going to work for plsql records types -- they are private solely to plsql.


it can work with object types only - and you'll want to peek at the supplied packages guide and other docs (goto otn.oracle.com, documentation and search for anytype in the docs)

ANYTYPE Continued

Yuan, March 08, 2005 - 10:29 am UTC

I tried using objects and get the same error.

ESL-CHATDV>CREATE OR REPLACE TYPE OBJ_ORIG_REC AS
2 OBJECT (TrkNbr VARCHAR2(35))
3 /

Type created.

ESL-CHATDV>CREATE OR REPLACE TYPE OBJ_DLVR_REC AS
2 OBJECT (Pay NUMBER(7,2),
3 DlvrDt DATE)
4 /

Type created.

ESL-CHATDV>CREATE OR REPLACE TYPE OBJ_EXCP_REC AS
2 OBJECT (Descr VARCHAR2(4000),
3 Resolution VARCHAR2(4000),
4 Dt DATE)
5 /

Type created.

CREATE OR REPLACE PACKAGE BODY Pkg_Dlvr_File IS
...
PROCEDURE Proc_Orig_Rec(pivLeadID ESL_Lead.Lead_ID%TYPE,
piobjOrigRec OBJ_ORIG_REC,
pidOrigFailDt Dlvr_Fail_Rec.Orig_Fail_Dt%TYPE := NULL) IS

lnStsID ESL_Lead_Status.Status_ID%TYPE;

PROCEDURE Log_Bad_Orig_Rec(pivDescr Dlvr_Fail_Rec.Descr%TYPE) IS
BEGIN
Log_Bad_Rec(pivLeadID, piobjOrigRec, pidOrigFailDt, pivDescr); -- Uncast call gets PLS-00306: wrong number or types of arguments in call to 'LOG_BAD_REC'
NULL;
END Log_Bad_Orig_Rec;

BEGIN
...
END Proc_Orig_Rec;
...
PROCEDURE Proc_Dlvr_Rec(pivLeadID ESL_Lead.Lead_ID%TYPE,
piobjDlvrRec OBJ_DLVR_REC,
pidOrigFailDt Dlvr_Fail_Rec.Orig_Fail_Dt%TYPE := NULL) IS

lnStsID ESL_Lead_Status.Status_ID%TYPE;

PROCEDURE Log_Bad_Dlvr_Rec(pivDescr Dlvr_Fail_Rec.Descr%TYPE) IS
BEGIN
Log_Bad_Rec(pivLeadID, CAST(piobjDlvrRec AS ANYTYPE), pidOrigFailDt, pivDescr); -- Cast call gets PLS-00382: expression is of wrong type
NULL;
END Log_Bad_Dlvr_Rec;

BEGIN
...
END Proc_Dlvr_Rec;
...
PROCEDURE Log_Bad_Rec(pivLeadID ESL_Lead.Lead_ID%TYPE,
piobjRec Dlvr_Fail_Rec.Rec%TYPE,
pidOrigFailDt Dlvr_Fail_Rec.Orig_Fail_Dt%TYPE := NULL,
pivDescr Dlvr_Fail_Rec.Descr%TYPE) IS

BEGIN
INSERT INTO Dlvr_Fail_Rec (Lead_ID, Orig_Fail_Dt, Rec, Descr)
VALUES (pivLeadID, pidOrigFailDt, piobjRec, pivDescr);
EXCEPTION
WHEN OTHERS THEN
gnDummy := LD.Capture_Error(SQLCODE, SQLERRM, 'ESL_Info.Set_Sts');
RAISE;
END Log_Bad_Rec;
...
END Pkg_Dlvr_File;

The documentation actually says:

<quote>
Oracle has three special SQL datatypes that enable you to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type, including object and collection types. You can also use these three special types to create anonymous (that is, unnamed) types, including anonymous collection types. The types are SYS.ANYTYPE, SYS.ANYDATA, and SYS.ANYDATASET. The SYS.ANYDATA type can be useful in some situations as a return value from table functions.
</quote>

Since it specifically talked about collection types, I also tried making table types of my record types. That got the same error as well.

Tom Kyte
March 08, 2005 - 11:33 am UTC

have you read the docs on anytype and how to use the constructors and how to use this?

ANYDATA.CONVERTOBJECT()

Yuan, March 09, 2005 - 12:17 pm UTC

I did indeed do the search of the documentation that you recommended, but did not find anything to help me. I googled some more and stumbled upon ANYDATA.CONVERTOBJECT(), which got me a step closer. I searched OTN's documentation for ANYDATA.CONVERTOBJECT() and only found results for 10g even though it works perfectly fine in 9.2.

Anyway, I went back to using ANYDATA rather than ANYTYPE, then I wrapped ANYDATA.CONVERTOBJECT() around the objects in my procedure call and it compiled fine, but got a run time error. I think it was got non-numeric when expecting numeric.

I was able to get past that and accomplish what I wanted by giving up on the generic Log_Bad_Rec procedure. It appears to me that the problem (in 9.2 anyway) is that ANYDATA type parameters do not handle ANYDATA.CONVERTOBJECT() passed objects correctly.

i am getting error " PLS-00306: wrong number or types of arguments in call to

SUBBU, June 02, 2005 - 5:25 am UTC

CREATE OR REPLACE PACKAGE BODY HAI
IS

PROCEDURE PROC_ADD_HEADER(pApplicationUser VARCHAR2,pTaskName VARCHAR2,pStartTime DATE,pEndTime DATE)
IS

l_db_version dba_group.schema_version_t.version%TYPE;

FUNCTION curDbVersion(pSCHEMANAME VARCHAR2) RETURN VARCHAR2
IS
l_version dba_group.schema_version_t.version%TYPE;

BEGIN

SELECT
sv.VERSION INTO l_version
FROM dba_group.schema_version_t sv
WHERE
UPPER(sv.SCHEMA_NAME)=UPPER(pSCHEMANAME);

RETURN l_version ;

END curDbVersion;

BEGIN

l_db_version:=curDbVersion(pApplicationUser);

/* TASK DATA */

gTaskData.AppName := pApplicationUser;
gTaskData.AppVersion := l_db_version;
gTaskData.TaskName := pTaskName;
gTaskData.StartTime := pStartTime;
gTaskData.EndTime := pEndTime;
gTaskData.Comments := 'DATA';
gTaskData.DataPoints := NULL;

END PROC_ADD_HEADER;


PROCEDURE PROC_ADD_DATA_POINTS(pNAME VARCHAR2,pVALUE VARCHAR2,pTYPE VARCHAR2)
IS

BEGIN
----Detail Information

gDataPoint:=NULL;

gDataPoint.Name :=pName;
gDataPoint.Value :=pValue ;
gDataPoint.TYPE :=pType;

IF CountDP=1 then

gDataPoints:=DATAPOINTSARRAY(gDataPoint);

END IF;

gDataPoints.EXTEND(1);

gDataPoints(CountDP):=gDataPoint;

CountDP:=CountDP+1;

dbms_output.put_line(gDataPoints(CountDP).Name||gDataPoints(CountDP).Value||gDataPoints(CountDP).TYPE);

END PROC_ADD_DATA_POINTS;

PROCEDURE PROC_CALL_PKG
IS

vresult BOOLEAN;

lDataPoints DATAPOINTSARRAY;

BEGIN



gTaskData.DataPoints:=gDataPoints;

gTaskData.DataPoints.TRIM;


dbms_output.put_line(gTaskData.DataPoints.Last);

dbms_output.put_line(gTaskData.DataPoints.Count);



FOR i IN 1..gTaskData.DataPoints.LAST LOOP


dbms_output.put_line(gTaskData.DataPoints(i).Name||'" value="'||gTaskData.DataPoints(i).Value||'" type="'||gTaskData.DataPoints(i).TYPE);


END LOOP;


/* HERE I AM GETTING THE ABOVE ERROR HERE (COULD YOU PLZ RECTIFY IT PLZ*/

vresult := SYSTEMEKG.SEC.SEND_TO_EKG(gTaskData);

IF vresult THEN

DBMS_OUTPUT.PUT_LINE('SUCCESS!');

ELSE

DBMS_OUTPUT.PUT_LINE('FAILURE!');

END IF;


CountDP:=1;


END PROC_CALL_PKG;

END HAI;
/


Tom Kyte
June 02, 2005 - 4:37 pm UTC

make the example really small -- it should require, oh -- about 2 lines of code to reproduce.

and then you'll probably see the error.

see the problem is, your code won't even compile on my system and I'm not a plsql parsing compiler.

PLS-00306

Richard Young, July 21, 2005 - 10:43 am UTC

create table employee
( employee_id number(5),
last_name varchar2(30),
salary number(7,2)
);

insert into employee values (1,'Bert',10);
insert into employee values (2,'Fred',20);
commit;


create or replace package p1
as
procedure test;
function new_compensation (e_rec IN employee%rowtype) return employee.salary%type;
end;
/

create or replace package body p1
as
function new_compensation (e_rec IN employee%rowtype) return employee.salary%type
is
begin
return 50;
end new_compensation;

procedure test is
myname employee.last_name%type;
mysal employee.salary%type;
rec1 employee%ROWTYPE;
test1 employee.salary%type;
begin
for rec in (select * from employee)
loop
rec1 := rec;
update employee
set salary = new_compensation(rec)
where employee_id = rec.employee_id
returning salary, last_name
into mysal, myname;

dbms_output.put_line('New salary for ' || myname || ' = ' || mysal);
end loop;
test1 := new_compensation(rec1);
dbms_output.put_line(test1);
end test;
end p1;
/
show err
18/8 PL/SQL: SQL Statement ignored
19/28 PLS-00306: wrong number or types of arguments in call to
'NEW_COMPENSATION'

19/28 PL/SQL: ORA-00904: "P1"."NEW_COMPENSATION": invalid identifier
19/45 PLS-00382: expression is of wrong type

I thought 'rec' is of employee%Rowtype so I can't figure out
why I'm getting the message



Tom Kyte
July 21, 2005 - 4:28 pm UTC

You can only bind SQL types in SQL

you cannot pass the "record" like that.



ops$tkyte@ORA9IR2> create or replace package p1
  2  as
  3    procedure test;
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body p1
  2  as
  3    cursor c1 is select rowid rid, x.* from employee x;
  4
  5    function new_compensation (e_rec IN c1%rowtype) return employee.salary%type
  6    is
  7    begin
  8      return 50;
  9    end new_compensation;
 10
 11    procedure test
 12    is
 13      type RecArray is table of c1%rowtype index by binary_integer;
 14      type RidArray is table of rowid index by binary_integer;
 15      type SalArray is table of employee.salary%type index by binary_integer;
 16
 17      l_data recArray;
 18      l_rid  ridArray;
 19      l_sal  salArray;
 20    begin
 21        open c1;
 22      loop
 23          fetch c1 bulk collect into l_data limit 100;
 24          for i in 1 .. l_data.count
 25          loop
 26              l_sal(i) := new_compensation( l_data(i) );
 27              l_rid(i) := l_data(i).rid;
 28          end loop;
 29          forall i in 1 .. l_data.count
 30              update employee set salary = l_sal(i) where rowid = l_rid(i);
 31          exit when c1%notfound;
 32      end loop;
 33      close c1;
 34    end test;
 35  end p1;
 36  /
 
Package body created.
 
ops$tkyte@ORA9IR2> exec p1.test
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from employee;
 
EMPLOYEE_ID LAST_NAME                          SALARY
----------- ------------------------------ ----------
          1 Bert                                   50
          2 Fred                                   50
 

Thanks very much

Richard Young, July 22, 2005 - 5:53 am UTC


Anytype vs. Anydata

Michael Friedman, December 25, 2005 - 12:07 pm UTC

Tom Says:
---------------
Followup:
You would need to use anytype, anydata is for scalar types.

------------------------

That is actually not correct. ANYTYPE represents any datatype. Not a piece of data of any datatype but any datatype.

ANYDATA can also be used for collections. There is a constructor method - ConvertCollection - that is specifically for that.

---

Now, on to my question.

We use a pre-compiler on our PL/SQL. One thing we want to do is to generate tracing code that can store the contents of any variable in a DB table and then recover it.

We can make this work pretty easily with all object types and standard types (ie. Number, Varchar2, etc) but we can't figure out how to do it with internal types like records or integer indexed arrays. IS there any way to figure out how to store a variable into an ANYDATA based just on the variable's name and its datatype's name? Once you do that how can you recover the contents of the ANYDATA and print them if the original type is no longer in scope?

On a related note, how can you recover the contents of an ANYDATA that was created from an Object if the original object no longer exists or has been changed?



Tom Kyte
December 25, 2005 - 2:39 pm UTC

using anydata in that fashion will be somewhat "ugly" and it'll work with SQL types (not plsql types). index by arrays, records - those are plsql types.

I haven't explored anydata beyond simple scalars. Ugly doesn't begin to describe what it ends up looking like.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library