Parameters wrong assignment (PL/SQL, ORACLE)

Question

I'm trying to run below pl/sql block but I'm getting error:

set serveroutput on
declare
rowBefore            VARCHAR2(32000);
myschema             VARCHAR2(32000) := 'abc';
mytable              VARCHAR2(32000) := 'table1';
param1               VARCHAR2(32000) := 'Tom';
begin
    select count(*) into rowBefore from myschema.mytable where colA = param1;
   --select count(*) into rowBefore from abc.table1  where colA = 'Tom';
    DBMS_OUTPUT.PUT_LINE(rowBefore);
End;

How to correctly use my all parameters into select statement?

Update error:

Error report -
ORA-06550: linia 7, kolumna 50:
PL/SQL: ORA-00942: tabela lub perspektywa nie istnieje
ORA-06550: linia 7, kolumna 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Telling us what error you get is usually helpful. But you can’t use variables for object names in a SQL statement - you’d have to use dynamic SQL. There are lots of examples of how to do that around. — Alex Poole
– Alex Poole, Commented Jan 10, 2018 at 13:31
I update question. the comment select working, but when I'm trying to use parameters I got error — 4est
– 4est, Commented Jan 10, 2018 at 13:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

You can't use a variable as an identifier in a SQL statement. It is looking for a table that is actually called mytable - not one with the value of the variable with that name as you expect. And the same for the schema.

From the documentation:

If the statement is a SELECT statement, the PL/SQL compiler removes the INTO clause.

The PL/SQL compiler sends the statement to the SQL subsystem.

The SQL subsystem checks the syntax of the statement.

If the syntax is incorrect, the compilation of the PL/SQL unit fails. If the syntax is correct, the SQL subsystem determines the names of the tables and tries to resolve the other names in the scope of the SQL statement.

...

It tries to resolve other names, e.g. functions; but table names (and schema names, and column names) have to be valid to the SQL parser.

You have to use dynamic SQL to create a string that contains the statement, concatenating in the identifiers:

execute immediate 'select count(*) from ' || myschema ||'.'|| mytable
  || ' where colA = :value'
into rowBefore using param1;

If you print out that generated statement with dbms_output you should see it matching the commented-out version, except for the use of a bind variable for the columns value. That would also help highlight any errors - like not including whitespace between the concatenated parts of the statement, which is a common and easy mistake.

It's often a good idea to build the statement as a string variable to make such debugging easier:

declare
  ...
  v_stmt varchar2(4000):
begin
  v_stmt := 'select count(*) from ' || myschema ||'.'|| mytable
    || ' where colA = :value';
  -- for debugging
  dbms_output.put_line(v_stmt);
  -- and run it
  execute immediate vStmt into rowBefore using param1;
  ...

If you are really getting the identifiers passed in by a user or client you should validate them to avoid unexpected errors and SQL injection attacks. Also, if the identifiers might be case sensitive (i.e. schemas/tables created with quoted identifiers) then you may need to add double-quotes to the generated statement, and make sure the variables are the correct case.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 10, 2018 at 13:43

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

4est Over a year ago

thanks, I but something is wrong with colA, even I used it as parameter

Alex Poole Over a year ago

@4est - sorry, I assumed colA was also a variable; seems like that's a fixed name regardless of the schema and table name? Updated to use that literal name.

4est Over a year ago

thanks! now it's fine....but can do like: vS = 'select (*) into...' and then execute?

Alex Poole Over a year ago

Yes, absolutely - that make it much easier to print and debug the statement. But without the into ... part. I've added a version that does that.

4est Over a year ago

thank you once again, that is what I'm looking for, much appreciated your help @Alex!

eifla001 · Accepted Answer · 2018-01-10 14:02:18Z

0

error is ORA-942: table or view does not exists. run first below query and see if error still occurs.

-- should you use abc.table1, instead of myschema.mytable?
select count(*) rowBefore from myschema.mytable where colA = 'a';

if there is still error, then fix the SELECT statement first before you use it in the PL/SQL block.

answered Jan 10, 2018 at 14:02

eifla001

1,1578 silver badges9 bronze badges

Collectives™ on Stack Overflow

Parameters wrong assignment (PL/SQL, ORACLE)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related