1. Create a stored procedure named insert_glaccount that lets a user add a new r
ID: 3776014 • 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.
Use the following schema for the next two questions.
You will only require the INVENTORY table for the next two questions. You can create this table by issuing the following command:
7. Create a procedure named 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. Create 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.
shipment line F shipmen id NUMBER (10 NUMBER (10) v id ship quantity NUMBER (4) date eceived DATE P CONSTRAINT UNKNOWN shipment line IDX (CONSTRAINT shipment P shipmen id NUMBER (10) date expected DATE G shipment shipment id pk (shipment id orde ne PFR order id NUMBER (8 NUMBER (10) PF nv id order quantity NUMBER (4) order line orderid invid pk (order d, in id) Inventory NUMBER (10) nv id NUMBER (8) em id item size VARCHAR2 (10) VARCHAR2 (20) Colo NUMBER (6.2) price qoh UMBER (4) inventory inv id pk (inv id) em em id NUMBER (8) item desc VARCHAR2 (100 F category id NUMBER (2 BLOB em mage em em id_pk (item_id) category category id category desc VARCHAR2 (20 category category id pk (category id) ColO Colo VARCHAR2 (20 colo color pk (color) cust order orde NUMBER (8 order date DATE meth pm VARCHAR2 (10) NUMBER (5) cust id order source id NUMBER 3) cust order orde d pk (order id) customer cust id NUMBER (5) ast VARCHAR2 (30) VARCHAR2 (30) first CHAR (1) VARCHAR2 (30 address city VARCHAR2 (30 state CHAR (2) VARCHAR2 (10) zip d phone VARCHAR2 (10 e phone VARCHAR2 (10) custome cust id pk (cust id)Explanation / Answer
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.
Answer :-
Step 1 : first we can create General_Ledger_Accounts table column details
CREATE TABLE General_Ledger_Accounts (
id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
APschema VARCHAR(10),
)
Step 2 : we can create insert procedute .
create PROCEDURE insert_glaccount
(
@id INTEGER,
@first_name VARCHAR(10),
@last_name VARCHAR(10),
@APschema VARCHAR(10)
)
AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
insert into employee (id,first_name,last_name,APschema )values( @id, @first_name, @last_name, @APschema)
END
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.