Re: proplem writing function in PL/SQL

If what you mean is that MIS.VISIT has several columns with numbers in them and you want to pass e.g 'COLUMNA' to have the function do ;

select max(mv.columna) from mis.visit

then your problem is that you are trying to do dynamic SQL. The structure of normal SQL statements can't be altered on the fly by using variables to change keywords etc. If you want that kind of functionality you have to use dynamic SQL where you build an SQL statement as a string, parse it and execute it manually.

Fortunately ORACLEV7.1 RDBMS has a package called DBMSSQL.SQL (in $ORACLE_HOME/rdbms/admin/dbmssql.sql on most UNIXes) which has functions which allow you to build dynamic SQL statements in PL/SQL. See the header of this file and the documentation on using Dynamic PL/SQL for more details and examples.

Hope this helps.

(As usual these views are my own and not those of my organsisation...) Received on Thu Oct 10 1996 - 00:00:00 CEST