This is what I was looking for! Thanks!

It sounds very useful. But unfortunately when I try
select * from TABLE(virtual_table(5) ); after finishing previous steps in my 8.1.7 server, I am get
select * from TABLE(virtual_table(5) )
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

The type and the function was creted successfully.

try:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from TABLE( cast ( virtual_table(5) as virtual_table_type ) );

COLUMN_VALUE
------------
           1
           2
           3
           4
           5


sorry about that -- forgot the CAST which is needed in 8i, but not 9i 

Hi, Tom,

Greate solution, but chanllenge is: it it possible we build query based on SQL type in the ORACLE report/form?

Somthing like: ---- query in report
select *
from t1
where pk_col in (select * from TABLE(cast(PL_sql_tb as
some_sql_type)));

it seems problem to me when i try it, please also clarify
if it is OK in ORACLE form?

Thanks

Hi, TOm,

Would you spend some valuable time instruct me of
how to construct a query to form a report by
"dynamic sql" using select * from TABLE(CAST....))
in ORACLE REPORT 6.0?

BTW, are there any inpact if i use a view to form a
report instead of a table if the view is fairly
complicated(lot of conditions in where clause, but still based on one or two tables), is this view will be changed when some values in the based tables changed?

Thanks

Hello,

Very interesting feature indeed.

however.....

Was trying to put everything in a package. Does not seem to work!! Thanks for looking into it...

Best Regards
Yogeeraj
====================================================
SQL> @connect yd/yd@mydb
Connected. 
yd@MYDB.CMT.MU> SQL> create or replace type virtual_table_type as table of number;
  2  /

Type created.

yd@MYDB.CMT.MU> create or replace function virtual_table( p_num_rows in number )
return virtual_table_type
is
l_data virtual_table_type := virtual_table_type();
begin
 for i in 1 .. p_num_rows
  loop
  l_data.extend;
   l_data(l_data.count) := i;
  end loop;
 return l_data;
end;
/ 
Function created.

Elapsed: 00:00:00.24
yd@MYDB.CMT.MU>

yd@MYDB.CMT.MU> select * from TABLE( cast ( virtual_table(5) as virtual_table_type ) );

COLUMN_VALUE
____________
           1
           2
           3
           4
           5

5 rows selected.

Elapsed: 00:00:00.05
yd@MYDB.CMT.MU>
---------------------------------------
NOW. I am packaging everything...
---------------------------------------
yd@MYDB.CMT.MU> 
CREATE OR REPLACE PACKAGE test_pkg IS
type virtual_table_type is table of number;
function virtual_table( p_num_rows in number )
   return virtual_table_type;
END;
/
Package created.

Elapsed: 00:00:00.03

yd@MYDB.CMT.MU> 
CREATE OR REPLACE PACKAGE BODY test_pkg IS
function virtual_table( p_num_rows in number )
   return virtual_table_type
   is
     l_data virtual_table_type := virtual_table_type();
 begin
      for i in 1 .. p_num_rows
      loop
                    l_data.extend;
                    l_data(l_data.count) := i;
       end loop;
       return l_data;
 end;
END;
/
Package body created.

Elapsed: 00:00:00.19
yd@MYDB.CMT.MU>                                            
yd@MYDB.CMT.MU> select * from TABLE( cast ( test_pkg.virtual_table(5) as test_pkg.virtual_table_type ) );
select * from TABLE( cast ( test_pkg.virtual_table(5) as test_pkg.virtual_table_type ) )
                     *
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item


Elapsed: 00:00:00.02
yd@MYDB.CMT.MU>

yd@MYDB.CMT.MU> select * from TABLE( cast ( test_pkg.virtual_table(5) as virtual_table_type ) );
select * from TABLE( cast ( test_pkg.virtual_table(5) as virtual_table_type ) )
                                     *
ERROR at line 1:
ORA-00902: invalid datatype

Elapsed: 00:00:00.08
yd@MYDB.CMT.MU>

yd@MYDB.CMT.MU> select * from TABLE( cast ( virtual_table(5) as test_pkg.virtual_table_type ) );
select * from TABLE( cast ( virtual_table(5) as test_pkg.virtual_table_type ) )
                     *
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

Elapsed: 00:00:00.01
yd@MYDB.CMT.MU>
====================================================
 

