From: Thiele, Elke, fiscus GmbH, Bonn (E.Thiele@fiscus.info)
Date: Thu Sep 11 2003 - 16:34:42 CEST
Message-ID: <C17BCB05104A044C8B962C695496BC110313EF@bonn104.bonn.fiscus.de> From: "Thiele, Elke, fiscus GmbH, Bonn" <E.Thiele@fiscus.info> Date: Thu, 11 Sep 2003 16:34:42 +0200 Subject: WG: [suse-oracle] Dynamic SQL and CAST...MULTISET ???
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
This archive was generated by hypermail 2.1.7 : Thu Sep 11 2003 - 16:34:59 CEST