CODEWITHSUNDEEP
oracle-databaseplsql
SASPYTHON
SASPYTHON

Reputation: 1621

How to change to dynamic sql escape by using REPLACE function

so I try to change this code to dynamic SQL 

SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM table

excute

12121-President''S Office

I changed to dynamic sql

EXECUTE IMMEDIATE
    ' SELECT  LISTAGG(accounting_dept || ''-''||REPLACE(full_desc,'''''',''''''''), ''; '') WITHIN GROUP (ORDER BY accounting_dept)  FROM table '

INTO v_task;

the error is ORA-00907: missing right parenthesis

The problem is this part REPLACE(full_desc,'''''',''''''''),

how can I change to dynamic sql without the cauing error?

Upvotes: 0

Views: 1069

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

If you put your statement into a variable you can output it before it's executed:

set serveroutput on

declare
  v_stmt varchar2(2000);
  v_task varchar2(2000);
begin
  v_stmt := 'SELECT  LISTAGG(accounting_dept || ''-''||REPLACE(full_desc,'''''',''''''''), ''; '') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table';
  dbms_output.put_line(v_stmt );
  execute immediate v_stmt into v_task;
end;
/

which displays the statement it tries to execute, and then the error that gets:

SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,''',''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table

Error report -
ORA-00911: invalid character
ORA-06512: at line 7

If you run that generated statement manually in SQL Developer you'll get ORA-00907: missing right parenthesis; not entirely sure how or why you're getting that error dynamically too - it should be complaining about the semicolon first, with the ORA-00911.

You can see that the generates statement does not match the original static statement you started with. You need to have even more escaped quotes:

declare
  v_stmt varchar2(2000);
  v_task varchar2(2000);
begin
  v_stmt := 'SELECT  LISTAGG(accounting_dept || ''-''||REPLACE(full_desc,'''''''',''''''''''''), ''; '') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table';
  dbms_output.put_line(v_stmt );
  execute immediate v_stmt into v_task;
end;
/

SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM some_table

PL/SQL procedure successfully completed.

But do as Tony Andrews suggests, and use the alternative quoting mechanism instead.

Not that you even need dynamic SQL here; this does the same:

declare
  v_task varchar2(2000);
begin
  SELECT LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ')
    WITHIN GROUP (ORDER BY accounting_dept)
  INTO v_task
  FROM tableinto v_task;
end;
/

Upvotes: 5

Tony Andrews
Tony Andrews

Reputation: 132570

If turning the whole select into a string, use Q quote like this:

q'[SELECT  LISTAGG(accounting_dept || '-'||REPLACE(full_desc,'''',''''''), '; ') WITHIN GROUP (ORDER BY accounting_dept)  FROM table]'

Upvotes: 4

Related Questions