Dynamic SQL with PL/SQL

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. Received on Tue Feb 29 2000 - 00:00:00 CST