Re: cast question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Feb 1999 14:29:08 GMT
Message-ID: <36c2515c.4411212@192.86.155.100>

A copy of this was sent to "Ed Zappulla" <zappullae_at_rcn.com> (if that email address didn't require changing) On Fri, 5 Feb 1999 08:10:21 -0500, you wrote:

>Can anyone tell me what is wrong with the following code? note vendor is a
>table with a key vend_id numbe
>
>SQLWKS> declare
> 2> type x is table of number;
> 3> z x;
> 4>
> 5> y number := 0;
> 6> begin
> 7> z := x();
> 8> z.extend();
> 9> z(1) := 1;
> 10>
> 11> select vend_id into y from THE(select cast(z as x) from dual) a;
> 12>
> 13> end;
> 14>
> select vend_id into y from THE(select cast(z as x) from dual) a;
> *
>ORA-06550: line 11, column 50:
>PLS-00382: expression is of wrong type
>ORA-06550: line 11, column 2:
>PL/SQL: SQL Statement ignored
>
>

it is because the type X is only known to pl/sql -- not to SQL. the cast is failing since (z as x) isn't understood by sql (the type X is not known).

The way to do this is as follows:

SQL> REM instead of putting a type in a spec, do this:
SQL> 
SQL> create or replace type myTableType as table of number;

  2 /
Type created.

SQL> REM here is an example of selecting from a 'static' dynamic table. SQL> declare
  2 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   3
  3 begin

  4          for x in ( select sum( a.column_value ) val
  5                                   from THE ( select cast( l_x as mytableType
) from dual ) a
  6                                   )
  7          loop
  8                  dbms_output.put_line( x.val );
  9          end loop;

 10 end;
 11 /

45
PL/SQL procedure successfully completed.

here, myTableType is known to sql and can be selected from  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Feb 06 1999 - 08:29:08 CST