Page 1 of 10 123 ... LastLast
Results 1 to 20 of 194

Thread: Geeks - Tip of the Day

  1. #1
    Administrator
    Join Date
    May 2006
    Answers
    331

    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.

  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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.

  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

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


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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


  5. #5
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    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;


  6. #6
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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


  7. #7
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    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.

  8. #8
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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


  9. #9
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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.


  10. #10
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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.

  11. #11
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    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'


  12. #12
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    To move table from one tablespace to another Tablespace

    ALTER TABLE tablename MOVE TABLESPACE tablespacename;


  13. #13
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Geeks - Tip of the Day

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


  14. #14
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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.


  15. #15
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

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


  16. #16
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    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


  17. #17
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    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;


  18. #18
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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;


  19. #19
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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.


  20. #20
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

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


Page 1 of 10 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact