database function

vinodkumarn · #1 (**permalink**) 03-18-10, 12:40

I have a function as below, which is working fine.

this function excutes a package to get all the column values for a given aid from procurementaction table and is stored in a variable l_pa
i select one of the column like awardnbr and display the value of it

my requirenment is in "l_pa.awardnbr", i want function to take the column name from the input parameter, instead of hardcoding the column name
meaning it should say something like "select l_pa.p_col_name from dual"

how do i do this

create or replace function get_value(p_aid in number, p_col_name in varchar2) return varchar2 is
l_pa procurementaction%ROWTYPE;
b VARCHAR2(500);
begin
l_pa := ina_pkg.get_pa(p_aid);

select l_pa.awardnbr into b from dual;

return b;
end;

Thanks

anacedent · #2 (**permalink**) 03-18-10, 13:00

bad design, but is a loaded pistol so you can shoot yourself

EXECUTE IMMEDIATE

please Read The Fine Manual on command above

vinodkumarn · #3 (**permalink**) 03-18-10, 13:13

i did create function as folllows

create or replace function get_value
(p_aid in number
,p_col_name in varchar2)
return varchar2 is
l_pa procurementaction%rowtype;
b varchar2(500);
begin
l_pa := ina_pkg.get_pa(p_aid);
execute immediate 'select l_pa.'||p_col_name||' from dual' into b;
return b;
end;

but when try to execute the function by following i get error

select get_value(19913,'awardnbr') from dual

ora-00904 lpa.awardnbr invalid identifier

anacedent · #4 (**permalink**) 03-18-10, 13:30

When using EXECUTE IMMEDIATE it is a Best Practice to construct into a single string variable the complete & valid SQL statement prior to passing it to EXECUTE IMMEDIATE.

use DBMS_OUTPUT.PUT_LINE to display the SQL so it can be manually validated in sqlplus or other client.

vinodkumarn · #5 (**permalink**) 03-18-10, 14:06

'select l_pa.p_col_name from dual'
is the string and obviously it does not excute

so is there any better approach

anacedent · #6 (**permalink**) 03-18-10, 14:14

>I have a function as below, which is working fine.
>is the string and obviously it does not execute

Please make up your/our mind.

>so is there any better approach
I don't have your tables.
I don't have your data.
I don't have your functions or procedures.
I don't understand your exact requirements.

using sqlplus along with CUT & PASTE post results showing whole session where code functions as desired utilizing a single hard coded value as a starting point.

MCrowley · #7 (**permalink**) 03-18-10, 14:46

Quote:

ora-00904 lpa.awardnbr invalid identifier

Where is the underscore in the name of the table? Is the table named l_pa, or lpa?

EDIT: Fixed quote tag

Littlefoot · #8 (**permalink**) 03-18-10, 16:42

In my opinion, there's one (packaged) function too many - you don't need it as everything can be done right here.

Here's an example based on Scott's schema. First, create a function which accepts employee number (EMPNO) and column name as parameters and returns that column's value.

Code:

SQL> create or replace function get_value
  2    (par_empno in number, par_col_name in char)
  3    return char
  4  is
  5    l_str     varchar2(500);
  6    retval    varchar2(30);
  7  begin
  8    l_str := 'select ' || par_col_name || ' from emp where empno = ' || par_empno;
  9    execute immediate (l_str) into retval;
 10    return (retval);
 11  end;
 12  /

Function created.

SQL>

A few examples:

Code:

SQL> select get_value(7369, 'ENAME') result from dual;

RESULT
--------------------------------------------------------------------------------------
SMITH

SQL> select get_value(7369, 'SAL') result from dual;

RESULT
--------------------------------------------------------------------------------------
800

SQL> select get_value(7369, 'HIREDATE') result from dual;

RESULT
--------------------------------------------------------------------------------------
17.12.1980

SQL>

See? You don't have to select the whole row in order to get just a part of it.

Internet Services

Web Development

Digital Marketing

Consumer Tech