Select INTO with binding parameters not working with execute immediate

Question

I have the following query that returns 1 when executed:

SELECT COUNT(*)  FROM TABLE_NAME WHERE Column1='x' AND Column2='y';

In my PL/SQL block, I need to evaluate the condition above in order to execute the business logic. Below is a simplified version:

DECLARE
   column1 VARCHAR(20):='x';
   column2 VARCHAR(20):='y';
   rows_no NUMBER :=0;
   sql_query VARCHAR2(200) :='SELECT COUNT(*)  FROM TABLE_NAME WHERE Column1=:1 AND Column2=:2';

BEGIN

  EXECUTE IMMEDIATE sql_query INTO rows_no USING column1, column2;
  DBMS_OUTPUT.PUT_LINE('ROWS NO: '|| rows_no);

END;

The result from the execution of the PL/SQL block is 0 that is different from the result when the query is executed that is 1. I think I abiding by the rules of the binding parameter to query select into in the PL/SQL. I will appreciate any help or guide.

Best Regards, Rando.

P.S

When I make the modification below :

DECLARE
   column1 VARCHAR(20):='x';
   column2 VARCHAR(20):='y';
   rows_no NUMBER :=0;
   sql_query VARCHAR2(200) :='SELECT COUNT(*)  FROM TABLE_NAME WHERE Column1=''x'' AND Column2=''y''';

BEGIN

  EXECUTE IMMEDIATE sql_query INTO rows_no;
  DBMS_OUTPUT.PUT_LINE('ROWS NO: '|| rows_no);

END;

The result is: 1

I have a loop that reads from an excel file and inserts it into the database table(Table_name). The result above is needed to prevent the insertion of dublicates records. The commit is issued at the end of the procedure.

That means that if a record is added from the procedure it remains uncommitted till the procedure finishes.

I doubt this is a problem, uncommitted inserts are not accessible from other connections but in the current connection, they should be accessible. The following has all the simplified logic of the plsql block:

DECLARE;
--declaration variables
BEGIN
      LOOP
         -- fetching information from excel file
         EXECUTE IMMEDIATE sql_query INTO rows_no;
         DBMS_OUTPUT.PUT_LINE('ROWS NO: '|| rows_no);
         IF rows_no=0 THEN
           -- insert the information read from excel in database table
           rows_inserted:=rows_inserted+1;
         END IF;
      END LOOP;
   IF  rows_inserted>0 THEN
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('ROWS INSERTED: '||rows_inserted);
        ELSE 
        DBMS_OUTPUT.PUT_LINE('No rows were inserted');
   END IF;
END

What is the output of the PL/SQL block if you don't use a bind variable and use the same constant query as your normal query which gives 1 as an output? Your PL/SQL block seems correct. — Popeye, Nov 26 '20 at 08:49
Cannot replicate the issue [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c65b111d0621fdcce6bc4513a493dbe5). (Are you sure you have `COMMIT`ted the data?) — MT0, Nov 26 '20 at 10:33
If I run the query the data are available. The same query run inside the plsql with binding parameter is returning 0 data. The commit is issued at the end of the procedure. The condition is needed to prevent the insertion duplicates records. — Rando Shtishi, Nov 26 '20 at 10:38

score 0 · Answer 1 · answered Nov 26 '20 at 08:59

The problem is most probably outside from Oracle, please try to re-test following script (The output is shown as comment)

create table tab (column1 varchar2(1), column2 varchar2(1));

set SERVEROUTPUT ON
DECLARE
   column1 VARCHAR(20):='x';
   column2 VARCHAR(20):='y';
   rows_no NUMBER :=0;
   sql_query VARCHAR2(200) :='SELECT COUNT(*)  FROM TAB WHERE Column1=:1 AND Column2=:2';

BEGIN

  EXECUTE IMMEDIATE sql_query INTO rows_no USING column1, column2;
  DBMS_OUTPUT.PUT_LINE('ROWS NO: '|| rows_no);

END;
/

-- ROWS NO: 0

insert into tab (column1, column2) values ('x','y');

DECLARE
   column1 VARCHAR(20):='x';
   column2 VARCHAR(20):='y';
   rows_no NUMBER :=0;
   sql_query VARCHAR2(200) :='SELECT COUNT(*)  FROM TAB WHERE Column1=:1 AND Column2=:2';

BEGIN

  EXECUTE IMMEDIATE sql_query INTO rows_no USING column1, column2;
  DBMS_OUTPUT.PUT_LINE('ROWS NO: '|| rows_no);

END;
/

-- ROWS NO: 1

Thank you for your answer. Unfortunately, I have retested several times, also by closing the connection and creating a new connection. It doesn't work as it should. — Rando Shtishi, Nov 26 '20 at 09:16

Select INTO with binding parameters not working with execute immediate

1 Answers1