What is New in Oracle APEX 19.2

What’s New in Oracle APEX 19.2? Faceted Search APEX dev team introduced in faceted search option in APEX 19.2. As per the DEV team. Introducing Faceted Search, a new component that enables you to quickly search and filter your data like never before. Empower your users to see data in new ways, and discover new …

ORA-00972: identifier is too long

ORA-00972: identifier is too long. Till Oracle 12 C, the identifier length is 30 characters. Starting from 18 C Oracle increased the length of identifier to 128 character. So now we can define our identifiers till 128 characters if you have upgraded to 18 C. Let see one example. Database version 18 C. Select ename as …

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 …

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 …

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 …

APEX shortcut variables

APEX shortcut variables are best in dynamic use in Javascript code. We can fetch data from database or can use any hard coded text. Under the shared components we have pretty awesome feature of APEX shortcut. We have several source type available to populate the shortcut variables. * PL/SQl Function body * HTML Text * …

Get Current Schema and DB name

Some times we do not know about our schema exist in which db and which is our schema hare is the method to find db and schema name. DECLARE CURSOR ashish_db_name IS SELECT NAME FROM v$database; My_db_name VARCHAR2 (4000); BEGIN OPEN ashish_db_name; FETCH ashish_db_name INTO My_db_name ; CLOSE ashish_db_name; …

Refresh a materialized view

To refresh a Materialized view we need DBMS_SNAPSHOT.REFRESH procedure. Steps. CREATE TABLE demo_m ( id NUMBER (10) primary key, name VARCHAR2 (400) ); / INSERT INTO DEMO_M ( ID, NAME) VALUES (1001, 'VIKAS' ); / SELECT * FROM demo_m; One record is available in the table. Create Materialized view. CREATE …

Use of Merge Statement

Sometimes whenever we want to insert records into table with the conditions For existing records (if found then) Update, For Non Existing records (If not found) then insert Step 1 : Create table a_merge as select empno , ename from emp; Now the PL/SQL Block or SQL query: DECLARE l_start NUMBER; BEGIN l_start := …

RANK and DENSE_RANK

RANK() : function always returns sequential order for each record. DENSE_RANK() : It returns continuous order for each record. SELECT empno, deptno, sal, mgr, RANK () OVER (PARTITION BY deptno ORDER BY sal) "rank", DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "dense rank" FROM emp;

Constraints in Nested table

In my previous blog “Working With Nested Table Oracle ” i have explained about how to work with nested tables. Here we can also define Constraints to the nested table. According to the previous blog “Working With Nested Table Oracle ” we have Table := dept_details nested table := employees with …

Working With Nested Table Oracle

Nested table is awesome feature of Oracle. You can create table inside a table, Which means one column can be treated as a table. Nested tables are faster than the parent child table relationship. Steps : Create Object TYPE CREATE OR REPLACE TYPE emp_rec_type AS OBJECT ( EMPNO NUMBER (4), ENAME VARCHAR2 (10 BYTE), …