v_string := 'SELECT eid FROM employee WHERE name LIKE 'ABC%';
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_string, 2);
dbms_sql.define_column(v_cursor,01,v_pess_cod_pessoa);
v_rows := dbms_sql.execute(v_cursor);
while dbms_sql.fetch_rows(v_cursor) > 0 loop
  dbms_sql.column_value(v_cursor,01,v_pess_cod_pessoa);
  ..
  ..
end loop;
dbms_sql.close_cursor(v_cursor);

'SELECT eid FROM employee WHERE name LIKE ''ABC%''';

SELECT eid FROM employee WHERE name LIKE 'ABC%';

SELECT eid FROM employee WHERE name LIKE 'ABC';

SELECT eid FROM employee WHERE name = 'ABC%';

SQL>  DECLARE
  2     sqlstr  VARCHAR2(50);
  3     tCursor PLS_INTEGER;
  4   BEGIN
  5     sqlstr := 'select * from raghav.sriram where ename like ''S%''';
  6     tCursor := dbms_sql.open_cursor;
  7     dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
  8     dbms_sql.close_cursor(tCursor);
  9   END;
 10   /

PL/SQL procedure successfully completed.

SQL>  DECLARE
  2     sqlstr  VARCHAR2(50);
  3     tCursor PLS_INTEGER;
  4   BEGIN
  5     sqlstr := 'select sal from raghav.sriram where ename like ''S%''';
  6     tCursor := dbms_sql.open_cursor;
  7     dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
  8     dbms_sql.close_cursor(tCursor);
  9   END;
 10  /
 DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

DECLARE
  v_string           VARCHAR2(1000);
  v_where            VARCHAR2(700);
  v_pessoas          VARCHAR2(250);
  v_cursor           NUMBER;
  v_rows             NUMBER;
  v_pess_cod_pessoa  NUMBER;
  v_flag             BOOLEAN;
