Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: database function

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    bad design, but is a loaded pistol so you can shoot yourself

    EXECUTE IMMEDIATE

    please Read The Fine Manual on command above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2010
    Posts
    3
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2010
    Posts
    3
    'select l_pa.p_col_name from dual'
    is the string and obviously it does not excute

    so is there any better approach

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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.
    Last edited by anacedent; 03-18-10 at 15:28.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •