sort display by clicking head of column in forms

[hudo]

Is there a possibility in Forms to sort the table/display records by clicking
on the head of a certain column (maybe a PushButton) as it is possible in many Microsoft Software Products as Outlook, ...

 

[hudo]

Hello,
the display/output is based on a stored procedure, so I created another
TextItem called SORT_ORDER

By pushing on the PushButton over a column I set the value of the TextItem
SORT_ORDER to a certain value, e.g :SORT_BLOCK.SORT_ORDER := 'ENAME DESC'.
MYSORTORDER corresponds to :SORT_BLOCK.SORT_ORDER and is a input Parameter
for the stored procedure

PACKAGE BODY EMP_TOOLS
AS

PROCEDURE display_emp (Resultset IN OUT EMP_CURSOR, MYSORTORDER VARCHAR2)
IS

l_sortorder VARCHAR2(20);
l_sql_stmt VARCHAR2(2000);


BEGIN

-- check innput parameters
IF ( MYSORTORDER IS NULL ) THEN
l_sortorder := 'EMPNO';
ELSE
l_sortorder := MYSORTORDER;
END IF;

-- create dynamic query
l_sql_stmt := 
'SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
WHERE 1=1
ORDER BY  '||l_sortorder;


OPEN Resultset

FOR

SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
WHERE 1=1
ORDER BY  MYSORTORDER;


--- alternative: Resultset with dynamic query
--OPEN Resultset

--FOR  l_sql_stmt;



EXCEPTION
WHEN VALUE_ERROR THEN
NULL;
WHEN OTHERS THEN
NULL;


END display_emp;

END EMP_TOOLS;

With the static variante --> ORDER BY MYSORTORDER; the ORDER BY clause is simple ignored.


If I try now the dynamic variante , occurs the error PLS-00455 : no dynamic statement in CURSOR

How can this problem be solved ? Exists a workaround ?

 

[sem]

Is using stored procedure a part of your task or a part of attemt to solve it? By clicking specific column header you may change ORDER_BY block propery and use :system.last query to repeat last query with a new sort order. As for your code, it contains an obvious error: you should pass ORDER BY column lexically or otherwice you're sorting by fixed value thus no sorting is occured. So I can not understand why you build more or less correct query and then open cursor for another incorrect one. Another question is why should you specify dummy 1=1 where clause instead of omitting it. Is that make any sence?

Regards, Dima

http://seminihin.narod.ru

 

[hudo]

Hello sem,

my example with scott and emp is not the task itself, I used it as example because "everyone" knows the table scott.emp.
The WHERE 1=1 could be omitted here, its just a placeholder.
The stored procedure is part of the task.
What do you mean by ...pass ORDER BY column lexically ... ?

I got a WHEN-BUTTON-PRESSED Trigger:

SET_BLOCK_PROPERTY('EMP_BLOCK',ORDER_BY,'ENAME DESC');
EXECUTE_QUERYSYSTEM.LAST_QUERY);

Then the error occurs: FRM-40738: Argument 1 to builtin EXECUTE_QUERY cannot be null.

How do I use System.Last_query correctly ?

 

[sem]

:SYSTEM.LAST_QUERY may be used if your block is based on query rather that procedure, thus forget about it. The main issue with your procedure has been mentioned above: you build a query

l_sql_stmt := 
'SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
WHERE 1=1
ORDER BY  '||l_sortorder;

but then, due to some unknown reason open cursor for completely another statement

OPEN Resultset

FOR

SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
WHERE 1=1
ORDER BY  MYSORTORDER;

Note that the value of MYSORTORDER is evaluated during binding, thus you're opening a cursor for something like

OPEN Resultset

FOR

SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
WHERE 1=1
ORDER BY 'EMPNO';

I suggest you to replace your statement by

OPEN Resultset
FOR l_sql_stmt;

Implementing query by example is expected to be your next challenge. Is it really necessary to use stored procedure?

Regards, Dima

http://seminihin.narod.ru

 

[hudo]

Hello,

OPEN Resultset
FOR
SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
FROM EMP
ORDER BY  MYSORTORDER;

The above code is not a completely new statement.
Either I use the above code; than the ORDER BY clause is not evaluated,
thats my problem, thats why I started with building the l_sql_stmt.

Or I comment the above code:

--OPEN Resultset

--FOR

--SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME , EMP.SAL SAL
--FROM EMP
--WHERE 1=1
--ORDER BY  MYSORTORDER;

and use

OPEN Resultset
FOR l_sql_stmt;

which results as mentioned already in error: PLS-00455

What else possibility than a stored procedure exists, if the output is based
on a more complex SELECT-statement and depends on many "Input Parameters" e.g.
the time-range is divided into for fields: "startdate" (day, month, year),
starttime (hour and minutes) stopdate, stoptime. These dates are bond in the procedure
to form two complete dates (minute,hour,day,month,year).
There is also a lot of branching with IF and ELSE depending on the values of the
input parameters.

