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    |

 

 

ANSWERS

Questions

More Resources by Google:

SQL

 

PL/SQL

 

FORMS

 

REPORTS

 

DBA Fundamentals I

 

DBA Fundamentals II

 

Performance Tuning

 

Oracle 10g New Features

PL/SQL Fundamental Exam Answers

 

NOTE: The answers go with their sequences. If a question was not answer, that means that it a repeating question and the answer was given by the previous questions or it is not in the scope of this subject.

 

“Watch your thoughts; they become words.  Watch your words; they become actions.  Watch your actions; they become habits.  Watch your habits; they become character.  Watch your character; it becomes your destiny.”


--Frank Outlaw

Answers:

 

Q:   What is PL/SQL?

A:   PL/SQL is a language that was provided by Oracle.

 

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

A:   It can be stored on the Oracle database (server side) or in a library that was created in the users’ PC (client side).

 

Q:   What is the PL/SQL body section?

A:    It is a section that executes PL/SQL statements.

 

Q:   What is the PL/SQL declaration section?

A:   It is a section that you can declare the stored procedure’s variables.

 

Q:   What does the SET SERVEROUTPUT command?

A:   We use the SET SERVEROUTPUT command to display the content of the Oracle buffer into our screen.

 

Q:   What does the DBMS_OUTPUT.PUT_LINE procedure?

A:   The DBMS_OUTPUT.PUT_LINE procedure writes the passing string into the Oracle buffer.

 

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

A:   SQL> DECLARE

   v_dname VARCHAR2(14);

BEGIN

   v_dname := 'HR';

   dbms_output.put_line(v_dname);

END;

/

 

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

A:   There are so many ways. But one way is:

            SQL> SAVE c:\iself\test_myblock.sql

 

Q:   How do you use the %TYPE keyword?

A:      v_dname             dept.dname%TYPE;

We use the %type keyword, to declare the v_dname variable as the same datatype and size of the department name

 column of the dept table.

 

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

A:   SQL> GET c:\iself\test_myblock.sql

 

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

A:   SQL> run c:\iself\test_myblock.sql

 

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

A:   The %ROWTYPE keyword creates a composite datatype in which all the columns of a row are 

pieced together into a record.

 

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

A:   If we define our cursor in the PL/SQL body, it will be called an implicit cursor.

 

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

A:   INTO

 

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

A:   The SQL%NOTFOUND reserved word returns the FALSE value if there are 

records to read from cursor and returns the TRUE value if there are not record exist to read from cursor.

 

Q:  What does the “SET SERVEROUTPUT ON?”

A:   It displays the Oracle buffer used by the DBMS_OUTPUT package.

 

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

A:   SQL> BEGIN

   dbms_output.put_line('Hello iselfschooling');

END;

/

 

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

A:        DECLARE

   v_dname dept.dname%TYPE;

BEGIN

   v_dname  := 'HR';

   dbms_output.put_line(v_dname);

END;

/

 

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.

A:               declare

   v_drec dept%rowtype;

begin

    select * into v_drec

        from dept

        where deptno = 30;

             if sql%notfound then

dbms_output.put_line('Record was not found.');

   else

dbsm_output.put_line(v_drec.dname);

   end if;

end;

/

 

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

A:   Easy to read.

 

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

A:   It is a cursor that was declared in the PL/SQL declaration section and returns more than one records.

 

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

A:  

1-     Explicit cursor will be defined in the declaration section but implicit cursor will be defined in the execution or body section.

2-     The implicit cursor must have INTO clause in its SQL statement.

3-     The explicit cursor can return more than one record but the implicit cursor should only return one and only one record.

 

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

A:   In the PL/SQL declaration section.

 

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

A:   In the PL/SQL body section.

 

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

A:   It is a loop that we must program our exit from the loop. We must make sure that we don’t get in an infinite loop.

 

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

A:   OPEN  cursor_name;

 

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

A:    It reads one record at a time.

 

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

A:   EXIT  WHEN condition2exit;

 

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

A:         OPEN  cursor_name;

            CLOSE cursor_name;

 

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

A:               DECLARE

              -- Declare a variable for a cursor.

TYPE t_ds IS  RECORD (

dname      dept.dname%type,

ttemp      number(3),

ttsal       number(8,2),

avsal     number(8,2));

 

                    -- define department statistics

                    CURSOR c_ds is

                    select dname, count (*) ttemp,

sum(sal) ttsal, avg(sal) avsal

                             from dept d, emp e

                             where d.deptno = e.deptno

group by dname

order by 1;

 

-- define a variable for cursor

v_ds     t_ds;

          BEGIN

-- open the cursor

     OPEN  c_ds;

