-
Administrator
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
Last edited by admin; 02-27-2008 at 08:06 AM.
-
Expert Member
Re: Geeks - Tip of the Day
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;
Last edited by krishnaindia2007; 02-27-2008 at 11:36 PM.
-
Expert Member
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'));
-
Expert Member
Re: Geeks - Tip of the Day
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
-
Expert Member
Re: Geeks - Tip of the Day
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;
-
Expert Member
Re: Geeks - Tip of the Day
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
-
Expert Member
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
Last edited by susarlasireesha; 03-01-2008 at 02:25 AM.
-
Expert Member
Re: Geeks - Tip of the Day
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
-
Expert Member
Re: Geeks - Tip of the Day
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.
-
Expert Member
Re: Geeks - Tip of the Day
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
Last edited by krishnaindia2007; 03-05-2008 at 08:35 AM.
-
Expert Member
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'
-
Expert Member
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.
-
Expert Member
Re: Geeks - Tip of the Day
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.
-
Expert Member
Re: Geeks - Tip of the Day
You can not specify when clause for statement trigger and instead of triggers.
-
Expert Member
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
-
Expert Member
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;
-
Expert Member
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;
-
Expert Member
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.
In order to use : new or old, the trigger must be a row level trigger.
-
Expert Member
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);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules