Create a PL/SQL block – Hope Papers

Create a PL/SQL block

1. Create a PL/SQL block to complete the followings:

*Output the cheapest movie information. You can use the view you created in project 1 – task 1

*In your block, referential type should be used to receive the cursor return.

*Use DBMS_OUTPUT to output your result.

*Possible Exception should be handled in exception handling section. You are not required to use user-defined exception. Use Oracle predefined exception. When exception occurs, you need to output error code and error message.

*Spool the output to a text file. Don’t forget to use “Set serveroutput on”

*Submit your code as .sql file and spooled output

2. Create a stored procedure based on task 1 with an input parameter movie_id. Modify your cursor to use movie_id to select desired movie information. And execute the procedure and spool the execution result.

*Spool the output to a text file. Don’t forget to use “set serveroutput on”

*Submit your code as .sql file and spooled output

3. Create a statement trigger on orders table. The trigger fires after updating the table. When the trigger fires one record insert into the following temp table using the insert statement shown below:

Note: you need to create temp_table and its sequence using the following code.

PROMPT creating table temp_table ….
DROP TABLE temp_table;
CREATE TABLE temp_table
( num_col NUMBER(5) not null primary key,
char_col VARCHAR2(30),
date_col VARCHAR2(30));

PROMPT creating SEQUENCE trigger_seq ….
DROP SEQUENCE trigger_seq;
CREATE SEQUENCE trigger_seq
START WITH 1
INCREMENT BY 1;

INSERT INTO temp_table (num_col, char_col, date_col)
VALUES (trigger_seq.NEXTVAL, ‘After Statement trigger’, TO_CHAR(sysdate, ‘DD-MON-YYY HH24:MI:SS’));

4. Create a row trigger on order_items table to fire after inserting the data into order_items table. When the trigger fires it inserts a record into temp_table using the following insert statement:

*INSERT INTO temp_table (num_col, char_col, date_col)VALUES (trigger_seq.NEXTVAL, ‘After Row Trigger’, TO_CHAR(sysdate, ‘DD-MON-YYY HH24:MI:SS’));

Test your triggers.

*Update orders table to set total payment to 300. fire statement trigger on orders table.

*Insert one record into order_items. Fire row trigger on order_items table.

*Query temp_table to get the inserted records for trigger firing.

*Spool the output to a text file and submit it.

-Submit code as .sql file

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
Uncategorized