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

From: Thiele, Elke, fiscus GmbH, Bonn (E.Thiele@fiscus.info)
Date: Thu Sep 11 2003 - 13:02:17 CEST 

Message-ID: <C17BCB05104A044C8B962C695496BC110313EA@bonn104.bonn.fiscus.de>
From: "Thiele, Elke, fiscus GmbH, Bonn" <E.Thiele@fiscus.info>
Date: Thu, 11 Sep 2003 13:02:17 +0200
Subject: [suse-oracle] Dynamic SQL and CAST...MULTISET ???

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 archive was generated by hypermail 2.1.7 : Thu Sep 11 2003 - 13:08:27 CEST