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

Answer 1

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