CODEWITHSUNDEEP
oracle-databaseplsqlparametersprocedureoracle12c
4est
4est

Reputation: 3168

Parameters wrong assignment (PL/SQL, ORACLE)

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:

Upvotes: 0

Views: 234

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

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:

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

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

  3. 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.

Upvotes: 4

eifla001
eifla001

Reputation: 1157

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.

Upvotes: 0

Related Questions