10 Oracle SQL features you probably didn’t know



10 Oracle SQL features you probably didn’t know

I can not guarantee that you never heared of this. But the majority of developers doesn’t know or doesn’t seem to know. And honestly –  most of this information is fairly useless – and mostly harmless.

10. Why we have the CONCAT function

There is a concat function that can be used instead of the concat operator ||.

Nobody uses that! But there is a reason why it exists.

This is from the 8.1.7 Oracle documention

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function…

View original post 2,420 more words



Why APEX running inside the database is a GOOD thing!

Johns Blog

I’ve had quite a few discussions with people regarding whether APEX running inside the database is a good thing or a bad thing. Personally I believe it is definitely a good thing, I see no downsides really. However a recent forum thread

For me, the fact that APEX runs inside the database means that one of the major positives is you are able to take full advantage of each and every database available to you. A recent forum thread illustrates this very nicely. The poster in the thread has deleted some files and has unfortunately performed a commit before realising their mistake.

Here is where the features of the database can help you, for example we are able to take advantage of the Flashback feature to get the database back to the state it was in before the files were deleted.

The user in the forum deleted some files from…

View original post 838 more words

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.

If your variable is a CLOB it might be confusing as it was for me..

However, you ‘ll figure out the reason soon enough: Those string parts that you were collecting are bigger than 32K

And you ‘ll realize that you should tell it what to do.

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_chunk PLS_INTEGER;
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.