Discussion :

[kisitomomotene]

Salut à tous

Est il possible d'utiliser une fonction PL/SQL dans une requête SQL?

Par exemple est ce que je peux ecrire une requête comme ceci

"select MyFunction(col1,col2) from MyTable" ou quelque chose de semblable, ou MyFunction est une fonction créer par l'instruction "create or replace function MyFunction .."

Merci

[pifor]

Oui, c'est possible à condition de respecter quelques règles:

D'après le Oracle9i Application Developer's Guide - Fundamentals:

To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:

* It must be a stored function, not a function defined within a PL/SQL block or subprogram.
* It must be a row function, not a column (group) function; in other words, it cannot take an entire column of data as its argument.
* All its formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.
* The datatypes of its formal parameters must be Oracle Server internal types, such as CHAR, DATE, or NUMBER, not PL/SQL types, such as BOOLEAN, RECORD, or TABLE.
* Its return type (the datatype of its result value) must be an Oracle Server internal type.

et

Restrictions

When a SQL statement is run, checks are made to see if it is logically embedded within the execution of an already running SQL statement. This occurs if the statement is run from a trigger or from a function that was in turn called from the already running SQL statement. In these cases, further checks occur to determine if the new SQL statement is safe in the specific context.

The following restrictions are enforced:

* A function called from a query or DML statement may not end the current transaction, create or rollback to a savepoint, or ALTER the system or session.
* A function called from a query (SELECT) statement or from a parallelized DML statement may not execute a DML statement or otherwise modify the database.
* A function called from a DML statement may not read or modify the particular table being modified by that DML statement.

These restrictions apply regardless of what mechanism is used to run the SQL statement inside the function or trigger. For example:

* They apply to a SQL statement called from PL/SQL, whether embedded directly in a function or trigger body, run using the new native dynamic mechanism (EXECUTE IMMEDIATE), or run using the DBMS_SQL package.
* They apply to statements embedded in Java with SQLJ syntax or run using JDBC.
* They apply to statements run with OCI using the callback context from within an "external" C function.

You can avoid these restrictions if the execution of the new SQL statement is not logically embedded in the context of the already running statement. PL/SQL's new autonomous transactions provide one escape. Another escape is available using OCI from an external C function, if you create a new connection, rather than using the handle available from the OCIExtProcContext argument.

Lire en détail (avec des exemples) http://download-uk.oracle.com/docs/c...10pck.htm#5980

[kisitomomotene]

Merci
J'ai fais un test avec une simple fonction PL/SQL et ça à marcher.
En fait, j'aimerais pouvoir appeler du code Java à travers une fonction PL/SQL, puis réutiliser cette function dans mes requêtes SQL.

Où puis je trouver des exemples pour m'aider à réaliser cela?