 Changing default order by and where of a block based on stored Proc [message #197941] |
Fri, 13 October 2006 06:30  |
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 #198221 is a reply to message #198158] |
Mon, 16 October 2006 04:01  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
