Dynamic Sql instructions

f97be60a-6d23-4403-9ace-3ce56554d59c

Hi all,

First sorry for my English if there is some mistakes.

In a professional project, I want to give as a parameter to a stored procedure oracle, a list of SQL statements containing SELECT to execute them and get the results.

Example for parameter :
"declare csSelect varchar(4000);
begin
csSelect := 'SELECT * FROM JOURNAL';
if 'A' != '%' then
    csSelect := csSelect || 'WHERE JOURNAL.NOM LIKE ''A%'' ';
end if;
"

I need this for oracle and sqlserver. I found for sqlserver but not for oracle.

Renato Pedroso

What i dont undestand is what exactly you need. IF you want to know how to execute dynamic sql, you use the execute immediate sentence:

declare

   text varchar2(1000);

begin

     text := 'select * from table';

     execute immediate text;

end;

ps: be careful when building dynamic queries to protect against sql injection.

Asterix45

Hello

If it's just to filter and to avoid SQL injections, you can do this :

CREATE PROCEDURE TEST (p_filtre VARCHAR2 DEFAULT NULL)AS BEGIN    SELECT *    FROM TABLE_YOU_WANT    WHERE p_filter IS NULL     OR COLUMN_YOU_WANT like 'p_filtre';END TEST;