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.
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
FROM {existing_table}
WHERE {
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)
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.
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 FROMtable_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.
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
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.
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
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)
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.
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.
Subscribe to:
Posts
(
Atom
)