set echo off
set feed off
set sqlp ''
set head off
set pages 0
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 ;
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
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:
Thus our example can be translated to as:
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.
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
A.col1 = B.col1
A.col2 = B.col2
For a script that does not uses ROWID concept, read this post.
Validating NULL Values
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.
SELECT NVL(NULL, 'This is the output') null_test FROM dual;
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 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.
NVL2(variable1, variable1_if_not_null, variable1_if_null)
SELECT NVL2(NULL,'NOT NULL','NULL') nvl2_test FROM dual;
SELECT NVL2('some value','NOT NULL','NULL') nvl2_test FROM dual;
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.
Anyway you can choose to save this query as a function for easy retrieval.
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
AND segment_name = t_table_name;
WHEN OTHERS THEN
SELECT get_table_size('EMP') Table_Size from dual;
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.
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.
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:
l_single_quote CHAR(1) := '''';
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
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.
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.