Home » Developer & Programmer » Forms » Changing default order by and where of a block based on stored Proc
icon4.gif  Changing default order by and where of a block based on stored Proc [message #197941] Fri, 13 October 2006 06:30 Go to next message
yogen
Messages: 39
Registered: October 2006
Location: UK
Member
Hi All,

I am using Oracle forms 6i.I have based a block on a store procedure (for which i have created a Package having 5 procdures to select ,update,delete ,insert and lock)
it works fine but my problem is that of sorting the data at run time based on user's click on the field.

i am able to do it with forms which are based on table and views.But here my Order by property of the block is not changing at run time.

Second try i did was i passed the sorting order (fields in the sequence to be considered for order by) to the select procedures and tried to use dynamic sql to prepare the query for opening ref cursor at run time in the select procedure of the package.
Procedure compiles fine but at the time of querieng the form it results in error.
Help me if you know the answer.
Any suggestions are also most welcome
Thanks in Advance.

[Updated on: Fri, 13 October 2006 06:59]

Report message to a moderator

Re: Changing default order by and where of a block based on stored Proc [message #198158 is a reply to message #197941] Sun, 15 October 2006 21:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What was the error code and error message?

David
Re: Changing default order by and where of a block based on stored Proc [message #198221 is a reply to message #198158] Mon, 16 October 2006 04:01 Go to previous message
yogen
Messages: 39
Registered: October 2006
Location: UK
Member
Thanks for the reply and interest.
Now that problem is solved.following is the query now i am using in slct procedure of my package on which my form is based.

open resultset for
'select aa, '
||' ab ,'
||' ac, '
||' ad,'
||' ae, '
||' af, '
||' ae, '
||' ag, '
||' Y_N_FLAG '
||' from SDB_CUSTOMER_TECHCONFIG_FORM a '
|| ' where a.ax = '
||' '''|| User_input||''''||
' order by ' ||' '||User_order_by;



where User_order_by and User_input are the values which will be formed as a string when user clicks on some of the fields on the screen.So my doubt of using Dynamic SQL in a procedure for a oracle forms is solved.The Problem in basing a block in forms on a procedure is that if the select procedure result in exception or error it will not show any error message and data on the screen.so for that we need to check the select Procedure.

[Updated on: Mon, 16 October 2006 04:04]

Report message to a moderator

Previous Topic: few questions in d2k
Next Topic: Destroying Text files after use
Goto Forum:
  


Current Time: Sun Dec 04 20:40:18 CST 2016

Total time taken to generate the page: 0.08049 seconds