0

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

gt.guybrush
  • 1,320
  • 3
  • 19
  • 48
  • You can use REF CURSORs if your caller can handle receiving one and fetching from the cursor (e.g. a PL/SQL caller). You can use an object table type that you define (CREATE TYPE objecttype AS OBJECT..., CREATE TYPE tabletype AS TABLE OF objecttype) and then fetch the content into a variable of type tabletype and return it. You can also make that a pipelined function and return each row individually. These last two options can all be consumed by regular SQL in the FROM clause just like a normal table. Lastly, as of 19.7 you have SQL macros you can leverage. Lots of options. – Paul W May 30 '23 at 12:53
  • object table type requires to define it for every query structure, REF CURSOR too. macro is interesting but can you provide a little code example. not need every detail – gt.guybrush May 30 '23 at 13:12
  • https://oracle-base.com/articles/21c/sql-macros-21c ... they backported it to 19.7 – Paul W May 30 '23 at 13:43
  • can't see how to use them in a function to accept any kind of table as input parameters – gt.guybrush Jun 08 '23 at 14:56

0 Answers0