Re: Dynamic SQL with PL/SQL

Hi.

 In Oracle V7.x and 8.0 you must use DBMS_SQL package ( look at   Application Developer Guide ).
 In 8i there is a new feature called "Native dynamic SQL" - it is  much simplier to use then DBMS_SQL.

 HTH. Michael.

In article <s4Vu4.22101$jb7.534470_at_carnaval.risq.qc.ca>,   "Yves Thibault" <Thibault.Yves_at_tourisme.gouv.qc.ca> wrote:
> Environment: Oracle 7.3 and 8i
> Subject : PL/SQL
>
> I have heard it is possible to execute a dynamic query in PL/SQL
 without
> using the DBMS_EXEC in the PL/SQL function. Is this true? If so,
 here a
> test function that I have written (obviously, it does not work):
>
> CREATE OR REPLACE FUNCTION FUNCTION TEST_SQL
> (p_table IN VARCHAR2,
> p_code IN VARCHAR2,
> p_descr IN VARCHAR2,
> p_search IN VARCHAR2)
> RETURN VARCHAR2 IS
>
> -- *****************
> -- * Body function *
> -- *****************
> retour VARCHAR2(100) := '';
> BEGIN
> -- End of execution if one of the parameter is NULL.
> IF p_table = '' OR
> p_code = '' OR
> p_descr = '' OR
> p_search = '' THEN
> RETURN '';
> END IF;
>
> -- Return the description (p_descr) of the table (p_table)
> -- for the record that matches with "p_code = p_search"
> BEGIN
> SELECT p_descr INTO retour
> FROM p_table
> WHERE p_code = p_search;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> retour := '';
> WHEN OTHERS THEN
> retour := '';
> END;
> RETURN retour;
> END TEST_SQL;
> -- End of function TEST_SQL
> -- ************************
> /
>
> Please note that all the fields that begin with "p_" are parameters.
 How
> can I use them? What should be modified in order for the function to
 work?
>
> We are using tables of codes that are common to one big system. Each
 table
> of codes has the same structure: a code number, a description, a
 beginning
> date and an ending date.
>
> Thank you very much for all your help.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 29 2000 - 00:00:00 CST