All posts by m.ogun

note to mezelf: chunk CLOB?

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_buf_size VARCHAR2(32000);
v_offset PLS_INTEGER DEFAULT 1;
v_size PLS_INTEGER;
v_len PLS_INTEGER;
v_chunk PLS_INTEGER;
v_clob CLOB := p_clob;
BEGIN
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);
END IF;
v_buf_size := NULL;
dbms_lob.READ(v_clob, v_size, v_offset, v_buf_size);
htp.prn(v_buf_size);
v_offset := v_offset + v_size;
END LOOP;
END;

– 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.


my_pck.chunk_clob(l_show_html);

Oracle Database In-Memory and Hash Keys

Data Warehousing with Oracle

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

Limiting the percentage of ordered rows retrieved in Oracle 12c

Lczinkoc's Blog

In a previous post I compared two solutions for the same ranking
problem. Now I would like to compare two solutions for the same
percentages report.
The goal of the query: Let's see the first 5% rows of the total
rows according to the salary in descending way.
Let's consider the solution in Oracle 12c, where we can use the
new FETCH {FIRST|NEXT}  <pct> PERCENT ROWS clause:
One of the possible traditional solution uses
the cumulative distribution (CUME_DIST) analytic function.
Let's consider the execution plan for that query which 
uses the new FETCH {FIRST|NEXT} <pct> PERCENT ROWS clause:

Now we examine the execution plan of the query that uses the CUME_DIST analytic function:

We can observe that BOTH queries use the SAME EXECUTION plan,
but with different number of consistent gets (1551 versus 7!). 
Other metrics are same. 
I created a greater table:I executed the following queries:

Of course, I…

View original post 48 more words

Work with WITH option in Oracle 12c!

oracle 12c: using functions with WITH

Lczinkoc's Blog

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 plsql_declarations clause
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,
but…

View original post 124 more words

Analytic Views: Powerful Oracle 12.2 Feature for Business Intelligence

Oracle 12.2: Analytic Views

Data Warehousing with Oracle

Analytic Views are one of the main features for Business Intelligence introduced with Oracle 12c Release 2. They provide a lot of new possibilities for analytical queries on star schemas. This blog post gives an overview of the new functionality.

View original post 2,453 more words

ANSI Join Syntax and Query Rewrite

which JOIN syntax?

Data Warehousing with Oracle

There are many good reasons to use ANSI join syntax instead of the old Oracle join syntax. With one exception: If you want to use your Materialized Views for Query Rewrite, avoid to write them with ANSI joins.

View original post 1,471 more words

10 Oracle plsql things you probably didn’t know

Iconic!

svenweller

Many people enjoyed reading my last blog post “10 Oracle SQL features you probably didn’t know”. So I decided to spice it up a little more and do something similar for plsql.

I hope you like that one too.

With our further ado, let’s get started with the list.

10. The first Oracle version to feature plsql was Oracle DB version 6 (1988)

And no. Steven Feuerstein did NOT invent it.

At that time PLSQL did not have stored procedures nor did it have proper exception handling. But it already had embedded SQL.

I learned that from the great Lewis Cunningham. One of the godfathers of development with SQL and PLSQL.

Stored Procedures were added in Oracle 7 (1992). 7.3 was the version when I started to work with an Oracle Database. At that point plsql was in version 2.x. However there never was a version 3. Plsql versioning…

View original post 2,297 more words

What is a Group?

Rationalising The Universe

Good morning RTU followers. Today I want give you a very brief introduction to an integral part of Theoretical Physics – Group Theory. When studying for my MSc I knew I had to get my head around this subject which to me initially seemed very dry and written in very purely mathematical language (i.e. not easy to read!) However once I’d dedicated some time the beauty of group theory began to fall into place – it’s a slow burner. The purpose of group theory is to classify and understand symmetries in the natural world. So without further ado let me lay down the principles of group theory as clearly as I can.

Firstly a group, denoted G, is a collection of elements call them g(1), g(2), g(3)…. and there exists a group operation, denote it *, which determines how the elements act on each other. Now the elements of the…

View original post 928 more words