Hello,

Thank you it works...But i dunno if it makes sense what our Project Leader wants to implement...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
..A PACKAGE to hold all the TYPE definitions per project..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks you again for your time and guidance.

Best Regards
Yogeeraj
======================================================
SQL> @connect yd/yd@mydb
Connected.
yd@MYDB.CMT.MU> CREATE OR REPLACE PACKAGE test_pkg IS
function virtual_table( p_num_rows in number )
   return virtual_table_type;
END;
  /

Package created.

Elapsed: 00:00:00.33
yd@MYDB.CMT.MU> CREATE OR REPLACE PACKAGE BODY test_pkg IS
function virtual_table( p_num_rows in number )
   return virtual_table_type
   is
     l_data virtual_table_type := virtual_table_type();
     begin
      for i in 1 .. p_num_rows
      loop
                    l_data.extend;
                    l_data(l_data.count) := i;
      end loop;
      return l_data;
 end;
END;
/

Package body created.

Elapsed: 00:00:00.18
yd@MYDB.CMT.MU> select * from TABLE( cast ( test_pkg.virtual_table(5) as virtua
l_table_type ) );

COLUMN_VALUE
____________
           1
           2
           3
           4
           5

5 rows selected.

Elapsed: 00:00:00.18
yd@MYDB.CMT.MU>

 

This is very useful for what I am doing now, but how could I pass parameter to function?

The following is the view I am using, and GlSales is PL/SQL function with one parameter runDate,
===
Create View GL_View AS
SELECT
transactiondate
,postingdate
,networknumber
,location
,transactioncode
,transactiontype
,SUM(quantity) quantity
,SUM(dollaramount) dollaramount
,isdropship
,debitorcredit
,itemnumber
,itemvariant1
,itemvariant2
,itemvariant3
FROM
TABLE (CAST(GlSales(runDate) AS GlSalesTableType))
GROUP BY
transactiondate
,postingdate
,networknumber
,location
,transactioncode
,transactiontype
,isdropship
,debitorcredit
,itemnumber
,itemvariant1
,itemvariant2
,itemvariant3;

If I use Func(parm) like above, it gets error
TABLE (CAST(GlSales(runDate) AS GlSalesTableType))
*
ERROR at line 64:
ORA-00904: invalid column name
Thanks.

Hi Tom,

Thanks for the explanation on pipelined functions. What do you mean when you refer to 'suspend' in your response to the original poster? What does suspend mean in this context? Also, can you give us examples of situations where a pipelined function might come in handy? Finally, is there a context switch involved when a pipelined function is called (it looks like there is), or any other potential performance issues? Thanks in advance.

Kashif

the suspend means -- when you call pipe row, you "suspend" your procedure and control can return to the client.

Do this

ops$tkyte@ORA920> create or replace type virtual_table_type as table of number
  2  /
Type created.

ops$tkyte@ORA920> create or replace function virtual_table( p_num_rows in number )
  2  return virtual_table_type
  3  PIPELINED    -- NOTE the pipelined keyword
  4  is
  5  begin
  6      for i in 1 .. p_num_rows
  7      loop<b>
  8          dbms_output.put_line( 'going to pipe' );</b>
  9          pipe row( i );<b>
 10          dbms_output.put_line( 'done pipeing' );</b>
 11      end loop;
 12
 13      return; -- note return with no value!
 14  end;
 15  /
Function created.

ops$tkyte@ORA920> create or replace package my_pkg
  2  as
  3          cursor c is
  4      select * from TABLE( virtual_table(5) );
  5  end;
  6  /
Package created.

ops$tkyte@ORA920> begin
  2          dbms_output.put_line( 'opening' );
  3          open my_pkg.c;
  4          dbms_output.put_line( 'opened' );
  5  end;
  6  /
opening
opened

PL/SQL procedure successfully completed.

<b>Ok, so we opened the cursor that references our function.  Our function did nothing so far -- we've not seen any output from it</b>


ops$tkyte@ORA920> declare
  2          l_n number;
  3  begin
  4          dbms_output.put_line( 'fetching' );
  5          fetch my_pkg.c into l_n;
  6          dbms_output.put_line( 'fetched ' || l_n );
  7  end;
  8  /
fetching
going to pipe
fetched 1

PL/SQL procedure successfully completed.

<b>Ahh, but here our procedure started to run, it printed out going to pipe -- and then suspended itself.  It is in the middle of execution but control obviously returned to the client!</b>


ops$tkyte@ORA920> /
fetching
done pipeing
going to pipe
fetched 2

PL/SQL procedure successfully completed.

<b>and here you can see it picked up again -- done piping AND THEN going to pipe.  It is suspended on the pipe row call...</b>


Where might a pipelined function come in handy?  Use your imagination.  A complex transformation (ETL), getting feedback from a long running procedure as it is running (select * from TABLE( long_running_thing_that_pops_out_messages_as_it_processes) )

anywhere you can think of.


Yes, there is a context switch, nothing for free here. 

I wonder how do you use this method to return multiple columns. Is it possible to return multiple columns (like empono and salary) using table type in Oracle8i way ?


Thanks,

Randy

I asked if it is possible by Oracle8i way. The link show how to implement in using PIPELINE which is was not there prior to 9i. Can you show how you can do it in Oracle8i ?

Hi Tom,

Good technique, but sometimes doesn't work for me. Can you please take a look? My database is 9.2.0.4. Thanks much!

create or replace type virtual_table_type as table of number
/

Type created.

create or replace function virtual_table( p_num_rows in number )
return virtual_table_type
PIPELINED -- NOTE the pipelined keyword
is
begin
for i in 1 .. p_num_rows loop
pipe row( i );
end loop;
return; -- note return with no value!
end;
/

Function created.

drop table test;
create table test as select * from table(virtual_table(5));

select * from test;

COLUMN_VALUE
------------
1
2
3
4
5

create or replace procedure del_test(n number) as
begin
delete from test
where column_value in (select * from table(virtual_table(n)));
end;
/

Procedure created.

exec del_test(3);

BEGIN del_test(3); END;

*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at "SCOTT.DEL_TEST", line 3
ORA-06512: at line 1

ops$tkyte@ORA9IR2> create or replace procedure del_test(n number) as
  2  begin
  3    delete from test
  4      where column_value in (select * from table(cast( virtual_table(n) as virtual_table_type) ));
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> exec del_test(1)
 
PL/SQL procedure successfully completed.
 

Yes, it helps! I am just a little confused because earlier on this page you have mentioned that CAST is not needed in 9i.

Thank you anyway!

Try the following to remove the need for CAST:

ALTER SESSION SET cursor_sharing=exact;

Works for me! (9.2.0.6)

Um the cursor_sharing=EXACT hint from a reader is creative, but does not work as our cursor_sharing is already set to exact. Just in case, I tried it anyway. Still no dice.

In 8i it made my brain boggle that in _compiled_ P code, Oracle was very aware of my scalar nested table collection datatype, and yet a bit further in the code, you had to tell it all over again what datatype it was by CASTing it.

I was relieved to read that oversight was "fixed" in 9i. However, it only seems to be fixed for simple SELECT statements. Is Tom or anyone else aware of whether this is considered a known bug or whether there is a TAR open for it? I have Metalink access but haven't found much on it. Seems that no one cares the more simple TABLE(collection) syntax isn't supported in DML?

-- Test script
SET TERMOUT OFF
DROP TABLE mytest
/
CREATE TABLE mytest(myval VARCHAR2(30))
/
DROP TYPE vc_nt
/
CREATE TYPE vc_nt AS TABLE OF VARCHAR2(30)
/

SET TERMOUT ON

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_arr vc_nt := vc_nt();
l_count INTEGER := 0;
CURSOR cur_search_arr(i_arr IN vc_nt) IS
SELECT t.COLUMN_VALUE tab_nm FROM TABLE(i_arr) t;
BEGIN
-- Test initialization statements here. Other forms of collection initialization
-- have the same effect, be they defined in the declare section above, or
-- EXTENDed and then set by literals here. This bulk collect seems easiest.
SELECT table_name BULK COLLECT INTO l_arr FROM user_tables;

-- Implicit cursor works fine.
FOR lr IN (SELECT t.COLUMN_VALUE tab_nm FROM TABLE(l_arr) t) LOOP
DBMS_OUTPUT.put_line('(Implicit) TableName: ' || lr.tab_nm);
END LOOP;

