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.
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!
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.
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;
/
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
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?
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.