CODEWITHSUNDEEP
sqloracle-databaseplsql
Robert Mason
Robert Mason

Reputation: 181

select statement not working with variables, but does with fixed values

So i have this statement:

execute immediate  'SELECT count(*) INTO counter FROM (SELECT table_a.NUMBERWANG FROM table_a MINUS SELECT table_b.NUMBERWANG FROM table_b )';

But if i try and make one of the values into a variable, the statement seems to no longer work.

If it try

execute immediate  'SELECT count(*) INTO counter FROM (SELECT table_a.NUMBERWANG FROM table_a MINUS SELECT table_b.NUMBERWANG FROM ' || variable_1 || ' )';

I get the missing keyword error.

Where it gets unusual is if i remove the "INTO COUNTER" it will run.

Is it my placement of the INTO that is wrong or is it something else entirely?

Upvotes: 1

Views: 66

Answers (2)

user330315
user330315

Reputation:

You can't have an into clause inside the string you are executing.

You need to move this out of the string and tell the execute immediate to do that:

declare
  l_count integer;
  l_sql   varchar(4000);
begin
  l_sql := 'SELECT count(*) FROM (SELECT table_a.NUMBERWANG FROM table_a MINUS SELECT table_b.NUMBERWANG FROM table_b )';
  execute immediate l_sql into l_count;
end;
/

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

I don't think you can perform a SELECT...INTO... dynamically. When I try this:

DECLARE 
  counter  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO COUNTER FROM TEST_TABLE';

  DBMS_OUTPUT.PUT_LINE('COUNTER=' || COUNTER);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

I get ORA-00905: missing keyword. I can get rid of the error by removing the INTO clause, but then of course I still don't get a value into counter.

If you want to execute this sort of statement dynamically and be able to retrieve the value(s) into variables you'll need to use a cursor, as in:

DECLARE
  counter  NUMBER;
  c        SYS_REFCURSOR;
BEGIN
  OPEN c FOR 'SELECT COUNT(*) FROM TEST_TABLE';
  FETCH c INTO counter;
  CLOSE c;

  DBMS_OUTPUT.PUT_LINE('COUNTER=' || COUNTER);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Share and enjoy.

Upvotes: 0

Related Questions