Required: Based on the given table: Employees table as shown below, write the PL

Question

Required:

Based on the given table: Employees table as shown below, write the PL/SQL that applies single row function in order to get the printed output per number.

Copy and paste the PL/SQL code on the space provided after each questions.

CREATE TABLE EMPLOYEES(

EMPLOYEE_ID NUMBER(3) PRIMARY KEY,

FIRSTNAME VARCHAR(10),

LASTNAME VARCHAR(10)NOT NULL,

JOB_ID VARCHAR(10),        

SALARY NUMBER(8,2),

MANAGER_ID NUMBER(6),

DEPARTMENT_ID NUMBER(4));

INSERT INTO EMPLOYEES VALUES (100,'STEVEN','KING','AD_PRES',24000,NULL,90);

INSERT INTO EMPLOYEES VALUES (101,'NENA','KOCHAR','AD_VP',17000,100,90);

INSERT INTO EMPLOYEES VALUES (102,'LEX','DE HAAN','AD_VP',17000,100,90);

INSERT INTO EMPLOYEES VALUES (103,'ALEXANDER','HUNOLD','IT_PROG',NULL,101,60);

INSERT INTO EMPLOYEES VALUES (104,'BRUCE','ERNST','IT_PROG',6000,102,60);

INSERT INTO EMPLOYEES VALUES (107,'DIANA','LORENTZ','IT_PROG',4200,103,60);

INSERT INTO EMPLOYEES VALUES (124,'KEVIN','MOURGOS','ST_MAN',5800,100,50);

INSERT INTO EMPLOYEES VALUES (141,'TRINA','RAJS','ST_CLERK',3500,124,50);

INSERT INTO EMPLOYEES VALUES (142,'CURTIS','DAVIES','ST_CLERK',3100,124,50);

INSERT INTO EMPLOYEES VALUES (143,'RANDALL','MATOS','ST_CLERK',2600,124,50);

INSERT INTO EMPLOYEES VALUES (144,'PETER','VARGAS','ST_CLERK',2500,124,50);

INSERT INTO EMPLOYEES VALUES (149,'ELENI','ZLOTKEY','SA_MAN',10500,100,80);

INSERT INTO EMPLOYEES VALUES (174,'ELLEN','ABEL','SA_REP',11000,149,50);

INSERT INTO EMPLOYEES VALUES (176,'JONATHAN','TAYLOR','SA_REP',8600,149,80);

INSERT INTO EMPLOYEES VALUES (178,'KIMBERLEY','GRANT','SA_REP',7000,149,NULL);

INSERT INTO EMPLOYEES VALUES (200, 'JENNIFER','WHALEN','AD_ASST',4400,101,10);

INSERT INTO EMPLOYEES VALUES (201,'MICHAEL','HARTSTEIN','MK_MAN',13000,100,20);

INSERT INTO EMPLOYEES VALUES (202,'PAT','FAY','MK_REP',6000,201,20);

INSERT INTO EMPLOYEES VALUES (205,'SHELLEY','HIGGINS','AC_MGR',12000,101,110);

INSERT INTO EMPLOYEES VALUES (206,'WILLIAM','GIETZ','AC_ACCOUNT',8300,205,110);

EMPLOYEE ID FIRSTNAME LASTNAME JOB ID SALARY MANAGER ID DEPARTMENT ID 24000 17000 17000 100 STEVEN AD PRES 0 DE HAANAD VP 103 ALEXANDER HUNOLD 104 BRUCE 07 DIANA 12 KEVIN 141 TRINA 142 CURTIS LORENTZ 5 5 DAVIES 144 PETER 149 ELENI 17 ELLEN 176 JONATHAN TAYLOR 178 KIMBERLEY GRANT 200 JENNIFER WHALEN 201 MICHAEL HART 202 PAT 205 SHELLEY 206 WILLIAM GI VARGAS 10500 11000 1 STEIN MK MAN 13000 1 12000 HIGGINS ETZ King,Steven Hunold,Alexander Ernst, Bruce Lorentz,Diana FIRSTNAME 2 SALARY BONUS 24000 $$$26300 17000 $$$18600 17000 $$$18600 LEXANDER 6000 $$$$6500 4200 $$$$4520 10500 $$$11450 8600 $$$$9360 13000 $$$14200 6000 $$$$6500 3. LASTNAME SALARY RPAD (SALARY,4, TAYLOR WHALEN GIETZ 4200 4200 5800 5800 8600 8600 7000 7000 4400 4400 8300 8300

Explanation / Answer

CREATE OR REPLACE FUNCTION
rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' )
RETURN VARCHAR2
AUTHID CURRENT_USER AS
TYPE c_refcur IS REF CURSOR;
lc_str VARCHAR2(2000);
lc_colval VARCHAR2(2000);
c_dummy c_refcur;
l number;
BEGIN
OPEN c_dummy FOR p_slct;
LOOP
FETCH c_dummy INTO lc_colval;
EXIT WHEN c_dummy%NOTFOUND;
lc_str := lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE c_dummy;
RETURN SUBSTR(lc_str,2);
EXCEPTION
WHEN OTHERS THEN
lc_str := SQLERRM;
IF c_dummy%ISOPEN THEN
CLOSE c_dummy;
END IF;
RETURN lc_str;
END;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

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