Link to home
Start Free TrialLog in
Avatar of tonysnow
tonysnow

asked on

PLS-00801: internal error [22914]

I encounter the following error whene using a variable in
the where clause of a table(cast) select.

(1): PLS-00801: internal error [22914]

I have included all pertinent info below.


/******************************/
----create object type
/******************************/
CREATE OR REPLACE
type wpa_staging_type as object
( Entity number,
  Prod_type varchar2(30),
  Prod_rel NUMBER,
  Serial_id number);
/

/******************************/
----create table of object type
/******************************/
CREATE OR REPLACE
type wpa_staging_tab is table of wpa_staging_type;
/

/******************************/
--- package spec
/******************************/
create or replace PACKAGE     TEST_TS_PKG IS

PROCEDURE Test_it;
 temp_var number;
 wp_staging_type wpb_staging_tab := wpb_staging_tab(wpb_staging_type(1,'1',1,1)
                                    ,wpb_staging_type(2,'2',2,2));

END test_ts_pkg; /* End Package Specification test_ts_pkg */
/

/******************************/
-- this works
/******************************/
create or replace PACKAGE BODY     TEST_TS_PKG IS
PROCEDURE Test_it is
 i NUMBER;
BEGIN
  SELECT max(Entity)
  INTO i
   FROM TABLE(CAST(wp_staging_type AS wpb_staging_tab)) WHERE Prod_type=1;
  dbms_output.put_line(substr('Value of i ='||i ,1,255));

   EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END Test_it;  
END test_ts_pkg;


/******************************/
-- this this gets "(1): PLS-00801: internal error [22914]" during compile
/******************************/
create or replace PACKAGE BODY     TEST_TS_PKG IS
PROCEDURE Test_it is
 i NUMBER;
BEGIN
  SELECT max(Entity)
  INTO i
   FROM TABLE(CAST(wp_staging_type AS wpb_staging_tab)) WHERE Prod_type=temp_var;
  dbms_output.put_line(substr('Value of i ='||i ,1,255));

   EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END Test_it;  
END test_ts_pkg;
Avatar of Daniel Stanley
Daniel Stanley
Flag of United States of America image

have you tried moving the temp_var declaration into your procedure body. i'm not sure but i think it may be out of scope in the package specification.

good luck,
dan
Avatar of waynezhu
waynezhu

Just a guess:
initialize or assign a value to temp_var?
Hi,

You need to alias the CASTED TABLE to reference in the WHERE clause:

PROCEDURE Test_it is
 ........
 SELECT max(Entity) INTO i
   FROM TABLE(CAST(wp_staging_type AS wpa_staging_tab)) tab WHERE tab.Prod_type=temp_var;
 ...........

There are a cpl of other typos in yr code, but I guess u made it while pasting the example here. The above works.

Ali
ASKER CERTIFIED SOLUTION
Avatar of smswart
smswart

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial