Write a script that uses dynamic SQL and a cursor to loop through each row of th

Question

Write a script that uses dynamic SQL and a cursor to loop through each row of the Administrators table and

(1) create a login ID for each row in that consists of the administrator’s first and last name with no space between;
(2) set a temporary password of “temp” for each login;
(3) set the default database for the login to the MyGuitarShop database;
(4) create a user for the login with the same name as the login; and
(5) assign the user to the OrderEntry role.

This is what the Administrators table looks like:

Results & Messages Admin|D EmailAddress Password FirstName LastName 2 2-aam@rmurayguitarshop.com admin@myguitarshop .com 6a718fbd768e2378b511f8249b5489940e9022 Admin User wie joel @murach .com 971e95957d3b74d70d79c20c94e9cd91b85f7aae Joel Murach mike @murach .com 5f2975c819cefc686282456aeae3a137bf896ee8 Mike Murach 123

Explanation / Answer

hi,

please find your answers below:-

====================================================================================

1)

DECLARE

   a_id Administrators.AdminId%type;

   a_email Administrators.EmailAddress%type;

   a_pwd Administrators.Password %type;

   CURSOR a_admin is

      SELECT AdminID, EmailAddress, Password FROM Administrators;

BEGIN

   OPEN a_admin;

   LOOP

   FETCH a_admin into a_id, a_email, a_pwd;

      EXIT WHEN a_admin%notfound;

      dbms_output.put_line(a_id || ' ' || a_email || ' ' || a_pwd);

   END LOOP;

   CLOSE a_admin;

END;

=======================================================================================

2)

ALTER TABLE Administrator
ADD LoginID varchar(30);

SELECT CONCAT (FIRSTNAME, LASTNAME) AS LoginID FROM Administrator;

========================================================================================

3)

ALTER TABLE Administrator
ADD Tempwd varchar(30);

UPDATE Administrator
SET Tempwd = 'temp';

======================================================================================

4)

CREATE LOGIN @LoginID WITH PASSWORD = 'temp', DEFAULT_DATABASE='MyGuitarShop' DEFAULT_LANGUAGE='us_english', CHECK_POLICY= OFF;

========================================================================================

5)

=================================================================================

Please let me know in case of any clarification.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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