- Shopping Bag ( 0 items )
Ships from: acton, MA
Usually ships in 1-2 business days
|Unit I||Preparing for OCP DBA Exam I: SQL and PL/SQL|
|1||Selecting Data from Oracle||3|
|2||Advanced Data Selection in Oracle||49|
|3||Creating the Oracle Database||95|
|4||Creating Other Database Objects in Oracle||149|
|Unit II||Preparing for OCP Exam 2: Developing PL/SQL Program Units|
|6||Procedures and Functions in PL/SQL Development||259|
|7||Debugging PL/SQL Procedures and Functions||317|
|8||Managing Procedures, Functions, and Procedural Dependency||371|
|9||Developing and Using Packages||417|
|10||Developing and Using Database Triggers||471|
|Unit III||Preparing for OCP Exam 3: Building Forms I|
|11||Introduction to Oracle Developer/2000||509|
|13||Forms Design I||603|
|14||Forms Design II||653|
|15||Working with Triggers||709|
|Unit IV||Preparing for OCP Exam 4: Building Forms II|
|19||Working with Menu Modules||857|
|20||Advanced Forms Programming I||889|
|21||Advanced Forms Programming II||927|
|22||Advanced Forms Programming III||959|
|Unit V||Preparing for OCP Exam 5: Building Reports|
|23||Introducing Oracle Reports||991|
|24||Oracle Reports: Introduction to Report Builder||1043|
|25||Enhancing Data Content in Report Builder||1097|
|26||Enhancing Layout and Properties in Report Builder||1155|
|27||Developing Other Features in Reports||1203|
|28||Advanced Topics in Report Design||1261|
|App||Preparing for Developer 2.0 New Features Upgrade Exam||1321|
In this chapter, you will cover the following areas of PL/SQL program development:
Once developed, procedures and functions require some maintenance management for continued efficiency and performance. You need to understand some key points about the management of procedures and functions, and the dependencies Oracle enforces on procedures and functions stored in the Oracle database that use database objects like tables, views, indexes, and the like. This area, though perhaps less obvious to developers as how to construct for loops and if - then statements, is critical to maintenance programmers who must support PL/SQL running on production systems while simultaneously designing enhancements or new functionality into the production application. This subject area comprises 23% of OCP exam 2 test content.
Managing Procedures and Functions
In this section, you will cover the following points on managing procedures and functions:
Managing stored procedures and functions has many of the same challenges inherent in managing code for other environments. There are problems of where and how to find information about the code, and application security issues. Beyond the scope of Oracle certification but certainly no less important is source code version control. These issues form the core of what you need to know about source code management in Developer/2000. In this section, you will cover discussions in these areas.
Dictionary Views on Stored Procedures
There are many views in the data dictionary in Oracle that hold information about stored procedures and functions. Those views store everything from information about who owns the procedures to who can execute them, even the source code of a stored procedure, function, trigger or package. As stated in chapter 3, there are three categories of most data dictionary views corresponding to the scope each view has over the total data in the Oracle database, USER_, ALL_ and DBA_. Sometimes the management of stored procedures and functions ultimately may fall on the DBA like management of most database objects does. In some cases, there may be a production developer or maintenance and enhancement team that handles this functionality. The DBA_ views will be considered in this discussion, because they provide the most comprehensive view on the database, though you should bear in mind that the USER_ and ALL_ views will be of some limited use in source code management as well.
The DBA_OBJECTS view stores a wide variety of information about objects in the database, including PL/SQL blocks. The columns in this view include OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, and STATUS. Pay attention to rows in this view where OBJECT_TYPE is 'PACKAGE', 'PACKAGE BODY', or 'PROCEDURE'. This view offers information about the PL/SQL block such as when it was created, last changed, and whether it's valid or not. Questions about the validity of a PL/SQL block touches on the topic of procedural dependency, covered later in the chapter. The formatting in this output is not standard, it has been modified for readability.
SQL> select * from dba_objects where object_type in
('PROCEDURE','PACKAGE','PACKAGE BODY') and owner = 'STACY';
OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ----------- --------- --------- -------------------
STACY FLURB 1079 PROCEDURE 07-SEP-99 07-SEP-99 1999-09-07:17:02:54 VALID
STACY FOO 1078 PROCEDURE 06-SEP-99 06-SEP-99 1999-09-06:15:05:29
Perhaps the most important of dictionary views related to PL/SQL blocks, DBA_SOURCE stores the source code for PL/SQL blocks. This source may either be in plain text format or encoded using the PL/SQL wrapper. A wrapper is an encryption method that allows the developer of PL/SQL packages to distribute them in a portable format for use on other Oracle databases, yet in such a way as to prevent others from viewing the source code directly. This method allows an individual or company to protect complex logic, business rules, or other coding secrets while allowing others to use the functionality the procedures and functions provide. Much of Oracle's server packages such as DBMS_SQL are distributed using the PL/SQL wrapper.
The columns of the DBA_SOURCE dictionary view include OWNER, NAME, TYPE, LINE, and TEXT. The key to this dictionary view is that, when you want to view only the source code, you must remember to reference only the TEXT column in your select statements. Thus, select * from DBA_SOURCE where NAME = 'proc_name'; is a bad idea that usually results in a buffer overflow error in SQL*Plus. Instead, you can switch the * for TEXT. This allows you to see only the source code corresponding to the PL/SQL block named by the query. The following block contains a statement that allows you to select data from this view for a procedure name that you can enter interactively. Note the use of the ampersand (&) character, which precedes any input variable, and the upper( ) SQL built-in function, which converts characters to their uppercase form.
SQL> select text from dba_source where name = upper('&plsql_codename')
2> order by line;
Enter value for plsql_codename: foo
old 1: select text from dba_source where name = upper('&plsql_codename')
new 1: select text from dba_source where name = upper('foo')
dbms_output.put_line('I am foo, who are you?);
After covering debugging in chapter 7, you know that the DBA_, USER_ or ALL_ERRORS dictionary views contain the errors produced during compilation of PL/SQL procedures, functions and packages. These errors are only valid for the most recent compile. A new compile causes the old errors to be lost. As explained in chapter 7, this view is a little difficult to use, because the line numbers it gives for error location correspond to the line number of the PL/SQL code as it appears in the SQL statement buffer in SQL*Plus during the compilation. Thus, it is usually wise to view this information in a session separate from the session you use to perform the actual compile. The columns in the DBA_ERRORS view include OWNER, NAME, TYPE, SEQUENCE, LINE, POSITION, and TEXT, while USER_ERRORS subtracts the the OWNER column that defines who owns the code producing these errors. The following code block shows a selection from USER_ERRORS.
OWNER NAME TYPE SEQUENCE LINE POSITION
----- ------------------------ ------------ --------- --------- ---------
STACY FOO PROCEDURE 1 2 1
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
( ; is with as compress compiled wrapped
The symbol "is" was substituted for "BEGIN" to continue.
STACY FOO PROCEDURE 2 3 22
PLS-00103: Encountered the symbol "I am foo, who are you?);
" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
<a number> <a single-quoted SQL string> avg count exists max
min prior sql stddev sum variance
The final dictionary view considered here is the DBA_JOBS view. This dictionary view on the Oracle database tracks information about the Oracle database job scheduler, which is used to run PL/SQL code for regularly scheduled batch operations without the use of job scheduling from the operating system or a third-party vendor. The benefit of using Oracle for job scheduling is that you don't need to develop support into the batch job to handle situations where the database is not available of if a user's password has changed. Since these jobs are internal to Oracle, they will fire automatically as long as the database is running. If the database is not running, the job will not fail because the job will not be run. Also, Oracle retries jobs for a period of time you specify automatically, eliminating the need to retry a job manually when you learn it has not run. Oracle-scheduled jobs can be used in conjunction with packages and features in the Oracle database like DBMS_OUTPUT, DBMS_IO, and the spool command to write text output to a file. This method of job scheduling is as effective as batch job scheduling with Unix shell scripts, Pro*C programs, crontab, or other operating system-driven methods.
The columns in the DBA_JOBS view give information about the jobs scheduled to run on the Oracle database. Several columns of data are provided, including JOB, a number used to identify uniquely each job entered in the database. Several columns track the different users that may be involved in job execution, including LOG_USER, PRIV_USER, and SCHEMA_USER. Several dates for job execution are tracked as well, including LAST_DATE, LAS_SEC, THIS_DATE, THIS_SEC, NEXT_DATE, and NEXT_SEC, indicating the last, current, and next time the job did run or will run. Several other things are tracked as well, in the following columns: TOTAL_TIME, BROKEN, INTERVAL, FAILURES, WHAT, CURRENT_SESSION_LABEL, CLEARANCE_HI, CLEARANCE_LO, NLS_ENV, and MISC_ENV. Several of these columns are used to track whether a job is broken, and if so, how many times has it ended abnormally.
TIP: The DBA_JOBS view is a little annoying to select data from because it contains a few really long columns. When you issue a select against it, you may get a "buffer overflow" error in SQL*Plus. Issue SET ARRAYSIZE 5 and SET MAXDATA 32767 from your SQL prompt and you should be fine.
Security for Owner and User on Stored PL/SQL
Recall from chapter 6 that, to create PL/SQL program components on the Oracle database, you require the create procedure system privilege granted by the DBA or some other privileged user on the database. This is required for server-side PL/SQL only, client-side PL/SQL can be created and used by anyone. To run server-side procedures, you must have the execute object privilege granted to you. If you're still wondering about system and object privileges related to PL/SQL code development, review chapter 6.
There is an important factor to consider when running stored procedures and functions, related to what the function does and whether the user of the procedure or function is allowed to do it. Say for example that user ATHENA wants to run user SPANKY's procedure find_mouse( ). This procedure performs a select statement on the MOUSE_HOUSE table, for which ATHENA does not have select privileges but user SPANKY does.
You might think that ATHENA would not be able to run the find_mouse( ) procedure, because even though she has execute privileges on that procedure, she doesn't have select privileges on MOUSE_HOUSE. This assumption is wrong, in fact. ATHENA can run the procedure successfully. Why? Because Oracle only cares that SPANKY, the owner of the procedure, has the select privilege required to execute the procedural components successfully. What's more, user SPANKY must have these privileges on the objects referenced in the program unit granted explicitly to him, not through a role, or else SPANKY's own compilation of his program unit will fail. Thus, the user of a procedure needn't have the underlying object privileges required to run the statements in a stored procedure or function, she need only have the execute privilege on the procedure. The owner of the procedure or function, however, must have all privileges required by the procedure for it to compile and run successfully. This fact touches on functional dependency, which is covered later this chapter as well.
TIP: The procedure owner must have all privileges required to run statements in a procedure. The user of that procedure needn't have the privileges required to execute every statement in the procedure, so long as the user has execute privileges on the procedure.
User ATHENA only needs to concern herself with obtaining execute privileges on that stored function. SPANKY, meanwhile, must have all privileges granted to him that are required to run the function successfully, even though the function will be run by other people. Thus, the developer of an application can not only modularize the application functionality by encapsulating logic into stored procedures and functions, she can modularize database access privileges via stored procedures as well.
TIP: The owner of any program unit must have all object privileges necessary to run the program unit granted directly to them. The privilege cannot be granted via roles.
By giving the application schema owner the actual object privilege to access table data, she can effectively moderate the actual access to the database any user may have. An application schema owner (SPANKY in this case, for example) owns the database access privileges and moderates them to user ATHENA by only allowing her to view as much data as the function find_mouse( ) will provide her. So, the developer can then revoke select privileges to table MOUSE_HOUSE from ATHENA while still allowing her to do her job finding mice with the assistance of application code. Figure 8-1 illustrates the principle of maintaining security on the Oracle database by allowing users access to data programmatically.
Several advantages are offered with employment of this method throughout the database and application. The database is more secure because no user other than the application schema owner has object privileges that would allow them to go out and run SQL statements that view or modify data that are not part of a stored procedure or function. This design limits the overall usage of the system to flow only through stored procedures and functions. Interestingly, it gives the user as much access as the procedure will allow, so even though ATHENA may not have select access on MOUSE_HOUSE, she can still see the data she needs to see in that table anyway. PL/SQL then extends the basic functionality provided by Oracle in the form of granting execution on program privileges to users that cannot otherwise access the data.
Managing Procedure Dependencies
In this section, you will cover the following points about managing procedural dependencies:
It has been said that no man is an island. That author, of course, neglected to realize that women, like men, are not islands, either. The point here is that people and things in a society are usually interconnected. The same holds true in Oracle, the software used to represent or model reality. Objects like tables, indexes, views, and program units, are interconnected. As a result of this interconnectivity, there are dependencies placed on one object such that if the object were to fail or disappear, its absence would be noticed by its dependents. This section covers the management of dependency in PL/SQL applications. There are two different types of dependencies a PL/SQL block may have - dependency on another PL/SQL block, called procedural dependency, and dependency on a database object, called object dependency. The tracking of procedural dependencies in the Oracle database will be covered in this section, along with analyzing the effects of database object changes on PL/SQL blocks. The special responsibilities involved in managing dependency on single and distributed databases are covered, too.
Tracking Procedural Dependencies
In the course of PL/SQL development, you will encounter many different types of applications, from financial applications and accounting software to HR and employee-related service applications, to telemarketing applications, to just about anything else you can think of. One common thread between all these applications, however, will be that there is a strong chance the application will be big. Take this to mean that you may be required to develop and maintain applications with several thousand or more lines of source code. Now, that's a complicated task, so you will need to understand the implications of maintaining such complexity.
Often, to simplify the complexity of a large application, you will want to use modularity in program design. This means you will take your overall program activity and break it down into logical units of work. Once this is complete, you will take your logical units of work and develop PL/SQL blocks to handle only the logical component. In particular, this method helps if there is a repeatable task that happens in the application, such as the selection of data for validation purposes. You simply develop your function or procedure to handle the logical mechanisms required, and define parameters to be passed in and out to handle the specifics of the work.
In this development scenario, you are creating procedural dependencies. Procedural dependency in this situation literally means "one procedure depends on another." When developing code in a modular fashion, beware the dependencies you create. For example, if a defect appears in a procedure that is called by 17 other procedures, then the defect will occur in at least that many places. Without a clear idea or knowledge of your overall application, you will become very frustrated very quickly unless you have methods available to track the dependencies one unit of code has on another.
Fortunately, there are some items at your disposal for tracking procedural dependencies between database PL/SQL programs. There are a group of dictionary views that were held back from prior discussion that benefit your quest to find the procedural dependencies in your Oracle database. This group is the USER_, ALL_ and DBA_DEPENDENCIES views. As with most dictionary views, there are three possible limitations on scope, including the current user's dependencies, all dependencies in the database the current user can see, and all dependencies in the database, period. Due to the importance of tracking procedural dependencies in the Oracle database with respect to PL/SQL programs, the following is a list of columns in the DBA_DEPENDENCIES view, with an explanation of the contents of that column. Here we go:
Let's examine now the usage of the DBA_DEPENDENCIES view. Assume we have a complicated set of procedures, functions, and packages on the Oracle database for an application that conducts international financial transactions. One of these functions, convert_money( ), is used to convert a monetary amount from one currency to another. The convert_money( ) function accepts four variables, from_crcy, to_crcy, from_amt and valid_date, and returns data of type NUMBER. Notice this function violates the good programming practice of only using in parameters for functions, allowing it to return only one value. The code for this function appears in the following code block.
CREATE FUNCTION convert_money (
p_to_crcy IN OUT VARCHAR2,
) RETURN NUMBER IS
SELECT exch_rate INTO my_exch_rate FROM EXCH_RATE
WHERE to_crcy = p_to_crcy AND from_crcy = p_from_crcy
AND valid_date = sysdate;
my_ret_val := p_from_amt*my_exch_rate;
As you can see, to perform its application logic, convert_money( ) it obtains an exchange rate from the EXCH_RATE table. The table consists of four columns, TO_CRCY, FROM_CRCY, EXCH_RATE, and VALID_DAY. The FROM_CRCY and TO_CRCY columns contains character strings that represent world currencies, like GBP for the British pound, USD for the US dollar, or IR for Indian rupee, that represent the currency you convert from and to, respectively. The EXCH_RATE column contains a small number used as the conversion factor from the one currency to another. The last column, VALID_DAY, contains a date on which that value in EXCH_RATE is valid.
Several PL/SQL procedures in the applications call the convert_money( ) function. Several of these procedures are experiencing difficulties in obtaining correct conversions for the day they pass into the application. You are the developer in this situation, and have been asked to take a look at the situation. To determine the different procedures that call the convert_money( ) function, the SQL statement in the following code block can be used.
SQL> select name, referenced_name from dba_dependencies
2> where referenced_name = 'CONVERT_MONEY';
Based on the data coming from this view, you can now identify the three different procedures that depend on this function. Judging strictly by the names of the procedures that call convert_money( ), you can see that three types of transactions need to convert money, those that happen today, those that happened yesterday but are processed today, and those that happen monthly. Using this information, you should be able to determine the problem - simply run proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ) separately to determine which one converts money properly. Upon execution of each procedure, and further examination, you will notice that the error lies in the convert_money( ) function, in that the select statement never actually uses p_valid_day at all - instead, convert_money( ) uses the sysdate keyword in the overall selection criteria for obtaining the appropriate exchange rate
Posted February 1, 2001
Posted April 9, 2000
This hefty book gives outstanding coverage of Forms. Decent coverage of PL/SQL too. Its price is cheap compared to the time it will save you.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted March 20, 2000
Posted January 11, 2000
The first two sections of this book, the ones on SQL and PL/SQL, are not that great, because they use lame exercises and contain a lot of errors. But Oracle Press has other books on SQL that are great. It's the Forms sections that make the book. No other book really covers Forms well, and this one makes things crystal clear. For those parts alone I give it five stars.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted December 30, 1999
I guess I just wanted to learn some ORACLE and become certified after reading the chapters in the guide. I have passed all the guide's exams with 85-100% but failed miserably on the first exam. The questions in the guide are jokes compared to the certification questions, not to talk about the typing/logical errors I keep finding in the book even after reviewing the Errata. It was a very humbling experience and now I can really understand what is meant when someone is certified in ORACLE.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted December 22, 1999
The book was well organized. Based on my experience with other SQL products I knew exactly what I was looking for. The book made it easy for me to find the details I wanted and apply the techniques. Everything worked as I expected. Good tool!Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.