Hi from france
Thank you for your clever help but if you do not know about object types and onject tables, just pass your way.
I am using 12c (12.1.0.2) on OEL 6.7 inside Virtual box
here is my use case
drop table envparams;
drop type envparam_obj;
drop table params;
drop type param_obj;
create or replace type param_obj as object
(
PName varchar2(32),
RegExp varchar2(128),
member function GetName return varchar2,
member function GetRegexp return varchar2,
member function ToString return varchar2,
member procedure Display
);
/
create or replace type body param_obj as
member function GetName return varchar2 is
begin
return (PName);
end;
member function GetRegExp return varchar2 is
begin
return (RegExp);
end;
member function ToString return varchar2 is
OutStr varchar2(1000);
begin
OutStr := 'Parameter ' || PName;
if (RegExp is not null) then OutStr := OutStr || ' uses control regexp (' || RegExp || ')'; end if;
return (OutStr || '.');
end ;
member procedure Display is
begin
dbms_output.put_line (self.ToString());
end ;
end;
/
create table params of param_obj (pname primary key) object identifier is primary key;
create unique index params_un01 on params (upper (pname));
insert into params values (new param_obj ('nls_lang', null));
select * from params;
select ref (p) rparam from params p where p.pname = 'nls_lang';
drop table envparams;
drop type envparam_obj;
create or replace type envparam_obj as object
(
paramref ref param_obj,
pvalue sys.anydata,
member function GetParamRef return ref param_obj,
member function GetPValue return sys.anydata,
member function ToString return varchar2,
member procedure Display
);
/
create or replace type body envparam_obj as
member function GetParamRef return ref param_obj is
begin
return (ParamRef);
end;
member function GetPValue return sys.anydata is
begin
return (PValue);
end;
member function ToString return varchar2 is
OutStr varchar2(200);
TypeCode pls_integer;
ValueType sys.anytype;
Num number;
Str varchar2(20000);
Dummy pls_integer;
begin
select deref (ParamRef).ToString() into OutStr from dual;
if (PValue is not null) then
TypeCode := PValue.GetType (ValueType);
case TypeCode
when dbms_types.typecode_number then
Dummy := PValue.GetNumber (Num);
Str := to_char(Num);
when dbms_types.typecode_varchar2 then
Dummy := PValue.GetVarchar2 (Str);
end case;
OutStr := OutStr || ' Value is (' || Str || ')';
else
OutStr := OutStr || ' Value is (NULL)';
end if;
return (OutStr || '.');
end;
member procedure Display is
begin
dbms_output.put_line (self.ToString());
null;
end ;
end;
/
create table envparams of envparam_obj;
create or replace procedure test as
ThisParamRef ref param_obj;
ThisParam param_obj;
ThisValue anydata;
ThisEnvParam envparam_obj;
begin
SELECT ref (p), value(p), anydata.convertvarchar2('FRENCH_FRANCE.WE8ISO8859P1') into ThisParamRef, ThisParam, ThisValue
from params p WHERE p.PName = 'nls_lang';
ThisEnvParam := envparam_obj (ThisParamRef, ThisValue);
ThisEnvParam.Display ();
INSERT INTO envparams
SELECT envparam_obj (ref (p), anydata.convertvarchar2('FRENCH_FRANCE.WE8ISO8859P1'))
FROM params p
WHERE p.PName = 'nls_lang';
end;
declare
begin
test;
end;
test is giving :
RA-22979: impossible d'insérer une REF d'une vue objet ou une REF définie par l'utilisateur (translation is below...)
ORA-06512: à "MMN.TEST", ligne 16
ORA-06512: à ligne 3
22979. 00000 - "cannot INSERT object view REF or user-defined REF"
*Cause: Attempt to insert an object view REF or user-defined REF in a
REF column created to store system generated REF values"
*Action: Make sure the REF to be inserted is not from an object view
or from a user-defined REF column**
if i modify test and write :
INSERT INTO envparams values (ThisEnvParam); ===> Get the same message
ORA-22979: impossible d'insérer une REF d'une vue objet ou une REF définie par l'utilisateur
ORA-06512: à "MMN.TEST", ligne 16
ORA-06512: à ligne 3
22979. 00000 - "cannot INSERT object view REF or user-defined REF"
*Cause: Attempt to insert an object view REF or user-defined REF in a
REF column created to store system generated REF values"
*Action: Make sure the REF to be inserted is not from an object view
or from a user-defined REF column
if i modify test and write
insert into envparams values (envparam_obj (ThisParamref, ThisValue));
; ===> Get the same message
ORA-22979: impossible d'insérer une REF d'une vue objet ou une REF définie par l'utilisateur
ORA-06512: à "MMN.TEST", ligne 16
ORA-06512: à ligne 3
22979. 00000 - "cannot INSERT object view REF or user-defined REF"
*Cause: Attempt to insert an object view REF or user-defined REF in a
REF column created to store system generated REF values"
*Action: Make sure the REF to be inserted is not from an object view
or from a user-defined REF column
WELL, i am stuck from the stuckhouse.
How can I make test work
Thanks again for reading me
Regards
Michel M-N