CODEWITHSUNDEEP
jsonoracle-databasetable-valued-parameters
gt.guybrush
gt.guybrush

Reputation: 1388

Oracle - How to send query result as parameter to a function

I need to create a function that get 2 string and a table parameters to store them i a table like that

create table Result(
  Id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  RefCode VARCHAR2 (10),
  Notes VARCHAR2 (4000),
  DynamicContent CLOB)
);

function would be like that:

   CREATE FUNCTION AddAlert(RefCode VARCHAR2 (10),
      Notes VARCHAR2 (4000),
      DynamicContent **XXX**) 

   BEGIN 
      // code to convert  DynamicContent into json and insert all into Result table
      // something like that          
      SELECT RefCode, Notes , (select JSON_OBJECT(*) from DynamicContent) INTO Result; 
   END;

i have to manage actual situation where caller simply insert data with a select into statement, so i would like to let them send directly query result as parameter.

query can have any sort of structure so i cannot use cursor or other static type

is there a way or i have to let them convert table to json and then pass json to my function?

example of wanted api:

**xxx** result := Select * from myTable;

AddAlert('rc1', 'some notes', result);

thanks

Upvotes: 0

Views: 170

Answers (0)

Related Questions