From: Harald Maier (maierh@myself.com)
Date: Thu Sep 11 2003 - 17:14:23 CEST
From: Harald Maier <maierh@myself.com> Date: Thu, 11 Sep 2003 17:14:23 +0200 Message-ID: <m3isnzuzlc.fsf@ate.maierh> Subject: Re: [suse-oracle] Dynamic SQL and CAST...MULTISET ???
"Thiele, Elke, fiscus GmbH, Bonn" <E.Thiele@fiscus.info> writes:
> ...
> Hi everybody,
>
> 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
The problem is the *minus* operator. Here a small test case. If you
remove the minus operator and the rest all works fine. Maybe you can
use the not in operator.
,----
| connect scott/tiger;
|
| create type num_tab as table of number(4);
| /
|
| declare
| t_num number(4);
| t_tab num_tab;
| begin
| execute immediate
| 'select empno,
| cast(multiset(
| select deptno from dept
| minus
| select deptno from dept
| where deptno = 60
| ) as num_tab)
| from emp
| where empno = 7499';
| end;
| /
`----
Harald
---------------------------------------------------------------------
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:14:53 CEST