Example for PL/SQL that uses htp.p or apex_json.write

[JoseArostegui]

Hi, Is there any sample of calling an stored procedure/function/package that writes using htp.p or apex_json.write? Thanks, Jose.

[cjbj]

The question is off-topic for here, but I'll leave it open in case someone knows. Try on forums like https://community.oracle.com/community/groundbreakers/database/developer-tools/sql_and_pl_sql

[JoseArostegui]

Sorry, my doubt is not related with how to do it in PLSQL... I'm missing a sample of calling it from node-oracledb and processes the html/json returned by the Database. Thanks, Jose.

[dmcghan]

@JoseArostegui Grabbing data from the HTP buffer is possible, but you'd have to jump through hoops. Is it required? APEX_JSON can write to a CLOB instead. Would that work?

What database version are you using? There are built-in, better alternatives to APEX_JSON in 12.2+.

[JoseArostegui]

Thanks for your help Dan.

Sure, using a Clob would be fine. In fact, I've already implemented it using dbms_output instead of HTP. As I'm completely new in node-oracledb (and node as well) and just thought it was a pending sample to create.

What database version are you using? There are built-in, better alternatives to APEX_JSON in 12.2+.

I'm using 18 XE, and 12.2 too, please indicate the alternatives to apex_json. BR, Jose.

[dmcghan]

What do you mean when you say you "implemented it using dbms_output instead of HTP"? As far as I can tell, you shouldn't be using either. If you're using dbms_output to instrument your code, I suggest you look into using something like Logger instead: https://github.com/OraOpenSource/Logger

You said you are new to node-oracledb and Node.js, but not Oracle Database, is that correct? Just trying to get a sense here...

If using a CLOB would be fine, then I'm curious, why do you want to know about "htp.p or apex_json.write"? What's the use case for either?

Regarding the alternatives to apex_json, here's a link to Generation of JSON Data with SQL/JSON Functions chapter in the 18c JSON Developer's Guide: https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A

You want to start by learning how to use these four functions in SQL:

• JSON_OBJECT
• JSON_ARRAY
• JSON_OBJECTAGG
• JSON_ARRAYAGG

You might find these slides useful too, which give a lay of the land on JSON and Oracle (though the slides are now over a year old, they should be fine for 18c XE): https://www.slideshare.net/DanielMcGhan/json-and-oracle-database-a-brave-new-world

However, developers using the driver don't usually start with these functions. They just use regular SQL queries. The driver maps datatypes between JavaScript and Oracle Database, so when you run a normal SQL query, you get the data back as JavaScript data types. If you want JSON, converting is very natural with JSON.stringify in JavaScript.

If you want to use stored procedures to keep the logic out of your JavaScript code, it's not uncommon to use PL/SQL stored procedures with OUT cursors. That requires the data to be consumed a little differently (streamed), but as the data scales up, that's what you'd want to do anyway.

As you can see, there's no shortage of options! :) To help more, we'd need to know more about what you're building, your goals, etc.

[JoseArostegui]

Hi Dan,

I gladly explain the motivation here. I'm starting with Node because of Cypress. I'm starting writing automated web tests against Oracle Apex web applications.

Following: https://insum.ca/unit-testing-apex-3-methods-bypass-login-using-cypress-io, I'm improving the speed of the tests by bypassing login. I've implemented the "Method 2: Getting session and cookie with PL/SQL", but also following this recommendation from David Lawton: "You could write a plugin based on the official Oracle NodeJS driver that lets you issue queries directly to the database with a cy.oracle() command. It means that any DB interaction required takes place directly in the test itself."

So here is where I get to node-oracledb. I'm executing a PLSQL code that writes a Json like this:

apex_json.open_object;
apex_json.write('app_session',l_app_session);
apex_json.write('cookie_value',l_cookie_value);
apex_json.close_object;

{
   "app_session": "129871kjAASD0120983211",
   "cookie_value": "hjkdasnmsvn,sdn,mfsdn"
}

As I didn't know how to process the HTP buffer I changed it to dbms_output like this:

 dbms_output.put_line ('{ "app_session":'||l_app_session||',');
 dbms_output.put_line ('  "cookie_value": "'||l_cookie_value||'"}');

And using sample dbmsoutputgetline.js I got this working, but you're right it's not too elegant.

After that, in Cypress I'm calling the node-oracledb script and processing the result like this:

  beforeEach(function () {
    cy.exec('node get_apex_session_cookie.js').then((result) => {
      // yields the 'result' object
      // {
      //   code: 0,
      //   stdout: "Files successfully built",
      //   stderr: ""
      // }
      console.log(result.stdout);
      cy.clearCookie('ORA_WWV_APP_500');
      valid_session = (JSON.parse(result.stdout)).app_session;      
      app_cookie = (JSON.parse(result.stdout)).cookie_value;
      cy.setCookie('ORA_WWV_APP_500', app_cookie);  
    }) 
  })

[dmcghan]

I see, thanks for the explanation. So you're using a modified Method 2, that uses Node.js instead of an ORDS REST handler.

Why do you only show us small parts of your Node.js code? It would be more helpful to see the entire function that's executing the query.

I'll respond in three parts...

1. You said that you're following the recommendation from David Lawton, but not quite. His recommendation was to use cy.oracle, but you're using cy.exec. I assume you have your reasons, but in theory, you should be able to get the data directly with cy.oracle and not have to go through stdout.

   Having said that, I'm not sure there's much value in converting to cy.oracle now that you have cy.exec working. I've often found that wrappers for the driver, if not properly maintained, fall by the wayside and become more of a hindrance than a help. You going directly to the driver ensures this will not be a problem.

2. Your use case is in support of testing, not testing actual code used in an APEX app - though both are valid use cases. If you were testing code that wrote to the HTP buffer, then knowing how to access the HTP buffer would be important. While it could be useful to create examples like the dbms_output ones that show how to use the HTP buffer, it seems the APIs weren't documented, so I'm not sure if this is okay. I'll look into that a little, but no guarantees.

3. Now that I know a little more about your use case, I would wager you don't need to work with the HTP buffer, the OUTPUT buffer, or the even the JSON functions in the database. All you need to do is query the data in your view and console.log it out with a call to JSON.stringify. If you show the entire function you're using, I could show you how to modify it to work more simply.

[cjbj]

Closing - no activity.

Going back to the original mention of HTP, I did recall last week that I once wrote a wrapper PL/SQL function to pipe MOD_PLSQL output. See p 225 of The Underground PHP and Oracle Manual.

The code was:

create or replace type modpsrow as table of varchar2(512);
/
show errors
create or replace function mymodplsql(proc varchar2) return modpsrow pipelined is
  param_val owa.vc_arr;
  line      varchar2(256);
  irows     integer;
begin
  owa.init_cgi_env(param_val);
  htp.init;
  execute immediate 'begin '||proc||'; end;';
  loop
    line := htp.get_line(irows);
    exit when line is null;
    pipe row (line);
end loop;
return; end;
/
show errors

which you could call by binding the name of your existing PL/SQL code that uses HTP in the query:

select * from table(mymodplsql(:proc))

[briandunn]

Anyone else who wound up here may also find this useful. Explains how to get the markup from a web package in an anonymous block.