Thread: Geeks - Tip of the Day

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

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