Re: proplem writing function in PL/SQL
Date: 1996/10/03
Message-ID: <5311b8$ove_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <32532204.0_at_news.palm.cri.nz>, mwest_at_hort.cri.nz writes:
|> I have to following PL/SQL function:
|>
|> FUNCTION NewVisitID (field_names IN varchar2)
|> RETURN number
|> IS
|> next_ID number;
|>
|> BEGIN
|> select max(field_names) into next_ID from MIS.VISIT;
|> next_ID := next_ID + 1;
|> return(next_ID);
|> END;
|>
|>
|> I want the function to read in a field_name, run the select statement and
|> return the max +1 number.
|>
|> I think the problem is that the select is trying to get the max of what i
|> put into field_names rather than the max of the column specified in
|> field_names. Can anyone provide a solution?????????
Sounds like you need dynamic SQL. Check out the DBMS_SQL package online in $ORACLE_HOME/rdbms/admin/dbmssql.sql, the Oracle _Application Developer's Guide_, or Chapter 10 of _Oracle PL/SQL Programming_ (info below).
|>
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Thu Oct 03 1996 - 00:00:00 CEST