How to use table functions

User_RFKSX

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!

mathguy

Nice try.

You defined a function locally, in an anonymous block; and you tried to use it still within the same block, hoping that this will work: the function is used within the same anonymous block, so that should work, even if the function is not known to the outside world. Unfortunately, as odie pointed out already, SQL is different from PL/SQL. When you run SQL statements, even from within a PL/SQL block, the SQL engine doesn't have a concept of "who called me"; it doesn't recognize, much less honor, the PL/SQL "scope" it was invoked from. A SQL statement, no matter how it was initiated, will look for "global" functions - either defined on their own with a CREATE FUNCTION statement or as part of a package. The local function you defined is not visible to the SQL statement, because that is not how SQL works.

It would be good to understand WHY you wanted to do this. One common reason given by other posters with similar questions is that they don't have the privileges needed to create functions at the schema level, or for some other reason they simply can't or don't want to create such "permanent" functions. If that is your reason, perhaps you have similar restrictions against creating types (like NAMES_NT) at the schema level, too.

Since Oracle version 12.1 there is a solution to that kind of problem. (Do you understand, then, why we always, always ask posters to tell us their Oracle version - even if they may not understand very well why that matters?)

It goes the other way around though. You can create functions locally in a SQL statement (most often in SELECT, but also in INSERT and other DML). Also, the type returned by a function needs to be declared externally to that function; and you can't declare types locally to a SQL statement. But you don't really need your user-defined type; you can use an Oracle predefined type instead. SYS.ODCIVARCHAR2LIST will work.

You can do something like this. Note a couple of syntax details: (1) If you use a function in the WITH clause in a nested statement (here: in a SELECT statement nested within INSERT), then you must use the /*+ with_plsql */ hint;  (2) The function definition is NOT terminated by a slash; (3) Instead, the whole SQL INSERT statement must be terminated by a slash.

create table tmp_ft (field varchar2(1000));Table TMP_FT created.insert /*+ with_plsql */ into tmp_ft (field)  with    function lotsa_names (      base_name_in in varchar2    , count_in     in integer    )    return sys.odcivarchar2list    is      retval sys.odcivarchar2list := sys.odcivarchar2list( );    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;select * from table(lotsa_names('Scott', 5))/5 rows inserted.select * from tmp_ft;FIELD --------Scott 1Scott 2Scott 3Scott 4Scott 5

As you can see, everything is local to the INSERT statement - you didn't need to create anything outside it. As soon as the statement completes, the function is gone; and there is no user-defined type to worry about either, since this approach uses an already-available collection type.

odie_63

4050852 wrote:I get the following error:
ORA-06550: line 26, column 15:PLS-00231: function 'LOTSA_NAMES' may not be used in SQLWhy?

The function must be declared at schema level, either standalone or in a package, but not in an anonymous PL/SQL block.

mathguy

Nice try.

You defined a function locally, in an anonymous block; and you tried to use it still within the same block, hoping that this will work: the function is used within the same anonymous block, so that should work, even if the function is not known to the outside world. Unfortunately, as odie pointed out already, SQL is different from PL/SQL. When you run SQL statements, even from within a PL/SQL block, the SQL engine doesn't have a concept of "who called me"; it doesn't recognize, much less honor, the PL/SQL "scope" it was invoked from. A SQL statement, no matter how it was initiated, will look for "global" functions - either defined on their own with a CREATE FUNCTION statement or as part of a package. The local function you defined is not visible to the SQL statement, because that is not how SQL works.

It would be good to understand WHY you wanted to do this. One common reason given by other posters with similar questions is that they don't have the privileges needed to create functions at the schema level, or for some other reason they simply can't or don't want to create such "permanent" functions. If that is your reason, perhaps you have similar restrictions against creating types (like NAMES_NT) at the schema level, too.

Since Oracle version 12.1 there is a solution to that kind of problem. (Do you understand, then, why we always, always ask posters to tell us their Oracle version - even if they may not understand very well why that matters?)

It goes the other way around though. You can create functions locally in a SQL statement (most often in SELECT, but also in INSERT and other DML). Also, the type returned by a function needs to be declared externally to that function; and you can't declare types locally to a SQL statement. But you don't really need your user-defined type; you can use an Oracle predefined type instead. SYS.ODCIVARCHAR2LIST will work.

You can do something like this. Note a couple of syntax details: (1) If you use a function in the WITH clause in a nested statement (here: in a SELECT statement nested within INSERT), then you must use the /*+ with_plsql */ hint;  (2) The function definition is NOT terminated by a slash; (3) Instead, the whole SQL INSERT statement must be terminated by a slash.

create table tmp_ft (field varchar2(1000));Table TMP_FT created.insert /*+ with_plsql */ into tmp_ft (field)  with    function lotsa_names (      base_name_in in varchar2    , count_in     in integer    )    return sys.odcivarchar2list    is      retval sys.odcivarchar2list := sys.odcivarchar2list( );    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;select * from table(lotsa_names('Scott', 5))/5 rows inserted.select * from tmp_ft;FIELD --------Scott 1Scott 2Scott 3Scott 4Scott 5

As you can see, everything is local to the INSERT statement - you didn't need to create anything outside it. As soon as the statement completes, the function is gone; and there is no user-defined type to worry about either, since this approach uses an already-available collection type.

Solomon Yakobson

BTW, there is, IMHO, a bug in Oracle error messages that can force people into making wrong conclusions. If someone would try CTE with pipelined function first they would get:

ORA-06553: PLS-653: aggregate/table functions are not allowed in PL/SQL scope

and possibly make wrong conclusion CTE doesn't support table functions .

SY.

EdStevens

Also being discuss on OraFAQ

User_RFKSX

Thank you very much Mathguy,

I have one more simple question.

I didn't know about Oracle predefined type like SYS.ODCIVARCHAR2LIST. But if I have to define complex types, I must have the privileges needed to create them, mustn't I? For example if I try to create one like

CREATE TYPE mytype IS OBJECT (

   name   VARCHAR2 (60),

   sirname  VARCHAR2 (100));

I get

ORA-01031: insufficient privileges

mathguy

Yes, that is correct. As I said, a common reason for people wanting to do things like what you were trying to do is exactly that: they lack the privileges to create "things", like types and functions, at the schema level.

If you need to create complex types in your work, you need to explain this need to your DBA, so that they will grant the required privileges to you. If they ask you to drive the bus, they shouldn't tell you "and by the way, we can't give you any fuel" - that makes no sense. Not unheard of, though, unfortunately - not by a long shot. (On the other hand, if you are just learning and you are your own DBA, this is good practice - it forces you to understand the process, and specifically which grants are needed for what, etc.)