QuestionQuestion

Oracle SQL Developer

DESCRIPTION
The metadata layer is very important in systems integration. We always refer to it in order to collect information about the participating systems. We need the information stored in the metadata layer to generate queries on the fly that are submitted to each local system. In this homework we are going to extract information from the metadata table and instead of generating a set of SQL queries we are going to print this information on the screen. We will do this through an Oracle PL/SQL procedure.
Our task is to create a PL/SQL procedure called IDENTIFY_TOKEN that takes a string (a token) as input parameter. The procedure searches the Metadata table to find the token in the Canonical representation column. If it does not find it, it outputs a message to that effect. If it successfully locates the token in the Canonical representation column it needs to output the following:
• Token name
• Token identification as ‘Field’ or ‘TABLE’ (if it is not TABLE then it is a field)
• Corresponding name, datatype and function (if available) in local DB1
• Corresponding name, datatype and function (if available) in local DB2
Create and test the IDENTIFY_TOKEN procedure. You will probably need to use a cursor to search the metadata table and retrieve the requested information.

Example call of IDENTIFY_TOKEN using the Oracle Web interface (the field names are taken from HW3)
Exec IDENTIFY_TOKEN (‘Customer’);
The output should be:
Token: Customer; Token type: Field
DB1 Name: Client; DB1 DataType: Varchar2(20); DB1 Function: None
D21 Name: Patron; DB2 DataType: Char(50); DB2 Function: None

Local Database 1

CREATE TABLE books
                   (
                   id NUMBER PRIMARY KEY ,
                   isbn VARCHAR2(255) NOT NULL,
                   title VARCHAR2(255) NOT NULL,
                   author VARCHAR2(255) NOT NULL,
                   price NUMBER NOT NULL,
                   category varchar(255) NOT NULL
                   )
                  
INSERT INTO books (id, isbn, title, author,price,category) VALUES (1,'100101', 'The Park', 'Antonio', 200, 'drama')

