Geeks - Tip of the Day

Hello All,

Nobody knows as much as we all know collectively
What better way of increasing our knowledge than sharing what we know at one common place.
With "Thought for the day" messages from different members helping us chisel our personality each day
Let "Tips for the day" messages sharpen our tech skills every day too
Let the tips flow, let the knowledge grow...

Thanks,
admin

It is a good idea.
[b]how to create a read only table in oralce?[/b]
create trigger tab_readonly
before delete or insert or update on emp
for each row
begin
raise_application_error(-20001, 'table status: read only.');
end;

To Display a String Vertically use this


SELECT SUBSTR('GEEK INTERVIEW', ROWNUM, 1)
FROM user_objects
WHERE ROWNUM <= LENGTH(TRIM('GEEK INTERVIEW'));

[B]How to remove spaces in the spooled output ?[/B]
Generally while spooling file we use set linesize 500 or 1000.
If your table rows are only 100 in size then remaining character are filled with blanks or tabs. This will increase the size of your file. The solution is to use the SET TRIMSPOOL ON at the beginning of your SQL script. This will trim the unneeded spaces in your file and dramatically reduce the size of your file.
SQL> set trimspool on

[B]Alternative to imp/exp utility [/B]
Copy command is alternative to the IMP and EXP commands that lets you copy data between two SQL*Net connected databases.

You can quickly copy data from one database instance to another using an SQL query, that lets you CREATE a new table, REPLACE an existing table, INSERT values to an existing table or APPEND values to an existing table.

Example
conn scott/tiger@orcl1

copy from scott/tiger @ORCL1 -
to scott/tiger @ORCL2-
create emp_test using select * from emp;

[B]How to get database object definition?[/B]
With DBMS_METADATA you can retrieve complete database object definitions (metadata) from the dictionary by specifying:
·The type of object, for example, tables, indexes, or procedures
·Optional selection criteria, such as owner or name
Examples:-
select dbms_metadata.get_ddl ('TABLE','EMP','SCOTT')
"Definition of EMP table"
from dual;
select dbms_metadata.get_ddl ('FUNCTION','MGR_EMP','SCOTT')
"Definition of MGR_EMP function"
from dual

To find text source of stored objects
user_source describes the text source of the stored objects owned by the current user
Example
select TEXT from USER_source where type='FUNCTION' AND NAME='NUMTOSTRING'
ORDER BY LINE
or
select TEXT from sys.all_source where type='FUNCTION' AND NAME='NUMTOSTRING' order by line

Note : sys.all_source contails the text source of the stored objects owned by all the users

[B]To Suppress unneccessary blank spaces in output using FM[/B]

select to_char(1234.89, '$999,990.00') num_format from dual
select to_char(SYSDATE, 'Day, Month DD, YYYY') date_format from dual

If you observe the output of above statements it will display unnecessary blank spaces.

To suppress zeros and blanks use FM as follows

select to_char(SYSDATE, 'FMDay, Month DD, YYYY') date_format from dual

[B]To set SQL *PLUS environment[/B]
We use some commands regularly to setup our SQL *PLUS environment. Instead of typing those commands each and every time, you may save it in LOGIN.SQL . It is a startup script and executed automatically at the time of starting your session.
Example:-
SQL> ED LOGIN.SQL
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
set termout off
set sqlprompt 'SSAPL> '
set termout on.

[u][b]comments[/b][/u]
use comment command to insert a comment of upto 255 characters about a table or column. These are especially very useful for new users when there is no documentation for them to know the existing tables details.

Example to add a comment on a table
comment on table emp is ‘employee information’

example to add a comment on a column
comment on column emp.empno is ‘employee no should not be empty’

example to remove a comment issue the command without a comment
comment on column emp.empno is ‘’

all the comments are inserted into data dictionary.to see the comments use one of the following data dictionary views
user_col_comments (to view column comments)
all_col_comments (to view column comments)

user_tab_comments (to view table comments)
all_tab_comments (to view table comments)

Regards
Krishna

USER_DEPENDENCIES describes dependencies between objects in the current user's schema

SELECT NAME, TYPE, REFERENCED_OWNER,
REFERENCED_NAME, REFERENCED_TYPE FROM USER_DEPENDENCIES
WHERE REFERENCED_NAME = 'TABLENAME'

To move table from one tablespace to another Tablespace

ALTER TABLE tablename MOVE TABLESPACE tablespacename;

Trigger body can't declare any long or lob datatype.

[B] NULL's and Decode Function[/B]
Null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers [B]two nulls to be equal when evaluating a DECODE function.[/B] Observe the following output.

create table test_1 (empcode varchar2(10), comm number)

insert into test_1 values('1001',NULL)

[B]select decode (comm,NULL,'EQUAL','NOT EQUAL') from test_1[/B]

The output will be equal.
Handle null values carefully while using Decode Function.

You can not specify when clause for statement trigger and instead of triggers.