-- Explicit cursor works fine.
FOR lr IN cur_search_arr(l_arr) LOOP
DBMS_OUTPUT.put_line('(Explicit) TableName: ' || lr.tab_nm);
END LOOP;

-- Another simple SELECT works fine.
SELECT COUNT(*) INTO l_count FROM TABLE(l_arr) t;
DBMS_OUTPUT.put_line('Num rows in l_arr: '||l_count);



-- INSERT with SELECT values clause blows up with ORA-22905
/*
INSERT INTO mytest
SELECT t.COLUMN_VALUE val_nm FROM TABLE(l_arr) t;
*/
INSERT INTO mytest SELECT table_name FROM user_tables; -- substitute

-- UPDATE with equi-join to result of subquery barfs with ORA-22905
/*
UPDATE mytest
SET myval = 'MYTABLE'
WHERE myval = (SELECT t.COLUMN_VALUE tab_nm FROM TABLE(l_arr) t WHERE t.COLUMN_VALUE = 'MYTEST');
*/
UPDATE mytest SET myval = 'MYTABLE' WHERE myval = 'MYTEST'; -- substitute

-- DELETE with IN subquery also fires off a ORA-22905, strike 3. Seems the Oracle developer went home early before fully implementing the fix.
/*
DELETE FROM mytest
WHERE myval IN (SELECT t.COLUMN_VALUE val_nm FROM TABLE(l_arr) t);
*/
DELETE FROM mytest WHERE myval <> 'MYTABLE'; -- substitute

SELECT COUNT(*) INTO l_count FROM mytest;
DBMS_OUTPUT.put_line('Num rows left in mytest (expecting 1): '||l_count);

END;
/

hi tom .
good evening.
i have a small doubt.as follows
i create a procedure by name p100 with one in parameter
and one out parameter based on a table.
my question is can i use the procedure p100 as a table
to create another procedure p200.
awaiting for ur suggestion.

Hi Tom

Your site is like a Bible for Oracle. Now I am working on a project and the most of the code is written in backend (SQL Server). My manager does not want to change the logic (Design) of the code. Is it possible to return a Table values (rows & columns) through a function. Here is the sample code in SQL Server.

create procedure dbo.P_CAPTURE_STAGE_DOCU_ACK(@dtDate datetime)
as
begin

declare @tblDocuAck table
(
DOCUMENT_SNAPSHOT_ID int not null ,
MESSAGE_ID int not null,
ACK_FLAG bit null
)

-- Get all the data from the real-time system
insert @tblDocuAck
( DOCUMENT_SNAPSHOT_ID, MESSAGE_ID, ACK_FLAG )
select DOCUMENT_SNAPSHOT_ID, MESSAGE_ID, ACK_FLAG
from dbo.F_GET_STAGING_DOUC_ACK(@dtDateIn)

update PRIVDWStaging.dbo.Docu_ACK
set MESSAGE_ID = tca.MESSAGE_ID, ACK_FLAG = tca.ACK_FLAG
from PRIVDWStaging.dbo.Docu_ACK ca
inner join @tblDocuAck tca
on ca.DOCUMENT_SNAPSHOT_ID = tca.DOCUMENT_SNAPSHOT_ID

insert PRIVDWStaging.dbo.DOCU_ACK ( DOCUMENT_SNAPSHOT_ID, MESSAGE_ID, ACK_FLAG )
select tca.DOCUMENT_SNAPSHOT_ID, tca.MESSAGE_ID, tca.ACK_FLAG
from @tblDocuAck tca
left join PRIVDWStaging.dbo.DOCU_ACK ca
on ca.DOCUMENT_SNAPSHOT_ID = tca.DOCUMENT_SNAPSHOT_ID
where ca.DOCUMENT_SNAPSHOT_ID is null

end
go


create function dbo.F_GET_STAGING_DOCU_ACK(@dtDateIn datetime)
returns table
as
return (
select DOCUMENT_SNAPSHOT_ID, MESSAGE_ID, ACK_FLAG
from [TMRealTime].Priv.dbo.DOCU_ACK
where MESSAGE_ID in (select MESSAGE_ID
from [TMRealTime].Priv.dbo.DOCU_HISTORY
where MESSAGE_TIME >= @dtDateIn and
MESSAGE_TIME < dateadd(day,1,@dtDateIn)
)
)
go

Hi Tom

I got that information in your site already. I was able to get this far.

create table DB_ROW_COUNT
( COUNT_DATE DATE,
TABLE_NAME VARCHAR2(20),
ROW_COUNT NUMBER(7,0)
)
/

create or replace package types
as
type cursorType is ref cursor;
end;
/

create or replace function F_GET_STAGING_DOCU_ACK
( P_DATE in date )
return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for
select COUNT_DATE, TABLE_NAME, ROW_COUNT
from DB_ROW_COUNT
where trunc(COUNT_DATE) = trunc(P_DATE) ;

return l_cursor;
end F_GET_STAGING_DOCU_ACK ;
/

Variable host_cur refcursor
Call F_GET_STAGING_DOCU_ACK(sysdate) into :host_cur;

Print host_cur

COUNT_DATE TABLE_NAME ROW_COUNT
------------------- -------------------- ----------
06/29/2005 12:28:15 HELLO_TABLE 100


How do you use the result set as a Table (VERTUAL) and do data manipulation from a calling Procedure (P_CAPTURE_STAGE_DOCU_ACK). I am using Oracle 10G. Can you please explain with an example?

Hi Tom

Got your point "but you don't treat a result
set/cursor like a table". This is what SQL Server is doing

insert into #tblDocuAck
( DOCUMENT_SNAPSHOT_ID, MESSAGE_ID, ACK_FLAG )
select DOCUMENT_SNAPSHOT_ID, MESSAGE_ID, ACK_FLAG
from dbo.F_GET_STAGING_DOUC_ACK(@dtDateIn)

I tried the same thing in Oracle, but it errors out.

create or replace procedure P_CAPTURE_STAGE_DOCU_ACK
( P_dtDate in date
,RS_DATA_SET in out types.cursorType)
as
begin
-- Get all the data from the real-time system
-- RS_DATA_SET := F_GET_STAGING_DOCU_ACK( P_dtDate ) ;
insert into STG_DB_ROW_COUNT
select F_GET_STAGING_DOCU_ACK( P_dtDate ) from dual ;
end P_CAPTURE_STAGE_DOCU_ACK ;
/

Errors for PROCEDURE P_CAPTURE_STAGE_DOCU_ACK:

LINE/COL ERROR
-------- -------------------------------------
8/5 PL/SQL: SQL Statement ignored
8/17 PL/SQL: ORA-00947: not enough values

Any idea of how this can be achieved.

FYI - column_value is the default column name for a simple scalar column returning from a pipelined function if the type is defined in a package.

i.e.

select column_value from table(virtual_table(5));

It will get around the ORA-22905 error without having to alter your session (at least in 9205).

I have created a simple pipelined function and trying to call it in another pl/sql block. However I am getting ORA-22905 when I use UNION operator with the pipelined function. The example code is given below. Database version is 9.2.

CREATE OR REPLACE PACKAGE test_package IS
TYPE tbl_number IS TABLE OF NUMBER;

TYPE typ_refcursor IS REF CURSOR;

FUNCTION get_pipelined_table (
some_arg IN NUMBER
)
RETURN tbl_number PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY test_package IS
FUNCTION get_pipelined_table (
some_arg IN NUMBER
)
RETURN tbl_number PIPELINED IS
BEGIN
PIPE ROW (1);
RETURN;
END get_pipelined_table;
END;
/

DECLARE
pcursor test_package.typ_refcursor;
arg NUMBER := 1;
BEGIN
OPEN pcursor
FOR
SELECT 1
FROM TABLE (test_package.get_pipelined_table (arg))
UNION
SELECT 1
FROM TABLE (test_package.get_pipelined_table (arg));
END;
/

I get rid of the error :-

1. By passing 1 into the function instead of variable eliminates the error.
2. If I type cast the table and create the type outside the package.

What I am not able to understand is why the error is eliminated if I pass "1" instrad of argument? Any help would be appreciated.

Thanks

We have bunch of SQL's that run on Oracle 10.2.0.1
that has statement like "select * from TABLE(get_request('95885'))" and runs successfully.

However, when the same SQL's are executed in our client place who also has Oracle 10g fails with the following error.
[Oracle][ODBC][Ora]ORA-22905: cannot access rows from a non-nested table item

