How to use table functions [message #680720] |
Wed, 03 June 2020 10:06 |
|
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 #680723 is a reply to message #680721] |
Wed, 03 June 2020 10:58 |
|
Michel Cadot
Messages: 68665 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.
|
|
|
|
|