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'); …

GitHub Account setup

GitHub Account setup: GitHub is “Built for developers"and a development platform inspired by the way you work. From open source to business, you can host and review code, manage projects, and build software alongside 40 million developers. First thing we are gonna need is one GitHub account. Signup Create …

Ref cursor switch based on condition

A ref cursor is a datatype that holds a cursor value in the same way that a varchar2 variable will hold a string value. Ref cursor will allow us to open difference sets of query where is the explicit cursor will not allow us to open a query other then the one which is associated to that cursor pointer. Using ref …

Row level, statement level Trigger

Create two table based on query: CREATE TABLE product (Message varchar2(50), Current_Date date ); CREATE TABLE product_log (Message varchar2(4000), Current_Date date ); Statement level trigger CREATE or REPLACE TRIGGER Before_Update_Stat_product BEFORE UPDATE ON product Begin INSERT INTO product_log …