Home » SQL & PL/SQL » SQL & PL/SQL » How to use table functions
How to use table functions [message #680720] Wed, 03 June 2020 10:06 Go to next message
Mark70
Messages: 18
Registered: June 2020
Junior Member
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!

Re: How to use table functions [message #680721 is a reply to message #680720] Wed, 03 June 2020 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NAMES_NT is a PL/SQL object & actually unknown to the SQL statement (since it is out of scope).
LOTSA_NAMES can only be used within PL/SQL code.

SQL is a TOTALLY different language & processed by totally different "engine" than PL/SQL.
Re: How to use table functions [message #680723 is a reply to message #680721] Wed, 03 June 2020 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So a solution is to define your SQL table type or use a predefined one, and create a SQL function:
SQL> CREATE OR REPLACE FUNCTION lotsa_names (
  2     base_name_in   IN   VARCHAR2
  3   , count_in       IN   INTEGER
  4  )
  5     RETURN sys.odcivarchar2list
  6  IS
  7     retval sys.odcivarchar2list := sys.odcivarchar2list();
  8  BEGIN
  9     retval.EXTEND ( count_in );
 10     FOR indx IN 1 .. count_in
 11     LOOP
 12        retval ( indx ) := base_name_in || ' ' || indx;
 13     END LOOP;
 14
 15     RETURN retval;
 16  END lotsa_names;
 17  /

Function created.

SQL> SELECT *  FROM TABLE ( lotsa_names ( 'Steven', 5 )) ;
COLUMN_VALUE
---------------------------------------------------------------
Steven 1
Steven 2
Steven 3
Steven 4
Steven 5

5 rows selected.
You could investigate on what Oracle calls pipelined table function which will not use the memory of a real table:
SQL> CREATE OR REPLACE FUNCTION lotsa_names (
  2     base_name_in   IN   VARCHAR2
  3   , count_in       IN   INTEGER
  4  )
  5     RETURN sys.odcivarchar2list pipelined
  6  IS
  7  BEGIN
  8     FOR indx IN 1 .. count_in
  9     LOOP
 10        pipe row (base_name_in || ' ' || indx);
 11     END LOOP;
 12
 13  END lotsa_names;
 14  /

Function created.

SQL> SELECT *  FROM TABLE ( lotsa_names ( 'Steven', 5 )) ;
COLUMN_VALUE
----------------------------------------------------------------
Steven 1
Steven 2
Steven 3
Steven 4
Steven 5

5 rows selected.
Note: Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
Re: How to use table functions [message #680732 is a reply to message #680723] Thu, 04 June 2020 09:30 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Also being discuss on OTN - https://community.oracle.com/thread/4333089
Re: How to use table functions [message #682008 is a reply to message #680732] Fri, 25 September 2020 03:43 Go to previous message
Mark70
Messages: 18
Registered: June 2020
Junior Member
Thank you to everyone!
Previous Topic: How to add SOAP elements to XML
Next Topic: Date format with UTC
Goto Forum:
  


Current Time: Thu Mar 28 13:52:24 CDT 2024