-
Notifications
You must be signed in to change notification settings - Fork 1.1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Example for PL/SQL that uses htp.p or apex_json.write #1254
Comments
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 |
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. |
@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+. |
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.
I'm using 18 XE, and 12.2 too, please indicate the alternatives to apex_json. |
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:
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 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. |
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. So here is where I get to node-oracledb. I'm executing a PLSQL code that writes a Json like this:
As I didn't know how to process the HTP buffer I changed it to dbms_output like this:
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:
|
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...
|
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)) |
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. |
Hi,
Is there any sample of calling an stored procedure/function/package that writes using htp.p or apex_json.write?
Thanks,
Jose.
The text was updated successfully, but these errors were encountered: