Strange error on SELECT from TABLE CAST

From: <Mark.Wright_at_bristol.ac.uk>
Date: Tue, 8 Feb 2005 16:29:55 GMT
Message-ID: <IBLptv.7Ap@bath.ac.uk>

I cant seem to use SUBSTR() when I'm selecting from a CAST table object. E.g.

---

SET SERVEROUTPUT ON
CREATE OR REPLACE TYPE id_row AS OBJECT (id VARCHAR2(10));
/

CREATE OR REPLACE TYPE id_tab AS TABLE OF id_row;
/

DECLARE
    id_list id_tab := id_tab();
    v_str VARCHAR2(10);
BEGIN

    id_list.EXTEND;
    id_list(1) := id_row(NULL);
    id_list(1).id := 'ABCDEFG';

    SELECT id INTO v_str
-- SELECT SUBSTR(id,1,2) INTO v_str

    FROM TABLE(CAST(id_list AS id_tab));

    dbms_output.put_line('id = '||v_str); END;
/

---

This works fine and displays "id = ABCDEFG". However, uncomment the SUBSTR line and comment the other, and then I get:

    SELECT SUBSTR(id,1,2) INTO v_str
    *
ERROR at line 10:

ORA-06550: line 10, column 5:
PLS-00801: internal error [22914]
ORA-06550: line 10, column 5:

PL/SQL: SQL Statement ignored

Why cant I use SUBSTR()? I have experimented and seem to be able to use SUM(), but not TO_NUMBER()! This is very irritating, as I need to use SUBSTR() on the column I am selecting...

SQL> select * from v$version;
BANNER

---

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production

Any help appreciated,
Cheers,
Mark

-- 

Received on Tue Feb 08 2005 - 10:29:55 CST