-- start loop

                LOOP  

    --read a record

          FETCH  c_ds INTO v_ds;

                             -- exit from loop

                                     EXIT  WHEN c_ds%notfound;

                             -- list dept. name

                             dbms_output.put_line

                                    (v_ds.dname ||

‘ has ’ || v_ds.ttemp || ‘ employees.’);

                          -- end the loop

                    END  LOOP ;

                    CLOSE c_ds;

          END ;

          /

   

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

A:    It is a loop statement.

 

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

A:   We don’t need to use the OPEN, CLOSE, FETCH, and EXIT PL/SQL statements, 

and also to declare a cursor variable since the “FOR  LOOP ” statement does them implicitly.

 

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

A:   It is very simple to write.

 

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

A:   It displays the last existing PL/SQL errors that was compiled.

 

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

A:   It is an example of controlling process flow.

 

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.

A:   DECLARE

           CURSOR c_ds IS

                   SELECT  dname, count (*) ttemp,

sum(sal) ttsal, avg(sal) avsal

                        FROM  dept d, emp e

                                    WHERE d.deptno = e.deptno

GROUP BY  dname ORDER BY 1;

            BEGIN

                         -- for loop  to read cursor record.

FOR   v_ds IN  c_ds LOOP

          IF v_ds.ttemp > 4 THEN

                                        DBMS_OUTPUT.PUT_LINE

                                        (v_ds.dname || ‘ has ’ || v_ds.ttemp || ‘ employees.’);

                                    END  IF;

                        END  LOOP ;

            END ;

         /

 

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)

A:  SQL> CREATE TABLE dept_stat

                 (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.

A:

DECLARE

                        -- define department statistics

                        cursor c_ds is

                        SELECT dname, count (*) ttemp,

                                    SUM(sal) ttsal, AVG(sal) avsal

                                                FROM dept d, emp e

                                                WHERE d.deptno = e.deptno

                                    GROUP BY dname;

            BEGIN

                        -- loop to read cursor record.

                        FOR  v_ds IN  c_ds LOOP

                             -- insert into dept_stat

                             insert into dept_stat

                             values (v_ds.dname, v_ds.ttemp,

v_ds.ttsal, v_ds.avsal);

                        END  LOOP ;

                    -- save the insert transaction.

                    commit;

            END;

            /

 

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

A:   It is a parameter that we pass to a cursor.

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

A:   In the PL/SQL CURSOR statement in the PL/SQL declaration section.

 

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.  

A:  >>        DECLARE

                        -- define department statistics

                        cursor c_ds (p_deptno dept.deptno%TYPE) is

                        select dname, count (*) ttemp,

sum(sal) ttsal, avg(sal) avsal

                                    from dept d, emp e

                                    where d.deptno = e.deptno

                                    and d.deptno = p_deptno

group by dname;

-- define deptno variable

v_deptno NUMBER (2);

            BEGIN

                    -- assign deptno 10

                    v_deptno := 10;

                        -- loop to read cursor record.

                        for v_ds in c_ds (v_deptno) loop

                                    -- insert into dept_stat

                                    insert into dept_stat

                                    values (v_ds.dname, v_ds.ttemp,

v_ds.ttsal, v_ds.avsal);

                        end loop;

                        -- save the insert transaction.

                        COMMIT;

            END ;

            /

 

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

A:   The PL/SQL EXCEPTION section is a place that handles your errors that occurs 

in the execution time.

 

Q:   What do you use the EXCEPTION section for?

A:    For stored procedure’s error handling.

 

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

A:    If there is an execution error, then it will crash.  It crashes since the program didn’t know how 

to handle the errors.

 

Q:   What is an Oracle Defined EXCEPTION?

A:   They are those exceptions that were defined by Oracle.

 

Q:   What is a User Defined EXCEPTION?

A:   They are those exceptions that were defined by developers.

 

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

A:   The user defined exception needs to be declared and checked in the PL/SQL body section.

 

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.”

A:   >>        DECLARE

                        -- define department statistics

                        CURSOR c_ds (p_deptno dept.deptno%type) IS

                        SELECT  dname, count (*) ttemp,

sum(sal) ttsal, avg(sal) avsal

                                    FROM  dept d, emp e

                                    WHERE d.deptno = e.deptno

GROUP BY  dname;

-- define deptno variable

v_deptno NUMBER (2);

not_enough_emp     EXCEPTION ;

            BEGIN

                        -- assign deptno 10

                        v_deptno := 10;

                        -- loop to read cursor record.

                        FOR  v_ds in c_ds (v_deptno) LOOP

                             IF v_ds.ttemp < 10 THEN

                                       raise not_enough_emp;

                             END  IF;

                                    -- insert into dept_stat

                                    INSERT INTO  dept_stat

                                    VALUES (v_ds.dname, v_ds.ttemp,

v_ds.ttsal, v_ds.avsal);

                        END  LOOP ;

                        -- save the insert transaction.

                        COMMIT ;

            EXCEPTION

-- example of user define exception

     WHEN not_enough_emp THEN

          dbms_output.put_line(‘We need more employees’);

                        -- check deptno

                        WHEN invalid_number THEN

                                    dbms_output.put_line(‘Invalid deptno: ‘ || v_deptno);

                        WHEN others THEN

                                    dbsm_output.put_line(‘Other problem.’);

            END ;

            /

 

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

A:   I just open NOTEPAD, write my PL/SQL program, and then save it.

 

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)