INSERT INTO books (id, isbn, title, author,price,category) VALUES (2,'200201', 'The Snakes', 'Annet', 300, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (3,'300303', 'The Bus Ride', 'Jerry', 400, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (4,'400404', 'The African Elephats', 'Jackie', 400, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (5,'500505', 'The Big Giant', 'Antonio', 200, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (6,'600606', 'The Loud Mouse', 'Antonio', 300, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (7,'1230099', 'Journey to Pluto', 'Antonio', 300, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (8,'12311101', 'Things Fall Apart', 'Achebe', 300, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (9,'45612001', 'A Red Cow', 'Milner', 300, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (10,'456101101', 'Wonderful World', 'Milner', 300, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (11,'43576890', 'Green Eggs and Ham', 'Seuss', 150, 'children');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (12,‘3458902', 'Cat in Hat', 'Seuss', 200, 'childrens');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (13,'9870923', 'Hamlet', 'Shakespear', 300, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (14,'9083459', 'Othello', 'Shakespeare', 200, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (15,'90323891', 'Macbeth', 'Shakespeare', 100, 'drama');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (16,'902437', 'Charisma', 'Milner', 300, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (17,'908324', 'Arrow of God', 'Achebe', 300, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (18,'89403', 'No Longer at Ease', 'Achebe', 200, 'fiction');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (19,'267321', 'Carrie', 'King', 200, 'horror');

INSERT INTO books (id, isbn, title, author,price,category) VALUES (20,'37823', 'IT', 'King', 150, 'horror');


CREATE TABLE music_cds (
                   id NUMBER PRIMARY KEY,
                   album_title varchar(255) NOT NULL,
                   artist varchar(255) NOT NULL,
                   recording_campant varchar(255) NOT NULL,
                   price NUMBER (11) NOT NULL,
                   genre varchar(255) NOT NULL
                   )
               
INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (1, 'Maybach', 'Ricky', 'Defjam', 200, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (2, 'School Dance', 'Wayne', 'Convict', 300, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (3, 'Zigzig', 'Young Thug', 'Young Money', 200, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (4, 'Coming Home', 'Michael', 'Michael records', 200, 'Rock');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (5, 'We Are Home', 'Mush', 'Defjam', 100, 'Jazz');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (6, 'Bambam', 'Mush', 'Defjam', 100, 'Jazz');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (7, 'Bad Girl', 'Minaj', 'Young Money', 100, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (8, 'Miss Boss', 'Minaj', 'Young Money', 100, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (9, 'Long Way To Go', 'Cassie', 'Lions Gate', 100, 'Jazz');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (10, 'Mr. Piano', 'Brandy', 'Lions Gate', 400, 'Jazz');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (11, 'The Time is Now', 'David', 'RCA', 350, 'R&B');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (12, 'Invasion of Privacy', 'Cardi B', 'Atlantic Records', 450, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (13, 'Bobby Valentino II', 'Valentino', 'Fabulus Poodles', 300, 'Hip Hop');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (14, 'Where’s The Drop', 'deadmau5', 'Mau5Trap', 100, 'Classical');

INSERT INTO music_cds (id,album_title,artist,recording_campant,price,genre) VALUES (15, 'Seasons of Change', 'McCreery', 'Thirty Tigers', 100, 'Country');

CREATE TABLE stock (
                   id NUMBER PRIMARY KEY,
                   table_id NUMBER(11) NOT NULL,
                   stock_type varchar(255) NOT NULL,
                   warehouse_id NUMBER(11) NOT NULL,
                   date_added VARCHAR2(255) NOT NULL
                   )
                  
INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (1, 1, 'book', 1, '2019-03-08');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (2, 2, 'book', 1, '2019-03-13');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (3, 3, 'book', 2, '2019-03-08');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (4, 4, 'book', 2, '2019-03-13');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (5, 1, 'music', 3, '2019-03-08');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (6, 2, 'music', 3, '2019-03-13');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (7, 3, 'music', 4, '2019-03-14');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (8, 4, 'music', 4, '2019-03-15');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (9, 5, 'book', 1, '2019-03-21');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (10, 6, 'book', 1, '2019-03-21');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (11, 7, 'book', 1, '2019-03-21');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (12, 8, 'book', 1, '2019-03-21');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES(13, 9, 'book', 2, '2019-03-12');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (14, 10, 'book', 3, '2019-03-12');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (15, 5, 'music', 2, '2019-03-15');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (16, 6, 'music', 3, '2019-03-15');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (17, 7, 'music', 3, '2019-03-15');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (18, 8, 'music', 2, '2019-03-15');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (19, 9, 'music', 1, '2019-03-15');

INSERT INTO stock (id,table_id,stock_type,warehouse_id,date_added) VALUES (20, 10, 'music', 4, '2019-03-12');

CREATE TABLE warehouses(
                   id NUMBER(11) PRIMARY KEY,
                   address VARCHAR2(255) NOT NULL,
                   manager VARCHAR2(255) NOT NULL,
                   phone_number NUMBER(11) NOT NULL,
                   located VARCHAR2(255) NOT NULL
                   )

INSERT INTO warehouses (id,address,manager,phone_number,located) VALUES (1, '205 New York', 'Kim', 1234567890, 'New York');

INSERT INTO warehouses (id,address,manager,phone_number,located) VALUES (2, '205 Geogia', 'John', 4049786543, 'Georgia');

INSERT INTO warehouses (id,address,manager,phone_number,located) VALUES (3, '765 Detroit', 'Mike', 6789000990, 'Detroit');

INSERT INTO warehouses (id,address,manager,phone_number,located) VALUES (4, '987 Ohio', 'Mary', 9065435439, 'Ohio');

Local Database 2

create table amazing_warehouse
(amazing_w_id number,
amazing_w_name varchar2(400),
amazing_w_street varchar2(400),
amazing_w_city varchar2(400),
amazing_w_state varchar2(400),
amazing_w_zip varchar2(400),
CONSTRAINT warehouse_pk PRIMARY KEY(amazing_w_id)
);
insert into amazing_warehouse values (10, 'Warehouse 1', '100 Main St.', 'New Yorky', 'MD', '21000');
insert into amazing_warehouse values (20, 'Warehouse 2', '200 Main St.', 'Los Pappas', 'NJ', '07000');
insert into amazing_warehouse values (30, 'Warehouse 3', '300 Main St.', 'Poe Polis', 'CA', '90000');
insert into amazing_warehouse values (40, 'Warehouse 4', '400 Main St.', 'Ocean Land', 'NY', '10000');
insert into amazing_warehouse values (50, 'Warehouse 5', '500 Main St.', 'Flamingo', 'AZ', '85000');

create table amazing_books
(amazing_b_id number(10),
amazing_b_title varchar2(400),
amazing_b_author varchar2(400),
amazing_b_publisher varchar2(400),
amazing_b_price number(10, 2),
amazing_b_num_of_copies number,
amazing_b_stored_at number,
CONSTRAINT books_pk PRIMARY KEY (amazing_b_id),
CONSTRAINT fk_books
    FOREIGN KEY (amazing_b_stored_at)
    REFERENCES amazing_warehouse(amazing_w_id)
);
insert into amazing_books values (1, 'Book 1', 'Author1, Author3', 'Publisher1', 29.99, 103, 10);
insert into amazing_books values (2, 'Book 2', 'Author1', 'Publisher1', 19.99, 105, 10);
insert into amazing_books values (3, 'Book 3', 'Author1, Author5', 'Publisher2', 39.99, 271, 20);
insert into amazing_books values (4, 'Book 4', 'Author2', 'Publisher1', 29.99, 151, 10);
insert into amazing_books values (5, 'Book 5', 'Author2, Author3', 'Publisher2', 39.99, 104, 20);
insert into amazing_books values (6, 'Book 6', 'Author2', 'Publisher2', 29.99, 223, 30);
insert into amazing_books values (7, 'Book 7', 'Author2, Author1', 'Publisher4', 19.99, 91, 40);
insert into amazing_books values (8, 'Book 8', 'Author3', 'Publisher3', 9.99, 28, 10);
insert into amazing_books values (9, 'Book 9', 'Author3', 'Publisher3', 29.99, 10, 10);
insert into amazing_books values (10, 'Book 10', 'Author4', 'Publisher3', 19.99, 120, 50);
insert into amazing_books values (11, 'Book 11', 'Author4, Author1', 'Publisher2', 29.99, 135, 50);
insert into amazing_books values (12, 'Book 12', 'Author4', 'Publisher2', 39.99, 113, 40);
insert into amazing_books values (13, 'Book 13', 'Author4', 'Publisher1', 49.99, 27, 30);
insert into amazing_books values (14, 'Book 14', 'Author5', 'Publisher4', 9.99, 98, 10);
insert into amazing_books values (15, 'Book 15', 'Author5, Author2', 'Publisher4', 19.99, 152, 20);
insert into amazing_books values (16, 'Book 16', 'Author5', 'Publisher3', 29.99, 118, 30);
insert into amazing_books values (17, 'Book 17', 'Author5, Author4', 'Publisher3', 19.99, 244, 40);
insert into amazing_books values (18, 'Book 18', 'Author5', 'Publisher2', 9.99, 308, 50);
insert into amazing_books values (19, 'Book 19', 'Author6, Author1', 'Publisher4', 19.99, 321, 10);
insert into amazing_books values (20, 'Book 20', 'Author6, Author1', 'Publisher1', 29.99, 722, 20);
insert into amazing_books values (21, 'Book 21', 'Author6', 'Publisher1', 39.99, 130, 30);

create table amazing_cds
(amazing_c_id number,
amazing_c_title varchar2(400),
amazing_c_num_songs number,
amazing_c_producer varchar2(400),
amazing_c_price number (10, 2),
amazing_c_num_of_copies number,
amazing_c_stored_at number,
CONSTRAINT cds_pk PRIMARY KEY (amazing_c_id),
CONSTRAINT fk_cds
    FOREIGN KEY (amazing_c_stored_at)
    REFERENCES amazing_warehouse(amazing_w_id)
);
insert into amazing_cds values (1, 'CD1', 10, 'Producer1', 19.99, 202, 10);
insert into amazing_cds values (2, 'CD2', 11, 'Producer1', 19.99, 432, 20);
insert into amazing_cds values (3, 'CD3', 13, 'Producer2', 19.99, 311, 10);
insert into amazing_cds values (4, 'CD4', 12, 'Producer2', 9.99, 151, 30);
insert into amazing_cds values (5, 'CD5', 16, 'Producer2', 29.99, 721, 20);
insert into amazing_cds values (6, 'CD6', 13, 'Producer3', 9.99, 321, 20);
insert into amazing_cds values (7, 'CD7', 12, 'Producer3', 19.99, 520, 30);
insert into amazing_cds values (8, 'CD8', 10, 'Producer3', 29.99, 97, 50);
insert into amazing_cds values (9, 'CD9', 17, 'Producer3', 19.99, 84, 30);
insert into amazing_cds values (10, 'CD10', 9, 'Producer4', 9.99, 340, 40);
insert into amazing_cds values (11, 'CD11', 10, 'Producer4', 9.99, 211, 40);
insert into amazing_cds values (12, 'CD12', 15, 'Producer4', 19.99, 904, 40);
insert into amazing_cds values (13, 'CD13', 17, 'Producer4', 29.99, 409, 50);
insert into amazing_cds values (14, 'CD14', 13, 'Producer4', 19.99, 332, 50);
insert into amazing_cds values (15, 'CD15', 10, 'Producer1', 9.99, 122, 50);

METADATA

INSERT into Metadata VALUES ('BOOK_CATEGORY', 'Category', 'VARCHAR2(20)', NULL, NULL);

INSERT into Metadata VALUES ('BOOK_AUTHOR', 'Author', 'VARCHAR2(35)', 'amazing_b_author', 'VARCHAR2(400)');

INSERT into Metadata VALUES ('BOOK_PUBLISHER', 'Publisher', 'VARCHAR2(35)', 'amazing_b_publisher', 'VARCHAR2(400)');

INSERT into Metadata VALUES ('BOOK_YEARISSUE', 'Year_issue', 'NUMBER(4)', NULL, NULL);

INSERT into Metadata VALUES ('BOOK_PRICE', 'Price', 'NUMBER(6,3)', 'amazing_b_price', 'NUMBER(10,2)');

INSERT into Metadata VALUES ('BOOK_NUMCOPIES', NULL, NULL, 'amazing_b_num_of_copies', 'NUMBER');

INSERT into Metadata VALUES ('CD', 'CDS', 'TABLE', 'AMAZING_CDS', 'TABLE');

INSERT into Metadata VALUES ('CD_ID', 'idCD', 'NUMBER(6)', 'amazing_c_id', 'NUMBER');

INSERT into Metadata VALUES ('CD_TITLE', 'Album_title', 'VARCHAR2(100)', 'amazing_c_title', 'VARCHAR2(400)');

INSERT into Metadata VALUES ('CD_ARTISTS', 'Artists', 'VARCHAR2(200)', NULL, NULL);

INSERT into Metadata VALUES ('CD_COMPANY', 'Recording_company', 'VARCHAR2(35)', NULL, NULL);

INSERT into Metadata VALUES ('CD_GENRE', 'Genre', 'VARCHAR2(35)', NULL, NULL);

INSERT into Metadata VALUES ('CD_YEAR', 'Year_issue', 'NUMBER(4)', NULL, NULL);

INSERT into Metadata VALUES ('CD_PRODUCER', NULL, NULL, 'amazing_c_producer', 'VARCHAR2(400)');

INSERT into Metadata VALUES ('CD_PRICE', 'Price', 'NUMBER(6,3)', 'amazing_c_price', 'NUMBER(10,2)');

INSERT into Metadata VALUES ('CD_NUMSONGS', NULL, NULL, 'amazing_c_num_songs', 'NUMBER');

INSERT into Metadata VALUES ('WAREHOUSE', 'WAREHOUSES', 'TABLE', 'AMAZING_WAREHOUSE', 'TABLE');

INSERT into Metadata VALUES ('WAREHOUSE_ID', 'idWarehouse', 'NUMBER(6)', 'amazing_w_id', 'NUMBER');

INSERT into Metadata VALUES ('WAREHOUSE_IDMANAGER', 'IdManager', 'NUMBER(6)', NULL, NULL);

INSERT into Metadata VALUES ('WAREHOUSE_STREET', 'Street', 'VARCHAR2(30)', 'amazing_w_street', 'VARCHAR2(400)');

INSERT into Metadata VALUES ('WAREHOUSE_NAME', NULL, NULL, 'amazing_w_name', 'VARCHAR2(400)');

INSERT into Metadata VALUES ('WAREHOUSE_CITY', 'City', 'VARCHAR2(25)', 'amazing_w_city', 'VARCHAR2(400)');

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

1. The script that creates the procedure

CREATE OR REPLACE procedure IDENTIFY_TOKEN(p_token varchar2)
AS

CURSOR c_get_metadata IS
SELECT canonical,
       CASE WHEN db1_datatype = 'TABLE' OR db2_datatype = 'TABLE' THEN 'TABLE' ELSE 'Field' END Field,
       db1_name,
       db1_datatype,
       db2_name,
       db2_datatype
FROM metadata
WHERE lower(canonical) = lower(p_token);

l_no_data_found number:= 0;
BEGIN
   
FOR idx IN c_get_metadata LOOP
    l_no_data_found := 1;
    dbms_output.put_line('Token: ' || idx.canonical || ' Token type: ' || idx.field);   
    dbms_output.put_line('DB1 Name: '|| idx.db1_name || ' DB1 DataType: '|| idx.db1_datatype ||' DB1 Function: None');
    dbms_output.put_line('DB2 Name: '|| idx.db2_name || ' DB2 DataType: '|| idx.db2_datatype ||' DB2 Function: None');
END LOOP;...

By purchasing this solution you'll be able to access the following files:
Solution.docx.

50% discount

Hours
Minutes
Seconds
$77.00 $38.50
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats