Re: SELECT FROM TABLE in PL/SQL procedure

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 6 Feb 2003 15:22:51 +0300
Message-ID: <b1tk5m$9fa$1@babylon.agtel.net>

I think that by 'stalling' OP means that the session just hangs with no errors or dumps (infinite loop?) "Bad situation" of course means that this is undesirable.

I've experienced problems with nested tables and bulk-collecting from table(cast(a as nt_type)), too. Here's my favorite example:

Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production                      
PL/SQL Release 8.1.7.4.0 - Production                                           
CORE 8.1.7.2.1 Production                                                       
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production                          
NLSRTL Version 3.4.1.0.0 - Production                                           

SQL> CREATE OR REPLACE TYPE T1 AS TABLE OF NUMBER(10,0)   2 /

Type created.

SQL> CREATE OR REPLACE TYPE T2 AS TABLE OF VARCHAR2(100)   2 /

Type created.

SQL> CREATE TABLE T (ID NUMBER(10,0) PRIMARY KEY, NAME VARCHAR2(100)); Table created.

SQL> INSERT INTO T VALUES(1,'NAME1'); 1 row created.

SQL> INSERT INTO T VALUES(2,'NAME2'); 1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT ID
  2 FROM T
  3 WHERE NAME IN
  4 (SELECT UPPER(COLUMN_VALUE) FROM
  5 TABLE(CAST( T2('name1','name2') AS T2 ))   6 )
  7 /

        ID                                                                      
----------                                                                      
         1                                                                      
         2                                                                      

SQL> CREATE OR REPLACE FUNCTION MAPNAMES( T2INST IN T2 ) RETURN T1   2 AS
  3 T1INST T1;
  4 BEGIN
  5 SELECT ID
  6 BULK COLLECT INTO T1INST
  7 FROM T
  8 WHERE NAME IN
  9 (SELECT UPPER(COLUMN_VALUE) FROM

 10  --     THE( SELECT CAST( T2INST AS T2 ) FROM SYS.DUAL)
 11       TABLE(CAST( T2INST AS T2 ))

 12 );
 13 RETURN T1INST;
 14 END;
 15 /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION MAPNAMES:

LINE/COL ERROR                                                                  
-------- -----------------------------------------------------------------      
5/3      PLS-00801: internal error [22914]                                      
5/3      PL/SQL: SQL Statement ignored                                          

By commenting line 11 and uncommenting line 10 I was able to get around this compiler bug, but the workaround is obviously not optimal. Afaik it's still not fixed in 8i (and probably won't be.) In 9i this bug does not reproduce.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:b1tfvp$dc3$1_at_ctb-nnrp2.saix.net...


> Sergey Balter wrote:

> 

> > a session SOMETIMES stalls. It happens not very

> > often, but unpredictable..

> 

> Keep your airspeed up. Well, it works when flying aircraft.

> 

> What do you mean by "bad situation" and session "stalls"? Do you get an ORA 

> or PL/SQL error? Is performance slow? Errors in the alert log?

> 

> --

> Billy

Received on Thu Feb 06 2003 - 06:22:51 CST