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.
how to create a read only table in oralce?
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'));

How to remove spaces in the spooled output ?
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

Alternative to imp/exp utility
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;

How to get database object definition?
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

To Suppress unneccessary blank spaces in output using FM

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

To set SQL *PLUS environment
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.

comments
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.

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

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

insert into test_1 values('1001',NULL)

select decode (comm,NULL,'EQUAL','NOT EQUAL') from test_1

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.
In order to use : new or old, the trigger must be a row level trigger.

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);