Ever had a trouble while you were trying to print a variable, got an ORA-06502 error?
ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Here is what I came across to. A big and special thanks to oracle forums, of course :)
PROCEDURE chunk_clob(p_clob CLOB) IS
v_offset PLS_INTEGER DEFAULT 1;
v_clob CLOB := p_clob;
v_chunk := dbms_lob.getchunksize(v_clob);
v_len := dbms_lob.getlength(v_clob);
WHILE v_offset v_chunk
THEN v_size := v_chunk;
ELSE v_size := v_len - (v_offset - 1);
v_buf_size := NULL;
dbms_lob.READ(v_clob, v_size, v_offset, v_buf_size);
v_offset := v_offset + v_size;
– OKAY what is this and what am I going to do with this?
It ‘ll take care of your oversized strings so you get to see what your VAR has in it.
Hash keys are often used as primary keys in Data Vault environments. When loading dimensional data marts from a Data Vault schema, it seems to be obvious to use them as dimension keys, too. At least in combination with Oracle Database In-Memory, this is not a good idea.
View original post 14,906 more words
oracle 12c: using functions with WITH
Starting Oracle 9.0 – according to SQL 1999 standard – Oracle introduced the WITH option which can be considered
as the extension of the inline views.
Let’s see an “old” example:
(The origin of this statement is Oracle course,titled:
Oracle Database: SQL Fundamentals I
Volume II • Student Guide).
In Oracle 12c we have new enhancements for this kind of SQL statement:
We can define “in-line” functions or procedures after the WITH clause.
In the following example we are looking for those departments, where
total salary of the department is greater than the maximum of the average of the total salaries:
The result is:
The execution plan is the following ( not very simple!):
We can use the WITH option with
in DML statements as well.
Let’s consider the following example where we want to compute the
tax amount of the salaries and modify the tax_amount column,
View original post 124 more words