Have a demand of work, you need to write an oracle () function returns the query result of the json format data.
For example, have a function into the reference for the table name and the primary key id, the table name is dynamic,
Assumptions into the incoming table called student, with id 1.
The query results for
Id name age
Xiao Ming 15 1

The return value is the json format string.
{" id ":" 1 ", "name" : "xiao Ming", "age" : "15"}

This function should be
how to write?Oracle version of 12 c

The younger brother new to oracle
Please everyone a great god glad

Thank you very much!!!!!!!!!!!!!!

CodePudding user response:

12 c added support for json, simple implementation, such as:

 select JSON_OBJECT (key value id, 'id' key 'name' value name, key 'age' value age) 
The from (
Select 1 id, 'xiao Ming' name, the age of 15 from dual 
) 

CodePudding user response:

Thank you
But given the customer site, the possible oracle version is not 12 c can also be 11 g, so do not use this method.
I find a similar online,

 DECLARE 
TYPE ref_cursor IS REF CURSOR; 
L_cursor ref_cursor; 
L_curid NUMBER; 
L_col_cnt NUMBER; 
L_desc_tab dbms_sql. Desc_tab; 
The BEGIN 
The OPEN l_cursor FOR 'select * from student where id=1'. 
L_curid:=dbms_sql to_cursor_number (l_cursor); 
Dbms_sql. Describe_columns (l_curid l_col_cnt, l_desc_tab); 
FOR I IN 1.. L_col_cnt LOOP 
Dbms_output. Put_line (l_desc_tab (I). Col_name); 
END LOOP; 
Dbms_sql. Close_cursor (l_curid); 
END; 

The output value of
ID
The NAME
AGE

If you can obtain the value of the column can to splice,

Predecessors have good hair method excuse me?

CodePudding user response:

This can only be an dbms_sql describe_columns method to disassemble the cursor structure, line by line joining together again,
If it's 11 g library, turn the said work in a high-level language is better, after all Oracle to do the things, not so professional,

PS: there is a train of thought, is the select statement, put the cursor into ctas, to check the table,

CodePudding user response:

If only the mysql lane

CodePudding user response:

Can't storage to be used in the process of ETL, because a Java program to restore data is used in json,
So be joining together to save the json,