Forum Stats

  • 3,769,580 Users
  • 2,252,982 Discussions
  • 7,875,096 Comments

Discussions

Dynamic Sql instructions

f97be60a-6d23-4403-9ace-3ce56554d59c
edited Oct 25, 2016 11:49AM in .NET Stored Procedures

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.

Answers

  • Renato Pedroso
    Renato Pedroso Member Posts: 9
    edited Oct 3, 2016 8:03AM

    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
    Asterix45 Member Posts: 11
    edited Oct 25, 2016 11:49AM

    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; 
This discussion has been closed.