From: Thiele, Elke, fiscus GmbH, Bonn (E.Thiele@fiscus.info)
Date: Fri Sep 12 2003 - 15:24:27 CEST
Message-ID: <C17BCB05104A044C8B962C695496BC110313F9@bonn104.bonn.fiscus.de> From: "Thiele, Elke, fiscus GmbH, Bonn" <E.Thiele@fiscus.info> Date: Fri, 12 Sep 2003 15:24:27 +0200 Subject: [suse-oracle] Dynamic SQL and CAST...MULTISET ???
Hi,
thanks for all who've tried to help. I meanwhile found out that my problems
had nothing to do with dynamic sql. It seems to have been a problem with the
version of Oracle that I've used. I am now testing on a
SUSE/Oracle-Environment with the following specifications:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production (instead of
Oracle9i Enterprise Edition Release 9.0.1.3.0)
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
And though I get other errors now, at least it isn't complaining about CAST
... MULTISET anymore.
Once again, thanks for your help! :-)
Elke Thiele, M.A.
System-Analyst
_________________________________________
E-Mail: mailto:E.Thiele@fiscus.info
-----Ursprüngliche Nachricht-----
Von: Thiele, Elke, fiscus GmbH, Bonn
Gesendet: Freitag, 12. September 2003 10:05
An: 'Bennett Leve'
Betreff: AW: WG: [suse-oracle] Dynamic SQL and CAST...MULTISET ???
Hi,
I thought it was SUSE as some of my colleagues are working locally with
Oracle running on SUSE but the main database which I have to use is running
on Sun Solaris 9!
E. Thiele, M.A.
System-Analyst
_________________________________________
E-Mail: mailto:E.Thiele@fiscus.info
-----Ursprüngliche Nachricht-----
Von: Bennett Leve [mailto:bennett.leve@oracle.com]
Gesendet: Donnerstag, 11. September 2003 17:10
An: Thiele, Elke, fiscus GmbH, Bonn
Cc: 'suse-oracle@suse.com'
Betreff: 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 : Fri Sep 12 2003 - 15:25:54 CEST