Maybe you can show me alternatives ?

 

[sem]

What Oracle version do you use? Is it old enough to know nothing about opening REF CURSOR for string literal?
Your statement definitely does not what you want and I suppose I've explained the difference: the value of variable is evaluated during bind phase, while the ORDER BY clause should be here at parse time. Sorting by constant value makes no sense at all thus you obtain an error. If your Oracle is really too old, you may write some kind of switch, say

..
if upper(MYSORTORDER) = 'EMPNO' then
 OPEN Resultset
 FOR
 SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME ,EMP.SAL SAL
 FROM EMP
 ORDER BY EMPNO;
elsif upper(MYSORTORDER) = 'DEPTNO' then
 OPEN Resultset
 FOR
 SELECT EMP.EMPNO EMPNO, EMP.ENAME ENAME ,EMP.SAL SAL
 FROM EMP
 ORDER BY DEPTNO;
....

But in this case you should list all possible options.

I may also suggest to manage your query on client side, e.g. dynamically change DEFAULT_WHERE/ORDER_BY block properties for very old Forms versions or use "Query Data Source Type" = "From clause query" and dynamically set QUERY_DATA_SOURCE_NAME for more modern ones.

Regards, Dima

http://seminihin.narod.ru

 

[hudo]

Hello,
on my developer machine there is database 9.2.0.1.0 installed.
OS is Windows XP, I use Developer Suite 10g 9.0.4.0.1
The end-user uses database 8.1.7.2 and AS 10.
The (real) SELECT-statement has about 40 lines of code, I got already 8 IF ELSE branches.
There are about 15 columns displayed as a result from the stored procedure.
So I have a total of 120 branches with at least 4800 lines of code.
If I'd like to sort "ENAME DESC" and "ENAME ASC" everything doubles...

What are the size limits of a package/procedure in Oracle ? How do I split it up ?

...
IF -branch
...
-- near to limit of package size
ELSE exec myprocedure_2( RESULTSET IN OUT MYCURSOR, PARAM_1 VARCHAR2, ... , PARAM_2 DATE);

???

I tried already on the client side to set the "ORDER BY Clause in the Block-Property Palette.
I also tried to set it in the calling program unit "call_datesearch":

SET_BLOCK_PROPERTY('EMP_BLOCK', ORDER_BY, 'ENAME ASC');
GO_BLOCK('EMP_BLOCK');
CLEAR_BLOCK(No_Validate);
--- here the stored procedure is started
EXECUTE_QUERY;

I also tried it with a PRE-QUERY Trigger, but this failed also.

Is it old enough to know nothing about opening REF CURSOR for string literal?
What do you intend to say ?

I just know the "OPEN RESULTSET FOR" syntax for using a stored procedure in forms.
Are there any other ways ? I'm looking forward to new solutions.

 

[sem]

How is your EMP_CURSOR type declared? Is it weak or strong? If you fix a type of record returned by cursor you can not open it for and arbitrary statement. To be opened for arbitrary string literal it must be declared as weak REF CURSOR. Older database versions ( didn't support such construct at all. Though in case of week cursor it's impossible to use Forms wizard because it can't get any information about expected data structure. The workaround is to declare cursor as strong, create block using wizard, then edit declaration to change strong to weak and after all edit procedure that opens it. Alternatively you may build your block manually.

I still can not understand your task. If you need only to change sort order dynamically, there's no need to base your block on stored procedure, especially when you're not familiar with the technology: base it on query or table or view.

Regards, Dima

http://seminihin.narod.ru

 

[hudo]

Thats the declaration of the EMP_CURSOR:

PACKAGE EMP_TOOLS IS

TYPE EMP_REC IS RECORD(
EMPNO EMP.EMPNO%TYPE,
ENAME EMP.ENAME%TYPE,
SAL  EMP.SAL%TYPE
);

TYPE EMP_CURSOR IS REF CURSOR RETURN EMP_REC;

PROCEDURE display_emp (Resultset IN OUT EMP_CURSOR, MYSORTORDER VARCHAR2);


END EMP_TOOLS;

I thought I gave you an idea why query is not implemented directly in forms. A table with all the requested data does not exist (lot of aggregat functions), the branching is also too big to implement it directly in forms. The only alternative (I know) would be creating a view, querying this view, but this is not allowed.
Greetings

 

[sem]

I can't see any difference (besides slightly increasing network traffic) if you replace your server procedure opening cursors for different queries depending on parameter(s) being passed by client procedure, changing QUERY_DATA_SOURCE_NAME block property appropriately. Note that this property may hold not only a name of some object like table or view, but also a whole SELECT statement.
I suppose I've explained you how to resolve the issue if you still insist on using stored procedure: after creating Forms block you should remove RETURN EMP_REC from type declaration and then replace your opening statement by

OPEN Resultset
FOR  l_sql_stmt;

Regards, Dima

http://seminihin.narod.ru