How to convert RECORD type parameter to XML or json in oracle?

user8326781

R_Al.op_record is a user defined RECORD type in oracle. I can not compile the following code. What I want is to convert the user defined RECORD type in to an xml or json without referring to its attributes one by one. Is this possible to do? Can we do this using dynamic sql?

The compilation error i get is .....Error: PLS-00306: wrong number or types of arguments in call to 'XMLTYPE'

TYPE op_record IS RECORD (
        operation_row_count           BINARY_INTEGER,
        operation_id_tab              number_type,
        operation_no_tab              number_type,
        operation_description_tab     char_50_type);

PROCEDURE Update_S(
       op_rec_ IN R_Al.op_record)
    IS
        v_xml   SYS.XMLTYPE;
        mytype R_Al.op_record;
    BEGIN
        mytype := op_rec_;
        v_xml := xmltype(mytype);
    END Update_S;

Solomon Yakobson

You need to show type definition. Anyway, something like:

SQL> CREATE OR REPLACE
  2    TYPE OP_RECORD
  3      AS OBJECT(
  4                TEXT CLOB
  5               )
  6  /


Type created.


SQL> CREATE OR REPLACE
  2    PROCEDURE Update_S(
  3                       op_rec_ IN op_record)
  4      IS
  5          v_xml  XMLTYPE;
  6          mytype op_record;
  7      BEGIN
  8          mytype := op_rec_;
  9          v_xml := xmltype(mytype.text);
 10      END Update_S;
 11  /


Procedure created.


SQL>

SY.

User_H3J7U

create type typex1 as object (
 n number,
 s varchar2(100 char)
);
/

select xmltype(typex1(123, 'abc')) x, json_object(typex1(123, 'abc')) j from dual;

X                                       J                  
---------------------------------------- --------------------
<TYPEX1><N>123</N><S>abc</S></TYPEX1>   {"N":123,"S":"abc"} 

user8326781

Sorry, its a user defined record type. Not an object type.

Paulzip

A record is a PL/SQL object, so as far as I know, it's not possible. It's not known to the SQL engine.

I don't understand why you don't construct it anyway? Can you explain why you don't want to? Generating XML from a record is trivial.

user8326781

I have hundreds of record types in several procedures as parameters and I want to see the actual values of the record type column during the runtime. I m hoping to log them by storing it in a xml or a clob and storing that xml or clob inside a table column. I m hoping to see the passed row type parameter values after a transaction is done. Is there a way to achieve this?

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16799340#Comment_16799340

You just answered your own question. The XMLType() constructor does not operate on PL/SQL objects. If you are going to convert your "records" into XML types, you would do much better to define the "record" type at the database level (that is: define an object type, not a PL/SQL record type).

Otherwise you will need to pass the values from your record type to an object type (or a ref cursor) you create just so that you can call XMLType() on it (or parse the record, which you said you don't want to do). That doesn't make much sense to me.

Note that, separate from all of this, you didn't explain the member data types. There are no predefined number_type and char_50_type types in PL/SQL; so, what do they mean?

user8326781

TYPE number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

TYPE char_50_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;