Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use table functions

User_RFKSXJun 3 2020 — edited Jun 4 2020

Hi,

I was trying to use tabel functions but after creating this block:

create table tmp_ft

(field   VARCHAR2 ( 1000 ));

DECLARE

TYPE names_nt IS TABLE OF VARCHAR2 ( 1000 );

FUNCTION lotsa_names ( 

   base_name_in   IN   VARCHAR2 

, count_in       IN   INTEGER 

   RETURN names_nt 

IS 

   retval names_nt := names_nt ( ); 

BEGIN 

   retval.EXTEND ( count_in ); 

 

   FOR indx IN 1 .. count_in 

   LOOP 

      retval ( indx ) := base_name_in || ' ' || indx; 

   END LOOP; 

 

   RETURN retval; 

END lotsa_names;

BEGIN

insert into tmp_ft

SELECT *

FROM TABLE ( lotsa_names ( 'Steven', 5 )) ;

END;

/

I get the following error:

ORA-06550: line 26, column 15:

PLS-00231: function 'LOTSA_NAMES' may not be used in SQL

Why?

Thanks!

This post has been answered by mathguy on Jun 4 2020
Jump to Answer

Comments

Processing

Post Details

Added on Jun 3 2020
6 comments
487 views