Hi,
I have a requirement where i need to use ROWTYPE and %ROWCOUNT to display first 3 employees with highest credits.
I have the sample table and what i tried and unable to succeed.
Am getting this error - PLS-00222: no function with name 'C1' exists in this scope
Any help is appreciated. thanks.
{
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
WITH tab1 AS (
SELECT 'Susan' first_name, 'Brown' last_name, 30 credit FROM DUAL
UNION
SELECT 'jim', 'kern', 25 FROM DUAL
UNION
SELECT 'Martha', 'Woods', 40 FROM DUAL
UNION
SELECT 'Ellen', 'Owens', 15 FROM DUAL
)
DECLARE
TYPE t_karthik_tab IS TABLE OF karthik%ROWTYPE; -- same as tab1
employee_rec t_karthik_tab;
CURSOR c1
IS
WITH tab1
AS (SELECT 'Susan' first_name, 'Brown' last_name, 30 credit
FROM DUAL
UNION
SELECT 'jim', 'kern', 25 FROM DUAL
UNION
SELECT 'Martha', 'Woods', 40 FROM DUAL
UNION
SELECT 'Ellen', 'Owens', 15 FROM DUAL)
SELECT *
FROM tab1
ORDER BY credit DESC;
BEGIN
SELECT *
BULK COLLECT INTO employee_rec
FROM karthik
ORDER BY credit DESC NULLS LAST;
FOR i IN c1
LOOP
IF c1%ROWCOUNT <= 3
THEN
DBMS_OUTPUT.PUT_LINE ('Employee name: ' || c1 (i).first_name);
END IF;
END LOOP;
END;
/
}