Re: giving field name as variable of a function

From: Steven Whatley <swhatley_at_blkbox.com>
Date: 2 Jun 1999 15:44:29 GMT
Message-ID: <928338268.901679@news.blkbox.com>

Diablo <diabl_at_email.com> wrote:
: I need to write a function that takes a field name as variable like this:

: Function F_PUR_F05
: ( FieldName IN VARCHAR2)

: cursor C_PER is
: SELECT * FROM sometable

        dbms_sql.column_value(sql_cursor,  1, t_col1);


:     WHERE FieldName ='XXXX';

: .......

Check out the DBMS_SQL package for doing dynamic SQL.

Function F_PUR_F05( FieldName IN VARCHAR2) return whatever is

    sql_cmd    varchar2(2000);
    sql_cursor number;
    rtn_val    number;

    t_col1 varchar2(10);

    t_col2 varchar2(25);
    t_col3 number;
begin

    sql_cmd := 'SELECT col1, col2, col3 FROM sometable WHERE ' ||

                FieldName || '=''XXXX'''; -- Note: no ; in the string     sql_cursor := dbms_sql.open_cursor;

    dbms_sql.parse(sql_cursor, sql_cmd, dbms_sql.v7);
    dbms_sql.define_column(sql_cursor,  1, t_col1, 10);
    dbms_sql.define_column(sql_cursor,  2, t_col2, 25);
    dbms_sql.define_column(sql_cursor,  3, t_col3);

    rtn_val := dbms_sql.execute(sql_cursor);     LOOP

        IF dbms_sql.fetch_rows(sql_cursor) = 0 THEN
             EXIT;
        END IF;
        dbms_sql.column_value(sql_cursor,  1, t_col1);
        dbms_sql.column_value(sql_cursor,  2, t_col2);
        dbms_sql.column_value(sql_cursor,  3, t_col3);
	....

   end loop;
   dbms_sql.close_cursor(sql_cursor);
   ...
end F_PUR_F05;

If you have this in a package you may need to include the progrma:

PRAGMA RESTRICT_REFERENCES (F_PUR_F05, WNDS, WNPS, RNDS, RNPS); if you plan to use the function in another select statement:

select F_PUR_F05(col1) ...

Yes, dynamic SQL can be a pain at times but it is very powerful. One note on DBMS_SQL, the dynamic SQL statements run under the permissions of the owner of the PL/SQL procedure and not who ran it.

Good luck,
Steven
--

                 _|_  |  _|_   "I am the way and the truth and the life.
                  | --|-- |     No one comes to the Father except through
Steven Whatley    |   |   |      me.  If you really knew me, you would
Houston, Texas        |           know my Father as well.  From now on,
swhatley_at_blkbox.com   |            you do know him and have seen him."
http://www.blkbox.com/~swhatley/        -- Jesus Christ (John 14:6-7 NIV)

Received on Wed Jun 02 1999 - 10:44:29 CDT