PL/SQL Raises ORA-01403 With Where Char Column Comparing To Varchar2 Bind Variable, Hard-Coded Condition Works (Doc ID 1175113.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

A PL/SQL procedure is failing to run successfully.  The implicit cursor embedded in the block (SELECT statement) is returning 0 rows:

The following SQL will generate the test table & data to illustrate the issue. Notice that the first record is only 4 characters but the following three records have 5 due to the space added to the end.

CREATE TABLE char_test(field1 char(10));

INSERT INTO char_test VALUES ('Test');
INSERT INTO char_test VALUES ('Test ');
INSERT INTO char_test VALUES ('Test ');
INSERT INTO char_test VALUES ('Test ');

COMMIT;

The following PL/SQL procedure, when executed, will produce the error.

CREATE OR REPLACE PROCEDURE extn_yfs_import_out_to_file (ikey varchar2) IS
v_count number;
v_data  varchar2(15);
BEGIN
 SELECT count(field1) INTO v_count FROM char_test WHERE field1 = 'Test';
  dbms_output.put_line(v_count);  --> #rows in table
 SELECT field1
 INTO v_data
 FROM char_test
 WHERE field1 = extn_yfs_import_out_to_file.ikey;
  dbms_output.put_line(v_data);
END ;
/

set serveroutput on
execute extn_yfs_import_out_to_file ('Test');

The following shows the output from executing the above.

4
BEGIN extn_yfs_import_out_to_file ('Test'); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "REFRESH.EXTN_YFS_IMPORT_OUT_TO_FILE", line 8
ORA-06512: at line 1


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms