Home » Developer & Programmer » Forms » Variables in single quotation signs (Oracle forms 6i, WindowXP)
Variables in single quotation signs [message #421514] Wed, 09 September 2009 00:01 Go to next message
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

Re: Variables in single quotation signs [message #421542 is a reply to message #421514] Wed, 09 September 2009 02:38 Go to previous message
Littlefoot
Messages: 20901
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.
Previous Topic: Update Data in a view
Next Topic: Problem while installing Oracle forms 10g- Checking Swab Space
Goto Forum:
  


Current Time: Fri Dec 09 03:38:34 CST 2016

Total time taken to generate the page: 0.14161 seconds