How to fix this dynamic SQL query (Apex Oracle)

Question

I'm trying to get this dynamic SQL query to run, but keep running into issues. It is meant to show the number for employees with the role 'Manager' from the 'emp' Oracle Apex sample database.

Declare 
Manage_num Number(10);
Manage_count VarChar(150);

Begin 
Manage_count := 'Select COUNT(JOB) from emp where JOB='MANAGER'';
Execute Immediate Manage_count into Manage_num;
DBMS_OUTPUT.PUT_LINE ('Total number of Managers is'||Manage_num);

END

This is the error message I keep receiving:

Error at line 4/0: ORA-06550: line 3, column 1: PLS-00103: Encountered the symbol "MANAGE_COUNT" when expecting one of the following:

:= ; not null default character The symbol ";" was substituted for "MANAGE_COUNT" to continue. ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 797 ORA-06550: line 5, column 1: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

:= ; not null default character The symbol ";" was substituted for "BEGIN" to continue. ORA-06550: line 6, column 56: PLS-00103: Encountered the symbol "MANAGER" when expecting one of the following:

& = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol "* was inserted before "MANAGER" to continue. ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 782 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2035

Manage_num Number(10)
Manage_count VarChar(150)
Begin
Manage_count := 'Select COUNT(JOB) from emp where JOB='MANAGER'';
Execute Immediate Manage_count into Manage_num;

I've tried changing the semicolons and changing the syntax based on youtube tutorials with no success

Sorry if this is an easy solution or my question is vague I'm new to the forum.

Is there a reason for using 'EXECUTE IMMEDIATE' instead of just writing 'SELECT COUNT(*) INTO ...'? — Codo
– Codo, Commented Jul 24, 2023 at 19:34
You really don't want to use dynamic SQL here. If you are going to use dynamic SQL, use bind variables rather than string literals for your conditions. If you are going to use dynamic SQL and use string literals in that dynamic SQL, you'd need to escape the single quotes. My bias would be to use the q' quoting syntax but you could also just use two single quotes for each of the single quotes you have — Justin Cave
– Justin Cave, Commented Jul 24, 2023 at 20:00
I know I don't have to use it. I'm just trying to practice it in case I ever had to — Ryan_Brusseau
– Ryan_Brusseau, Commented Jul 25, 2023 at 20:46

Littlefoot · Accepted Answer · 2023-07-24 21:02:29Z

As already commented, code you posted isn't correct.

Basically, there's no need for dynamic SQL as there's nothing dynamic in that code. If you used

declare
  manage_num number;
begin
  select count(*) into manage_num from emp where job = 'MANAGER';
  dbms_output.put_line('Total number of Managers is ' || manage_num);
end;
/

you'd get the answer.

However, if you - for some reason - have to use dynamic SQL, then pay attention to single quotes usage. If you opt to double them whenever needed, code can get difficult to read and maintain, so the q-quoting mechanism helps to make it prettier. Also, Oracle recommends we use varchar2 (not varchar) datatype; that's not an error, though.

Declare 
  Manage_num   Number(10);
  Manage_count VarChar2(150);
Begin 
  Manage_count := q'[Select COUNT(JOB) from emp where JOB='MANAGER']';
  Execute Immediate Manage_count into Manage_num;
  DBMS_OUTPUT.PUT_LINE ('Total number of Managers is '||Manage_num);
END;
/

This is how it looks like in Apex SQL Workshop:

Collectives™ on Stack Overflow

How to fix this dynamic SQL query (Apex Oracle)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related