How to bulk return in FORALL insert?
BULK Return from FORALL insert. CREATE TABLE ashish_bulk_ret ( id NUMBER GENERATED AS IDENTITY PRIMARY KEY, name VARCHAR2 (31) ); DECLARE TYPE l_rec_typ IS RECORD ( id NUMBER, name VARCHAR2…
ORACLE INVISIBLE Column can be used for column ordering
How to reorder ORACLE table columns? Sample table CREATE TABLE test_column ( id NUMBER, name VARCHAR2 (200), created_by VARCHAR2 (50) ); Add a column ALTER TABLE TEST_COLUMN ADD EMAIL VARCHAR2(50);…
PLSQL Collection
PL/SQL provides three collection types − Index-by tables or Associative array Syntax TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name; Example TYPE l_tab_type IS TABLE…
3 ways to find second highest salary ORACLE SQL
Solution 1 : Copy CodeCopiedUse a different BrowserWITH t AS (SELECT sal, ename, DENSE_RANK () OVER (ORDER BY sal DESC) AS rnk FROM emp) SELECT sal, ename FROM t WHERE…
Table Blob to Oracle Directory
How to save table images to oracle directory? Files from table to Oracle directory Copy CodeCopiedUse a different BrowserDECLARE l_file UTL_FILE.file_type; l_buffer RAW (32767); l_amount BINARY_INTEGER := 32767; l_position INTEGER…
Cursors in PL/SQL
What is cursor: Pointer to the memory location where the information about sql stmt or DML statement got executed. There are two types of cursors − Implicit Cursor:= Any Select Stmt Or…
Pipelined Functions in Oracle
How to create Pipelined Functions in Oracle? Pipeline functions are used to return data in tabular format. It returns the data in the nested table type. Pipeline functions are useful…
Reading File From Oracle Directory
How to read Files from Oracle Directory using PL/SQL? File Structure ACCOUNTINGRESEARCHSALESOPERATIONS3 Records5 Records6 Records0 Records How to generate files into Oracle Directory? Copy CodeCopiedUse a different Browser DECLARE l_file…
Writing File to Oracle Directory
How to write file to ORACLE Directory using PL/SQL? Creating directory Copy CodeCopiedUse a different BrowserGRANT CREATE ANY DIRECTORY TO apex_19_2; CREATE OR REPLACE DIRECTORY my_dir AS 'C:\App\MY_DIR'; Writing File…
CLOB to BLOB using PLSQL
Create function to convert the CLOB to BLOB Copy CodeCopiedUse a different BrowserCREATE OR REPLACE FUNCTION clob_to_blob (i_clob_text IN CLOB) RETURN BLOB IS /* Version 1 29-mar-2020 */ l_blob_file BLOB;…