1. Create a stored procedure named insert_glaccount that lets a user add a new r
ID: 3765388 • Letter: 1
Question
1. Create a stored procedure named insert_glaccount that lets a user add a new row to the General_Ledger_Accounts table in the AP schema. This procedure should have two parameters, one for each of the two columns in this table. Then, code a CALL statement that tests the procedure. (Note that this table doesn’t allow duplicate account descriptions.)
2. Code a script that calls the procedure that you created in exercise 1 and passes the parameters by name. This procedure should provide exception handling that displays this message if the INSERT statement fails because the account number or account description is already in the table (a DUP_VAL_ON_INDEX error): A DUP_VAL_ON_INDEX error occurred. It should provide this message if any other type of error occurs: An unknown exception occurred.
3. Code a function named test_glaccounts_description that accepts one parameter that tests whether an account description is already in the General_Ledger_Accounts table. This function should return a value of 1 if the account description is in the table or zero if it isn’t. (Note: If a SELECT statement doesn’t return any data, it throws a NO_DATA_FOUND exception that your function can handle.)
4. Code a script that calls the function that you created in exercise 1. This script should display this message if the account description is in the table: Account description is already in use. It should provide this message if any other type of error occurs: Account description is available.
5. Modify the stored procedure that you created in exercise 1 and save it as insert_glaccount_with_test. This procedure should use the function that you created in step 3 to test whether the account description is going to be okay before it issues the INSERT statement. If the account description is a duplicate, this procedure should raise an application error with -20002 as the error number and an error message of Duplicate account description
6. Modify the script that you created in step 2 so it uses the procedure of exercise 5. This script should use the SQLERRM function to display the error number and message for the application error if the account description is already in use
please teach me how to do No 3 and No6 .
Explanation / Answer
Structure of creating a PROCEDURE:
CREATE OR REPLACE PROCEDURE insert_glaccount
(
parameter1 DATATYPE
parameter2 DATATYPE
)
AS
BEGIN
INSERT INTO tablename
VALUES (parameter1, parameter2);
END;
/
CALL insert_glaccount (acct_number value, ‘acct name value‘);
Exceiption handling:
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN *** handles when the input values are duplicates ***
WHEN OTHERS THEN ***handles all other applicaton errors ***
“passes the parameters by name”:
the order of parameters in the parentheses of the procedure does not matter.
paratemer name is assigned by the operator ” =>”….
For example,
CALL insert_glaccount( parameter1 => ‘value‘, parameter2 => value);
3. Code a function name test_glaccounts_description that accepts one parameter that test whether an account description is already in the General_Ledger_Accounts table. This function should return a value of 1 if the accont description is in the table or zero if it isn’t. (Note : If a SELECT statement doesn’t return any data, it throws a NO_DATA_FOUND exception that your function can handle.)
parameter: account_description
variable: account_number
Key: For a parameter passed by this function, if account number is NOT null, return 1, otherwise( i.e. WHEN NO_DATA_FOUND) return 0.
4. Code a script that calls the function that you created in exercise 1. This script should display this message if the acount description is in the table: “Account description is already in use.”
It should provide this message if any other type of error occurs: “Account description is available.”
5. Modify the stored procedure that you created in exercise 1 and save it as insert_glaccount_with_test. This procedure should use the function that you created in step 3 to test whether the account description is going to be okay before it issues the INSERT statement. If account description is a duplicate, this procedure should raise an applicaton error with -20002 as the error number and an error message of “Duplicate account description”.
For question 4 and 5,
IF function(parameter) is equal to zero, THEN INSERT INTO table VALUES;
ELSE error message with DBMS.OUTPUT_PUT.LINE( ‘ msg here ‘ );
or RAISE_APPLICATON_ERROR(number, ‘msg’);
END IF;
6. Modify the script that you created in step 2 so it uses the procedure of exercise 5. This script should use the SQLERRM function to display the error number and message for the application error if the account description is already in use.
I modified the procedure, insert_glaccount_with_test, from question 5 as replacing ELSE with ELSIF in case that the test function returns 1. Also I added EXCEPTION WHEN OTHERS THEN with proper error message.
I tried with EXCEPTION WHEN DUP_VAL_ON_INDEX THEN to handle the duplcate values, but it did not work, so I added ELSIF instead.
In the output, instead of having strong error messages with explanation, I got “…succeeded.” for processing the procedure and “ORA-0000: normal, successful completion” (as a result of SQLERRM function) with “You attempted to insert a duplicate value”. I don’t understand how ORA-0000 can be with error message.
7. Create a procedure name UPDATE_INVENTORY that updates the QOH column of the INVENTORY table based on the inventory ID and quantity amount specified by a calling program. The new inventory amount should reflect the previous inventory amount plus the amount of inventory received in the shipment or sold to a customer. The previous inventory amount is the amount of inventory already stored in the INVENTORY table, whereas the amount of inventory received is a parameter passed from the calling program.
8. Cread an anonymous PL/SQL program that calls the UPDATE_INVENTORY procedure created in question 7. Execute the anonymous PL/SQL program and update the INVENTORY table reflects that three (3) units of the item with inventory ID of 1 were sold. (HINT: Pass a negative value to the procedure). Execute a SELECT statement before and after to verify the QOH is update correctly.
If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that, allowing for a graceful and informative failure.
The form this failure takes does not, by the way, necessarily need to be an exception. When writing functions, you may well decide that in the case of certain exceptions, you will want to return a value such as NULL, rather than allow an exception to propagate out of the function.
This recommendation is easy to demonstrate with the ubiquitous SELECT INTO lookup query. An error that often occurs is NO_DATA_FOUND , indicating that the query did not identify any rows. In the following function, book_title , I put my SELECT INTO inside a function, but I do not allow the NO_DATA_FOUND exception to propagate out of the function:
CREATE OR REPLACE FUNCTION book_title (
isbn_in IN book.isbn%TYPE)
RETURN book.title%TYPE
IS
l_title book.title%TYPE;
BEGIN
SELECT title INTO l_title
FROM book
WHERE isbn =isbn_in;
RETURN l_rec.title;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
In other words, if the ISBN passed to the function finds no book, return NULL for the title. This is an unambiguous indicator of failure; a book must have a title.
I have decided in this case not to allow NO_DATA_FOUND to propagate (unhandled) out of the function. I use a SELECT INTO (an implicit query) to fetch the book title; Oracle's implementation of implicit queries means that NO_DATA_FOUND (as well as TOO_MANY_ROWS ) might be raised. That doesn't mean, however, that within my function, it really is an exception when no row is found. In fact, I might be expecting to not find a match.
By returning NULL rather than propagating an exception, I leave it up to the users of my function to decide how they would like to deal with a "no row found" situation. They might raise an exception:
BEGIN
l_title := book_title (l_isbn);
IF l_title IS NULL
THEN
RAISE NO_DATA_FOUND;
END IF;
or they might decide that such a result means that everything is, in fact, as expected:
BEGIN
l_title := book_title (l_isbn);
IF l_title IS NULL
THEN
process_new_book (l_isbn);
END IF;
Your programs are better behaved and more likely to be useful and used if you take care of expected errors. In this case, if I let the exception propagate out, this function would be unpredictable and hard to integrate into my application, because exception handlers must be coded in the caller's code block.
Avoid Hard-Coded Error Handling
The best way to achieve consistent, high-quality error handling throughout your application is to offer a set of predefined procedures that do all the basic plumbing of error handling, including the recording of error information (if desired), propagating the exception, and so on.
It is then crucial to make certain that development team members always use these procedures only in their WHEN clauses.
The following is the kind of code you should never write inside an exception handler:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_msg := 'No company for id ' ||
TO_CHAR (v_id);
v_err := SQLCODE;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES
(v_err, v_msg, v_prog,
SYSDATE, USER);
WHEN OTHERS
THEN
v_err := SQLCODE;
v_msg := SQLERRM;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES
(v_err, v_msg, v_prog, SYSDATE,
USER);
RAISE;
At first glance, you might say, "Well, what's wrong with it? After all, it is self-documenting. I can look at the code and understand exactly what it is doing and how it gets the job done."
I strongly support the objective of self-documenting code, but in this case, the transparency of the code's intent is actually built on dangerous ground. This delicate structure will come tumbling down with any of the following developments:
In essence, this sort of exception section makes sense only if you never make any mistakes, if you always get it right the first time, and if your code is perfect and frozen in time. That might be a tempting fantasy, but it will never be reality.
It is much better to strive for self-documenting code by hiding the implementation details, especially details involving the low-level "plumbing" of an application, such as exception handling and assertion logic. In the case of exception handling, the key is to move to using predefined, standardized error handler programs. Consider the following rewrite of the previous exception section:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
errpkg.handle (
'No company for id ' ||
TO_CHAR (v_id),
log => TRUE,
reraise => FALSE);
WHEN OTHERS
THEN
errpkg.handle (
log => TRUE,
reraise => TRUE);
The code remains transparent in its meaning:
Yet I have not hard-coded any specific implementation for either the logging or the reraising. Instead, I rely on the generic error handling package, errpkg, to do whatever is specified as the standard for my application.
This approach does much more than increase the consistency and robustness of the error handling. It also dramatically improves programmer productivity. Developers can focus more on the application-specific logic and less on the mundane tasks that we all know must be done for any application.
Use Named Constants
Oracle allocates 1,000 error numbers, between -20,000 and -20,999, for application-specific errors (such as "Employee must be 18 years old" or "Reservation date must be in the future").
It is extremely important that you put controls around how developers work with these error numbers. You should define all error numbers and their associated messages in a database table or operating system file. Build (or generate) a package that gives names to these errors, and then raise the errors by using those names and not any hard-coded values.
Here is a fairly typical hard-coded, error-prone programming example using RAISE_APPLICATION_ERROR : Sam Developer is told to write a procedure to stop updates and inserts when an employee is younger than 18. Sam thinks, "Surely no one has used error 20734 yet, so I will use it" and produces the following code:
CREATE OR REPLACE PROCEDURE check_hiredate
(date_in IN DATE)
IS
BEGIN
IF date_in < ADD_MONTHS (
SYSDATE, -1 * 12 * 18)
THEN
RAISE_APPLICATION_ERROR (
-20734, 'Employee is too young.');
END IF;
END;
Check out all that hard-coding! And while Sam is writing his code, of course, Natasha Programmer also decides that 20734 is a fine error number.
Here is a much cleaner approach:
CREATE OR REPLACE PROCEDURE check_hiredate
(date_in IN DATE)
IS
BEGIN
IF emp_rules.emp_too_young (date_in)
THEN
errpkg.raise(
errnums.emp_too_young);
END IF;
END;
First, I have moved the logic defining a "too young" employee to a function; it is a fundamental best practice to put formulas and business rules inside functions. For error handling, Sam now simply knows that he calls the errpkg.raise procedure to raise his error. Which error? Sam goes to the list of predefined exceptions (either in documentation or via a GUI interface) and picks, by name, the one that matches.
How are these errors and error messages defined? The best way is to put them in a relational table, provide an interface to that table, and, most important, build a utility that generates all the associated code (exception, error number, and function). The accompanying download includes the following msginfo files that take care of all those tasks for you:
Build Package-State Dump Modules
When an error occurs in one of your PL/SQL blocks, it is often useful to be able to determine the values of persistent package variables at the time of the failure. You can do this to some extent with the debuggers available with many interactive development environments (IDEs). That approach does not, however, give you access to the data values within a user's application session.
One way to obtain this information is to write a "dump" procedure in each of your packages. This procedure displays or records the contents of any relevant variables or data structures—whatever you determine is of value inside that package. You can then feed this information to an error handler, to provide as much information as possible to the person debugging your code.
Providing such dump procedures can dramatically reduce the time spent inserting debug messages that need to be removed later as well as recording problems that appear intermittently and are hard to reproduce.
The dump procedure approach relies on conformance to preestablished standards, so method names and stack formats can be interpreted, but all of these details can be hidden from view in a package, such as the callstack package defined in the callstack.sql script file. This package keeps track of the call stack, by recording in a PL/SQL table each piece of code as it "announces" itself. The package then uses that stack to determine which dump methods need to be called when an error occurs.
Unfortunately, there is no reliable (and supported) way right now to easily determine which packages "have state" even if they aren't in the call stack, but this may be possible in the future. A straightforward exercise might be to extend this package to write to a log file or pipe, instead of just using the standard DBMS_OUTPUT package.
The following DEMO_PKG conforms to the dump API by including a procedure named instantiate_error_context in the specification:
CREATE OR REPLACE PACKAGE demo_pkg
IS
PROCEDURE proc1;
PROCEDURE instantiate_error_context;
END;
/
The following proc1 procedure sets the module name in the stack; assigns a value to a variable; and then calls proc2 , which also announces itself and modifies a package variable. It then, however, raises an exception.
PROCEDURE demo_pkg.proc1 IS
BEGIN
--announce entry into this module
errpkg.set_module_name(
'demo_pkg.proc1');
--application processing here
application.field_1 := 'test string';
proc2;
errpkg.remove_module_name;
EXCEPTION
WHEN OTHERS
THEN
errpkg.set_err_msg ('DAT023');
errpkg.raise_error (
'Failed Operation');
END;
The instantiation procedure passes the values of the package data (the package state) to the error package.
PROCEDURE demo_pkg
.instantiate_ error_context
IS
BEGIN
errpkg.add_context (
'DEMO_PKG', 'Field #1',
application.field_1);
errpkg.add_context (
'DEMO_PKG', 'Field #2',
application.field_2);
errpkg.add_context (
'DEMO_PKG', 'Field #3',
application.field_3);
END;
When I run demo_pkg.proc1 , I see the following output:
SQL> exec demo_pkg.proc1
Adding demo_pkg.proc1 to stack
Adding demo_pkg.proc2 to stack
Error Log Time: 13:15:33
Call Stack: demo_pkg.proc1 -->
demo_pkg.proc2
Comments: Failed Operation
CFRS Error No: DAT027
Oracle Error: ORA-01403: no data
found
----------DEMO_PKG----------------------
Field #1: test string
Field #2: -37
Field #3: NULL
Note that the errpkg used in the example and found in the callstack.sql file requires you to explicitly list the packages that contain instantiate_error_context procedures. An improved implementation would rely on dynamic SQL (either DBMS_SQL or native dynamic SQL) to automatically construct the program call and execute it.
Use When Others for Unknown Exceptions
Don't use WHEN OTHERS to grab any and every error. If you know that a certain exception might be raised, include a handler specifically for that.
Here is an exception section that clearly expects a DUP_VAL_ON_INDEX error to be raised but buries that information in WHEN OTHERS :
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -1
THEN
update_instead (...);
ELSE
errpkg.log;
RAISE;
END IF;
Here is a much better approach:
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
update_instead (...);
WHEN OTHERS
THEN
errpkg.log;
RAISE;
By being as specific as possible, your code clearly states what you expect to have happen and how you want to handle your errors. That makes it easier to maintain and enhance. You can also more easily avoid hard-coding error numbers in your checks against SQLCODE .
Next Steps
READ
Part 1 of this article
more Feuerstein on
OTN
DOWNLOAD files
callstack.sql
errpkg.pkg
msginfo.fmb
msginfo.fmx
msginfo.pkg
msginfo.sql
Document Exceptions That Need Handling
When a developer designs a program well, a user of that program should never have to look at the body of code executed by that program to understand what it does and how it behaves. Yet the header of a program tells nothing about what errors it might raise and then send unhandled out of its exception section.
PL/SQL, unfortunately, doesn't offer a structured way to communicate this information. Consequently, you should consider creating your own conventions for including such news in the headers of your procedures and functions.
Suppose, for example, that I have built a package that calculates overdue fees on mortgages. The analyze_status procedure might raise the overdue.excessive_lateness or overdue.invalid_date_range exceptions. The count_by_borrower function could pass unhandled NO_DATA_FOUND or borrower.does_not_exist exceptions.
Here is a version of the package specification for overdue fees that makes explicit any error-raising behavior:
CREATE OR REPLACE PACKAGE overdue
IS
PROCEDURE analyze_status
(account_id_in IN account.id%TYPE)
/*
EXCEPTIONS RAISED:
borrower.excessive_lateness
syserr.invalid_date_range
*/
;
FUNCTION count_by_borrower
(borrower_id_in IN borrower.id%TYPE)
RETURN INTEGER
/*
EXCEPTIONS RAISED:
NO_DATA_FOUND
borrower.does_not_exist
*/
;
Notice that I use the same phrase "EXCEPTIONS RAISED" in both headers, so that it will be easy to search for this section in any of my programs. I also insert the comment between the header of the program (name and parameter list) and the closing semicolon for the statement. This placement ensures that the comment stays with the program.
Now, with a glance, any user of either of these programs understands that (a) she or he should include exceptions handlers for each of the documented exceptions and (b) a visit to the syserr (system-level error) or borrower packages will reveal more information about each of these user-defined exceptions.
Next Steps
READ
Part 1 of this article
more Feuerstein on
OTN
DOWNLOAD files
callstack.sql
errpkg.pkg
msginfo.fmb
msginfo.fmx
msginfo.pkg
msginfo.sql
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.