ORA-00933: SQL command not properly ended when using execute immediate

Question

When I run

SELECT COUNT(*) 
  INTO l_entry_found 
  FROM hera.hera_user@iam 
 WHERE username = docm.eb_key;

without execute immediate it works.

execute immediate'
      SELECT COUNT(*) INTO l_entry_found FROM hera.hera_user@iam WHERE 
      username = docm.eb_key;';

However throws an ora-00933.

What could be the Problem and how may i solve it? Thanks in advance!

Answer 1

you can't use select into in dynamic SQL and you do not need a semicolon either

declare
  l_entry number(10);
begin

  execute immediate'
        SELECT COUNT(*) FROM hera.hera_user@iam WHERE 
        username = '''||docm.eb_key||'''' INTO l_entry;
end;

Answer 2

You have some issues; say you have a table like

create table someTable(userName varchar2(100))

and a code like :

declare
    someVariable    varchar2(100);
    l_entry_found   number;
begin
    someVariable := 'someName';
    --
    SELECT COUNT(*)
    INTO l_entry_found
    FROM someTable
    WHERE username = someVariable;
end;

If you wat to switch to dynamic SQL, you need to

• remove the semicolon
• use bind variables to pass your parameter
• move the INTO outside the dynamic part

Your code could be:

declare
    someVariable    varchar2(100);
    l_entry_found   number;
begin
    someVariable := 'someName';
    --
    execute immediate 
    'SELECT COUNT(*)
    FROM someTable
    WHERE username = :bindVar'
    into l_entry_found
    using someVariable;
end;

Here I assume that you have a good reason to switch to dynamic SQL, for example, your table name could change based on some parameter; if not, plain SQL is good enough for your task.

Answer 3

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance

Continuing with your question

-- Case using 1 output column 'COUNT(*)', 1 filter variable 'eb_key'
DECLARE
  --
  eb_key VARCHAR2(100) := 'something';
  l_entry_found number;
  --
BEGIN 
  execute immediate 'SELECT COUNT(*) FROM hera.hera_user@iam WHERE username = :eb_key;' USING eb_key INTO l_entry_found;
END;

-- Case using 2 output column 'COUNT(*)', 3 filter variable 'eb_key'
DECLARE
  --
  eb_column1 VARCHAR2(100) := 'something';
  eb_column2 VARCHAR2(100) := 'something';
  l_entry_found1 number;
  l_entry_found2 number;
  --
BEGIN 
  execute immediate 'SELECT column1, column2 FROM hera.hera_user@iam WHERE username = :eb_1 AND lastname = :eb_2;' USING eb_column1, eb_column2 INTO l_entry_found1,l_entry_found2;
END;