Re: PROC: Dynamic SQL

> akumar_at_st6000.sct.edu (Ashwini Kumar) writes:
> Hi All,
> I have a Probelm with dynamic sql in PRO*C.
> the sql statement is like " select * from mytable where name = :f1".
> it works fine if the bind variable has all the N chars , where N
> is the length of the coulmn name. but it returns nothing if the bind
> variable has a value whose length is less than the column
> length. Any ideas?
>
> thanks a lot, ash.
>
>
>>>>

Your problem is probably the definition of the column NAME in the database. CHAR and VARCHAR are fixed length. They are padded with spaces to the declared length of the column. If this is the case, you need to change your where clause to:

where name = rpad(:f1, length(name))

Note that using a function on name will eliminate the use of any index on name.

Another alternative is to change the definition of the column to VARCHAR2. Note that this will not change any existing data on the databse. You will have to eliminate the trailing spaces yourself. You also need to consider the impact on any other programs in the application. They may expect the trailing spaces.

I am not sure why anyone uses anything but varchar2 for cahracter colums other than those that are only one position long.

Cheers,

Paul Smith
psmith_at_sprynet.co.uk Received on Sun Jul 21 1996 - 00:00:00 CEST