Home » Developer & Programmer » Forms » Problem with dbms_sql and LIKE clause (Forms 6i)
Problem with dbms_sql and LIKE clause [message #441516] Mon, 01 February 2010 14:03 Go to next message
marceloy
Messages: 11
Registered: December 2009
Location: Brazil
Junior Member
Hi there,

I've got a problem with dbms_sql while trying to search for '%' strings and LIKE clause.

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


I'm getting ORA-06502 that says "numeric or value error string" and I'm sure it's about the % character, but I don't know why dbms_sql doesn't recognize it.


Any idea ?


[]'s


Re: Problem with dbms_sql and LIKE clause [message #441518 is a reply to message #441516] Mon, 01 February 2010 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
'SELECT eid FROM employee WHERE name LIKE 'ABC%';

3 quotes, hmmm, can't be good, doesn't it?
'SELECT eid FROM employee WHERE name LIKE ''ABC%''';

This will be surely better.

Regards
Michel
Re: Problem with dbms_sql and LIKE clause [message #441619 is a reply to message #441516] Tue, 02 February 2010 04:26 Go to previous messageGo to next message
marceloy
Messages: 11
Registered: December 2009
Location: Brazil
Junior Member
Thanks Michel,

but I just type it wrong here. Shocked
It's actually:

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


I even tried something like CHR(39) (for ') but the output is the same. I got the same error message.


I think it's about how dbms_sql treats it. What do you think?


[]'s

Re: Problem with dbms_sql and LIKE clause [message #441623 is a reply to message #441619] Tue, 02 February 2010 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think it's about how dbms_sql treats it. What do you think?

I think you are wrong but there is too few information on your actual in your post to know where it comes from. We don't even know if the error comes from the lines you posted.

Regards
Michel
Re: Problem with dbms_sql and LIKE clause [message #441624 is a reply to message #441516] Tue, 02 February 2010 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
First rule of debuging dynamic sql: use dbms_output or similar to display the dynamic string you've built before you try to execute it.
Re: Problem with dbms_sql and LIKE clause [message #441639 is a reply to message #441623] Tue, 02 February 2010 05:45 Go to previous messageGo to next message
marceloy
Messages: 11
Registered: December 2009
Location: Brazil
Junior Member
Quote:
We don't even know if the error comes from the lines you posted.


Michel, I tried to comment all the other parts of code to make sure the error comes from here. It seems to be there.


Quote:
First rule of debuging dynamic sql: use dbms_output or similar to display the dynamic string you've built before you try to execute it.


CookieMonster, Laughing, I use a kind of alert message to show the query right before calling it from dbms_sql, and it shows exactly:

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



At first, I thought it was a problem with '%' and dbms_sql query, and so I tried the following (as test):

SELECT eid FROM employee WHERE name LIKE 'ABC';

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


The first query worked fine as if LIKE was '='.
The second query too, but it didn't brought any results (sure).
Both queries runned without error.


So, I just get error on LIKE and '%';


[]'s
Re: Problem with dbms_sql and LIKE clause [message #441640 is a reply to message #441516] Tue, 02 February 2010 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd like to hope that's not what it's displaying, dynamic sql strings don't generally need semi-colons.

I'm not aware of any issue with like and dbms_sql but then I virtually never use dbms_sql. Can you do what you need to do with execute immediate?
Re: Problem with dbms_sql and LIKE clause [message #441641 is a reply to message #441640] Tue, 02 February 2010 06:03 Go to previous messageGo to next message
marceloy
Messages: 11
Registered: December 2009
Location: Brazil
Junior Member
I'm working with Forms 6i. I read somewhere that a can't use Execute Immediate with Forms 6i. It's the reason I'm trying dbms_sql.
Re: Problem with dbms_sql and LIKE clause [message #441643 is a reply to message #441639] Tue, 02 February 2010 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel, I tried to comment all the other parts of code to make sure the error comes from here. It seems to be there.

Post the (uncommented) code and other necessary objects so we are able to reproduce it.

Regards
Michel
Re: Problem with dbms_sql and LIKE clause [message #441644 is a reply to message #441641] Tue, 02 February 2010 06:11 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
marceloy wrote on Tue, 02 February 2010 12:03
I'm working with Forms 6i. I read somewhere that a can't use Execute Immediate with Forms 6i. It's the reason I'm trying dbms_sql.


You can't directly no. but you might be able to do what you need to do in a database package (which'll run execute immediate quite happily) and call that from the form.

That said if you post everything like Michel asked we might be able to work out what's going on.
Re: Problem with dbms_sql and LIKE clause [message #441646 is a reply to message #441639] Tue, 02 February 2010 06:35 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
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





Is it like the above one what you are getting?

So As michel said ...please post the total method you are Using
So that we will help you.
sriram Smile

[Updated on: Tue, 02 February 2010 06:45]

Report message to a moderator

Re: Problem with dbms_sql and LIKE clause [message #441649 is a reply to message #441516] Tue, 02 February 2010 07:10 Go to previous messageGo to next message
marceloy
Messages: 11
Registered: December 2009
Location: Brazil
Junior Member
That's the non-commented code:

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;


Hope it helps

[]'s
Re: Problem with dbms_sql and LIKE clause [message #441654 is a reply to message #441516] Tue, 02 February 2010 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Next time post create table scripts along with inserts for some data.

I've tried it with some slight modifications. Used my own table:
CREATE TABLE test1 AS SELECT ROWNUM a, cast(ROWNUM AS varchar2(10)) b, SYSDATE c 
FROM dual CONNECT BY LEVEL < 10000;


Also changed :auin.dsp_pess_nom_pessoa into a local variable called dsp_pess_nom_pessoa and replaced the calls to Mostra and Msg_alert with calls to dbms_output.
Ran it in sqlplus and got this:
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> 


So works for me. Can you try it in sqlplus with your table?
Are you sure the results of your query will fit in a 250 character string? because that's what I would assume the problem really is.
Re: Problem with dbms_sql and LIKE clause [message #441655 is a reply to message #441649] Tue, 02 February 2010 07:30 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
what is Mostra and Msg_alert
Quote:
Post the (uncommented) code and other necessary objects so we are able to reproduce it.
Regards
Michel

As you did n`t specified the required one i will execute it in my own way...

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>


sriram Smile
Re: Problem with dbms_sql and LIKE clause [message #441656 is a reply to message #441655] Tue, 02 February 2010 07:32 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
oops i am late....!

sriram Smile
Re: Problem with dbms_sql and LIKE clause [message #441657 is a reply to message #441649] Tue, 02 February 2010 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Marceloy,

I understand that you have a problem with DBMS_SQL + LIKE. However, I didn't quite get what are you trying to do in Forms; perhaps there's a workaround, different from what you are doing now.

So, what should be the final result of the whole operation? How is Forms resultset supposed to look like?
Re: Problem with dbms_sql and LIKE clause [message #441661 is a reply to message #441516] Tue, 02 February 2010 07:51 Go to previous message
marceloy
Messages: 11
Registered: December 2009
Location: Brazil
Junior Member
GOTCHA ..

Quote:
Are you sure the results of your query will fit in a 250 character string? because that's what I would assume the problem really is.


It doesn't fit!! Embarassed Embarassed Embarassed

Sometimes the results are monstruous .. I put varchar2(30000) and it worked fine for most of the queries.


I'm really sorry for the troubles, I should have seen that.


Tonight, beer is on me Razz


[]'s
Previous Topic: about commit
Next Topic: user creation in form
Goto Forum:
  


Current Time: Tue Sep 27 16:06:46 CDT 2016

Total time taken to generate the page: 0.11685 seconds