To Convert a number to Roman number :
select to_char(&n,'RN') FROM DUAL;
For Example
n=10
select to_char(10,'RN') FROM DUAL;
output is
X

Placing nulls at last/first
The sort order of NULL values can be overridden using the NULLS FIRST/LAST clause.
To place nulls last
select * from scott.emp order by comm nulls last;
To place nulls first
select * from scott.emp order by comm nulls first;

To know howmany valid and invalid objects exists owned by this oracle user?
SELECT DISTINCT (object_type) object, status, COUNT(*)
FROM user_objects
GROUP BY object_type, status;

There are two types of DML triggers:
statement level
and row level.
The statement level trigger fires once per transaction, while the row level trigger fires for each record effected, per transactions.
[B]In order to use : new or old, the trigger must be a row level trigger. [/B]

Rollup fuction
Rollup is a analytical function and is used to calculate grand totals and subtotals
Examples
1.SELECT deptno,SUM(sal) from scott.emp
GROUP BY ROLLUP(deptno);
2.SELECT deptno,job, SUM(sal)
FROM scott.emp
GROUP BY ROLLUP(deptno,job);

[B][U]Analytical functions – I[/U][/B]
[B]Group functions and Analytical functions[/B]
Group functions operate on a set of rows to give one result of a group Where as Analytic functions return multiple rows for each group.

Ex 1:- SELECT deptno, count(*)deptcount
FROM emp
GROUP BY deptno
EX 2:- SELECT empno, ename deptno,
COUNT(*) OVER (PARTITION BY deptno) deptcount
FROM emp

The PARTITION BY clause is just like group by logically breaks a single result set into groups.

In absence of any PARTITION inside the OVER( ) portion, the function acts on entire record set
Ex 3:- SELECT empno, ename deptno,
COUNT(*) OVER () deptcount
FROM emp

[b][u]analytical functions – ii[/u] [/b]
[b]order of execution in analytical functions [/b]
join
where
group by
having
analytical functions
order by
so analytic functions can only appear in the select list and in the main order by clause of the query.

[B][U]Analytical functions – III[/U][/B]
[B]Difference between RANK and DENSE_RANK[/B]RANK and DENSE_RANK functions provide rank to the records based on some column value or expression. Both these functions provide same rank for rows with equal values. The only difference is
Dense_rank:- Rank values are not skipped in the event of ties .
Rank :- Rank values are Skipped in the event of ties
Ex :- . SELECT deptno, ename, sal,
DENSE_RANK()
OVER ( PARTITION BY deptno ORDER BY sal desc ) DENSERANK,
RANK()
OVER ( PARTITION BY deptno ORDER BY sal desc ) RANK FROM emp
ORDER BY deptno, sal DESC

[B][U]Analytical Functions - IV[/U][/B]
[B]LAG / LEAD[/B]
These two functions are useful for comparing one row of a result set with another row of a result set.
LAG provides access to a rows which are prior to that position
LEAD provides access to a rows which are beyond that position.(Which are going to come after the current row)

[B]Parameters[/B]
1.Expression from which value to be taken.
2.Offset:- It is index relative to the current row . Default is 1
3.Default is the value to return if the <offset> points to a row outside the partition range. Default value is null.

Ex:- SELECT deptno, ename, hiredate,
LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) last_hire,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) next_hire
FROM emp
ORDER BY deptno, hiredate

[B][U]Analytical functions – V[/U][/B]
[B]First Value / Last Value of a Group[/B]
The FIRST_VALUE and LAST_VALUE functions allow you to select the first and last rows from a group.
Ex :- How many days after the first hire of each department were the next employees hired?
SELECT empno, deptno, hiredate - FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;

NVL2 FUNCTION
The NVL function which checks for the existence of NULL values. NVL2 which checks for the existence of NOT NULL. The syntax for this function is as follows.

NVL2(exp1,exp2,exp3);

If exp1 is not null then the function will return exp2. Otherwise, the function will return exp3.
Example:
Select empno,ename,sal,comm,nvl2(comm,1,0)
from emp order by empno;

[B][U]Analytical functions – VI[/U][/B]
[B]Window Clause[/B]
In analytical functions we use The PARTITION BY clause to break a single result set into groups.
We can further sub-partition the result using window clause.
Window clause works with these functions
AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN and SUM, STDDEV,VARIANCE .

We can set up windows based on two criteria:
1.Range type window:- The definition is in terms of values before or after the current ORDER..
It is not valid to use RANGE with datatypes other than numbers and dates.

Ex:- SELECT ename, hiredate, hiredate-100 hiredate_pre,
COUNT(*)
OVER (
ORDER BY hiredate ASC
RANGE 100 PRECEDING
) cnt
FROM emp
ORDER BY hiredate ASC
The range window goes back 100 days from the current row's hiredate and then counts the rows within this range

2. Row Type Window:- The windowing clause is in terms of record numbers

