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_stagin g_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(subst r('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(subst r('Value of i ='||i ,1,255));
EXCEPTION
WHEN OTHERS THEN
RAISE;
END Test_it;
END test_ts_pkg;
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_stagin
,wpb_staging_type(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
dbms_output.put_line(subst
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
dbms_output.put_line(subst
EXCEPTION
WHEN OTHERS THEN
RAISE;
END Test_it;
END test_ts_pkg;
Just a guess:
initialize or assign a value to temp_var?
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
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
...........
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
good luck,
dan