You Asked
Hi TOM,
This is in continum to my previous Question.
OK Dynamic Qurying is Not Possible For ORACLE 8.0.3/D2K(2.1)
But as an alternative I've Created a Package on Database as Follows.
package emppkg is
type emprec is record (EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
type emptab is table of emprec index by binary_integer;
empcount number := 0;
emprecord emprec;
emptable emptab;
procedure fill_emptab;
end;
package body emppkg is
procedure fill_emptab is
c integer;
rows number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c,'Select * from emp',1);
dbms_sql.define_column(c,1,emprecord.empno);
dbms_sql.define_column(c,2,emprecord.ename,100);
dbms_sql.define_column(c,3,emprecord.job,100);
dbms_sql.define_column(c,4,emprecord.mgr);
dbms_sql.define_column(c,5,emprecord.HIREDATE);
dbms_sql.define_column(c,6,emprecord.SAL);
dbms_sql.define_column(c,7,emprecord.COMM);
dbms_sql.define_column(c,8,emprecord.DEPTNO);
rows := dbms_sql.execute(c);
empcount := 0;
while dbms_sql.fetch_rows(c) > 0 loop
empcount := empcount + 1;
dbms_sql.column_value(c,1,emptable(empcount).empno);
dbms_sql.column_value(c,2,emptable(empcount).ename);
dbms_sql.column_value(c,3,emptable(empcount).job);
dbms_sql.column_value(c,4,emptable(empcount).mgr);
dbms_sql.column_value(c,5,emptable(empcount).HIREDATE);
dbms_sql.column_value(c,6,emptable(empcount).SAL);
dbms_sql.column_value(c,7,emptable(empcount).COMM);
dbms_sql.column_value(c,8,emptable(empcount).DEPTNO);
end loop;
dbms_sql.close_cursor(c);
exception
when others then
dbms_sql.close_cursor(c);
end fill_emptab;
end ;
Now From forms(5.0) I'm Trying to Access this Package with Following Command
declare
cnt Number;
begin
emppkg.fill_emptab;
cnt := emppkg.empcount ;
...
...
end;
While Compiling above statement I'm getting following error
Implementation Restriction : 'EMPPKG.EMPCOUNT' Cannot directly access remote Package Variable or Cursor;
Can you Give me some Cause n Solution, Please.
Prakash.
and Tom said...
Well, in order to fix the above you would simply write a FUNCTION "get_empcount" that returned empcount. As the error message states -- you cannot directly access remote package variables or cursors -- you must write a function (get/set functions) to get and set the values in these remote variables.
But wait -- there is a much better, more efficient, easier method to do what you are trying to do above. Previously you asked me how to dynamically open a ref cursor -- I told you you cannot until you get Oracle8i, release 8.1 and up. Now I see you want to get a table of records -- we can do that in forms in a much easier way. We'll use a RECORD_GROUP. It would look like this:
DECLARE
rg_name VARCHAR2(40) := 'EmpDemo';
rg_id RecordGroup;
errcode NUMBER;
BEGIN
/*
** Make sure group doesn't already exist
*/
rg_id := Find_Group( rg_name );
/*
** If it does not exist, create it
*/
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query( rg_name,
'SELECT * from emp' );
END IF;
/*
** Populate the record group
*/
errcode := Populate_Group( rg_id );
Message( 'There are ' || get_group_row_count( rg_id ) ||
' rows in the result' );
for i in 1 .. get_group_row_count( rg_id )
loop
Message( 'Row ' || i || ' has deptno = ' ||
get_group_number_cell( 'EmpDemo.DEPTNO', i ) );
end loop;
END;
Record Groups will be much more efficient then using DBMS_SQL. Record groups will result in many less round trip calls to the server and use array fetching to fill themselves up. You can create a record group from any arbitrary query. They work much like a 2 dimensional array with rows and columns, sort of like a "result set" object.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment