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 …

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 := …