RE: [suse-oracle] Dynamic SQL and CAST...MULTISET ???

From: Reidy, Ron (Ron.Reidy@arraybiopharma.com)
Date: Thu Sep 11 2003 - 16:28:35 CEST 

Date: Thu, 11 Sep 2003 08:28:35 -0600
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B0CD3E9@fiji.arraybp.com>
From: "Reidy, Ron" <Ron.Reidy@arraybiopharma.com>
Subject: RE: [suse-oracle] Dynamic SQL and CAST...MULTISET ???

What version of Oracle?

-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.
303.386.1480

-----Original Message-----
From: Thiele, Elke, fiscus GmbH, Bonn [mailto:E.Thiele@fiscus.info]
Sent: Thursday, September 11, 2003 5:02 AM
To: 'suse-oracle@suse.com'
Subject: [suse-oracle] Dynamic SQL and CAST...MULTISET ???
Importance: High

Hi everybody,

I hope you can help me! I am trying to find a solution concerning a dynamic
sql string in which I want to use CAST(MULTISET(SELECT ...)). The code is as
follows:

    CREATE TABLE comp_tables ( tab VARCHAR2(50), col VARCHAR2(50), res_comp
VARCHAR2(50) );

    CREATE OR REPLACE TYPE vglType AS OBJECT
      ( vgl_res VARCHAR2(100) )
    /
    
    CREATE OR REPLACE TYPE vglTblType AS TABLE OF vglType
    /

    CREATE OR REPLACE PROCEDURE xxx (v_zkenn_src VARCHAR2, v_zkenn_comp
VARCHAR2) IS
    
     BEGIN
    
        FOR vgl_cur IN (SELECT col_names, tab_names FROM tabColNames_t)
        LOOP

              EXECUTE IMMEDIATE
              'INSERT INTO comp_tables VALUES (''' || vgl_cur.tab_names||
''', ''' || vgl_cur.col_names ||
                          ''', CAST( MULTISET (SELECT ' || vgl_cur.col_names
|| ' FROM '|| v_zkenn_src || '.' || vgl_cur.tab_names ||
                                             ' MINUS SELECT ' ||
vgl_cur.col_names || ' FROM '|| v_zkenn_comp || '.' || vgl_cur.tab_names ||
' )
                           AS vglTblType) )' ;
              COMMIT;
            
        END LOOP;
        
     END xxx;
     /

As you can see, I need to compare, columns of tables in different database
schemas. Together with the result, which can contain multiple rows, I want
to store the name of the column and table into a result table. I found out
that through CAST ... MULTISET it is possible to store a collection of data
into a single row of a table when used in STATIC-SQL. The question is: Can I
use it in DYNAMIC-SQL as well???

When I run the script I get the following error:

ERROR in row 1:
ORA-03001: Function not implemented
ORA-06512: in "ZZ111.XXX", row 8
ORA-06512: in row 1

Any suggestion is more than welcome! Thanks!

E. Thiele, M.A.
System-Analyst
_________________________________________
E-Mail: mailto:E.Thiele@fiscus.info

---------------------------------------------------------------------
To unsubscribe, e-mail: suse-oracle-unsubscribe@suse.com
For additional commands, e-mail: suse-oracle-help@suse.com
Please see http://www.suse.com/oracle/ before posting

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

---------------------------------------------------------------------
To unsubscribe, e-mail: suse-oracle-unsubscribe@suse.com
For additional commands, e-mail: suse-oracle-help@suse.com
Please see http://www.suse.com/oracle/ before posting

This archive was generated by hypermail 2.1.7 : Thu Sep 11 2003 - 16:28:55 CEST