Print CLOB Column content in Oracle Apex Region

We all facing to print clob content in oracle apex report region. In this post we will see how to print clob content in “PL/SQL Dynamic Content Region”. Step 1: Create PL/SQL Dynamic Content Region. Step2: Make PL/SQL script to print in a region, for example see below script. Step 3: Use HTP.P to print HTML content. Refer below link Click to See Reference That’s … Continue reading Print CLOB Column content in Oracle Apex Region

Parse json data using APEX_JSON in Oracle PL SQL

How to parse JSON data in oracle apex ? Yes, We can parse json data with the help of apex_json and apex_json.t_values. User apex_web_service.make_rest_request Authorization method is Bearer Token Store the response in CLOB variable Parse the response json content with apex_json.t_values Count no of rows in json content Create for loop and get the object values using apex_json.get_varchar2 Insert / Update object value variables … Continue reading Parse json data using APEX_JSON in Oracle PL SQL

Get Client IP Address from SQL PL/SQL

Hello All, This post explains how to get IP address from PL/SQL. “select owa_util.get_cgi_env(‘X-FORWARDED-FOR’) from dual;” ==================== O/P : 106.203.33.240, 23.205.118.109, 184.27.120.23 Function :- owa_util.get_cgi_env This function returns the value of the specified CGI environment variable. Although the WRB is not operated through CGI, many WRB cartridges, including the PL/SQL Gateway, can make use of CGI environment variables. Reference:- https://docs.oracle.com/cd/E28280_01/portal.1111/e12042/psutil.htm#YPREF417 Continue reading Get Client IP Address from SQL PL/SQL

Print CLOB Content in Oracle Apex

How to show clob content in oracle apex page ? is it possible to show ? Yes, Possible to print clob content in oracle apex page with using of PL/SQL Dynamic Content Region. Step 1: Split CLOB content into small pieces of content with using of dbms_lob.read¬†procedure. Example Code Snippet: DECLARE CURSOR crec IS SELECT html_message FROM mails_t WHERE email_id = 1001; rrec crec%rowtype; l_amt … Continue reading Print CLOB Content in Oracle Apex

Oracle Apex Use of apex_web_service.make_rest_request

Instead of use UTL_HTTP.BEGIN_REQUEST in PL/SQL oracle apex offered to make web service request through “apex_web_service.make_rest_request” API.  Example Web Service Sample URL: http://quotes.rest/qod.json Syntax:- DECLARE l_response CLOB; BEGIN l_response:= apex_web_service.make_rest_request(p_url => ‘http://quotes.rest/qod.json’, p_http_method => ‘GET’ ); END; Webservice Response:- { “success”: { “total”: 1 }, “contents”: { “quotes”: [ { “quote”: “If you want to test your memory, try to recall what you were worrying about … Continue reading Oracle Apex Use of apex_web_service.make_rest_request

Get domain name from Email in Oracle SQL

How to get domain name from given email id in Oracle SQL (or) PL/SQL Very easy to get domain name from mail id like ‘thamaraiapex@gmail.com’ =>¬†gmail SELECT (substr(substr(‘thamaraiapex@gmail.com’, instr(‘thamaraiapex@gmail.com’, ‘@’) + 1), 1, instr(substr(‘thamaraiapex@gmail.com’, instr(‘thamaraiapex@gmail.com’, ‘@’) + 1), ‘.’) – 1)) as domain_name FROM dual; Result:- gmail Continue reading Get domain name from Email in Oracle SQL