BEGIN
    
    -- Building WHERE
    v_flag := false;    
    IF (:auin.dsp_pess_nom_pessoa IS NOT NULL) THEN
      v_where := 'pess.pess_nom_pessoa LIKE ''' ||:auin.dsp_pess_nom_pessoa ||'''';
      v_flag := true;
    END IF;
    
    /* There are other conditions, but that's the one that matter*/
    
    -- Dynamic string
    v_string := 'SELECT pess.pess_cod_pessoa FROM pessoas pess WHERE ' ||v_where;
    
    Mostra(v_string); -- Message Alert
    -- here is the query of sample
    -- SELECT eid FROM employee WHERE name LIKE 'ABC%';    

    -- Searching ..
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.Parse(v_cursor,v_string,2);
    dbms_sql.Define_column(v_cursor,01,v_pess_cod_pessoa);
    v_rows := dbms_sql.Execute(v_cursor);
    v_pessoas := '(';
    
    v_flag := false;
    
    -- When I get the error I can't get in the loop ..
    -- .. the loop just gather info
    WHILE dbms_sql.Fetch_rows(v_cursor) > 0 LOOP
      dbms_sql.Column_value(v_cursor,01,v_pess_cod_pessoa);
      
      IF (v_flag = false) THEN
        v_pessoas := v_pessoas || To_char(v_pess_cod_pessoa);
        v_flag := true;
      ELSE
        v_pessoas := v_pessoas || ',' ||To_char(v_pess_cod_pessoa);
      END IF;
    END LOOP;
    
    IF (v_flag = true) THEN
      v_pessoas := v_pessoas ||')';
    ELSE -- No results Message/Exit
      Msg_alert('Não existem pessoas correspondentes aos valores informados.', 'E',true);
    END IF;
    
    dbms_sql.Close_cursor(v_cursor);
    
    Mostra(v_pessoas); -- Message Alert
  
  
/* Commented code */

END;

CREATE TABLE test1 AS SELECT ROWNUM a, cast(ROWNUM AS varchar2(10)) b, SYSDATE c 
FROM dual CONNECT BY LEVEL < 10000;

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> set serveroutput on
SQL> DECLARE
  2    v_string           VARCHAR2(1000);
  3    v_where            VARCHAR2(700);
  4    v_pessoas          VARCHAR2(250);
  5    v_cursor           NUMBER;
  6    v_rows             NUMBER;
  7    v_pess_cod_pessoa  NUMBER;
  8    v_flag             BOOLEAN;
  9    dsp_pess_nom_pessoa VARCHAR2(10) := '999%';
 10  BEGIN
 11      
 12      -- Building WHERE
 13      v_flag := false;    
 14      IF (dsp_pess_nom_pessoa IS NOT NULL) THEN
 15        v_where := 'b LIKE ''' ||dsp_pess_nom_pessoa ||'''';
 16        v_flag := true;
 17      END IF;
 18      
 19      /* There are other conditions, but that's the one that matter*/
 20      
 21      -- Dynamic string
 22      v_string := 'SELECT a FROM test1 WHERE ' ||v_where;
 23      
 24      dbms_output.put_line(v_string); -- Message Alert
 25      -- here is the query of sample
 26      -- SELECT eid FROM employee WHERE name LIKE 'ABC%';    
 27  
 28      -- Searching ..
 29      v_cursor := dbms_sql.open_cursor;
 30      dbms_sql.Parse(v_cursor,v_string,2);
 31      dbms_sql.Define_column(v_cursor,01,v_pess_cod_pessoa);
 32      v_rows := dbms_sql.Execute(v_cursor);
 33      v_pessoas := '(';
 34      
 35      v_flag := false;
 36      
 37      -- When I get the error I can't get in the loop ..
 38      -- .. the loop just gather info
 39      WHILE dbms_sql.Fetch_rows(v_cursor) > 0 LOOP
 40        dbms_sql.Column_value(v_cursor,01,v_pess_cod_pessoa);
 41        
 42        IF (v_flag = false) THEN
 43          v_pessoas := v_pessoas || To_char(v_pess_cod_pessoa);
 44          v_flag := true;
 45        ELSE
 46          v_pessoas := v_pessoas || ',' ||To_char(v_pess_cod_pessoa);
 47        END IF;
 48      END LOOP;
 49      
 50      IF (v_flag = true) THEN
 51        v_pessoas := v_pessoas ||')';
 52      ELSE -- No results Message/Exit
 53        dbms_output.put_line('Não existem pessoas correspondentes aos valores informados.');
 54      END IF;
 55      
 56      dbms_sql.Close_cursor(v_cursor);
 57      
 58      dbms_output.put_line(v_pessoas); -- Message Alert
 59    
 60    
 61  /* Commented code */
 62  
 63  END;
 64  /
SELECT a FROM test1 WHERE b LIKE '999%'
(999,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999)

PL/SQL procedure successfully completed.

SQL> 

SQL> DECLARE
  2    v_string           VARCHAR2(1000);
  3    v_where            VARCHAR2(700);
  4    v_pessoas          VARCHAR2(250);
  5    v_cursor           NUMBER;
  6    v_rows             NUMBER;
  7    v_pess_cod_pessoa  NUMBER;
  8    v_flag             BOOLEAN;
  9  BEGIN
 10        v_where := 'ename like ''S%''';
 11        v_flag := true;
 12      v_string := 'SELECT sal FROM sriram WHERE ' ||v_where;
 13     v_cursor := dbms_sql.open_cursor;
 14      dbms_sql.Parse(v_cursor,v_string,2);
 15      dbms_sql.Define_column(v_cursor,01,v_pess_cod_pessoa);
 16      v_rows := dbms_sql.Execute(v_cursor);
 17      v_pessoas := '(';
 18      v_flag := false;
 19      WHILE dbms_sql.Fetch_rows(v_cursor) > 0 LOOP
 20        dbms_sql.Column_value(v_cursor,01,v_pess_cod_pessoa);
 21        IF (v_flag = false) THEN
 22          v_pessoas := v_pessoas || To_char(v_pess_cod_pessoa);
 23          v_flag := true;
 24        ELSE
 25          v_pessoas := v_pessoas || ',' ||To_char(v_pess_cod_pessoa);
 26        END IF;
 27      END LOOP;
 28      IF (v_flag = true) THEN
 29        v_pessoas := v_pessoas ||')';
 30      END IF;
 31      dbms_sql.Close_cursor(v_cursor);
 32  END;
 33  /

PL/SQL procedure successfully completed.

SQL>