Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block.
PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block. [message #679293] Thu, 20 February 2020 02:59 Go to next message
msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad
Junior Member
Hi Sir,

I have written a small anonymous block to mask few column values from the table. I have used Execute Immediate a dynamic sql for my update statement. But unfortunately i am getting the error at the line of execute statement. Please help me to resolve this issue asap. Thank you in Advance.

Below is the anonymous Block

Declare
V_policy XXX.Policy%type;
V_Address varchar2(300);
V_Statement varchar2(1000);
v_cnt number(10) := 0;
--l_sql varchar2(1000);
Begin
V_policy := '0016162';
V_Address := '''' || V_policy || '_ADRS' || '''';
for rec in (select Table_Name, Masking_Field, Masking_Type from PACC_FTR where rownum < 1) Loop

IF rec.masking_type = 'ADDRESS' Then
V_Statement := 'Update schemaname.' || rec.Table_Name || ' Set ' ||
rec.Masking_Field || '=' || V_Address ||
' where policy =' || ' || V_policy || ' || ';';
dbms_output.put_line(V_Statement);
execute immediate V_Statement ;
End IF;
v_cnt := v_cnt + 1;
--dbms_output.put_line(v_cnt);

End Loop rec;

-- Commit;
End;

Error is :
ORA-00917: invalid Character
ORA-06512: at line 17

[Updated on: Thu, 20 February 2020 03:02]

Report message to a moderator

Re: PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block. [message #679294 is a reply to message #679293] Thu, 20 February 2020 03:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your specific error is caused by the semi-colon in quotes on line 17.
Statements sent to execute immediate don't need the terminator character - ; - as the act of passing the string to execute immediate implicitly terminates it.

Once you've fixed that you'll get an error saying it doesn't know what v_policy is - you need to use bind variables and the USING clause of execute immediate.
Re: PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block. [message #679296 is a reply to message #679294] Thu, 20 February 2020 03:34 Go to previous messageGo to next message
msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad
Junior Member
Yes i have used the bind variable along with USING Clause. But still i am getting the same error. Please help!
Re: PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block. [message #679298 is a reply to message #679296] Thu, 20 February 2020 05:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can't see your screen from here - you need to post the code you're currently running, along with the full error message.
Re: PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block. [message #679305 is a reply to message #679293] Thu, 20 February 2020 11:12 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topics:

Michel Cadot wrote on Sun, 08 January 2017 18:32
...
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Michel Cadot wrote on Thu, 02 March 2017 21:21

And review your previous topics and feedback and thank people something you only did for your first topic, why not in the subsequent ones?

Do it now.

Quote:
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

BlackSwan wrote on Tue, 16 October 2018 15:23
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...

[Updated on: Thu, 20 February 2020 11:12]

Report message to a moderator

Previous Topic: How to export multiple blob and character data into a text file?
Next Topic: Start Transactional Control in PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 07:56:50 CDT 2024