The procedure below receives a SYS_RefCursor as a parameter and constructs a simple JSON in Oracle 11g: procedure get_json_fnc(P_RS_i Sys_Refcursor) is P_RS SYS_REFCURSOR; L_COLS NUMBER; L_DESC DBMS_SQL.DESC_TAB; L_CURS INTEGER; …
Sometimes in PL/SQL programming we need to switch the logic between different SQL queries, but fetching from cursors into predefined variables is painful.In the following example we define 2 cursors…
Let's suppose that you want to return on Sys_RefCursor, some values, which does not exist in the database, thus should be created on the fly.One way to do this, is…
Here is an anonymous block for the demonstration of Web-Service call capability using Oracle PL/SQL.The Web-Service takes 1 parameter (cust) as input and outputs a list with 4 columns.We provide…
Here is an example how to use autonomous_transaction for logging purposes.In case of rollback only the main transaction is affected.declare l_categories_count varchar2(100); process_error Exception; procedure log(p_message varchar2) is …
The following piece of code, reads a list of problematic strings and tries to match them using a list of valid codes.In order to get the best match there is…
Here is a piece of code, which shows the structure of user defined exception:declare exc_invalid_request_id EXCEPTION;... IF l_accepted_request > 0 THEN RAISE exc_invalid_request; END IF;... EXCEPTION WHEN exc_invalid_request THEN
In the following example the procedure create_csv creates a CLOB, which is then printed out.CLOB can be printed out, either simply, using DBMS_OUTPUT.PUT_LINE, or with the procedure print_clob_out, which is…
The following script show how to calculate the time difference between 2 execution points:declare thediff number; TIME1 timestamp; TIME2 timestamp; i number;begin TIME1 := SYSDATE; for i in 1..1000000000 loop …
The official way to achieve pagination in Oracle DB is not very elegant and very very slow. The reasons why it is slow are 2:The order by command must be…