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: and later   [Release: 11.2 and later ]
Information in this document applies to any platform.


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 ');


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);
 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;

set serveroutput on
execute extn_yfs_import_out_to_file ('Test');

The following shows the output from executing the above.

BEGIN extn_yfs_import_out_to_file ('Test'); END;

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 1


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