Looking for a utility that converts a SQL statement into a dynamic SQL statement

Question

I am looking for a utility that will convert Oracle SQL to a string that can executed dynamically.

Edit:

Yes, consider this simple SQL

SELECT * FROM TABLE 
WHERE COLUMN_NAME = 'VALUE'

I have a utility which for T-SQL which converts the above SQL to a synamic SQL as follows:

BEGIN

DECLARE @Exe_String VarChar(2000)
DECLARE @Qt         Char(1)
DECLARE @Cr         Char(1)

SET @Qt = Char(39) 
SET @Cr = Char(10)

SET @Exe_String = 'SELECT * FROM TABLE ' + @Cr
SET @Exe_String = @Exe_String + 'WHERE COLUMN_NAME = ' + @Qt + 'VALUE' + @Qt + '' + @Cr

PRINT @Exe_String

--Execute (@Exe_String)

END

Granted that the code generated good probably be better, yo get the idea, I hope.

I'm looking for the same type of conversion for Oracle SQL.

Answer 1

Here is a tool that I have used a couple of times. You will have to change the output a little to get it to run but it sure beats having to figure out how to escape all the single ticks.

Sql Tuning

After you click on the link it will take you right to the site and a page with sample SQL. Click the "Static SQL to Dynamic SQL" button and you can see how it works. Then input your own sql you want converted and click the button again. Remove the extra tick (') marks in the end and beginning of each line with the exception of the first and last line and pipes (|) don't need to be there either. Hope this helps.

Answer 2

As a raw translation of your T-SQL to PL/SQL

DECLARE 
   Exe_String VarChar(2000);
   Qt CONSTANT Char(1) := CHR(39);
   Cr CONSTANT Char(1) := CHR(10);
BEGIN
   exe_string := 'SELECT * FROM TABLE '||Cr;
   exe_string := exe_string || 
              'WHERE COLUMN_NAME = ' || Qt || 'VALUE' ||Qt || '' ||Cr;
   dbms_output.put_line(exe_string);
   --
   EXECUTE IMMEDIATE exe_string;
END;

The obvious difference is that in Oracle the concatenation operator for strings is || rather than +.

Personally, I have a little string manipluation package (let's call it pstring) that I'd use in a case like this - includes functions like enquote(string), standard constants for newline,tab,etc and the ability to do C-style text replacement.

 exe_string := 
    pstring.substitute_text('SELECT * FROM %s \n WHERE %s = %s',
               table_name,column_name,pstring.enquote(value));

Have you considered using bind variables - i.e. :value - rather than dealing with escaping all the internal quotes? It's a good defence against SQL injection.

Obviously there's some difficulty if you have varying numbers of variables (you need to use DBMS_SQL to link them to the statement rather than a simple EXECUTE IMMEDIATE) but for your simple case it would look like this.

PROCEDURE (table_name IN VARCHAR2, column_name IN VARCHAR2)
IS
   Exe_String VarChar(2000);
BEGIN
    exe_string := 
        pstring.substitute_text('SELECT * FROM %s \n WHERE %s = :value',
                   table_name,column_name);
   dbms_output.put_line(exe_string);
   --
   EXECUTE IMMEDIATE exe_string USING pstring.enquote(value);
END;

Although of course you have to do something with the results of your SQL.

  EXECUTE IMMEDIATE exe_string INTO lresult USING pstring.enquote(value);

Which is difficult when the shape of the table may differ - again, you have to look at Type 4 dynamic SQL (DBMS_SQL).