Percent wildcard in dynamic statement doesnt work ??

From: Pierre H Pero <pepper_at_breedlove.ecn.purdue.edu>
Date: 12 Aug 1999 15:11:45 GMT
Message-ID: <7ouo7h$o8g$1@mozo.cc.purdue.edu>

I am new to oracle,sql,databases and am writing a C program to access a database. I can not get the % wildcard to work in a dynamic statement.

The following is a statement I am trying to prepare and execute

CREATE TABLE ECE_TEMP_1 AS SELECT * FROM ECE_INVENTORY WHERE UPPER( NAME ) LIKE 'L%TAL' ESCAPE '^' but it doesnt work.
In the database there is a row with the name lytal. There is no other rows with a name at all similar to lytal. below are some variations of the like clause and if they work or not.

 WHERE UPPER( NAME ) LIKE 'LYTAL'              works .
 WHERE UPPER( NAME ) LIKE 'L_TAL'              works .
 WHERE UPPER( NAME ) LIKE 'L%%YTAL'            works .
 WHERE UPPER( NAME ) LIKE 'LYTAL%%'            works .
 WHERE UPPER( NAME ) LIKE 'L%YYTAL'            works !  there is no  lyytal
 WHERE UPPER( NAME ) LIKE 'L%_YTAL'            works !  there is no  l*ytal
 WHERE UPPER( NAME ) LIKE 'L%YTAL'             doesnt work
 WHERE UPPER( NAME ) LIKE 'LYTAL%'             doesnt work and error messages.

The percent sign is not being used as a wildcard but I am left with the feeling that the % is at somepoint being used as an escape to ignore the following character.

If I run sql directly the % wildcard works as I expect it should.

Does anyone have some clues for the lost ??

Pierre Pero
pepper_at_ecn.purdue.edu Received on Thu Aug 12 1999 - 10:11:45 CDT