Re: Help with dynamic PL/SQL using dbms_sql

Robert Gauf <75301.2763_at_CompuServe.COM> wrote:
>Tommy
> I'm not so sure about your dynamic SQL error but...
>
>I too thought that a functin would be great, as in
> SELECT fiscal_year_end(date_field) FROM fiscal_info_table;
>but if you look closely in the appropriate manual (I don't
>remember which one) you'll find that it says that you can only
>put user-defined functions into PL/SQL, NOT into plain SQL !!!
>
>If anyone knows a way around this could you help both of us?!
>tia.
>
>Rob
>
>--
>Rob Gauf ph 403-930-6164
>I.S. Research & Technology The Brick Warehouse (Canada)

I didn't see the whole thread on this but I can answer the question

>remember which one) you'll find that it says that you can only
>put user-defined functions into PL/SQL, NOT into plain SQL !!!
>

The following is extracted from the Oracle7, PL/SQL 2.1 and Oracle Precompilers, Release 1.6 Addendum, First Edition, Chapter 2. If you need more info (or you don't havethis addendum but would like this whole chapter which gives you all the details on how to do this) let me know.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

Calling Stored Functions from SQL Expressions

A stored function is a user-defined PL/SQL function created with an Oracle tool and stored in the data dictionary. It is a database object, which can be referenced by any number of applications connected to that database. There are two types of stored functions: packaged and standalone. Packaged functions are defined within a PL/SQL package; standalone functions are defined independently.

With PL/SQL Version 2, calls to stored functions can appear only in procedural statements. With PL/SQL Release 2.1, calls to stored functions can also appear in SQL statements. Now you can call stored functions from the SELECT, VALUES, SET, WHERE, START WITH, GROUP BY, HAVING, and ORDER BY clauses--wherever expressions are allowed in a SQL statement.

That means you can use stored functions as if they were built-in SQL functions such as ROUND, LENGTH, and MONTHS_BETWEEN. For example, you might define a function named miles_between that returns the distance between two cities, then use the function in the WHERE clause of a SELECT statement to find employees who live within a given distance from company headquarters.

By extending SQL in this way, you can do complex data analysis within the Oracle Server; you need not retrieve the data into your application. This increases data independence.

Note: Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, procedures cannot be called directly from SQL expressions.