calling a function in a select statement in PL/SQL

Question

I'm trying to call a function in a select statement. It didn't work that is way I asking for help.

The following Code is like example and will be run in a Package:

THE FUNCTION:

Function sum_val(val1 in NUMBER, val2 in NUMBER) return float is

    u float;
BEGIN

    u := (val2+ val1) / 100;         

    return u;

END sum_val;

SELECT statement:

PROCEDURE xy_prc(pi_table_in   IN VARCHAR2) IS

BEGIN

sql_stmt := 'CREATE TABLE AS tbx
             SELECT 
            t.*, '|| sum_val(t.val1, t.val2) ||' AS Val3
            FROM '|| pi_table_in ||' t';

EXECUTE IMMEDIATE sql_stmt; 

END;

The problem is here:

'|| sum_val(t.val1, t.val2) ||' AS Val3

In this case I have to declare t.val1, t.val2 and the values of them from the SELECT statement will be not replaced.

This also didn't worked:

'|| sum_val('t.val1', 't.val2') ||' AS Val3

In this case 't.val1', 't.val2' will be values(STRINGS)!

It that possible to call this function with SELECT statement?

Many thanks for your Answers!

Answer 1

Seems to me you're trying too hard :P

can't you just let the function be called at the time the dynamic query executes?

PROCEDURE xy_prc(pi_table_in   IN VARCHAR2) IS

BEGIN

sql_stmt := 'CREATE TABLE  tbx AS
             SELECT t.*
                  , sum_val(t.val1, t.val2) AS Val3
            FROM '|| pi_table_in ||' t';

EXECUTE IMMEDIATE sql_stmt; 

END;

Answer 2

Many Thanks for your Answers!

The solution should to be acutely easy. I can call the function COUNT() from any DDL-Script, function or package. My function "sum_val()" could be only called in my Package.

As Mina said, the function has to be created as following:

create or replace Function...

to be able to called in the SQL-statment like the function COUNT() like Select count(*) from tb or any other function of SQL.

The Problem is, as (create or replace Function) function will be available in the schema and can be called from any package or DDL-Script. I wanted to create a private function, that can be only called from the Package.

My Solution is: the function has to be declared as a public object in the package, like this:

CREATE PACKAGE test AS
   PROCEDURE xy_prc(pi_table_in in varchar2);
   Function sum_val(val1 in NUMBER, val2 in NUMBER) return float;
END test;
/


CREATE OR REPLACE PACKAGE BODY test AS

    Function sum_val(val1 in NUMBER, val2 in NUMBER) return float is

        u float;
    BEGIN

        u := (val2+ val1) / 100;         

        return u;

    END sum_val;

   procedure xy_prc(pi_table_in in varchar2) is
      sql_stmt varchar2(1000);
   BEGIN

      sql_stmt := ' create table tbx as  SELECT 
               t.*, test.sum_val(t.val1, t.val2) as  Val3
               FROM '|| pi_table_in ||' t';

      EXECUTE IMMEDIATE sql_stmt; 

   END xy_prc;

END test;
/

with this way I can call the function test.sum_val(t.val1, t.val2) in any SQL-Statment inside the package.

Answer 3

The problem with your code is that you cannot define function SUM_VAL as a package function if you want to use it in SQL query. It has to be created in the schema.

create or replace Function sum_val(val1 in NUMBER, val2 in NUMBER) return float is

        u float;
    BEGIN

        u := (val2+ val1) / 100;         

        return u;

    END sum_val;

Of course your procedure can be in package. See the corrected syntax below

procedure xy_prc(pi_table_in in varchar2) is
sql_stmt varchar2(1000);
BEGIN

sql_stmt := ' create table tbx as  SELECT 
            t.*, sum_val(t.val1, t.val2) as  Val3
            FROM '|| pi_table_in ||' t';

EXECUTE IMMEDIATE sql_stmt; 

END;