Execute Immediate in oracle

Question

I have below query which gives an error as encounter an symbol ( in the line where loop is used. I am trying to develop a function which takes dynamic paramater as table_name,column_name,table_id and used for other tables as well.

FUNCTION get_encryp_pass( table_name IN varchar2,column_name IN varchar2,table_id IN varchar2) RETURN VARCHAR2
  IS
  BEGIN
   EXECUTE IMMEDIATE 'for c1 in (select * from' || table_name ||) loop   
      EXECUTE IMMEDIATE 'update ' || table_name || ' set ' || column_name = encrypt_val(c1.column_name) || ' where ' || table_id || ' = ' || c1.table_id and column_name is not null;
      end loop;   
  END get_encrypt_pass;

You do not have enough single quotes. For example, after table name is appended you need to put quotes again before ). `EXECUTE IMMEDIATE` needs a string. — Nitish, Apr 21 '16 at 13:11
@Nitesh i tried but still getting the same error. Jchomel can you please explain more ? — Andrew, Apr 21 '16 at 13:14
What does a sample table look like, and what are some possible values in the 3 input parameters for that table? — ruudvan, Apr 21 '16 at 13:52
But you are not actually getting an encrypted password as a string back, are you? You are doing an `UPDATE` inside the function and modifying the value in the table. `UPDATE` can't be done inside SQL anyway. — ruudvan, Apr 21 '16 at 13:56
Let's assume that you are not doing an update, but just a SELECT. Think about how many rows do you want to be returned. Is it 1 encrypted password that you're looking for or an entire table? If it is the entire table, then you need to think about returning arrays. If it is just 1 password, then you need a value for the `ID` to be passed in to identify that 1 row and filter by it. — ruudvan, Apr 21 '16 at 14:00

score 1 · Answer 1 · answered Apr 21 '16 at 13:17

1

keep care of what is a variable and what is a string-literal and must be single-quoted therefore ... and string-variables mus be double-quoted:

EXECUTE IMMEDIATE 'update ' || table_name || ' set ' || column_name || ' = ''' || encrypt_val(c1.column_name) || ''' where ' || table_id || ' = ' || c1.table_id || ' and column_name is not null';

Best practice is to concatenate the statement in a varchar2-variable first and inspect this. If the content of the variable is syntactical correct and executable, the EXECUTE IMMEDIATE should work as well

declare
   stmt varchar2(4000);
begin
   stmt := 'update ' || table_name || ' set ' || column_name || ' = ''' || encrypt_val(c1.column_name) || ''' where ' || table_id || ' = ' || c1.table_id || ' and column_name is not null';
   EXECUTE IMMEDIATE stmt;
end;

answered Apr 21 '16 at 13:17

oratom

271
1
5

a FOR - Loop is not a simple SELECT/INSERT/UPDATE/DELETE-Statement, so you have to wrap all your code in an anonymous-block like "**begin** for .... loop do_something; end loop; **end**" – oratom Apr 21 '16 at 13:38
Why are you updating that same 1 column for that same set of rows inside a FOR loop? – ruudvan Apr 21 '16 at 13:43
A simple dynamic update statement can also suffice the requirement – Avrajit Roy Apr 21 '16 at 13:47
Your code looks like you want to convert all uncrypted "column_name" to their crypted equivalent. If so, meaning you update ALL ROWS!, then drop that FOR-Loop as well as the where-predicate comparing a single ID ... simply update all: `update tbl1 set col1 = encrypt_val(col1) where col1 is not null` – oratom Apr 21 '16 at 13:50

Frank Ockenfuss · Accepted Answer · 2016-04-21T14:22:47.743

1

this should work:

CREATE PROCEDURE get_encryp_pass(table_name  IN varchar2,
                                 column_name IN varchar2,
                                 table_id    IN varchar2) IS
BEGIN
  EXECUTE IMMEDIATE 'begin for c1 in (select * from ' || table_name ||
                    ') loop update ' || table_name || ' set ' ||
                    column_name || ' = encrypt_val(c1.' || column_name ||
                    ') where ' || table_id || ' = c1.'||table_id||' and ' || column_name ||
                    ' is not null; end loop; end;'
    ;
END;

But why not simply call update FTP_SFTP_SERVER set PASSWORD=encrypt_val(PASSWORD) where PASSWORD is not null ?

edited Apr 21 '16 at 14:22

answered Apr 21 '16 at 13:49

Frank Ockenfuss

2,023
11
26

That means `table_id` is *not* the name of your column? In this case you have to pass a further parameter `table_id_name`. – Frank Ockenfuss Apr 21 '16 at 13:56
okay, I've adjusted the procedure. the parameter `table_id` is now the column name of your primary key. In my first version it was the value of the id :-) – Frank Ockenfuss Apr 21 '16 at 14:15
I've corrected it: binding variable table_id was not longer used – Frank Ockenfuss Apr 21 '16 at 14:23

Avrajit Roy · Answer 3 · 2016-04-21T13:56:35.907

0

I think i have one alternative for your question. MERGE can be used in this case. Please try it i dont have workspaceso dint test it but it should work Let me know if it helps.

FUNCTION get_encryp_pass(
    table_name  IN VARCHAR2,
    column_name IN VARCHAR2,
    table_id    IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  EXECUTE IMMEDIATE 'MERGE INTO '||table_name||' t1 USING 
(
SELECT * FROM '||table_name|| ')t2
ON
(
t1.table_id = t2.table_id
)
WHEN MATCHED THEN
UPDATE SET t1.'||column_name||' = encrypt_val(t2.'||column_name||')'
||' WHERE and t1.'||column_name||' IS NOT NULL';
END;

edited Apr 21 '16 at 13:56

answered Apr 21 '16 at 13:39

Avrajit Roy

3,233
1
13
25

so there must not be column named table_id for the table_name you are passing – Avrajit Roy Apr 21 '16 at 13:50
It works for me just checked. Y downvote plz justify – Avrajit Roy Apr 21 '16 at 13:57

Execute Immediate in oracle

3 Answers3