From: Bennett Leve (bennett.leve@oracle.com)
Date: Thu Sep 11 2003 - 17:10:20 CEST
Message-ID: <3F60905C.6090303@oracle.com> Date: Thu, 11 Sep 2003 11:10:20 -0400 From: Bennett Leve <bennett.leve@oracle.com> Subject: Re: WG: [suse-oracle] Dynamic SQL and CAST...MULTISET ???
Possibly you need to install the objects option. And is this on SUSE?
Saw 64bit in the banner.
-Bennett
Thiele, Elke, fiscus GmbH, Bonn wrote:
>Hi,
>
>I am working with the following Oracle Version:
>
>Oracle9i Enterprise Edition Release 9.0.1.3.0 - 64bit Production
>With the Partitioning option
>JServer Release 9.0.1.3.0 - Production
>
>Greetings,
>
>Elke Thiele, M.A.
>System-Analyst
>_________________________________________
>E-Mail: mailto:E.Thiele@fiscus.info
>
>
>
>-----Ursprüngliche Nachricht-----
>Von: Reidy, Ron [mailto:Ron.Reidy@arraybiopharma.com]
>Gesendet: Donnerstag, 11. September 2003 16:29
>An: Thiele, Elke, fiscus GmbH, Bonn; suse-oracle@suse.com
>Betreff: 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
>
>
>
---------------------------------------------------------------------
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 - 17:07:32 CEST