Inserting the text of a stored procedure into a record

Question

How can I insert into a record the text of a procedure stored in ALL_SOURCE.TEXT ?

This part of code gives me error: "missing comma"

  execute immediate '
               insert into results(SrcProcedure)  values(' ''
               || tabela_temporare(1).text ||
               '' ')';

I think this is due to the unescaped characters contained in the text of the stored procedure, but I can't find a way to fix it.

There are stored the text of procedures for every oracle user. — Desaretiuss, Apr 28 '15 at 14:25
There is no need of `execute immediate` statement here. Simple `insert into .. select text from all_source where name = 'proc_name'` will suffice. — Nick Krasnov, Apr 28 '15 at 14:34
I need to, beacause this statement is used within a for loop — Desaretiuss, Apr 28 '15 at 14:42
There is absolutely no need for an `execute immediate`. A simple insert with bind variable will do. — YoYo, Apr 29 '15 at 07:54

Kuvick · Answer 1 · 2015-04-28T14:58:11.677

0

If you want to have the string delimiting character (apostrophe: ') inside a Varchar, you must use two consecutive apostrophes and then another one to end the string. It gives you an error because you ended the string first. Try this piece of code:

EXECUTE IMMEDIATE 'INSERT INTO results(SrcProcedure)  values(''' || tabela_temporare(1).text || ''')';

EDIT: Better use Bind Variables, see my sample Code:

CREATE TABLE RESULTS (SRCPROCEDURE VARCHAR2 (300));

DECLARE
      v_SQL VARCHAR2(4000) := 'INSERT INTO results(SrcProcedure) VALUES(:1)';
BEGIN
     EXECUTE IMMEDIATE v_SQL USING 'something2';
END;

I hope it works now! :)

edited Apr 28 '15 at 14:58

answered Apr 28 '15 at 14:33

Kuvick

48
6

That's what I've writen in the question – Desaretiuss Apr 28 '15 at 14:37
look closely to see the difference between your code and mine and go through my answer text again ;-) – Kuvick Apr 28 '15 at 14:43
Your dynamic SQL using a bind variable is much better. However, there is no more need to use a dynamic sql, you can leave the `EXECUTE IMMEDIATE` itself out. Also your datatype for SRCPROCEDURE will be much safer being a CLOB. – YoYo Apr 28 '15 at 16:04

YoYo · Answer 2 · 2015-04-29T08:02:43.267

What about simply doing this in your PL/SQL:

INSERT INTO results(SrcProcedure) VALUES (tabela_temporare(1).text);

Internally it will use bind variables to pass your PL/SQL variable into the INSERT.

Note that it is highly advised to stay away from dynamic SQL (EXECUTE IMMEDIATE and the likes), because that code will be prone to SQL injection.

[UPDATE] I don't know what more to tell. However consider these things for using a dynamically built SQL within a FOR loop using string search and replace:

It will be terribly slow, because for each loop iteration, you recompile the dynamic SQL.
The SQL itself will execute slower because it cannot be cached.
You have the danger of SQL injection that can lead to bugs and security issues. You think you fixed it by search and replace quotes, but I bet there might be scenario's you did not take into account.
The process itself of search and replace for quotes is also terribly slow.

There are good uses of dynamic SQL, but that is just not one of them, and it is also against all possible advice to 'concatenate' parameters vs 'binding' them.

This time I only needed to make it work, using utl_match to compare source procedures of different users, though in the future I'll have to take into consideration these warnings on performance / security issues. — Desaretiuss, Apr 29 '15 at 09:31

score -1 · Accepted Answer · answered Apr 28 '15 at 15:17

If that text field contains actual stored pl/sql code, the only character likely to cause problems is the single quote.

So replace each instance with two single quotes which will then get parsed as a properly escaped single quote. Of course, you have to escape the quote in the replace statement to get it to work, but try this:

execute immediate '
           insert into results(SrcProcedure)  values(' ''
           || replace(tabela_temporare(1).text,'''','''''') ||
           '' ')';

Inserting the text of a stored procedure into a record

3 Answers3