Is there any settings in the parameters or anywhere to avoid getting this error? For the time being we have modified the query to use cast and that works fine in their environment.

Thanks,

Cast has fixed it (but the developers had to work on lot of SQL statements by putting the CAST function).

But we want to know why it is happening. Some people are suspecting because the client is running their database on UNIX. I don't think OS would matter here.

The ASP page executes the SQL statement and they have bind variables as part of the parameter to the pl/sql function that is called within a SQL statement.

Has it to do with CURSOR_SHARING parameter? I read in one of the article in oracle-base.com, the user has demonstrated that the CURSOR_SHARING = EXACT can fix this 22905 error.

If the parameter "compatible" is set to older version (like 8.1.7 for example) on a 10g database, can that cause the problem?

On our database, it is set to 10.2.0.1.0.

I know in Oracle 8i and earlier, we have to use CAST function.

Just wondering!!

Apparently I don't have a case where it works on one instance and not on the other. Actually it doesn't work in our customer place and works perfectly in our instance.

For my other question regarding values for "compatible" parameter, does it make sense if they have a older version value?

the same piece of code works on our server but not in the customer place. Actually we get dmp file from customer and load it on our servers in order to have some live data for future application enhancements.

the code we have and the customer has are exactly same. on both of our instances cursor_sharing was EXACT.

Thanks,

NO Problem of specifying a query as follow in Oracle Report 6i.

SELECT *
FROM TABLE (
REPORTBV344(
:CP_2,
'020301',
'2006',
'01/01/2007',
'15/01/2007')
)

GOT Problem of specifying a query as follow in Oracle Report 6i.
SELECT *
FROM TABLE (
REPORTBV344(
:PF_1,
'020301',
'2006',
'01/01/2007',
'15/01/2007')
)

