Oracle 1Z0-007 Certification Notes

Oracle 1Z0-147 Certification Notes

• Overview of PL/SQL Programs
  • Describe a PL/SQL program construct
  • List the components of a PL/SQL block PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or subproblem to be solved. Thus, PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement. A block (or sub-block) lets you group logically related declarations and statements. That way, you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure: DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN /* Executable section: procedural and SQL statements go here. */ /* This is the only section of the block that is required. */ EXCEPTION /* Exception handling section: error handling statements go here. */ END;
  • List the benefits of subprograms
  • Describe how a stored procedure/function is invoked
• Creating Procedures
  • Define what a stored procedure is

    Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.

    Advantages of Subprograms Subprograms provide extensibility; that is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates new departments, you can easily write one, as follows: PROCEDURE create_dept (new_dname VARCHAR2, new_loc VARCHAR2) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept; Subprograms also provide modularity; that is, they let you break a program down into manageable, well-defined modules. This supports top-down design and the stepwise refinement approach to problem solving. In addition, subprograms promote reusability and maintainability. Once validated, a subprogram can be used with confidence in any number of applications. If its definition changes, only the subprogram is affected. This simplifies maintenance. Finally, subprograms aid abstraction, the mental process of deriving a universal from particulars. To use subprograms, you must know what they do, not how they work. Therefore, you can design applications from the top down without worrying about implementation details. Dummy subprograms (stubs) allow you to defer the definition of procedures and functions until you test and debug the main program.
  • List the development steps for creating a procedure /*syntax*/ [CREATE [OR REPLACE]] PROCEDURE procedure_name[(parameter[, parameter]...)] [AUTHID {DEFINER | CURRENT_USER}] {IS | AS} [PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name]; where parameter stands for the following syntax: parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype [{:= | DEFAULT} expression] /*parameter syntax*/ parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype [{:= | DEFAULT} expression]
  • Create a procedure DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END;
  • Describe the difference between formal and actual parameters

    formal parameter—the identifier used in a method to stand for the value that is passed into the method by a caller. For example, amount is a formal parameter of processDeposit actual parameter—the actual value that is passed into the method by a caller. For example, the 200 used when processDeposit is called is an actual parameter.

  • List the types of parameter modes You use parameter modes to define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments (actual parameters) and return a single value. To have a function return multiple values is a poor programming practice. Also, functions should be free from side effects, which change the values of variables not local to the subprogram. Using the IN Mode An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. For example, the following assignment statement causes a compilation error: PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS minimum_purchase CONSTANT REAL DEFAULT 10.0; service_charge CONSTANT REAL DEFAULT 0.50; BEGIN IF amount < minimum_purchase THEN amount := amount + service_charge; -- causes compilation error END IF; ... END debit_account; The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized variable, or expression. Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Using Default Values for Subprogram Parameters". Using the OUT Mode An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. That means you can use an OUT formal parameter as if it were a local variable. You can change its value or reference the value in any way, as the following example shows: PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS hire_date DATE; bonus_missing EXCEPTION; BEGIN SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE bonus_missing; END IF; IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN bonus := bonus + 500; END IF; ... EXCEPTION WHEN bonus_missing THEN ... END calc_bonus; The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a constant or an expression. For example, the following procedure call is illegal: calc_bonus(7499, salary + commission); -- causes compilation error An OUT actual parameter can have a value before the subprogram is called. However, when you call the subprogram, the value is lost unless you specify the compiler hint NOCOPY (see "Passing Large Data Structures with the NOCOPY Compiler Hint") or the subprogram exits with an unhandled exception. Like variables, OUT formal parameters are initialized to NULL. So, the datatype of an OUT formal parameter cannot be a subtype defined as NOT NULL (that includes the built-in subtypes NATURALN and POSITIVEN). Otherwise, when you call the subprogram, PL/SQL raises VALUE_ERROR. An example follows: DECLARE SUBTYPE Counter IS INTEGER NOT NULL; rows Counter := 0; PROCEDURE count_emps (n OUT Counter) IS BEGIN SELECT COUNT(*) INTO n FROM emp; END; BEGIN count_emps(rows); -- raises VALUE_ERROR Before exiting a subprogram, explicitly assign values to all OUT formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters. Using the IN OUT Mode An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value and its value can be assigned to another variable. The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or an expression. If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
  • List the methods for calling a procedure with parameters A procedure is called as a PL/SQL statement. For example, you might call the procedure raise_salary as follows: raise_salary(emp_id, amount);
  • Describe the DEFAULT option for parameters
  • Create a procedure with parameters
  • Invoke a procedure that has parameters
  • Define a subprogram in the declarative section of a procedure
  • Describe how exceptions are propagated
  • Remove a procedure
