Re: Geeks - Tip of the Day
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;
Re: 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'));
Re: Geeks - Tip of the Day
[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
Re: Geeks - Tip of the Day
[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;
Re: Geeks - Tip of the Day
[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
Re: Geeks - Tip of the Day
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
Re: Geeks - Tip of the Day
[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
Re: Geeks - Tip of the Day
[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.
Re: Geeks - Tip of the Day
[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
Re: Geeks - Tip of the Day
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'
Re: Geeks - Tip of the Day
To move table from one tablespace to another Tablespace
ALTER TABLE tablename MOVE TABLESPACE tablespacename;
Re: Geeks - Tip of the Day
Trigger body can't declare any long or lob datatype.
Re: Geeks - Tip of the Day
[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.
Re: Geeks - Tip of the Day
You can not specify when clause for statement trigger and instead of triggers.
Re: Geeks - Tip of the Day
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
Re: Geeks - Tip of the Day
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;
Re: Geeks - Tip of the Day
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;
Re: Geeks - Tip of the Day
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]
Re: Geeks - Tip of the Day
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);