ORA-22905: cannot access rows from a non-nested table item FROM ==> Table (

Anyway, how can i get field value from parameter form (unbind input field where source is NULL). If i can get the value from those input field, i can overcome the above problem.

Thanks,
A newbie of Oracle (just started 1 week).
Ting.

How is it possible for the ora-22905 error to occur when compiling a package body ?
The package ,amongst other things, declares a pipelined function and also declares local collection (table) of the '%ROWTYPE' kinds.
Calls to the pipelined function start to fail randomly or so it seems, with the error 'ORA-06553: PLS-752: Table function S_VALIDATE is in an inconsistent state'.
Compiling just the body of the package produces the 22905 error.
Compiling spec & body makes the errors go away.
Any insight on this would be appreciated.

Sorry, missed mentioning that the Oracle version is 10.2.0.3 .

Problem was caused by 'someone' having dropped the 'SYS_xxx' Types created for the return type of the table-function.
Compiling the package spec. recreates these 'SYS_xxxx' Types.

This is as short as I could make it , Tom.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table va as select * from dba_objects where rownum<10;

Table created.

SQL> CREATE OR REPLACE package ugh as
2 TYPE kurser IS REF CURSOR RETURN va%ROWTYPE;
3 TYPE tab IS TABLE OF va%ROWTYPE;
4 FUNCTION ugh_ugh (p1 IN kurser)
5 RETURN tab
6 PIPELINED;
7 end;
8 /

Package created.

SQL> col object_name format a35
SQL> /

OBJECT_NAME OBJECT_TYPE OBJECT_ID
----------------------------------- ------------------- ----------
VA TABLE 88385
UGH PACKAGE 88240

REM - The automagically created types !

SQL> select object_name,object_type
2 from obj a
3 ,(select object_id from dba_objects where object_name in ('VA','UGH') ) b
4 where object_name like 'SYS_PLSQL_'||b.object_id || '%';

OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
SYS_PLSQL_88385_DUMMY_1 TYPE
SYS_PLSQL_88385_97_1 TYPE
SYS_PLSQL_88240_DUMMY_2 TYPE
SYS_PLSQL_88240_15_2 TYPE

SQL> desc SYS_PLSQL_88385_97_1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> desc SYS_PLSQL_88240_DUMMY_2
SYS_PLSQL_88240_DUMMY_2 TABLE OF NUMBER

SQL> desc SYS_PLSQL_88240_15_2
SYS_PLSQL_88240_15_2 TABLE OF SYS_PLSQL_88385_97_1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> CREATE OR REPLACE package body ugh as
2 cnt number;
3 FUNCTION ugh_ugh (p1 IN kurser)
4 RETURN tab
5 PIPELINED is
6 rec va%rowtype;
7 begin
8 loop
9 fetch p1 into rec;
10 exit when p1%notfound;
11 pipe row( rec);
12 end loop;
13 end;
14 begin
15 select count(*) into cnt
16 from table (ugh.ugh_ugh(cursor(select a.* from dba_objects a where owner=user) ));
17 end;
18 /

Package body created.

SQL> select * from table (ugh.ugh_ugh(cursor(select a.* from dba_objects a where rownum <3) ));

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
------------------------------ ----------------------------------- ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - -
SYS CON$ 28 28 TABLE 19-FEB-07 19-FEB-07 2007-02-19:13:36:54 VALID N N N
SYS I_COL2 46 46 INDEX 19-FEB-07 19-FEB-07 2007-02-19:13:36:54 VALID N N N

SQL> Rem Now drop one of the types create above (I'll drop the collection SYS_PLSQL_88240_15_2 )
SQL>
SQL> Drop type SYS_PLSQL_88240_15_2;

Type dropped.

SQL> select * from table (ugh.ugh_ugh(cursor(select a.* from dba_objects a where rownum <3) ));
select * from table (ugh.ugh_ugh(cursor(select a.* from dba_objects a where rownum <3) ))
*
ERROR at line 1:
ORA-06553: PLS-752: Table function UGH_UGH is in an inconsistent state.


SQL> alter package ugh compile body;

Warning: Package Body altered with compilation errors.

SQL> show err
Errors for PACKAGE BODY UGH:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/1 PL/SQL: SQL Statement ignored
16/6 PL/SQL: ORA-22905: cannot access rows from a non-nested table
item

16/17 PLS-00642: local collection types not allowed in SQL statements
SQL>
SQL> alter package ugh compile;

Package altered.

SQL> select * from table (ugh.ugh_ugh(cursor(select a.* from dba_objects a where rownum <3) ));

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
------------------------------ ----------------------------------- ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - -
SYS CON$ 28 28 TABLE 19-FEB-07 19-FEB-07 2007-02-19:13:36:54 VALID N N N
SYS I_COL2 46 46 INDEX 19-FEB-07 19-FEB-07 2007-02-19:13:36:54 VALID N N N

SQL> spool off

Is there any standard guidelines when to use function and when procedure

Hi Sir
can we create derived tables using stored procedure
need your help.
Thanks

Tom,

I am using Oracle 11.2. I have created two user defined functions and use them when creating a
view. But i always get a error table or view does not exist on the function call.

My function:
create or replace
function CZF_IS_NUMERIC (p_string varchar2)
return NUMBER AUTHID DEFINER
as
l_number number;
begin
l_number := p_string;
return 1;
exception
when others then
return 0;
end;

View: The view is really big...The part where i get error is below

create view RDC_wz.wz_observation_fact_view as
(select
a.etl_source_id,
a.etl_date,
a.data_source_id
from rdc_wz.wz_svc_allergy a
join rdc_wz.wz_service s
on a.service_id = s.service_id
and s.master_service_id is not null
union all
select p.svc_procedure_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_' ||
CASE WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
CASE
WHEN length(RDC_CZ.STRIPCHARACTERS(p.proc_code, '[^0-9]')) = 5 THEN 'CPT'
WHEN length(RDC_CZ.STRIPCHARACTERS(p.proc_code, '[^0-9]')) in (3,4) THEN 'ICD-9'
ELSE NULL
END
WHEN RDC_CZ.CZF_IS_NUMERIC(RDC_CZ.STRIPCHARACTERS(p.proc_code, '^A-Z') = 0 THEN
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN NULL
ELSE NULL
END
END
and s.master_service_id is not null
);

ERROR:
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
*
ERROR at line 54:
ORA-00942: table or view does not exist

The view might look wrong as i have just put some bits of the file but the view is correct and runs in sqlserver. It has been converted to oracle from that and the oracle functions have been used.

RDC_CZ is the schema under which the functions have been created. Execute and debug has been
granted to PUBLIC. Therefore i dont think there are any privilege issues.

Could you please guide me on this as to why am i getting a Table or view does not exist on a
FUNCTION call.

Thanks for all the help!