Home » Developer & Programmer » Forms » dynamic query (how to write a dynamic query)
dynamic query [message #295109] Mon, 21 January 2008 06:25 Go to next message
timor
Messages: 12
Registered: December 2007
Location: egypt
Junior Member
dear all
i need to make a dynamic query
to decide the conditions dynamic
i did it on the single query
but i want to do it at a multi row return query
something like cursor and we manipulate the data from it
i tried to use ref cursor but it doesn't work
i hope to find it
bye
Re: dynamic query [message #295140 is a reply to message #295109] Mon, 21 January 2008 07:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use EXECUTE IMMEDIATE with BULK COLLECT.

By the way, which version of Oracle Forms you are using?

By
Vamsi
Re: dynamic query [message #295151 is a reply to message #295109] Mon, 21 January 2008 08:26 Go to previous messageGo to next message
timor
Messages: 12
Registered: December 2007
Location: egypt
Junior Member
i'm using oracle 6i
i want to do the following
write an sql statment
that return more than one row
and then manipulate the records retrived
i want to make a cursor for example
and put the following at the query
where Mad ;
which means that i want to decide the fields to search and the conditions i use
i hope to understand me
bye
Re: dynamic query [message #295152 is a reply to message #295109] Mon, 21 January 2008 08:27 Go to previous messageGo to next message
timor
Messages: 12
Registered: December 2007
Location: egypt
Junior Member
sorry where :any_variable;

at the last post
Re: dynamic query [message #295216 is a reply to message #295152] Mon, 21 January 2008 15:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you, perhaps, looking for the SET_BLOCK_PROPERTY built-in and its DEFAULT_WHERE property?
Re: dynamic query [message #295257 is a reply to message #295216] Mon, 21 January 2008 21:59 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Look at the following:
Populate block using cursor
http://www.orafaq.com/forum/m/605/67467/?srch=populate+block+cursor+create_record#msg_605
and my feelings about it.
http://www.orafaq.com/forum/m/134237/67467/?srch=populate+block+cursor+create_record#msg_134237

David
Re: dynamic query [message #295281 is a reply to message #295257] Tue, 22 January 2008 00:07 Go to previous messageGo to next message
akramrabaya
Messages: 41
Registered: November 2007
Member
/*** this is an example *****/

declare

cursor c1 is select emp_no,emp_name,salary from emplyoee
where salary >5000

v_emp_no number;
v_emp_name varchar2(50);
v_salary number;

begin

open c1;
loop
fetch c1 into v_emp_no,v_emp_name,v_salary
exit when c1% notfound;

update employee set
salary = salary * 1.05
where emp_no=v_emp_no;

insert into increment values(emp_no,emp_name,v_salary*1.05 - v_salary);

end loop;
close c1;
commit;
end;



/****** akram *******/
Re: dynamic query [message #295285 is a reply to message #295281] Tue, 22 January 2008 00:24 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True, this is an example, but isn't related to a problem and, besides, wouldn't work. You fetched values into 'v_' variables but never used them. Also, you've used bunch of PL/SQL statements for something that can be rewritten as
INSERT INTO increment 
  (emp_no, emp_name, salary)
  (SELECT emp_no, emp_name, salary * 0.05);
(if I correctly understood what you are trying to insert).
Previous Topic: Unzip file from froms then load.
Next Topic: How to change English date in to Arabic date (Hijri) .
Goto Forum:
  


Current Time: Sun Dec 11 04:35:33 CST 2016

Total time taken to generate the page: 0.08069 seconds