Blob to Clob Using DBMS_LOB

Create function to convert the blob content to clob CREATE OR REPLACE FUNCTION blob_to_clob (i_blob_content IN BLOB) RETURN CLOB AS l_chunk VARCHAR2 (32767); l_position PLS_INTEGER := 1; l_buffer_size PLS_INTEGER := 32767; l_clob_text CLOB; BEGIN -- Create temprary DBMS_LOB.createtemporary (l_clob_text, TRUE); FOR i …

Restful API using ORDS

Creating Rest API using ORDS ORDS APIs follows the Module, Template, Method. Now let see the Hierarchy of these. Module is top level container, it can contain set of templates . Template is at second level container can contain multiple Handlers. Handler is logical area where the purpose needs to define or what are …

Getting into ORDS

Firstly we need to enable ORDS. Base url : <SERVER_URL>:<SERVER_PORT>/ords/ To hide the schema name from API endpoint we put any alias in url mapping pattern. BEGIN ORDS.enable_schema( p_enabled => TRUE, p_schema => '<SCHEMA_NAME>', p_url_mapping_type => 'BASE_PATH', …

CLOB to BLOB using PLSQL

Create function to convert the CLOB to BLOB CREATE OR REPLACE FUNCTION clob_to_blob (i_clob_text IN CLOB) RETURN BLOB IS /* Version 1 29-mar-2020 */ l_blob_file BLOB; l_lob_length PLS_INTEGER := DBMS_LOB.getlength (i_clob_text); l_position PLS_INTEGER := 1; l_buffer_size RAW (32767); BEGIN DBMS_LOB.createtemporary …

Use of $s $x $v

$x $x is used to find the control on the DOM meaning looking for any item available on DOM. If the control is found then it returns the dom of the Item else false. Syntax: $x('PAGE_ITEM'); $v $v is used to get the value from the item. Syntax: $v('PAGE_ITEM'); $s $s is used to set the values in Item. \ …

APEX Page Group

Page groups are very helpful for developers to manage application pages. Developer can use Page Groups to club the similar kind of Pages. These groups give the a centralized management for pages, For example if developer needs to build an application which includes around 10 to 15 modules then it will become hard to …

Create ZIP File using APEX_ZIP

Data Types The data types used by the APEX_ZIP package. t_files type t_files is table of varchar2(32767) index by binary_integer; declare l_zip_file blob; begin for l_file in ( select file_name, file_content from my_files ) loop apex_zip.add_file ( p_zipped_blob => l_zip_file, p_file_name => l_file.file_name, …

Generating JSON using APEX_JSON

Generating JSON using APEX_JSON Sample tables from Oracle Create Two table based on below query and Inserted some record for demo purpose: CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) ; CREATE TABLE EMP ( EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME …

ORA-06533: Subscript beyond count

ORA-06533: Subscript beyond count Cause: An in-limit subscript was greater than the count of a varray or too large for a nested table. Action: Check the program logic and explicitly extend if necessary. CREATE OR REPLACE TYPE l_rec_type IS OBJECT (id NUMBER, name VARCHAR2 (4000)); / CREATE OR REPLACE TYPE l_tab_type IS …

Plsql Collection Data Deletion

PLSQL collection data deletion CREATE OR REPLACE TYPE l_rec_type IS OBJECT (id NUMBER, name VARCHAR2 (1111)); / CREATE OR REPLACE TYPE l_tab_type IS TABLE OF l_rec_type; / DECLARE l_tab l_tab_type := l_tab_type (); l_check NUMBER; BEGIN l_tab := l_tab_type (NULL, NULL); l_tab (1) := l_rec_type (1000, 'ashish'); …