Re: Simple Question regarding PL-SQL and Cursors, with a FORMS twist.

Hi Michael Dean

> When using an explicit cursor tied to a select statement, is the select run
> automatically, or does it wait until you OPEN the cursor?

Until you Open it. In fact at this point it is only parsed.Collecting real data starts when you do the first fetch.

> Reason for my question.......
> I am building a form related to personnel. The first field is Social
> Security Number. In the "when-validate-item" trigger, I want to check to
> see if the current SSN exists in the table. If not, the user continues
> adding a new person. If a single value exits, I want to populate the
> remaining fields with that record. If more than one of the current SSN
> exists (hopefully not often) I want a list of values to come up with those
> records having that SSN, thus allowing the user to choose one to populate
> the remaing fields with.
>
> My idea is to user cursor(s), and an IF-THEN block. If rowcount for the
> cursor = 0 then enter new person, if rowcount = 1 then fetch the record, if
> rowcount >1 allow user to select with LOV.
>
> Is a cursor the best way to implement this functionality? Is there another
> way? I am concerned with running a select statement inefficiently, thus my
> question of when does the select statement run.
>
> Any suggestions would be greatly appreciated.
>

Dear Michael :
I think you should consider some basic concepts implemented in Forms. First of all : Think about difference between QUERY - MODE and NORMAL mode.

In a usual forms application Oracle would expect the user to do the following :

First press F7 ( or whatever key is defined for enter-query-mode) which switches
the form into a mode where user can give an example of what she or he likes to find.
In your case giving some or all of the the social security number ( depeding on type ... )
with or without wildcards. For instance '%1998%' to get all socialids which have the string 1998 anywhere.

After that user press F8 ( key for execute-query ) to start the search against database.
At this point form build up the query dynamically and sends it to the server. That is building
a cursor loop for the dynamically defined statement. In your case this statement would be something like this : select social_id, firstname, lastname, birthdate , .... from personnel where socialid like :PERSONELLBLOCK.SOCIALID ; Now there are two possible events :

A.) The query finds no entry for the given socialid : In this case Form stays in ENTER-QUERY-MODE, allowing the user to quickly requery with some other crieria. Maybe ( or more likely ) your user will try to find a person by name rather than by social id.
If there is really no person for the given socialid in the database and the user has to add it , she or he has to leave ENTER-QUERY-MODE ( pressing CTL-Q or whatever key ... ) which resets the form. It is then ready for inserting new records. That is the user simply fills out the form and presses COMMIT.

B.) One or more personens satisfy the query criteria. In this case FORMS automatically exits ENTER-QUERY-MODE and displays the first record ( or records if you have a multirecord block) in the form. Now the user can browser through the recordlist and see if the wanted person is among the retrieved persons.
If so can then add or update fields for that person If not so : Either ENTER-QUERY for new search or CLEAR-BLOCK -key or INSERT-RECORD - Key to create a new record in the block which is stored in the database when user presses commit.

So finally my suggestion is :
A.) FORMS provide you with all the functionallity your think you have to do by your own.
B.) read : GETTING STARTED with ORACLE RUN FORMS - USER - Manual to learn about FORMS concepts.

     ( I mean the manual for FORMS Users not FORMS programers).

FORGET about when validate item trigger at that stage. Validate item trigger is to validate if the syntax of the social id is correct. For instance if the socialId should have a special format like 'DD.MM.YYYY/09999' you could check that in when validate item trigger. But also for this problem can be handled by default form functionallity. You can set up a FORMAT MASK for the SOCIALID item and this MASK is then check whenever forms validates the item.

C.) It is a well known behaviour of Form beginners that she or he will try to code , catch and program each and everything because they think they have to.

     But instead you should read and learn more about default functionallity forms provides for you.

Please do not take the above as critisism but to help you not to make the same mistakes I did.
HTH.
Regards Robo.

Received on Fri Jun 26 1998 - 13:45:22 CDT