Ex:- SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename
ROWS 2 PRECEDING) "Sliding Total"
FROM emp
ORDER BY deptno, ename

The above query access the 2 rows prior to the current row in a group in order to sum the salaries.

The ROW or RANGE window cannot appear together in one OVER clause.

Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

SELECT Deptno, AVG (sal)
FROM emp
GROUP BY deptno
HAVING deptno != 10

This query gives better performance than above query.

SELECT Deptno, AVG (sal)
FROM emp
WHERE deptno != 10
GROUP BY deptno

[B][U]Difference Between Syntax Check and Semantic Check[/U][/B]
Parsing is the first step in the processing of any statement in Oracle. Parsing is the act of breaking the submitted statement down into its component parts. Determining what type of statement it is (query, DML, DDL) and performing various checks on it.
.
The parsing process performs two main functions:
1. Syntax Check: During this process it checks
Is the statement a valid one? Does it follow SQL Syntax rules?

Example for syntax error
select from where 2;
select from where 2
*
ERROR at line 1:
ORA-00936: missing expression

2.Semantic Analysis: Is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Whether the particular user has access to the objects or not ? Are there ambiguities in the statement ? For example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ? is ambiguous, we don’t know which table to get X from. And so on.

Ex :- select * from not_a_table;
select * from not_a_table
*
ERROR at line 1:
ORA-00942: table or view does not exist

[b][u]difference between hard and soft parsing[/u][/b]
oracle uses a piece of memory called the shared pool to enable sharing of sql statements. The shared pool is a piece of memory in the system global area (sga) and is maintained by the oracle database.
After completing first two phases of parsing i.e. Syntax and semantic checks it looks in the shared pool to see if that same exact query has already been processed by another session. If found, the parsed representation will be picked up and the statement executed immediately. This is called soft parsing.
If not found, then it has to go for next two steps in the process, that of optimization (this involves finding an optimal execution path for the statement) and row source generation. This entire process is called hard parsing.
A soft parse will save a considerable amount of cpu cycles when running your query. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very cpu intensive and a point of contention. If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.

To print matrix report in sql
SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job

While practicing commands you may disturb defualt tables emp, dept etc. To bring it to the normal form find demobld.sql and execute it.

Example:-
@ d:\oracle\oracle9ids\tools\dbtab90\demobld.sql

Where ever it is possible use UNION ALL , which gives better performance than UNION.
UNION first sorts records, then merge the records and finally eliminates duplicate records. Where as UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter.

If the FETCH statement in cursors fail to return a row, no exception is raised.
To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND.
Do not use NO_DATA_FOUND exception in cursors.

[B][U]Data Dictionary – I[/U][/B]
It is a series of table and views that contain information about the structures and users in the database.
It provides
- User names of oracle users.
- The rights and Privileges they have been granted
- Names of database objects
- Constraints applied to a table
- Who accessed and updated database objects

The data dictionary is created in the database when we give create database command.

The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easier for a users to understand. All these tables and views are owned by SYS.

Data Dictionary Provides three types of views
USER_XXX :- Objects owned by the user and that can be accessed
ALL_XXX :- List of all objects that the user has access to
DBA_XXX :- Users with DBA privilege only can access these views and can access any object in the database

[b][u]Data Dictionary – II[/u][/b]
Useful synonyms for data dictionary views most of the data dictionary views have long names. Public synonyms have been provided for convenient access.
Synonym name synonym for table
cat user_catalog
clu user_clusters
dict dictionary
ind user_indexes
obj user_objects
seq user_sequences
sys user_synonyms
tab user_tables
you can directly use these synonyms in queries
select * from obj

[B][U]Data dictionary – III[/U][/B]
Some dictionary views do not use prefixes user, all, dba. Examples for such views are
dictionary - list of all dictionary objects accessible to the user
dict_columns - lists columns in dictionary objects accessible to the user
role_role_privs - roles which are granted to roles
role_sys_privs - system privileges granted to roles role_tab_privs - table privileges granted to roles
session_privs - privileges which current user has set session_roles - roles which current user currently has enabled

[B][U]How to use apostrophe?[/U][/B]
When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol.

SQL> SELECT '''Hi There' Message FROM dual;

MESSAGE
--------------
'Hi There

In the middle of the string, you need to enter 2 single quotes

SQL> SELECT 'He''s always the first to arrive' Message FROM dual;

MESSAGE
-------------------------------
He's always the first to arrive

Single quote is at the end of a string, you need to enter 3 single quotes
SQL> SELECT 'Smiths''' FROM dual;

'SMITHS
-------
Smiths'
If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes
SQL> SELECT 'There' || '''' || 's Henry' Message FROM dual;

MESSAGE
-------------
There's Henry

Is this thread only for SQL/PL-SQL ? :confused:

Hi,

SQL every where!!!:eek:

i hope to get tip(s) from testing issues in near future!!!:):eek:

Thanks & Regards
Trainee_tester