• Creating Functions
  • Define what a stored function is
  • Create a function
  • List how a function can be invoked
  • List the advantages of user-defined functions in SQL statements
  • List where user-defined functions can be called from within an SQL statement
  • Describe the restrictions on calling functions from SQL statements
  • Remove a function
  • Describe the differences between procedures and functions
• Managing Subprograms
  • Contrast system privileges with object privileges
  • Grant privileges
  • Contrast invokers rights with definers rights
  • Identify views in the data dictionary to manage stored objects
• Creating Packages
  • Use DESCRIBE command to describe packages and list their possible components
  • Identify a package specification and body
  • Create packages: Create related variables , cursors, constants, exceptions, procedures, and functions
  • Designate a package construct as either public or private
  • Invoke a package construct
  • Use a bodiless package
  • Drop Packages
  • Identify benefits of Packages
• More Package Concepts
  • Write packages that use the overloading feature
  • Use Forward Referencing
  • Describe errors with mutually referential subprograms
  • Initialize variables with a one-time-only procedure
  • Identify persistent states in package variables and cursors
  • Identify restrictions on using Packaged functions in SQL statements
  • Invoke packaged functions from SQL
  • Use PL/SQL tables and records in Packages
• Oracle Supplied Packages
  • Describe the benefits of Execute Immediate over DBMS_SQL for Native Dynamic SQL
  • Identify the flow of execution
  • Use EXECUTE IMMEDIATE
  • Describe the use and application of some Oracle server-supplied packages: DBMS_SQL, DBMS_OUTPUT, UTL_FILE
• Manipulating Large Objects
  • Compare and contrast LONG and large object (LOB) data types
  • Describe LOB datatypes and how they are used
  • Differentiate between internal and external LOBs
  • Identify and Manage Bfiles
  • Migrate from LONG To LOB
  • Use the DBMS_LOB PL/SQL package
  • Create LOB columns and populate them
  • Perform SQL operations on LOBS: Update LOBs with SQL, Select from LOBS, Delete LOBS
  • Describe the use of temporary LOBs
• Creating Database Triggers
  • Describe the different types of triggers
  • Describe database triggers and their uses
  • List guidelines for designing triggers
  • Create a DML trigger
  • List the DML trigger components
  • Describe the trigger firing sequence options
  • Use conditional predicates in a DML trigger
  • Create a row level trigger
  • Create a statement level trigger
  • Use the OLD and NEW qualifiers in a database trigger
  • Create an INSTEAD OF trigger
  • Describe the difference between stored procedures and triggers
  • Describe the trigger execution model
  • Alter a trigger status
  • Remove a trigger
• More Trigger Concepts
  • Define what a database trigger is
  • Describe events that cause database triggers to fire
  • Create a trigger for a DDL statement
  • Create a trigger for a system event
  • Describe the functionality of the CALL statement
  • Describe the cause of a mutating table
  • List what triggers can be implemented for
  • List the privileges associated with triggers
  • View trigger information in the dictionary views
• Managing Dependencies
  • Track procedural dependencies
  • Describe dependent objects and referenced objects
  • View dependency information in the dictionary views
  • Describe how the UTLDTREE script is used
  • Describe how the IDEPTREE and DEPTREE procedures are used
  • Describe a remote dependency
  • List how remote dependencies are governed
  • Describe when a remote dependency is unsuccessfully recompiled
  • Describe when a remote dependency is successfully recompiled
  • List how to minimize dependency failures