Category: Oracle

This was the question in my mind that how to concatenate multiple rows into single column. We can do with built-in function called ‘listagg’.This is the example of the code

Sometimes, Oracle SQL Developer display the date format as ‘DD-MM-YY’ although I changed the format on the preference. To change it manually on the session run following query. ALTER SESSION…Continue readingHow to change NLS Setting

The syntax for the CREATE TABLE AS statement that copies all of the columns in Oracle/PLSQL is: CREATE TABLE new_table AS (SELECT * FROM old_table); Example Let’s look at a…Continue readingOracle / PLSQL: CREATE TABLE AS Statement

How to use for loop in Oracle FOR loop_counter IN [REVERSE] lowest_number..highest_number LOOP {…statements…} END LOOP; [REVERSE]: Optional. The loop counter will count in reverse. If REVERSE is specified, then…Continue readingFor Loop

From the Oracle SQL Developer, when you can’t see any output although you added DBMS_OUTPUT as following: BEGIN DBMS_OUTPUT.PUT_LINE(‘This should be displayed’); END; It doesn’t display anything as following: You…Continue readingDBMS_OUTPUT not working (Doesn’t display anything)

It’s Oracle Script. The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data…Continue readingMerge Code Sample – Oracle

DBMS_LOB.GetLength returns the number of characters (bytes) in the CLOB/BLOB column. SELECT DBMS_LOB.GetLength(“BlobColum”)/1024/1024 AS SizeMB FROM MY_SCHEMA.MY_TABLE_WITH_BLOB_CLOB  

This is the query to identify what query were executed on my machine. Since Oracle doesn’t have Profiler as SQL Profiler, this is the script that we can get through…Continue readingTo trace query executed for Oracle