iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

 

PL/SQL QUESTIONS

We are against multiple choice questions. 

Online Oracle Training

PL/SQL Fundamental Exam Questions

Basic PL/SQL

Lesson 01

Q:   What is PL/SQL?

Q:   Where can you store a PL/SQL procedure?

Q:   What is the PL/SQL body section?

Q:   What is the PL/SQL declaration section?

Q:   What does the SET SERVEROUTPUT command?

Q:   What does the DBMS_OUTPUT.PUT_LINE procedure?

Q:   How do you define a variable or variables in the PL/SQL declaration section?

Q:   How do you save a PL/SQL block in the client environment?

Q:   How do you use the %TYPE keyword?

Q:   How do you open a saved PL/SQL block?

Q:   How do you run a saved PL/SQL block?

Q:   What does the %ROWTYPE keyword in the PL/SQL language?

Q:   What is an implicit cursor in the PL/SQL language?

Q:   An implicit cursor must have _________ on its SELECT SQL statement?

Q:   What does the SQL%NOTFOUND reserved PL/SQL word?

Q:  What does the “SET SERVEROUTPUT ON?”

Q:  Write a PL/SQL block, to output the "Hello iSelfSchooling" message.

Q:  Use the %TYPE keyword, to declare a variable as the same datatype and size of the department name column of the dept table.

Q:  Use the implicit cursor to query the department table information where deptno is 30.  Check, if no record was found then print “Record was not found.” Else print the department name only.

 

Explicit Cursor Handling

Lesson 02

Q:   Describe that why do we need to use a solid naming convention in our PL/SQL program.

Q:   What is the explicit cursor in the PL/SQL language?

Q:   What are the differences between the explicit and implicit cursors?

Q:   Where do you declare an explicit cursor in the PL/SQL language?

Q:   Where do you declare an implicit cursor in the PL/SQL language?

Q:   What is a simple loop in the PL/SQL language?

Q:   How do you open an explicit cursor in the PL/SQL language?

Q:   What does the FETCH statement in the Oracle PL/SQL language?

Q:   How do you terminate from a simple loop in the PL/SQL language?

Q:   How do you OPEN or CLOSE a cursor in the PL/SQL language?

Q:  Declare a cursor to list the department name (dname), total number of employees (ttemp), total salary (ttsal), and average salary (avsal) for each department from the department table and employee table order by the department name.

Write all department names with their total number of employees for each department using the notepad editor.

For example: ACCOUNTING has 3 employees.

(Note: Don’t use the ttemp, ttsal, and avsal item at this time)

 

Controlling Process Flow

Lesson 03

Q:   What does the FOR LOOP statement in the PL/SQL language?

Q:   What are the differences between a SIMPLE LOOP and FOR LOOP?

Q:   What are the advantages of using the FOR LOOP statement?

Q:   What does the SHOW ERRORS statement in the PL/SQL language?

Q:   What is the IF-THEN-ELSE statement?

Q:  Modify the previous PL/SQL block and use the “FOR  LOOP ” statement vs the simple “LOOP” statement.  Also, list only the department name that their total number of employees is more than 4.

 

Populating Table using PL/SQL

Lesson 04

Q:   Create a table named "dept_stat".  The table should have four columns:  department name (dname), total number of employees (total_empno), total salary of employees (total_sal), and average salary of employees (avg_sal).  And the department name should be a primary key. The following are its columns, datatypes and index constraint:

                  dname                         VARCHAR2(20) primary key

                  total_empno                      NUMBER(3)

      total_sal                      NUMBER (8,2)

      avg_sal                       NUMBER (8,2)

Q:  Write a PL/SQL block to populate the department table statistics into the “dept_stat” table.

Statistics Information:

The Department Number,

The total number of employees in each department,

The total salary paid in each department, and

The average salary paid in each department.

 

Cursor Parameter

Lesson 05

Q:  What is the cursor parameter in the PL/SQL language?

Q:  Where do you define a cursor parameter in the PL/SQL language?

Q:  Write a PL/SQL block to populate the department table statistics into the “dept_stat” table for a specific department.

Statistics Information:

The Department Number,

The total number of employees in each department,

The total salary paid in each department, and

The average salary paid in each department.  

 

EXCEPTION

Lesson 06

Q:   What is the EXCEPTION section in the PL/SQL language?

Q:   What do you use the EXCEPTION section for?

