Page 5 of 10 FirstFirst ... 34567 ... LastLast
Results 81 to 100 of 194

Thread: Geeks - Tip of the Day

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

    Re: Geeks - Tip of the Day

    Cursors retrieve the values one at a time from the database, and process them sequentially.
    Collections retrieve all values in one go and You can carry out operations on the group of values as a whole.


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

    Re: Geeks - Tip of the Day

    What is an autonomous transaction?
    -An autonomous transactions is available from Oracle 8i.
    -An autonomous transaction is an independent transaction that is initiated by another transaction.
    -It must contain at least one Structured Query Language (SQL) statement
    -Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions.
    -When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended.
    -The autonomous transaction must commit or roll back before it returns control to the calling transaction.
    -Autonomous transactions can be nested. In theory, there is no limit to the possible number of nesting levels.
    -An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
    -This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block.


  3. #83
    Junior Member
    Join Date
    May 2008
    Answers
    1

    Re: Geeks - Tip of the Day

    Why do you want to join our team?


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

    Re: Geeks - Tip of the Day

    Trigger may have same name as that of other schema objects, such as tables, views, and procedures. However, to avoid confusion, this is not recommended.


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

    Re: Geeks - Tip of the Day

    The BEFORE and AFTER options cannot be used for triggers created over views.


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

    Re: Geeks - Tip of the Day

    Difference between Object Type and Record Type

    Record type works only in Pl/Sql.

    Object type gets stored in database.
    We can use it both in sql and pl/sql.


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

    Re: Geeks - Tip of the Day

    When an exception is raised inside a cursor for loop, the cursor is closed implicitly before the handler is invoked. So the value of explicit cursor are not available in the handler.


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

    Re: Geeks - Tip of the Day

    If you want to execute the same sequence of statements for more than one exception then list the exception names separated by the keyword OR. We should not use the others exception in the list separated by OR. It must appear by itself


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

    Re: Geeks - Tip of the Day

    A goto statement can’t be branch to an exception handler nor can it branch from an exception handler to the current block


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

    Re: Geeks - Tip of the Day

    Compiler hint nocopy

    By default in subprograms
    IN parameter is passed by reference.
    OUT and IN OUT parameters are passed by value.

    When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.


    NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned

    By default, if a subprogram exits with an unhanded exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."


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

    Re: Geeks - Tip of the Day

    Cast Function

    cast function converts one datatype to another.

    It is available from Oracle 9i version.

    Syntax:- cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

    Ex:- SELECT CAST(SUBSTR('ABZ123',-3,3) AS NUMBER) FROM DUAL

    In the above example cast converts last three digits of string to number data type.


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

    Re: Geeks - Tip of the Day

    How to find number of ‘e’s in a word ?

    SELECT length('geekinterview.com') - length (replace ('geekinterview.com', 'e'))
    FROM DUAL;


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

    Re: Geeks - Tip of the Day

    A subprogram can have only one other exception in a program.


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

    Re: Geeks - Tip of the Day

    Exceptions cannot be propagated across remote procedure calls


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

    Re: Geeks - Tip of the Day

    ADD_MONTHS returns the date plus integer months.
    If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.Otherwise, the result has the same day component as date.

    Code:
    SQL> SELECT add_months('31-JAN-2008',1) 
      2  FROM dual;
    
    ADD_MONTH
    ---------
    29-FEB-08
    
    SQL> SELECT add_months('31-JAN-2008',2)
      2  FROM   dual;
    
    ADD_MONTH
    ---------
    31-MAR-08



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

    Re: Geeks - Tip of the Day

    If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.


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

    Re: Geeks - Tip of the Day

    If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

    From Session1

    Code:
    SQL> UPDATE    emp
      2  SET       sal  = 5000
      3  WHERE     empno = 7369;
    
    1 row updated.
    From Sesion2

    SQL> ALTER TABLE emp ADD (Dateofjoin DATE);
    ALTER TABLE emp ADD (Dateofjoin DATE)
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified


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

    Re: Geeks - Tip of the Day

    Aggregate functions can appear in
    - select lists
    - ORDER BY
    - HAVING clauses

    Single Row functions can appear in
    - select lists
    - WHERE clauses
    - START WITH
    - CONNECT BY clauses
    - HAVING clauses.


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

    Re: Geeks - Tip of the Day

    The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database irrespective of whether it is hard parsing or soft parsing.


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

    Re: Geeks - Tip of the Day

    A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.


Page 5 of 10 FirstFirst ... 34567 ... 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