Create a procedure that accepts input and uses native dynamic SQL to perform thi
ID: 3566576 • Letter: C
Question
Create a procedure that accepts input and uses native dynamic SQL to perform this task. Test the procedure by adding a column named MEMBER to the BB_SHOPPER table with the data type CHAR(1).
1. Review the uncompleted procedure code, and then finish the EXECUTE IMMEDIATE statement to allow column additions.
Uncompleted procedure code:
2. Create the procedure
3. Run the procedure by using the following block to add the MEMBER column:
.
BEGIN
dyn_addcol_sp('member', 'bb_shopper', 'CHAR(1)');
END;
.
4. Type DESC bb_shopper and press Enter to list the table structure and confirm the addition of the MEMBER COLUMN.
.
.
BB_SHOPPER table:
CREATE TABLE BB_Shopper (
idShopper number(4),
FirstName varchar2(15),
LastName varchar2(20),
Address varchar2(40),
City varchar2(20),
State char(2),
ZipCode varchar2(15),
Phone varchar2(10),
Fax varchar2(10),
Email varchar2(25),
UserName varchar2(8),
Password varchar2(8),
Cookie number(4) DEFAULT 0,
dtEntered date DEFAULT SYSDATE,
Province varchar2(15),
Country varchar2(15),
CONSTRAINT shopper_id_pk PRIMARY KEY(idShopper) );
create sequence bb_shopper_seq
start with 30;
insert into bb_shopper
values (21, 'John', 'Carter', '21 Front St.', 'Raleigh',
'NC','54822', '9014317701', NULL, 'Crackjack@aol.com', 'Crackj',
'flyby', 1, '13-JAN-2012', NULL, 'USA');
insert into bb_shopper
values (22, 'Margaret', 'Somner', '287 Walnut Drive', 'Cheasapeake',
'VA','23321', '7574216559', NULL, 'MargS@infi.net', 'MaryS',
'pupper', 1, '03-FEB-2012', NULL, 'USA');
insert into bb_shopper
values (23, 'Kenny', 'Ratman', '1 Fun Lane', 'South Park',
'NC','54674', '9015680902', NULL, 'ratboy@msn.net', 'rat55',
'kile', 0, '26-JAN-2012', NULL, 'USA');
insert into bb_shopper
values (24, 'Camryn', 'Sonnie', '40162 Talamore', 'South Riding',
'VA','20152', '7035556868', NULL, 'kids2@xis.net', 'kids2',
'steel', 1, '19-MAR-2012', NULL, 'USA');
insert into bb_shopper
values (25, 'Scott', 'Savid', '11 Pine Grove', 'Hickory',
'VA','22954', '7578221010', NULL, 'scott1@odu.edu', 'fdwell',
'tweak', 1, '19-FEB-2012', NULL, 'USA');
insert into bb_shopper
values (26, 'Monica', 'Cast', '112 W. 4th', 'Greensburg',
'VA','27754', '7573217384', NULL, 'gma@earth.net', 'gma1',
'goofy', 1, '09-FEB-2012', NULL, 'USA');
insert into bb_shopper
values (27, 'Pete', 'Parker', '1 Queens', 'New York',
'NY','67233', '1013217384', NULL, 'spider@web.net', '',
'', 0, '14-FEB-2012', NULL, 'USA');
ALter table bb_shopper
ADD (promo CHAR(1));
Explanation / Answer
/* Completed Procedure dyn_addcol_sp */
CREATE OR REPLACE PROCEDURE dyn_addcol_sp
(p_col IN VARCHAR2,
p_table IN VARCHAR2,
p_type IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'alter table ' || p_table || ' add '|| p_col || ' ' || p_type;
END dyn_addcol_sp;
/* Test Procedure */
BEGIN
dyn_addcol_sp('member','bb_shopper','CHAR(1)');
END;
/* Describe Table bb_shopper */
DESC bb_shopper;
/*
Name Null Type
--------- -------- ------------
IDSHOPPER NOT NULL NUMBER(4)
FIRSTNAME VARCHAR2(15)
LASTNAME VARCHAR2(20)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(20)
STATE CHAR(2)
ZIPCODE VARCHAR2(15)
PHONE VARCHAR2(10)
FAX VARCHAR2(10)
EMAIL VARCHAR2(25)
USERNAME VARCHAR2(8)
PASSWORD VARCHAR2(8)
COOKIE NUMBER(4)
DTENTERED DATE
PROVINCE VARCHAR2(15)
COUNTRY VARCHAR2(15)
PROMO CHAR(1)
MEMBER CHAR(1) */
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.