Q:   What would be happen if you don’t define your exception in the PL/SQL procedure?

Q:   What is an Oracle Defined EXCEPTION?

Q:   What is a User Defined EXCEPTION?

Q:   What are the differences between a User Defined and an Oracle defined exceptions?

Q:  Modify the previous PL/SQL block --last assignment in the previous hands-on practice--to add a user defined exception, to check the total number of employees in a department.  Check if the total number of employees less than 10 then the procedure raises an exception and print a message – “We need more good employees.”

 

Create PL/SQL to populate table using Notepad

Lesson 07

Q:   How do you write a PL/SQL language using NOTEPAD?

Q:   Create a table to keep your customer’s portfolio statistics and name it CUST_STAT.  You should populate into this table a customer last name, his/her traded date, and total stock market value for the traded date. See the following columns and datatypes:

            customer_lname               VARCHAR2(20)

                  trade_date                         DATE

                  portfolio_value                       NUMBER(8,2)

Q:  Write a stored procedure to populate the customer statistics table. Declare a cursor to query all the customer last names, the traded date, and the total stock market value for the traded date. Use a sub-query with a MAX (trade_date) function to guarantee the current stock market value for the traded date.  In the PL/SQL body, use the “FOR” statement to read the cursor information one record at a time.  Then insert the summary statistics data into the customer statistics table.  Use “commit” to save the transaction.  In the exception section, add the “no data found” exception and use the “dbms_output” package to display the error message. Add the “invalid number” exception to detect any invalid input data into the insert command. Add the “Others” exception to detect other problems. Always use the “others” exception in case you miss some other exceptions.

Q:   Then run your created procedure.

Q:   Verify that your table was populated.

 

Create PL/SQL to add department row using procedure builder

Lesson 08

Q:   What is the Procedure Builder Tool?

Q:   What is the listener in the Oracle database?

Q:   How do you start or stop your listener?

Q:   What is the Object Navigator in the Procedure Builder tool?

Q:   How to you open a database using the Procedure Builder tool?

Q:   What is a user’s schema?

Q:   What type of objects can you have under a schema?

Q:   How do you create a procedure using the Procedure Builder Tool?

Q:   What is a Program Unit?

Q:   Write a PL/SQL stored procedure to add a record into the department table (dept).  You use three input parameters to pass the department's columns (Department number “DEPTNO,” department name “DNAME,” and department location “LOC”); and use one output parameter to check the status of the insert transaction. You should use the Procedure Builder.

Note that you should use the "p_" prefix to name the parameters. You use this parameter as an output parameter to check the status of your transaction. Use comments in your programs.  Use double dashes for a single line comment. In addition, use “/*” ended with “*/” for a multiple lines comment. In the “EXCEPITON” section, define the exception. Use the “duplicate value on index” exception, the “invalid number” exception, and the “OTHERS” exception. Use the others in case you are missing other exceptions.

Q:   Write a stored procedure to test the ‘add_department’ procedure.  Declare a status variable and make sure to call the “add_department” procedure. Enter an invalid department number to see the exception error message.  To display the status of your transaction value, use the TEXT_IO  instead of the DBMS_OUTPUT, when you run the procedure locally.

Q:   What is the client side environment?

Q:   What is the server side environment?

Q:   How do you save the above PL/SQL procedure in your local library?

 

Create PL/SQL to remove department row

Lesson 09

Q:   Write a procedure to remove a department record. Make sure to define one input parameter for the department number; and an output parameter as a status parameter. You will use this parameter to test the status of the deleted transaction.

In the PL/SQL body, delete the department record where its department number matches with the input department number parameter. Save the deleted transaction and assign "OK" to the status output parameter for a successful deleted transaction.

Q:   Write a PL/SQL procedure to test the above-created PL/SQL procedure.

Q:   What does the TEXT_IO package?

Q:   Name one procedure that is in the TEXT_IO package.

Q:   What are the differences between the TEXT_IO and DBMS_OUTPUT packages?

 

Create PL/SQL to concatenate customer’s name

Lesson 10

Q:   What is the PL/SQL function?

Q:   What are the differences between the PL/SQL function and procedure?

Q:   When do you create the PL/SQL function?

Q:   write a PL/SQL Function to concatenate the customer's last name and first name to be separated by a comma. For example: Kazerooni, John. Name the function "Full_Name,” and declare a datatype for the Function return value. Declare a first name and last name input parameters. Their datatypes should match with the datatype of the firstname and lastname in the customers table.

In the PL/SQL body, return the customers’ concatenated name. Write the exception. In the exception section, do nothing in the case of an error handling exception.

Q:   How do you execute the above-created PL/SQL function in the SQLPLUS tool?

Q:   What is the PL/SQL interpreter?

Q:   How do you execute a PL/SQL procedure in the PL/SQL interpreter?

 

Create PL/SQL to return department name

Lesson 11

Q:   Write a PL/SQL Function to return the department name (dname).  You use one input parameter to pass the department number (DEPTNO) and return its department name.

Q:   In the “PL/SQL interpreter” section, use the “select” statement and use the department number 10 to test the function.

Q:  To test the exception, call the function again using the department number that does not exist in the department table.

Q:   Query the department name function against the employee table sorted by the employee name.

 

Debugging PL/SQL Stored Procedure

Lesson 12

Q:   How do you debug a PL/SQL procedure?

Q:   How do you move a PL/SQL procedure to the PL/SQL interpreter’s source area?

Q:   What is the BREAKPOINT indicator in the PL/SQL interpreter?

Q:   How do you create a BREAKPOINT in the PL/SQL interpreter?

Q:   How do you activate the Step Into, Step Out, and Reset icon in the PL/SQL interpreter?

Q:   What does the Step Into icon in the PL/SQL interpreter?

Q:   What does the Step Out icon in the PL/SQL interpreter?

Q:   What does the Reset icon in the PL/SQL interpreter?

Q:   What does the STACK section contain?

Q:   How can you see the columns and variables values in the PL/SQL program using the PL/SQL interpreter?

Q:   How do you store a PL/SQL procedure in the PL/SQL library?

Q:   Can you have multiple versions of a PL/SQL procedure in the PL/SQL library?

Q:   How do you store a PL/SQL procedure in your database server?

Q:   How can you copy a PL/SQL procedure to your database server?

Q:   What would be happen if you move or copy a locally PL/SQL procedure with its local packages into the database server?

 

Granting Object privileges

Lesson 13

Q:   What is an Object Privilege?

Q:   What are System Privileges?

Q:   How do you create a user in the Oracle database?

Q:   How do you assign a default and temporary tablespace to a user in the Oracle database?

Q:   What are the System Privileges in the RESOURCE and CONNECT roles?

Q:   How do you grant an object privilege to a user?

Q:   How do you grant a system privilege to a user?

Q:   What is the Public Synonym in the Oracle database?

Q:   How do you create a PUBLIC SYNONYM?

Q:   Why do you need a PUBLIC SYNONYM?

Q:   What is the EXECUTE privilege? Is it a system privilege or an object privilege?

Q:   Can you grant the EXECUTE privilege to a table?

Q:   What is the Private Synonym in the Oracle database?

Q:   What are the differences between a private synonym and public synonym?

Q:   How do you revoke a system privilege from an Oracle user?

Q:   How do you revoke an object privilege from an Oracle user?

Q:   Mr. A granted to Mr. B an object privilege with a ‘WITH GRANT OPTION’ and then Mr. B granted the same privilege to Mr. C. You decide to revoke the Mr. B’s object privilege. What would be happen to Mr. C’s granted object privilege?

Q:   Mr. A granted to Mr. B a system privilege with a ‘WITH ADMIN OPTION’ and then Mr. B granted the same privilege to Mr. C. You decide to revoke the Mr. B’s system privilege. What would be happen to Mr. C’s granted system privilege?

Q:   How do you know that a privilege is the system privilege or object privilege?

Q:   On the GRANT ALL statement, what “ALL” means if your grant is on a PL/SQL procedure?

 

Managing Objects Dependency

Lesson 14

Q:   What is an object dependency in the Oracle database?

Q:   What is a timestamp?

Q:   How do you query all the objects that was create by you (your schema)?

Q:   How do you change a datatype of a column in a table?

Q:   How do you compile a PL/SQL function?

 

Create a Packages

Lesson 15

Q:   What is the PL/SQL package?

Q:   What are the components of a PL/SQL package?

Q:   What is a package body in the PL/SQL language?

Q:   What is a package specification in the PL/SQL language?

Q:   Where do you save the package body and its package specification?

Q:   Can you store a PL/SQL package in a client environment?

Q:   How do you create a package specification and body?

Q:   What are the dependencies between a package body and its package specification?

Q:   Write a PL/SQL package to have all your created PL/SQL functions and procedures.

Q:   What is a public PL/SQL procedure or function in a PL/SQL package?

Q:   What is a private PL/SQL procedure or function in a PL/SQL package?

Q:   What are the differences between a public or private PL/SQL procedure?

Q:   How do you run a PL/SQL procedure or function in a PL/SQL package?

 

Developing and using Database Triggers

Lesson 16

Q:   What is a database trigger?

Q:   How do you create a trigger?

Q:   If you drop a table that contains a trigger, does its trigger drop?

Q:   Create a trigger to audit department table (dept) to keep track of all the insert, update, and delete transactions and insert the audited transaction to a table.

Q:   How do you compile a trigger?

Q:   How do you disable or enable a trigger?

Q:   How do you test your created trigger?

Q:   How do you modify a trigger?

Q:   How do you drop a trigger?

Q:   When you drop a trigger, does its table drop?

 

Using the DBMS_DDL package

Lesson 17

Q:   How do you increase the size of SERVEROUTPUT buffer?

Q:   Can you perform a DDL statement in the PL/SQL block?

Q:   How can you compile an object in a PL/SQL block?

Q:   What does the DBMS_DDL package?

Q:   What does the ANALZE_OBJECT procedure in the DBMS_DDL package and how can you verify that the object was ANALYZED?

Q:   What does the ALTER_COMPILE procedure in the DBMS_DDL package and how can you verify that the object was compiled?

 

Using Native Dynamic SQL

Lesson 18

Q:   What is a Native Dynamic SQL statement?

Q:   Write a stored procedure to pass the table name and get back the number of records that table contains. The SELECT statement must be created dynamically, since you don’t know what table you are getting statistics from. You should write your function so that your client can display the tables’ name, plus the number of records contained each table.

 

Calling JAVA Stream in Oracle

Lesson 19

Q:   How do you check that you have the JAVA tool installed in your server?

Q:   What should it be at least size for the JAVA pool memory usage?

Q:   How do you create a JAVA class?

Q:   How do you publish a JAVA class?

Q:   How do you test a JAVA function?

Q:   How do you drop a JAVA source and Function?

 

Inserting employees’ picture into the EMP table using BLOB

Lesson 20

Q:   How do you add a column to a table?

Q:   What does the EMPTY_BLOB() function?

Q:   How do you create a directory in the Oracle database?

Q:   Does everyone can create a directory in the Oracle database?

Q:   Write a stored procedure to read the employee number and its photo file name and then store the employee’s picture into the EMP table.

Q:   How do you test that there is a picture in a column?

Q:   What does the DBMS_LOB package?

Q:   What does the GETLENGTH() function in the DBMS_LOB package?

Q:   How do you drop a directory from your Oracle database?

Q:   How and when do you grant the CREATE ANY DIRECTORY privilege to a user?

Q:   How do you revoke the CREATE ANY DIRECTORY privilege from a user?

 

 In General:

Lesson 21

Q:   Describe the command line technique.

Q:   How do you use the define_editor command?

Q:   How many categories of PL/SQL blocks are there?

Q:   What is an anonymous block?

Q:   What is a named block?

Q:   What does a PL/SQL block contain?

Q:   What is the EXCEPTION section in the PL/SQL block?

Q:   What are the differences between an implicit cursor and explicit cursor?

Q:   How do you obtain data from a cursor?

Q:   What is a LOOP in the PL/SQL block and how many types are there?

Q:   What is advantage of the FOR LOOP statement verses of the simple LOOP statement?

Q:   How do you pass a parameter to a PL/SQL procedure or function?

Q:   What does the procedure builder tool?

Q:   Describe the server-side and client-side machine.

Q:   What is the Breakpoint in the Oracle Procedure builder tool?

Q:   What is the “DBA_DEPENDENCIES” dictionary view?

Q:   What is a PL/SQL package?

Q:   What is a package specification?

Q:   What is a package body?

Q:   What is a public and private procedure in the PL/SQL package?

Q:   In what sequence do you compile a package body and specification?

Q:   What is a database trigger?

Q:   What are the differences between the statement and row triggers in the Oracle database?

Q:   What do the UPDATING, DELETING, or INSERTING keywords?

Q:   How do you enable, disable, and drop a trigger in the Oracle database?

 

Answers

 

Google
 
Web web site