SQL & PL/SQL

Hi to all,

Oracle DB 12.2.0.1 on Linux 7

I am trying to store any data in anydata (not yet in database) and later, elsewhere in my program (other proc, other package)  get the structure of the anydata.

Here is my very simple usecase :

declare

  p_anydata sys.anydata; 

  p_anytype sys.anytype;

  i number(10,2);

  typecode number;

  typename varchar2(2048);

  t varchar2(32) := 'vvvvvvvv';

begin

  i := 4;

  p_anydata := sys.anydata.ConvertNumber (i);

  typecode := p_anydata.gettype (p_anytype);

  typename := sys.anydata.gettypename (p_anydata);

  dbms_output.put_line ('typecode=' || typecode);

  dbms_output.put_line ('typename=' || typename);

  if (p_anytype is null) then dbms_output.put_line ('p_anytype is null, getting info on length, scale, prec, ...  of the anydata is not possible as we need to use sys.anytype.getinfo (p_anytype)'); end if;

  p_anydata := sys.anydata.ConvertVarchar2 (t);

  typecode := p_anydata.gettype (p_anytype);

  dbms_output.put_line ('typecode=' || typecode);

  if (p_anytype is null) then dbms_output.put_line ('p_anytype is null, getting info on length, scale, prec, ... of the anydata is not possible as we need to use sys.anytype.getinfo (p_anytype)'); end if;

end;

/

and i get :

typecode=2

typename=SYS.NUMBER

p_anytype is null, getting info on length, scale, prec, ...  of the anydata is not possible as we need to use sys.anytype.getinfo (p_anytype)

typecode=9

p_anytype is null, getting info on length, scale, prec, ... of the anydata is not possible as we need to use sys.anytype.getinfo (p_anytype)

Refering to the doc (Oracle® Database Database PL/SQL Packages and Types Reference 12c Release 2 (12.2) E85717-03 March 2018)

It is Ok that p_anytype is null as the actual type of the anydata is number.

but i read also

278.3.5 GETTYPENAME Member Function

This function gets the fully qualified type name for the ANYDATA.

If the ANYDATA is based on a built-in type, this function will return NUMBER and other relevant information.

How can i get this relevant information ? What i am doing wrong as i am not able to get them ?

I would like to be able to use sys.anytype.getinfo for the type of the anydata i get.

Thank you very much for your enlightened answers.

Regards

Michel MN