A:   SQL> CREATE TABLE cust_stat

      (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  LOOP ” 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.

A:   create or replace procedure cust_stat_proc

IS

          -- define cursor

          CURSOR c_cs IS

          SELECT  last_name, trade_date,

sum(shares_owned*current_price) portfolio_value

                    FROM  customers, portfolio, stocks s

                    WHERE id = customer_id AND stock_symbol = symbol

                    AND trade_date = (SELECT  max(trade_date) FROM  stocks

                    WHERE symbol = s.symbol)

                    GROUP BY  last_name, trade_date;

BEGIN

          FOR  v_cs in c_cs LOOP

                    - insert into cust_stat

                    INSERT INTO  cust_stat

          VALUES (v_cs.last_name, v_cs.trade_date,

v_cs.portfolio_value);

                    -- save the insert transaction.

                    COMMIT ;

 

          END  LOOP ;

 

EXCEPTION

-- no data found

WHEN no_data_found THEN

     dbms_output.put_line(‘No data found.’);

          WHEN invalie_number THEN

                    dbsm_output.put_line(‘Invalid number’);

          WHEN others THEN

                    dbsm_output.put_line(‘Other problem.’);

END ;

/

 

Q:   Then run your created procedure.

A:   SQL> EXECUTE cust_stat;

 

Q:   Verify that your table was populated.

A:    SQL> SELECT  * FROM  cust_stat;

 

Q:   What is the Procedure Builder Tool?

A:   The procedure Builder tool is a software utility that helps developers to write, debug, save, 

and test their PL/SQL programs.

 

Q:   What is the listener in the Oracle database?

A:    A listener is an Oracle agent that monitors a specific port. It is a gateway of communication 

between clients and Oracle server.

 

Q:   How do you start or stop your listener?

A:         On NT, that will be done automatically.

            On UNIX, just type:      $ lsnrctl start – to start and

                                                $ lsnrctl stop -- to stop

 

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

A:   The Object Navigator window is a place that a developer can browse and navigate 

all its created objects.

 

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

A:   Select the ‘connect’ option in the File menu.

 

Q:   What is a user’s schema?

A:   We have user’s schema if the user owns objects. No objects no schema.

 

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

A:   Tables, Indexes, Procedures, Packages, Functions, Synonyms, etc.

 

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

A:   In the ‘Object Navigator ’ window, highlight "Program Units ” and click on the 

green ‘+’ sign which is the ‘Create’ icon.

 

Q:   What is a Program Unit?

A:   It is a stored procedure such as procedure, function, package body, and package specification.

 

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. And 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.

A:   PROCEDURE add_dept

            (p_deptno       IN              dept.deptno%TYPE,

             p_dname             IN              dept.dname%TYPE,

             p_loc             IN              dept.loc%TYPE,

             p_status             OUT              VARCHAR2 )

IS

            -- No variable

BEGIN

            /* This program add dept. record. */

            INSERT INTO  dept

                        VALUES (p_deptno, p_dname, p_loc);

            --- Save record.

            COMMIT ;

            -- Added successfully if the get to this line.    

            p_status := ‘OK’;

 

EXCEPTION

            -- Check for an Unique or Primary Key

            WHEN dup_val_on_index THEN

                        p_status := ‘DUPLICATE RECORD’;

            -- Check for invalid input data

            WHEN invalid_number THEN

                        p_status := ‘INVALID INPUT DATA’;

            -- Check for any other problems

            WHEN others THEN

                        p_status := ‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;

 

END  add_dept;

/

 

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.

A:   PROCEDURE test_add_dept

            -- This procedure will test add_dept procedure

            v_status            VARCHAR2 (40);

BEGIN

            -- Call add_dept with an invalid number.

            add_dept(100, ‘FINANCE’, ‘OHIO’, v_status);

            -- Print ‘OK’ value if there is no error.

            TEXT_IO .PUT_LINE (v_status);

EXCEPTION

            WHEN others THEN

                        p_status := ‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;

END  test_add_dept;

/

 

Q:   What is the client side environment?

A:    It is when we store the PL/SQL stored procedures in a PC or a Server that 

Oracle server doesn’t reside in.

 

Q:   What is the server side environment?

A:    It is when we store the PL/SQL stored procedures in the Oracle database.

 

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

A:   To save the program in the local library, go to the ‘Object Navigator ’ window, 

highlight PL/SQL libraries and click on the create icon. Click “OK.” Choose the 

“File” option and select “Save as.” Save any name library in a folder. Then click 

“OK” as “File System.” A library should be created. Now, drag the procedure 

into its “Program Units .” Highlight the library name and save it again.

 

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.

A:   PROCEDURE remove_dept

            (p_deptno       IN              dept.deptno%TYPE,

             p_status             OUT             VARCHAR2 )

IS

            -- Delete a record

            DELETE FROM  dept

                        WHERE deptno = p_deptno;

            -- Save the transaction.

            COMMIT ;

            -- Check the status.

            p_status := ‘OK’;

EXCEPTION

            WHEN no_data_found THEN

                        p_status := ‘NO DATA FOUND.’;

            WHEN others THEN

                        p_status := ‘Other Problems.’;

END  remove_dept;

/

 

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

A:   PROCEDURE test_remove_dept

            -- This procedure will test remove_dept procedure

            v_status            VARCHAR2 (40);

BEGIN

            -- Call remove_dept with a valid number.

            remove_dept(40, v_status);

            -- Print ‘OK’ value if there is no error.

            TEXT_IO .PUT_LINE (v_status);

EXCEPTION

            WHEN others THEN

                        p_status := ‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;

END  test_remove_dept;

/

 

Q:   What does the TEXT_IO package?

A:   It displays the results on the screen.

 

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

A:   PUT_LINE

 

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

A:   You use the TEXT_IO package in a client environment but use the 

DBMS_OUTPUT package in a server environment.

 

Q:   What is the PL/SQL function?

A:   It is a stored procedure that can have none or many input parameters, 

but it returns one and only one value.

 

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

A:   A function returns one and only one value but procedure can have many outputs.

 

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

A:   CREATE OR REPLACE FUCNTION function_name IS

 

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.

A:   FUNCTION full_name

            (p_fname       IN              customers.first_name%TYPE,

             p_lname            IN             customers.last_name%TYPE)

            RETURN  VARCHAR2

IS

            -- No variables

BEGIN

            -- Full name concatenation…       

            RETURN  p_lname || ‘, ‘ || p_fname;

EXCEPTION

            WHEN others THEN

                        -- Do nothing…

                        NULL;

END  full_name;

/

 

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

A:   PL/SQL> SELECT  full_name(‘John’,’Kazerooni’)

                 FROM  dual;

 

Q:   What is the PL/SQL interpreter?

A:   The PL/SQL interpreter is a module that allows the developers to run and debug their 

stored procedures. It reads PL/SQL statements interactively.

 

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

A:   Just type the procedure name ended with a semicolon.

 

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.

A:   FUNCTION dept_name

            (p_deptno       IN             dept.deptno%TYPE)

            RETURN  VARCHAR2

IS

            -- Define dname variable

            v_dname            dept.dname%TYPE;

BEGIN

            -- Get department name

            SELECT  dname INTO v_dname

                        FROM  dept

                        WHERE deptno = p_deptno;

            -- Return department name.

            RETURN  v_dname

EXCEPTION

            -- Error messages…

            WHEN no_data_found THEN

                        RETRUN ‘NO DATA FOUND…’;

            WHEN others THEN

                        RETURN  ‘Other PROBLEM…’;

END  dept_name;

/

 

Q:   In the “PL/SQL interpreter” section, use the “select” statement and use the department number 

10 to test the function.

A:   PL/SQL> SELECT  dept_name(10) as “Department Name”

                 FROM  dual;

 

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

in the department table.

A:   PL/SQL> SELECT  dept_name(55) as “Department Name”

                FROM  dual;

 

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

A:   PL/SQL> SELECT  ename, dept_name(deptno) as “Department Name”

                        FROM  emp

            ORDER BY 1;

 

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

A:   We should use the Procedure Builder debugger module. Choose the "Program" option and 

open the PL/SQL interpreter. Then run the procedure that needs to be debugged.

 

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

A:   Click on the icon next to the procedure and that will move the procedure's source program 

to the PL/SQL interpreter’s source area.

 

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

A:   It is a time the debugged program will stop and we can check the values of the program 

elements on that specific interruption time.

 

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

A:   Double click on line number that contains an execution statement in order to make a “BREAKPOINT .” 

Then a big red dot will appears.

 

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

A:   After defining the "breakpoint", you can run the debugged procedure which will activate the 

Step Into, Step Out, and Reset icons.

 

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

A:   The "Step Into" icon takes us to the next line.  

 

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

A:   The "Step Out" icon takes us to the next cycle of a breakpoint. 

 

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

A:   The "Reset" icon terminates the debug mode.

 

Q:   What does the STACK section contain?

A:   It contains the content of all variables.

 

 

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

A:   On the stack section, expand "procedure body."

 

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

A:   Yes.

 

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

A:   Click and drag the procedure into “Stored Program Units .”

 

Q:   What would be happen if you move or copy a locally PL/SQL procedure with its local 

packages into the database server?

A:   The procedure will not be compiled in the database server.

 

Q:   What is an Object Privilege?

A:   The object privileges will allow users to manipulate the object by adding, changing, 

removing, or viewing data plus the ALTER, REFERENCES, and EXECUTE privileges in 

the database object.

 

Q:   What are System Privileges?

A:   System privileges control the altering, dropping, and creating of all database objects, such as

rollback segments, synonyms, tables, and triggers.

 

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

A:   SQL> CREATE USER  newuser IDENTIFIED BY  newpass

            DEFAULT TABLESPACE  users

            TEMPORARY TABLESPACE  temp;

 

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

A:   SQL> ALTER USER  newuser

            DEFAULT TABLESPACE  users

            TEMPORARY TABLESPACE  temp;

 

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

A:   The CONNECT role contains the following system privileges:

ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, 

CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, 

CREATE TABLE, and CREATE VIEW.

The RESOURCE role contains the following system privileges:

CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, 

CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, 

CREATE TRIGGER, and CREATE TYPE.

 

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

A:   SQL> GRANT SELECT  ON customer TO newuser;

 

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

A:   SQL> GRANT CREATE ANY TABLE TO newuser;

 

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

A:   It is a synonym that all Oracle users can use it.

 

Q:   How do you create a PUBLIC SYNONYM?

A:   SQL> CREATE PUBLIC SYNONYM customer FOR  iself.customer;

 

Q:   Why do you need a PUBLIC SYNONYM?

A:   Easy of use and unique naming convention.

 

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

A:   The EXECUTE privilege will be given to a user in order to run other Oracle user’s 

stored procedures. It is an object privilege.

 

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

A:   No.

 

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

A:   It is used only privately for the creator of the object.

 

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

A:   The private synonym can not be accessed by public.

 

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

A:   SQL> REVOKE  CREATE ANY TABLE FROM  newuser;

 

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

A:   SQL> REVOKE  SELECT  ON emp FROM  newuser;

 

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?

A:   It will be revoked too.

 

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?

A:   Nothing.

 

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

A:   If there are not SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE, 

and ALTER then they are system priviledges.

 

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

A:   It means execute only.

 

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

A:   An object may be created based on the existence of another object or objects. The purity 

of the created object depends on the status of the other objects that have already been created. 

If any of those objects changed or deleted, the new object can not perform its task completely. 

Therefore, Oracle will change its status to an INVALID mode.

 

Q:   What is a timestamp?

A:   When you create or change something in an object, it’s created or modified date will be recorded. 

It is called a timestamp. Now any objects that were using this object are going to have an invalid status 

since the timestamp shows a date that is after creation of those objects.

 

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

A:   SQL> SELECT  object_name, object_type, status

            FROM  user_objects;

 

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

A:   SQL> ALTER TABLE dept

            MODIFY  (loc VARCHAR2 (14));

 

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

A:   SQL> ALTER FUNCATION dept_name COMPILE;

 

Q:   What is the PL/SQL package?

A:   A PL/SQL package is collection of stored procedures such as procedures and functions.

 

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

A:   A package should have a PL/SQL package specification and a PL/SQL package body.

 

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

A:   A ‘PL/SQL package body’ contains a complete PL/SQL stored procedures or functions.

 

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

A:   A ‘PL/SQL package specification’ contains all your PL/SQL functions header, procedures header, 

type, variables, etc.

 

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

A:   You can store them either in the client or server environments.

 

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

A:   Yes.

 

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

A:               Package specification:

            CREATE OR REPLACE PACKAGE pkg_dept IS

            For Package BODY:

            CREATE OR REPLACE PACKAGE BODY pkg_dept IS

 

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

A:   The package body contains the source programs and package specification contains the header programs.

 

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

A:   PACKAGE pkg_dept

IS

            -- No variables

-- This is the add_dept specification…

PROCEDURE add_dept

            (p_dept_rec    IN              dept%ROWTYPE,

             p_status            OUT             VARCHAR2 );

-- This is the remove_dept specification…

PROCEDURE remove_dept

            (p_deptno       IN             dept.depno%TYPE,

             p_status            OUT             VARCHAR2 );

-- Add more and more…

END  pkg_dept;

/

 

PACKAGE BODY  pkg_dept

IS

-- Add department procedure…

PROCEDURE add_dept

            (p_deptno       IN              dept.deptno%TYPE,

             p_dname             IN              dept.dname%TYPE,

             p_loc             IN              dept.loc%TYPE,

             p_status             OUT              VARCHAR2 )

IS

            -- No variable

BEGIN

            /* This program add dept. record. */

            INSERT INTO  dept

                        VALUES (p_deptno, p_dname, p_loc);

            --- Save record.

            COMMIT ;

            -- Added successfully if the get to this line.    

            p_status := ‘OK’;

EXCEPTION

            -- Check for an Unique or Primary Key

            WHEN dup_val_on_index THEN

                        p_status := ‘DUPLICATE RECORD’;

            -- Check for invalid input data

            WHEN invalid_number THEN

                        p_status := ‘INVALID INPUT DATA’;

            -- Check for any other problems

            WHEN others THEN

                        p_status := ‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;

END  add_dept;

-- Remove department procedure…

PROCEDURE remove_dept

            (p_deptno       IN              dept.deptno%TYPE,

             p_status             OUT             VARCHAR2 )

IS

            -- Delete a record

            DELETE FROM  dept

                        WHERE deptno = p_deptno;

            -- Save the transaction.

            COMMIT ;

            -- Check the status.

            p_status := ‘OK’;

EXCEPTION

            WHEN no_data_found THEN

                        p_status := ‘NO DATA FOUND.’;

            WHEN others THEN

                        p_status := ‘Other Problems.’;

END  remove_dept;

-- And more internal procedures.

END  pkg_dept;

/

 

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

A:   All the procedures that were declared in the package specification.

 

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

A:   Those procedures that are in the BODY but were not declared in the package specification.

 

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

A:   The private PL/SQL procedure can not be accessed by any users or objects.

 

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

A:   PL/SQL>             DECLARE

                                                v_status             VARCHAR2 (40);

                                    BEGIN

                                                pkg_dept.remove_dept(40, v_status);

                                                TEXT_IO .PUT_LINE (v_status);

                                    END ;

                                    /

 

Q:   What is a database trigger?

A:   A database trigger  is a set of PL/SQL statements that execute each time an event such as an 

update, insert, or delete statement occurs on the database. They are similar to stored PL/SQL 

statements.  They are stored in the database and attached to a table.

 

Q:   How do you create a trigger?

A:   Select “Triggers” and click on the "create" icon.

 

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

A:   Yes.

 

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.

A:               BEGIN

            -- audit if the user inserted a record…

            IF INSERTING  THEN

                        INSERT INTO  audit_dept

                        VALUES (user || ‘ inserted deptno: ‘ || :new.deptno);

            -- audit if the user updated a record…

            ELSIF UPDATING  THEN

                        INSERT INTO  audit_dept

                        VALUES (user || ‘ updated deptno: ‘ || :old.deptno);

            -- audit if the user deleted a record…

ELSIF DELETING  THEN

                        INSERT INTO  audit_dept

                        VALUES (user || ‘ deleted deptno: ‘ || :old.deptno);

            -- end if

            END  ID;

            END ;

 

Q:   How do you compile a trigger?

A:   In the trigger window, click save to compile. Then close the window.

            Or SQL> ALTER TRIGGER trigger_name COMPILE;

 

Q:   How do you disable or enable a trigger?

A:   One way is:

            PL/SQL> ALTER TRIGGER iself.audit_dept_table DISABLE:

            PL/SQL> ALTER TRIGGER iself.audit_dept_table ENABLE :

 

Q:   How do you test your created trigger?

A:   Execute a SQL statement that should fire the created trigger.

 

Q:   How do you modify a trigger?

A:   In the Object Navigator, on the database item, double click on the trigger icon to open the 

trigger, and then modify the trigger.

 

Q:   How do you drop a trigger?

A:   PL/SQL> DROP TRIGGER “audit_dept_table”;

 

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

A:   NO.

 

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

A:   SQL> SET SERVEROUTPUT  ON SIZE 400000

 

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

A:   Not directly. You should use the Oracle packages to perform such task.

 

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

A:   SQL> BEGIN
                             DBMS_DDL.ALTER_COMPILE 
                              ('PROCEDURE','ISELF','TEST02_4DDL_PKG');
                     END
                     /
 

Q:   What does the DBMS_DDL package?

A:    It will perform the DDL statements in the PL/SQL stored procedures.

 

Q:   What does the ANALZE_OBJECT procedure in the DBMS_DDL package and how 

can you verify that the object was ANALYZED?

A:   It analyze a table the same as the Oracle ANALYZE statement. We can use the 
following SQL statement to verify that the object was ANALYZED or not.

SQL>                SELECT
                              TO_CHAR (LAST_ANALYZED,'mm-dd-yy hh24:mi:ss')
                              last_analyzed_time
               FROM
 USER_TABLES 
               WHERE TABLE_NAME = 'TEST01_4DDL_PKG';
  

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

A:   It will compile a procedure. We can use the following SQL statement to verify that the object was compiled.

SQL> SELECT
 object_name, 
                    to_char
(last_ddl_time,'mm-dd-yy hh24:mi:ss') ddl_time
               FROM
 user_objects
               WHERE object_name = 'TEST02_4DDL_PKG';
  

Q:   What is a Native Dynamic SQL statement?

A:    Native Dynamic SQL allows an application to run SQL statements whose contents are not known until runtime. The statement is built up as a string by the application and is then passed to the server. Generally dynamic SQL is slower than static SQL so it should not be used unless absolutely necessary. Make sure to check the syntax, since syntax checking and object validation cannot be done until runtime.  The only advantage of dynamic SQL is that it allows you to perform DDL commands and also allows you to access objects that will not exist until runtime.

 

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.

A:   SQL> CREATE OR REPLACE FUNCTION  get_total_recs

            (loc VARCHAR2
            RETURN
 NUMBER  IS  
            Query_str VARCHAR2(1000); 
            Num_of_recs NUMBER; 
       BEGIN
 
             Query_str := 'SELECT
 COUNT(*) FROM  ' || loc; 
             EXECUTE IMMEDIATE
 query_str INTO num_of_recs; 
             RETURN num_of_recs; 
      END

SQL> / 

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

A:   SQL> SELECT  COUNT(*) FROM  dba_objects 
                       WHERE object_type LIKE 'JAVA%';

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

A:   You must have at least 30 megabytes of memory.

 

Q:   How do you create a JAVA class?

A:   SQL> CREATE OR REPLACE JAVA  SOURCE NAMED "iself" AS 
            public class iself { 
                 static public String message
 (String tail) { 
                 return "iSelfSchooling-" + tail; 
                
           
SQL> /

Q:   How do you publish a JAVA class?

A:   SQL> CREATE OR REPLACE FUNCTION  error_msg

            (str VARCHAR2
      RETURN
 VARCHAR2 
      AS 

      BEGIN
            LANGUAGE JAVA
 NAME 
            'iself.message (java.lang.String) 
            return java.lang.String'; 

      END  error_msg;
SQL> /

Q:   How do you test a JAVA function?

A:   SQL> SELECT  error_msg ('01320: Running JAVA  was successful.') 
            as "Message Function"
                  FROM
 dual
            SQL> /

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

A:   SQL> DROP JAVA SOURCE  "iself";

 

Q:   What does the EMPTY_BLOB() function?

A:   Empty the photo column in the EMP table. The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object). Use EMPTY_BLOB to initialize a BLOB to "empty." Before you can work with a BLOB, either to reference it in SQL DML statements such as INSERTs or to assign it a value in PL/SQL, it must contain a locator. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a valid BLOB locator.

 

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

A:   SQL> CREATE OR REPLACE

                        DIRECTORY photo_folder AS 'c:\ephoto';

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

A:   NO.

 

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.  

 

A:   SQL> CREATE OR REPLACE PROCEDURE  insert_photo 
      (p_empno NUMBER
, p_photo VARCHAR2
      AS 
      f_photo BFILE; 
      b_photo BLOB

      BEGIN

            -- Update the employee photo
            UPDATE
 emp
               SET photo = empty_blob()
               WHERE empno = p_empno
               RETURN
 photo into b_photo;
              -- find where the photo's pointer is located.
              f_photo := bfilename('PHOTO_FOLDER', p_photo);
              -- open the photo as read-only option. 
              dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
              -- load the photo into column photo. 
              dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
              -- close the photo's pointer. 
             dbms_lob.fileclose(f_photo);
             -- Save the loaded photo record. 
             COMMIT
;

      EXCEPTION
      -- Check for your error messages
      WHEN others THEN

            dbms_output.put_line('*** ERROR *** Check you procedure.');
      END

SQL> /

 

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

A:   SQL> SELECT  empno, ename,
      dbms_lob.getlength(photo) "Photo Size"
      FROM
 emp
SQL> /

Q:   What does the DBMS_LOB package?

A:   The DBMS_LOB package contains procedures and functions that manipulate Oracle large objects.

 

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

A:   The GETLENGHT() procedure is one of the stored procedures in the DBMS_LOB package. It returns the size of a large object in the Oracle database.

 

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

A:   SQL> DROP DIRECTORY photo_folder;

 

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

A:         How:

            SQL> GRANT CREATE ANY DIRECTORY  TO iself
           
When a user needs to write or read from that folder.

 

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

A:   SQL> REVOKE  CREATE ANY DIRECTORY FROM  iself

 

 

Q:   What is PL/SQL?

A:   PL/SQL is a language that was provided by Oracle. Stored procedure is a collection of PL/SQL. Stored procedure is like a program module in Oracle. It is available for developers to code stored procedures that easily integrate with database objects via the SQL statements such as INSERT, UPDATE, DELETE, and SELECT. This language offers variable DECLARATION, LOOP, IF-THEN-ELSE-END IF, EXCEPTION an advanced error handling, cursor, and more.

 

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

A:   A PL/SQL stored procedure can be stored in an Oracle Database server or user client machine in a PL/SQL library.

 

Q:   What is the PL/SQL body section?

A:   It is a section in a stored procedure to execute PL/SQL statements.  It is also called the execution section.

 

Q:   What is the PL/SQL declaration section?

A:   It is a section that all program variables, cursors, and types will be declared.

 

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

A:   INTO

 

Q:  Write an anonymous stored procedure to 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.

A:   >>Declare

   v_drec dept%rowtype;

begin

    select deptno, dname, loc into

        v_drec.deptno,v_drec.dname, v_drec.loc

        from dept

        where deptno = 30;

             if sql%notfound then

dbms_output.put_line('Record was not found.');

   else

dbsm_output.put_line(v_drec.dname);

   end if;

end;

/

 

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

A:  

4-     Explicit cursor will be defined in the declaration section but implicit cursor will be defined in the execution or body section.

5-     The implicit cursor must have INTO clause in its SQL statement.

6-     The explicit cursor can return more than one record but the implicit cursor should only return one and only one record.

 

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 name with their total number of employees for each department. List only the department name that their total number of employees is more than 100.

For example: ACCOUNTING has 145 employees.

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

A:   DECLARE

           -- define department statistics

d, emp e

                                    WHERE d.deptno = e.deptno

GROUP BY  dname

ORDER            CURSOR c_ds IS

                   SELECT  dname, count (*) ttemp,

Sum (sal) ttsal, avg(sal) avsal

                        FROM  dept BY 1;

            BEGIN

                         -- FOR LOOP statement to read cursor record.

FOR   v_ds IN  c_ds LOOP

          IF v_ds.ttemp > 100 THEN

                                        DBMS_OUTPUT.PUT_LINE

                                                         (v_ds.dname ||

         ‘ has ’ || v_ds.ttemp || ‘ employees.’);

                                    END  IF;

                        END  LOOP ;

            END ;

         /

 

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.

A:   CREATE OR REPLACE FUNCTION dept_name

            (p_deptno        IN             dept.deptno%TYPE)

            RETURN  VARCHAR2

IS

            -- Define dname variable

            v_dname            dept.dname%TYPE;

BEGIN

            -- Get department name

            SELECT  dname INTO v_dname

                        FROM  dept

                        WHERE deptno = p_deptno;

            -- Return department name.

            RETURN  v_dname

EXCEPTION

            -- Error messages…

            WHEN no_data_found THEN

                        RETRUN ‘NO DATA FOUND…’;

            WHEN others THEN

                        RETURN  ‘Other PROBLEM…’;

END  dept_name;

/

 

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

A:   REVOLE CREATE ANY TABLE FROM scott;

 

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?

A:  Mr. C will lose his granted object privilege too.

 

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

A:   ALTER table_name MODIFY (column_name new_datatype);

 

Q:   What is a PL/SQL package?

A:   A package is a collection of procedures and functions together as an object.

 

Q:   What is a package specification?

A:   A package should have a PL/SQL package specification and a PL/SQL package body.  A ‘PL/SQL package specification’ contains all your PL/SQL functions, procedures, type, variables, etc. All the declared PL/SQL functions, procedures, variables, etc in a package specification are called public procedures and functions.  They can be accessible to the users who have privilege to execute them. In the PL/SQL package specification, all the functions and procedures must have a PL/SQL procedure in its PL/SQL package body. It is not necessary that all the PL/SQL procedures in a PL/SQL package body have a specification entry in its PL/SQL package specification. Those PL/SQL procedures that have not have any specification entry in the PL/SQL package specification called private PL/SQL procedures.

 

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

A:   There are two types of database triggers: statement triggers and row triggers. A statement trigger will fire only once for a triggering statement. A row trigger fires once for every row affected by a trigger statement. Triggers can be set to fire either before or after Oracle processes the triggering insert, update, or delete statement.

 

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

A:   The keywords updating, deleting, or inserting can be used when multiple triggering events are defined.  You can perform different action based on the UPDATE, DELETE, or INSERT statement that you are executing.

 

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

A:         ALTER TRIGGER iself.audit_dept_table DISABLE:

            ALTER TRIGGER iself.audit_dept_table ENABLE :

            DROP TRIGGER iself.audit_dept_table;

           

Q:   What does the following PL/SQL statements? What is the output of the following SQL statement?

SQL> SELECT full_name (‘Joe’, ‘Smith’) as “Full Name” FROM DUAL;

CREATE OR REPLACE FUNCTION full_name

            (p_fname        IN              customers.first_name%TYPE,

             p_lname            IN             customers.last_name%TYPE)

            RETURN  VARCHAR2

IS

            -- No variables

BEGIN

            -- Full name concatenation…       

            RETURN  p_lname || ‘, ‘ || p_fname;

EXCEPTION

            WHEN others THEN

                        -- Do nothing…

                        NULL;

END  full_name;

/

 

The output is: 

                                    Full Name

                                    -----------------

                                    Smith, Joe

 

 

Google
 
Web web site