Oracle PL/SQL Interview Question - Part 01

1. TCO 6 - What is meant by the term package body? (Points: 10)
 
 

2. TCO 6 - Discuss two situations in which parameter modifications are not viewed as signature modifications and, therefore, do not prompt recompilation. (Points: 10)
 
 

3. TCO 8 - How does ColdFusion work? (Points: 10)
 
 

4. TCO 9 - Briefly discuss the difference between DAO and RDO. (Points: 10)
 
 

5. TCO 3 - Briefly discuss why you would most commonly use some form of loop structure to process data using ULT_FILE. (Points: 10)
 

Oracle SQL Interview Questions - Part 01

TCO 1 - Which of the following statements about the three-tier application model is true? (Points: 4)
        Application code is loaded onto the client machine. 
        Application code is not loaded onto the client machine. 
        All the application code resides on a database server. 
        All the application code is stored in a package. 


2. TCO 1 - ____ are named memory areas that hold values to allow the retrieval and manipulation of values in a program. (Points: 4)
        Loops 
        Assignment statements 
        Blocks 
        Variables 


3. TCO 1 - Which of the following serves as a visual representation of a database? (Points: 4)
        ERD 
        ERP 
        EDD 
        DFD 


4. TCO 2 - Oracle supplies a set of ____ or names associated with common Oracle errors. (Points: 4)
        predefined exceptions 
        looping constructs 
        exception handlers 
        Searched CASES 


5. TCO 2 - Which of the following refers to a SELECT statement in a PL/SQL block that retrieves more than one row? (Points: 4)
        NO_DATA_FOUND 
        ZERO_DIVIDE 
        CASE_NOT_FOUND 
        TOO_MANY_ROWS 


6. TCO 2 - Which of the following returns the Oracle error number? (Points: 4)
        TOO_MANY_ROWS 
        WHEN OTHERS 
        SQLERRM 
        SQLCODE 


7. TCO 3 -
CURSOR basketitem_curIS
SELECT idproduct, quantity, option1
FROM bb_basketitem
WHERE idbasket = :NEW.idbasket;
In the code fragment above, which of the following represents the correlation identifier? (Points: 4)
        idproduct
        bb_basketitem
        quantity
        :NEW.idbasket


8. TCO 3 - Which of the following statements is correct? (Points: 4)
        If multiple triggers exist on a table, the order in which the triggers will be fired can be set.
        A BEFORE statement level trigger will fire before a BEFORE row level trigger.
        If you have two statement level triggers, the order in which they are fired is dependent on the order in which they were written.
        Only one trigger can be constructed per table.


9. TCO 3 - ____ of OLD and NEW allow original and new row values of a DML action to be referenced. (Points: 4)
        Instead-Of triggers
        Conditional predicates
        Correlation identifiers
        Mutating tables
TCO 4 - ____ is the area of a program block that can identify a particular variable. (Points: 4)
        Variable scope 
        Cursor 
        Index-by table 
        DECLARE section 


2. TCO 4 - ____ are declared and manipulated in the PL/SQL block code for handling a set of rows returned by a SELECT statement.(Points: 4)
        Implicit cursors 
        Collections 
        Index-by tables 
        Explicit cursors 


3. TCO 4 - Which of the following code fragments correctly uses a record variable to hold the row of data queried for a shopper?(Points: 4)
        DECLARE
rec_shopper bb_shopper%ROWTYPE;
BEGIN
SELECT*
INTO rec_shopper
FROM bb_shopper
WHERE idshopper = :g_shopper;
DBMS_OUTPUT.PUT_LINE(rec_shopper.lastname);
DBMS_OUTPUT.PUT_LINE(rec_shopper.address);
DBMS_OUTPUT.PUT_LINE(rec_shopper.email);
END; 
        DECLARE
rec_shopper bb_shopper%ROW;
BEGIN
SELECT*
INTO rec_shopper
FROM bb_shopper
WHERE idshopper = :g_shopper;
DBMS_OUTPUT.PUT_LINE(rec_shopper.lastname);
DBMS_OUTPUT.PUT_LINE(rec_shopper.address);
DBMS_OUTPUT.PUT_LINE(rec_shopper.email);
END; 
        DECLARE
rec_shopper bb_shopper%TYPE;
BEGIN
SELECT*
INTO rec_shopper
FROM bb_shopper
WHERE idshopper = :g_shopper;
DBMS_OUTPUT.PUT_LINE(rec_shopper.lastname);
DBMS_OUTPUT.PUT_LINE(rec_shopper.address);
DBMS_OUTPUT.PUT_LINE(rec_shopper.email);
END; 
        DECLARE
rec_shopper bb_shopperROWTYPE;
BEGIN
SELECT*
INTO rec_shopper
FROM bb_shopper;
WHERE idshopper = :g_shopper;
END; 


4. TCO 5 - All of the following are purity level acronyms, except ____. (Points: 4)
        WNPS 
        RINDS 
        WNPS 
        RNPS 


5. TCO 5 - All of the following represent restrictions on functions, except ____. (Points: 4)
        Functions cannot modify any tables in Oracle8 and prior versions. 
        Functions cannot be used in a CHECK constraint or as a default value of a table column. 
        If used in a local operation, no reading or writing of packaged variables is allowed. 
        If used in a SELECT, VALUES, or SET clause, the function can write values to packaged variables; otherwise, it is not allowed. 


6. TCO 5 - Which of the following is identified with a set of acronyms that indicate the restrictions on using the function? (Points: 4)
        Compiler hint 
        Purity level 
        Pass by reference 
        Pass by value 


7. TCO 6 - Which of the following views can be used to review the source code of a procedure, function, or package? (Points: 4)
        USER_SOURCE 
        USER_OBJECT 
        USER_REVIEW 
        USER_DEPENDENCY 


8. TCO 6 - Which of the following can be used to check the status of database objects? (Points: 4)
        USER_OBJECTS 
        USER_REFERENCE 
        USER_CHECK 
        USER_SOURCE 


9. TCO 6 - All of the following represent packaged function purity levels, except ____. (Points: 4)
        WNDS 
        RNDS 
        WNPS 
        RNPS