Hi
edit: you can probably skip all this guff and go straight to the bottom...In the end this is probably just a question of how to use a function that returns a %rowtype. Thanks.
Currently reading Feuerstein's tome, 5th ed. I've downloaded and run the file genaa.sp, which is a code generator. Specifically, you feed it a table name and it generates code (package header and package body) that will create a cache of the specified table's contents.
So, I ran:
HR@XE> @"C:\Documents and Settings\Jason\My Documents\Work\SQL\OPP5.WEB.CODE\OPP5.WEB.CODE\genaa.sp"
749 /
Procedure created.
HR@XE> exec genaa('EMPLOYEES');
which generated a nice bunch of code, viz:
create or replace package EMPLOYEES_cache is
function onerow ( EMPLOYEE_ID_in IN HR.EMPLOYEES.EMPLOYEE_ID%TYPE) return HR.EMPLOYEES%ROWTYPE;
function onerow_by_EMP_EMAIL_UK (EMAIL_in IN HR.EMPLOYEES.EMAIL%TYPE) return HR.EMPLOYEES%ROWTYPE;
procedure test;
end EMPLOYEES_cache;
/
create or replace package body EMPLOYEES_cache is
TYPE EMPLOYEES_aat IS TABLE OF HR.EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER;
EMP_EMP_ID_PK_aa EMPLOYEES_aat;
TYPE EMP_EMAIL_UK_aat IS TABLE OF HR.EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY HR.EMPLOYEES.EMAIL%TYPE;
EMP_EMAIL_UK_aa EMP_EMAIL_UK_aat;
function onerow ( EMPLOYEE_ID_in IN HR.EMPLOYEES.EMPLOYEE_ID%TYPE)
return HR.EMPLOYEES%ROWTYPE is
begin
return EMP_EMP_ID_PK_aa (EMPLOYEE_ID_in);
end;
function onerow_by_EMP_EMAIL_UK (EMAIL_in IN HR.EMPLOYEES.EMAIL%TYPE)
return HR.EMPLOYEES%ROWTYPE is
begin
return EMP_EMP_ID_PK_aa (EMP_EMAIL_UK_aa (EMAIL_in));
end;
procedure load_arrays is
begin
FOR rec IN (SELECT * FROM HR.EMPLOYEES)
LOOP
EMP_EMP_ID_PK_aa(rec.EMPLOYEE_ID) := rec;
EMP_EMAIL_UK_aa(rec.EMAIL) := rec.EMPLOYEE_ID;
end loop;
END load_arrays;
procedure test is
pky_rec HR.EMPLOYEES%ROWTYPE;
EMP_EMAIL_UK_aa_rec HR.EMPLOYEES%ROWTYPE;
begin
for rec in (select * from HR.EMPLOYEES) loop
pky_rec := onerow (rec.EMPLOYEE_ID);
EMP_EMAIL_UK_aa_rec := onerow_by_EMP_EMAIL_UK (rec.EMAIL);
if rec.EMPLOYEE_ID = EMP_EMAIL_UK_aa_rec.EMPLOYEE_ID then
dbms_output.put_line ('EMP_EMAIL_UK lookup OK');
else
dbms_output.put_line ('EMP_EMAIL_UK lookup NOT OK');
end if;
end loop;
end test;
BEGIN
load_arrays;
end EMPLOYEES_cache;
/
which I have run successfully:
HR@XE> @"C:\Documents and Settings\Jason\My Documents\Work\SQL\EMPLOYEES_CACHE.sql"
Package created.
Package body created.
I am now trying to use the functionality within the package.
I have figured out that the section
BEGIN
load_arrays;
end EMPLOYEES_cache;
/
is the initialization section, and my understanding is that this is supposed to run when any of the package variables or functions are referenced. Is that correct?
With that in mind, I'm trying to call the onerow() function, but it's not working:
HR@XE> select onerow(100) from dual;
select onerow(100) from dual
*
ERROR at line 1:
ORA-00904: "ONEROW": invalid identifier
HR@XE> select employees_cache.onerow(100) from dual;
select employees_cache.onerow(100) from dual
*
ERROR at line 1:
ORA-06553: PLS-801: internal error [55018]
HR@XE> select table(employees_cache.onerow(100)) from dual;
select table(employees_cache.onerow(100)) from dual
*
ERROR at line 1:
ORA-00936: missing expression
He provides the code genaa.sp, and a very brief description of what it does, but doesn't tell us how to run the generated code!
Now, I have just done some googling, and it seems that what I am trying to do isn't possible. Apparently %ROWTYPE is PL/SQL, and not understood by SQL, so you can't call onerow() from sql. Correct?
So I try wrapping the call in an exec:
HR@XE> exec select employees_cache.onerow(100) from dual;
BEGIN select employees_cache.onerow(100) from dual; END;
*
ERROR at line 1:
ORA-06550: line 1, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement
HR@XE> exec select table(employees_cache.onerow(100)) from dual;
BEGIN select table(employees_cache.onerow(100)) from dual; END;
*
ERROR at line 1:
ORA-06550: line 1, column 14:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
HR@XE> exec employees_cache.onerow(100)
BEGIN employees_cache.onerow(100); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'ONEROW' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
No joy.
Of course, now that I'm looking at it again, it seems that the way to go is indicated by the first error:
PLS-00428: an INTO clause is expected in this SELECT statement
So am I supposed to create a type of EMPLOYEES%ROWTYPE in a PL/SQL procedure, and the idea of this code, is that the first call to onerow() runs the initialiation code, which populates the cache, and all subsequent calls to onerow() (whether by my session or any other) will use the cache?
I've had a stab at this, but still, no joy:
create or replace procedure testcache is
emp employees%rowtype;
begin
select employees_cache.onerow(100) from dual into emp;
dbms_output.put_line('Emp id: ' || emp.employee_id);
end testcache;
/
show errors
HR@XE> @testcache.sql
Warning: Procedure created with compilation errors.
Errors for PROCEDURE TESTCACHE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/9 PL/SQL: SQL Statement ignored
4/54 PL/SQL: ORA-00933: SQL command not properly ended
HR@XE>
Have a feeling this should be really easy. Can anybody help?
Many thanks in advance.
Jason
Edited by: 942375 on 08-Feb-2013 11:45