Home » Developer & Programmer » Forms » Attn: Oracle Forms Developers (Oracle Forms v?)
Attn: Oracle Forms Developers [message #376612] Thu, 18 December 2008 00:50 Go to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Can somebody pls. give me the syntax for writing Oracle Query Forms. I've done it before but forgotten the syntax.

The idea is to FORM a statement by APPENDING the search items on the Form & substituting in the WHERE clause.

This way, we don't have to write many IF statments.

For example, if user wants to query by NAME & COUNTRY, then the statement >> Block:Name ||&|| Block:Country
Where clause: Block:Name ||&|| Block:Country

Thanks - Mave

Re: Attn: Oracle Forms Developers [message #376618 is a reply to message #376612] Thu, 18 December 2008 01:01 Go to previous messageGo to next message
Supernova11
Messages: 12
Registered: December 2008
Location: Indonesia
Junior Member
do you mean change where clause in the data_block or in the program unit??

Re: Attn: Oracle Forms Developers [message #376629 is a reply to message #376618] Thu, 18 December 2008 01:36 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
program unit

b'coz a Button will call the Program unit
Re: Attn: Oracle Forms Developers [message #376678 is a reply to message #376629] Thu, 18 December 2008 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why would you bother? If the block is a data block (based on a table or a view, for example), all items are visible and accessible. By default, they are queryable so - you don't have to do anything: user presses the ENTER-QUERY key, enters (as you've put it) "name" and "country" values he/she is interested in and - finally - presses the EXECUTE-QUERY key/button. That's all.

So - where's the point in programming something that is for a long, long time built-in functionality?
Re: Attn: Oracle Forms Developers [message #376682 is a reply to message #376678] Thu, 18 December 2008 04:42 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
The items are CONTROL BLOCK items (non-base table.)

Queried records are based on TABLE - hence Query Forms.

So if the CONTRL BLOCK has 5 items, program unit should APPEND the items that need to be queried.

If user wants to query using 3 items >> APPEND statement using those 3 items & substitute in WHERE Clause

If user wants to query using 2 items >> APPEND statement using those 2 items & substitute in WHERE Clause

Catch the drift ?
I have written such forms but forgotten the SYNTAX

Mave
Re: Attn: Oracle Forms Developers [message #376716 is a reply to message #376682] Thu, 18 December 2008 06:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ok, I clearly missed the point. Very Happy I'll reply here instead of at the Oracle forums since they are experiencing some troubles

This is what I understand:
  1. you have a control block with search criteria and a push button "search"
  2. when you push the button all the values from these control block items are passed to a program unit
  3. this program unit should interpret these values and add them to the where clause of a cursor (or is it a block - still not clear, I'm afraid ?Neutral).


But you are confused about how to add these where clauses in a simple way and with as little code as possible. I think a NVL could do the trick:
SELECT <columns>
FROM   <table>
WHERE  db_column1 = NVL(:controlblock.field1, db_column1)
AND    db_column2 = NVL(:controlblock.field2, db_column2);


By using a NVL, empty criteria will be ignored. Here's a small code sample to illustrate:
DECLARE
   CURSOR mysearch (criterium1 IN VARCHAR2, criterium2 IN VARCHAR2)
   IS
      WITH mytable AS
           (SELECT 'Jim' fname, 'Boston' locat
            FROM   DUAL
            UNION ALL
            SELECT 'Jenny' fname, 'Tucson' locat
            FROM   DUAL
            UNION ALL
            SELECT 'Frank' fname, 'Bristol' locat
            FROM   DUAL
            UNION ALL
            SELECT 'Patrick' fname, 'Boston' locat
            FROM   DUAL
            UNION ALL
            SELECT 'Susan' fname, 'Houston' locat
            FROM   DUAL)
      SELECT   *
      FROM     mytable
      WHERE    fname = NVL (criterium1, fname)
      AND      locat = NVL (criterium2, locat)
      ORDER BY fname;
BEGIN
   DBMS_OUTPUT.put_line ('show all: ');

   FOR rec1 IN mysearch (NULL, NULL)
   LOOP
      DBMS_OUTPUT.put_line (rec1.fname || ' is located in ' || rec1.locat);
   END LOOP;

   DBMS_OUTPUT.put_line ('show Boston only: ');

   FOR rec2 IN mysearch (NULL, 'Boston')
   LOOP
      DBMS_OUTPUT.put_line (rec2.fname || ' is located in ' || rec2.locat);
   END LOOP;

   DBMS_OUTPUT.put_line ('show Susan:');

   FOR rec3 IN mysearch ('Susan', NULL)
   LOOP
      DBMS_OUTPUT.put_line (rec3.fname || ' is located in ' || rec3.locat);
   END LOOP;
END;


PS: Sorry if my assumptions are incorrect (again).

MHE

[EDITED by LF: fixed invalid [list] tags]

[Updated on: Thu, 18 December 2008 06:52] by Moderator

Report message to a moderator

Re: Attn: Oracle Forms Developers [message #376722 is a reply to message #376716] Thu, 18 December 2008 06:38 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Thanks for the code, but it does'nt need to get as complicated as this !

The code to achieve this is very, very simple. I know it is b'coz i've done it but can't remember.

Your code DOES NOT incorporate the use of WHERE CLAUSE of the BLOCK.

Assuming there are 5 items on the Control Block.
User wants to search using ONLY 2 items on the block say Name & Country.

In the program unit:

Create a statement by APPENDING Name & Country. If i can remember>

variable := :Control_Block.Name ||&|| :Control_Block.Country

Now we use the WHERE CLAUSE utility of BASE Table block & assign variable to it
Assign: Where Clause := variable

Now the block knows how to restrict search query b'coz we have assigned 2 items i.e Name & Country

SIMPLE AS THIS, but can't remember the exact syntax..

This approach does really work...

There's absolutely no need for any Cursors or Loops

Mave
Re: Attn: Oracle Forms Developers [message #376732 is a reply to message #376722] Thu, 18 December 2008 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you talking about SET_BLOCK_PROPERTY(DEFAULT_WHERE)?
Re: Attn: Oracle Forms Developers [message #376735 is a reply to message #376732] Thu, 18 December 2008 07:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Or, you could assign the values of the control block's items to the corresponding base table items in the pre-query and execute the query.

MHE
Re: Attn: Oracle Forms Developers [message #376740 is a reply to message #376732] Thu, 18 December 2008 07:26 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Littlefoot wrote on Thu, 18 December 2008 06:53
Are you talking about SET_BLOCK_PROPERTY(DEFAULT_WHERE)?


PRECISELY !!!
Re: Attn: Oracle Forms Developers [message #376742 is a reply to message #376740] Thu, 18 December 2008 07:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
D'oh! Are you serious? That's just classic: I missed the obvious. Do you have enough information?

MHE
Re: Attn: Oracle Forms Developers [message #376745 is a reply to message #376742] Thu, 18 December 2008 07:47 Go to previous message
Maverick27
Messages: 84
Registered: October 2008
Member
Maaher wrote on Thu, 18 December 2008 07:35
D'oh! Are you serious? That's just classic: I missed the obvious. Do you have enough information?

MHE


Good- now we're on the same wavelenght !!

1. Assign to a variable the items (from control block)
for query using concatenation symbols

2. Put variable in SET BLOCK PROPERTY

3. Assign program unit to Search button

If u can get it to work, pls. advise.

Thanks Folks.

Previous Topic: how can i import data from txt file using sqlldr can any one help me
Next Topic: call with button from report A to B
Goto Forum:
  


Current Time: Thu Dec 08 10:18:45 CST 2016

Total time taken to generate the page: 0.12618 seconds