1. Create a stored procedure named insert_glaccount that lets a user add a new r

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.

Help for question 2 please.

Explanation / Answer

when creating the General_Ledger_Accounts set primary key to id column

store procedure for insert value it have duplicate DUP_VAL_ON_INDEX the display the error message

create procedure insert_glaccount
as
@id int;
@name varchar(50);
Begin
insert into General_Ledger_Accounts (id,name) values (@id, @name);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Duplicate value on index');
End

--call the store procedure inseret_glaccount

call insert_glaccount (1, 'Deva');

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.