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

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).