Hi all, I am using SQLDeveloper 3.2.20 on Oracle 11gR2 and Windows 7 Prof sp1 and 'til now, smooth sailing.
I am using the SCOTT/tiger account i.e. limited access, definitely no sysdba role privilege.
I am practising the use of explicit cursors and my codes generate following errors:
Error report:
ORA-06550: line 5, column 17:
PLS-00352: Unable to access another database 'ROWTYPE'
ORA-06550: line 5, column 17:
PLS-00201: identifier 'EMP_CURSOR@ROWTYPE' must be declared
This is the code ( I tried orcl.scott.emp_cursor@ROWTYPE, but that didn't work either)
set serveroutput on;
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename
FROM scott.emp;
v_emp_record emp_cursor@ROWTYPE; -- don't even try them 1-by-1
v_sqlcode number;
v_errm varchar2(64);
BEGIN
IF NOT emp_cursor%ISOPEN THEN -- FETCH rows only when cursor is open!
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor INTO v_emp_record; -- gets the 1st row in cursor!
EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL; -- in case no rows
INSERT INTO tmp_list (empid, empname) -- insert into a temporary list
VALUES (v_emp_record.empno, v_emp_record.ename);
END LOOP;
COMMIT;
CLOSE emp_cursor;
EXCEPTION
when others then
v_sqlcode := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 64);
DBMS_OUTPUT.PUT_LINE('SQL code ' || v_sqlcode || ' ' || v_errm);
END;
/
I am beginning to suspect this is a "access privilege" thing.
Can anyone point me in the right direction please?