OraFAQ Forum: Forms » Variables in single quotation signs

Home » Developer & Programmer » Forms » Variables in single quotation signs (Oracle forms 6i, WindowXP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Variables in single quotation signs [message #421514]

Wed, 09 September 2009 00:01

sadiq106
Messages: 22
Registered: February 2009

Junior Member

I need guidance to use different type variables (date, string and number) in single quotation signs. for example I want use these in querying a block.

declare
v_date date := '01-aug-09';
v_number number := 4;
v_name varchar2(100) := 'abcd'

begin

set_block_property('XYZ',default_where, 'edate = v_date and id = v_number and name = v_name';

execute_query;

end;

it returns nothing; please guide.

thanks in advance

sadiq

Report message to a moderator

Re: Variables in single quotation signs [message #421542 is a reply to message #421514]

Wed, 09 September 2009 02:38

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

First of all,

v_date date := '01-aug-09';

is a wrong way to do that. You have said that "v_date" is a variable whose datatype is "DATE" - fine so far. But why did you put a string (which '01-aug-09' is) into a DATE datatype variable? You are forcing Oracle to perform implicit datatype conversion which may, but doesn't have to be successful. When dealing with dates, always use TO_DATE function:

v_date date := to_date('01-aug-09', 'dd-mon-yy');

Furthermore, DEFAULT_WHERE has been created as a string so, once it gets to the execution phase, query looks like this:

WHERE edate = v_date and id = v_number and name = v_name

which is wrong. What you really want is

WHERE edate = to_date('01-aug-09', 'dd.mm.yyyy')
  AND id = 4
  AND name = 'abcd'

In order to do that, you'll have to use concatenation when setting the DEFAULT_WHERE clause. Something like this:

'edate ' || v_date ' and id  = ' v_number

.

Perhaps it would be a good idea to create this clause as a variable which can be displayed by MESSAGE built-in, so that you'd see what you have created and then, once you are satisfied with the result, put it into the DEFAULT_WHERE.

Report message to a moderator

Previous Topic:	Update Data in a view
Next Topic:	Problem while installing Oracle forms 10g- Checking Swab Space

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:24:19 CDT 2024