Script to compile all invalid objects

The following script compiles all invalid objects in the schema. Login through SQL Plus and execute this file. In case all objects are not compiled even after the execution, execute the same file once or twice more. This is to ensure that all the dependent objects are compiled.

compile.sql
set echo off

set feed off
set sqlp ''
set head off
set pages 0
spool compile_objects.sql
select 'ALTER '||
DECODE(object_type, 'VIEW', 'VIEW', 'PACKAGE') || ' '||
LOWER(object_name)|| ' COMPILE '||
DECODE(object_type, 'PACKAGE', 'SPECIFICATION', 'PACKAGE BODY', 'BODY', '')|| ' ; ' from user_objects
where status = 'INVALID'
order by DECODE(object_type, 'VIEW', 1, 'PACKAGE', 2, 'PACKAGE BODY', 3), object_name ;
spool off
set echo on
set feed on
set sqlp 'SQL>'
set head on

Once the compile.sql is executed, execute the compile_objects.sql generated by compile.sql

MSSQL-Creating a table as duplicate of another table

I was very much frustrated with the simple but syntactical change between Oracle and MS-SQL Server. I was trying to just create a table with the normal Oracle syntax in MS-SQL Server:

CREATE TABLE test AS SELECT * FROM existing_table;

I got an error from MS-SQL and then I had to call up some experts on MS-SQL to understand how it is done in MS-SQL.

The syntax for creating such a duplicate table of an existing one in MS-SQL is as follows:

SELECT *
INTO {new_table}
FROM {existing_table}
WHERE {if_clause if any};

Thus our example can be translated to as:

SELECT *
INTO test
FROM existing_table;

This short article is to just keep up the difference between MS-SQL and Oracle. Oracle professionals may sometimes need to work in MS-SQL, and this kind of tips on MS-SQL comes handy in times of trouble.

Deleting duplicate rows from Oracle Table

Tip Courtesy: Oracle Tips by Burleson Consulting (dba-oracle)

Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques. This post shows some examples of using SQL to delete duplicate table rows using an SQL sub query to identify duplicate rows, manually specifying the join columns:

DELETE FROM
   table_name A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        table_name B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        );


For a script that does not uses ROWID concept, read this post.

Deleting/Listing duplicate records without ROWID

How to validate values in PL/SQL

I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one.

Validating NULL Values

NVL Function
This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function.

Syntax:
NVL(variable1, variable2)

Example:
SELECT NVL(NULL, 'This is the output') null_test FROM dual;

Result:
null_test
This is the output

Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query.

NVL2 Function
NVL2 function is an extension of NVL function that it can return two values, one when the variable to be tested is NULL and when the variable to be tested is NOT NULL.

Syntax:
NVL2(variable1, variable1_if_not_null, variable1_if_null)

Example 1:
SELECT NVL2(NULL,'NOT NULL','NULL') nvl2_test FROM dual;

Result:
nvl2_test
NULL

Example 2:
SELECT NVL2('some value','NOT NULL','NULL') nvl2_test FROM dual;

Result:
nvl2_test
NOT NULL

Validating NOT NULL Values
While validating NOT NULL values there is no need to use such functions as the value will be present already in the variable. But if the check is for whether the value is null or not then NVL2 function will be best suitable for the purpose.

Script for getting Oracle table size

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it?

Anyway you can choose to save this query as a function for easy retrieval.

select

segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;

Read more on what all to remember while getting the size of a table. Click here

Create your own function for the purpose:

CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS

l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM
user_extents
WHERE
segment_type='TABLE'
AND segment_name = t_table_name;

RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

/

Example:
SELECT get_table_size('EMP') Table_Size from dual;

Result:
Table_Size
0.0625

How to enter a single quotation mark in Oracle

Q: How to enter a single quotation mark in Oracle?

Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.

Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.

SELECT 'test single quote''' from dual;

The output of the above statement would be:
test single quote'

Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.

This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.

Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.

The same example inside PL/SQL I will use like following:

DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;

DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;

The output above is same as the Method 1.

Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:

SELECT 'test single quote'||CHR(39) FROM dual;

The output is same in all the cases.

Now don't ask me any other methods, when I come to know of any other methods I will share here.

Create your own Oracle OTN login for FREE

OTN Login enables you to download Oracle softwares, post in Oracle Forums etc. This is a unique email id which you will use across all Oracle websites excluding Oracle Metalink.

Click here to create your account




You will be shown a screen like the following:


Click the Create your Oracle account now. link.

Upon getting the screen enter your email address and a password for accessing the Oracle sites. (Not your email account password)




Fill in the mandatory fields in the following screen. Once you click Submit the following screen will be displayed:


Click Submit button in the Screen. You will be asked some confirmations hereafter, click them and you will be shown the login screen again. THIS IS ALL you need to do to create your FREE login. You may use this login for downloading softwares from otn website, login to oracle forum for posting queries or answering them.