CODEWITHSUNDEEP
sqloracle-database
Ewaver
Ewaver

Reputation: 1191

EXECUTE immediate and SQL Update

Can anyone help with this. I am trying to execute this code in APEX using a dynamic action associated with a button. Just trying to do a simple Update on the table, but my Pl/SQL is weak ;)

DECLARE
sql_stmt VARCHAR2(200);
BEGIN
EXECUTE IMMEDIATE sql_stmt := 
'update dos_visits set visit_type = 'IMV'where     visit_type = 'SMV''
END;

I am getting a error with = for this ..

Thanks so much!

Upvotes: 0

Views: 2686

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

In PL/SQL, single quotes in strings need to be escaped so that the PL/SQL engine knows that you want to indicate the presence of a single quote not to end the string. If you want to use a variable sql_stmt, you'd also need to assign the value to that variable in a separate step from the EXECUTE IMMEDIATE call.

This will work (note that these are two consecutive single quotes, not a single double quote character)

DECLARE
  sql_stmt VARCHAR2(200);
BEGIN
  sql_stmt := 'update dos_visits set visit_type = ''IMV'' where visit_type = ''SMV''';
  EXECUTE IMMEDIATE sql_stmt; 
END;

There doesn't appear, however, to be any reason to use dynamic SQL here. It would generally make more sense to use static SQL

BEGIN
  update dos_visits 
     set visit_type = 'IMV' 
   where visit_type = 'SMV';
END;

If you are going to use dynamic SQL for some reason, you'd probably want to use bind variables in your query unless those visit_type values really are hard-coded.

DECLARE
  sql_stmt VARCHAR2(200);
BEGIN
  sql_stmt := 'update dos_visits set visit_type = :1 where visit_type = :2';
  EXECUTE IMMEDIATE sql_stmt 
    USING 'IMV', 'SMV'; 
END;

Upvotes: 1

Related Questions