Page 4 of 10 FirstFirst ... 23456 ... LastLast
Results 61 to 80 of 194

Thread: Geeks - Tip of the Day

  1. #61
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Collections –VI
    Bulk Binding·
    Bulk Binds are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements sequentially, all of the operations are carried out at once, in bulk.
    · Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.
    · BULK_COLLECT and FORALL, together these two features are known as 'Bulk Binding'.
    · To do bulk binds with INSERT, UPDATE, and DELETE statements, use FORALL statement.
    Syntax:- FORALL index IN lower_bound..upper_bound
    sql_statement;
    FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine
    · To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.
    Syntax:-
    …BULK COLLECT INTO collection_name[, collection_name] ...
    BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine.
    You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses


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

    Re: Geeks - Tip of the Day

    How to find Oracle connection string?

    SELECT '&&_CONNECT_IDENTIFIER' FROM dual;
    But it works from oracle 9i version onwards only.


  3. #63
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    in vs exists

    In vs Exists which gives better performance depends upon the situation.

    The query using in
    select * from emp where deptno in (select deptno from dept)
    is processed as
    select * from emp x , (select distinct deptno from dept) y
    where x.deptno = y.deptno

    The inner query is first evaluated, distincted, indexed and then joined to the original table.
    Table in the subqery is small and table in the main query is large then in usaully makes sense.

    Where as in the case of exists
    select * from emp a where exists ( select deptno from dept b where a.deptno = b.deptno)
    is processed as

    for deptno in ( select * from emp )
    loop
    if ( exists ( select deptno from dept where deptno = emp.deptno )
    then
    statement
    end if
    end loop

    It always results in a full scan of emp.
    If the main table size is small and subquery table size is large then exists usually makes sense.

    If both the tables are large then which gives better performance depends upon indexes and other factors.


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

    Re: Geeks - Tip of the Day

    selecting 5 random records from a table
    we can use dbms_random.random to generate random numeric values. It returns binary_integer; ex:- select empno
    from
    (select empno
    from emp
    order by dbms_random.random)
    where rownum <= 5


  5. #65
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Oracle To_date function is used to convert a string to date.

    Never use to_date function with a date format.

    For example
    Select to_date(hiredate) from emp;

    Here to_date first converts hiredate to character format and then once again convert it back to date format. Sometimes this may give unexpected results .

    Last edited by krishnaindia2007; 05-13-2008 at 11:01 PM.

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

    Re: Geeks - Tip of the Day

    In PL/SQL , we can handle warning or error condition in exception handling part. In exception handling part we can handle runtime errors only. we can not catch compile time errors using exceptions.


  7. #67
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Reference Cursors - I

    Reference cursors are dynamic in nature. In static cursors once the cursor is defined the contents of the cursor’s are fixed. In case of dynamic cursors the contents of cursors can be dynamically changed depending on the requirement. Dynamic cursors can be defined using REF type.

    A REF CURSOR is basically a data type. A variable created based on such data type is called cursor variable. A cursor variable can be associated with different queries at run time.


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

    Re: Geeks - Tip of the Day

    Reference Cursors - II
    Example for associating cursor variable with different queries at run time.

    Create or replace procedure test_ref as
    -- declaring reference cursor
    type my_refcursor is ref cursor;
    -- declaring cursor variable
    my_ref_var my_refcursor;
    emp_rec emp&#37;rowtype;
    dept_rec dept%rowtype;
    begin
    --open cursor variable to select from emp table
    open my_ref_var for select * from emp;
    loop
    fetch my_ref_var into emp_rec;
    exit when my_ref_var%notfound;
    dbms_output.put_line( 'employee name is '||emp_rec.ename);
    dbms_output.put_line( 'employee job is '||emp_rec.job);
    dbms_output.put_line( 'employee sal is '||emp_rec.sal);
    end loop;
    close my_ref_var;
    --open cursor variable to select from dept table
    open my_ref_var for select * from dept;
    loop
    fetch my_ref_var into dept_rec;
    exit when my_ref_var%notfound;
    dbms_output.put_line( 'department name is '||dept_rec.dname);
    dbms_output.put_line( 'location is '||dept_rec.loc);
    end loop;
    close my_ref_var;
    end;
    /

    Last edited by krishnaindia2007; 05-17-2008 at 05:04 AM.

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

    Re: Geeks - Tip of the Day

    Rreference Cursors – III
    A ref cursor can be a strongly typed or weakly typed

    If return clause is omitted then it is a weakly typed ref cursor.
    Ex:- type my_refcursor is ref cusor.
    - This gives greater flexibility
    - Increases the likelihood of runtime errors because column mismatches are not picked up at compile time.

    Return type is mentioned then it is called strongly typed ref cursor.
    Ex:- type my_refcursor is ref cursor return emp&#37;rowtype.
    - This reduces the chances of runtime errors since column mismatches are detected at compile time.
    - It limits the flexibility of type

    The return type of strongly typed ref cursor can be
    1.%Rowtype
    2.%Type
    3.Record structure

    We can’t return a scalar type.

    Last edited by krishnaindia2007; 05-17-2008 at 05:09 AM.

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

    Re: Geeks - Tip of the Day

    Reference Cursors – IV
    Example for strongly typed ref cursor using &#37;rowtype

    Create or replace procedure test_ref as
    type my_refcursor is ref cursor return emp%rowtype;
    my_ref_var my_refcursor;
    emp_rec emp%rowtype;
    begin
    open my_ref_var for select * from emp;
    loop
    fetch my_ref_var into emp_rec;
    exit when my_ref_var%notfound;
    dbms_output.put_line( 'employee ' ||emp_rec.ename || ' salary is '||emp_rec.sal);
    end loop;
    close my_ref_var;
    end;
    /

    Last edited by krishnaindia2007; 05-17-2008 at 05:15 AM.

  11. #71
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Reference cursors – V
    Example for strongly typed ref cursor using %type

    create or replace procedure test_ref as
    emp_rec emp%rowtype;
    type my_refcursor is ref cursor return emp_rec%type;
    my_ref_var my_refcursor;

    begin
    open my_ref_var for select * from emp;
    loop
    fetch my_ref_var into emp_rec;
    exit when my_ref_var%notfound;
    dbms_output.put_line( 'employee ' ||emp_rec.ename || ' salary is '||emp_rec.sal);
    end loop;
    close my_ref_var;
    end;
    /


  12. #72
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Reference Cursors – VI
    Example for strongly typed ref cursor using record
    create or replace procedure test_ref as
    type emp_recordtype is record
    (
    ename varchar2(10),
    sal number(7,2)
    );

    type my_refcursor is ref cursor return emp_recordtype; my_ref_var my_refcursor;
    emp_recvar emp_recordtype;
    begin
    open my_ref_var for select ename,sal from emp;
    loop
    fetch my_ref_var into emp_recvar;
    exit when my_ref_var%notfound;
    dbms_output.put_line( 'employee ' ||emp_recvar.ename || ' salary is '||emp_recvar.sal);
    end loop;
    close my_ref_var;
    end;
    /


  13. #73
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Reference Cursors – VII

    Differences between a cursor and ref cursor.
    1. A cursor is static in nature. Once the cursor is defined the contents of a cursor is fixed. Where as in ref cursors the contents of the cursors can be dynamically changed depending upon the requirement.
    2. Ref cursor can be returned to a client where as a cursor cannot be returned to a client.
    3. Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)
    4. A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.
    5. Static sql (not using a ref cursor) is much more efficient than using ref cursors and that use of ref cursors should be limited to- returning result sets to clients- when there is NO other efficient/effective means of achieving the goal


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

    Re: Geeks - Tip of the Day

    Reference Cursors – VIII
    example for ref cursor passed as a parameter to a procedure
    declare type my_refcursor is ref cursor return emp%rowtype; my_refcur my_refcursor;
    procedure test_ref (emp_cur in my_refcursor) is
    emp_rec emp%rowtype;
    begin
    loop
    fetch emp_cur into emp_rec;
    exit when emp_cur%notfound;
    dbms_output.put_line(emp_rec.ename ||' is a ' || emp_rec.job);
    end loop;
    end;
    begin
    open my_refcur for select * from emp;
    test_ref(my_refcur);
    close my_refcur;
    end;


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

    Re: Geeks - Tip of the Day

    Reference Cursors – IX

    SYS_REFCURSOR

    SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it.
    SYS_REFCURSOR can be used to:
    Delcare a cursor variable in an Oracle stored precedure/function;
    Pass cursors from and to an Oracle stored precedure/function

    Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types.


  16. #76
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Reference Cursors – X
    How to get a refcursor cursor variable out from a function?
    --first compile the function.
    Create or replace function get_emp_by_dept (v_deptno emp.deptno%type)
    return sys_refcursor is
    emp_refcur sys_refcursor;
    begin
    open emp_refcur for select empno, ename from emp where deptno = v_deptno;
    return emp_refcur;
    end;

    --then execute the following block
    declare
    deptno emp.deptno%type;
    empno emp.empno%type;
    ename emp.ename%type;
    emp_refcur sys_refcursor;
    begin
    emp_refcur := get_emp_by_dept(10);
    dbms_output.put_line('empno ename');
    dbms_output.put_line('----- -------');
    loop
    fetch emp_refcur into empno, ename;
    exit when emp_refcur%notfound;
    dbms_output.put_line(empno || ' ' || ename);
    end loop;
    close emp_refcur;
    end;


  17. #77
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    How to view list of privileges?
    select * from system_privilege_map;


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

    Re: Geeks - Tip of the Day

    Difference between IW and WW?
    Both IW and WW retruns week of the year.
    IW element of the date format gives the ISO week number.

    The following are the differences.
    - ISO weeks are always seven days long
    - The ISO week 1 may start in the end of the previous year
    - There may or may not be a week 53
    - The last ISO week of the year may contain days from the next year.
    whereas
    - WW always has week 53
    - WW week 53 is 1 or 2 days long
    - WW week 1 always starts on 01-JAN
    - WW weeks only ever contain days from the year in question.

    Observe the difference

    select to_char(to_date('31/12/2008','DD/MM/YYYY'),'IW') from dual

    select to_char(to_date('31/12/2008','DD/MM/YYYY'),'WW') from dual


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

    Re: Geeks - Tip of the Day

    You must include an actual parameter for any IN OUT parameters, even if they have default values.


  20. #80
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Inside a package you can't reference host variables.


Page 4 of 10 FirstFirst ... 23456 ... 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