Home » Developer & Programmer » Forms » Help plss Should a cursor be created in database or program unit in forms like in a package
Help plss Should a cursor be created in database or program unit in forms like in a package [message #306046] Wed, 12 March 2008 11:46 Go to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Where can i create a cursor should it be in database i.e isql plus using internet explorer or should i create the cursor in forms

forms can i create the cursor so that i can use the cursor in the post item trigger of the userid item of login page

Can i create the cursor in a package specification and how can i call that cursor into a post_text item trigger

details below

Iam working on a Online exam for students Dummy project using forms

The student 1st comes to a WELCOME page i.e form which asks him if he is a new student or registered stud

If he clicks registered pushbutton it takes him to a LOGIN page where there are two items userid and password.I am using a Validate_item OR Post_item trigger to fire after the student enters his userid.

I need to compare the entered userid with the userid stored in the database STUDENTS table which i created which consists of the registered students details.

Our project Guide suggested that we create a cursor and fetch each userid everytime into the cursor and compare the entered userid with the cursor.
Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306070 is a reply to message #306046] Wed, 12 March 2008 13:41 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
Hi,

You can use cursors in forms as well as database.
If you have to validate i.e. check if the student is already
present in system or not, write cursor in trigger of forms.

You can use cursors in database also.

Identify the need/ the way you want to use cursors.

You can use such a cursor.
Cursor C_CHK_USER Is
Select 'X'
From Table_name
Where column_name (student_id) = value;

Regards,

Dhanuka. Rajesh

[Updated on: Wed, 12 March 2008 13:43]

Report message to a moderator

Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306079 is a reply to message #306046] Wed, 12 March 2008 14:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In my opinion, use of a cursor is stupid (in this case). It will ALWAYS be a single user (student) who will enter his/her credentials which will have to be verified.

So, why would you want to use
FOR cur_s IN (SELECT stud_username, stud_password FROM students)
LOOP
  IF cur_s.stud_username <> :login_form.username THEN
     NULL;
  ELSE
     IF cur_s.stud_password <> :login_form.password THEN
        do something;
     ELSE
        do something different
     END IF;
  END IF;
END LOOP;
when, obviously, this can be done simply as
SELECT stud_password INTO l_variable 
FROM students 
WHERE stud_username = :login_form.username;

IF l_variable <> :login_form.password THEN
   do something;
...
I hope you do plan to make sure that student IDs (actually, 'stud_usernames') are UNIQUE!
Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306083 is a reply to message #306070] Wed, 12 March 2008 14:41 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Thanks a lot for the reply

So your saying o can create the cursor directly in the

when_validate_item trigger

cursor c_chk_user is
select user_id
from students(table)

if c_chk_user = userid(item name

where student_id in database = entered value

if c_chk_user = entered value then go_item('password')
else raise form_trigger_failure or message ('user does not exist');

thanks for your time.


Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306085 is a reply to message #306083] Wed, 12 March 2008 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No.

I am saying NOT to use cursor at all.
Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306088 is a reply to message #306079] Wed, 12 March 2008 14:59 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Bravo Littlefoot Bravo thanks for the reply and good alternate examples

Iam just trying to understand the second option you gave

SELECT stud_password INTO l_variable
FROM students
WHERE stud_username = :login_form.username;

IF l_variable <> :login_form.password THEN
do something;
...

your saying to write this in the trigger of form validation

or item validation trigger

i guess its form

so select stud_password into 1_var
from students so far so good
where stud_username from database = :login_form.username- entered value ok so if the entered userid is wrong then it will return nothing right i mean can i make it to display some sort of message like user does npt exist

if the entered user is existing then you are checking for password

If 1_variable isnotequalto :login_form.password the entered password
go_form('exam_details')

then it needs to go to the next_form
Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306172 is a reply to message #306088] Thu, 13 March 2008 02:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sqlstar_student

so select stud_password into 1_var
from students so far so good
where stud_username from database = :login_form.username- entered value ok

so if the entered userid is wrong then it will return nothing right i mean can i make it to display some sort of message like user does npt exist


Right; SELECT will return nothing if there's no such "username" in a table. It will raise unhandled NO-DATA-FOUND exception which you will have to take care of. Normally, it is done by writing an exception handler section, such as
BEGIN
  SELECT ...

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    message/or alert (inform student that invalid username was given)
    RAISE FORM_TRIGGER_FAILURE;
END;

[Updated on: Thu, 13 March 2008 02:28]

Report message to a moderator

Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306323 is a reply to message #306172] Thu, 13 March 2008 11:59 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Thanks a lot the code was great to understand i will put it to test immediately

I am using the code in a when_validate_form level/POST_form trigger as the code is checking both userid and password at once after i enter both userid and password

should i create some button at the end like NEXT and code a when_button_pressed trigger in that

should i use the code i think it will be better in when_button_pressed right?

I mean when_button_pressed it checks and if its succesful it will open/go to next form i.e the students details once he log in.

Thanks a lot for putting time to read and answer.
Re: Help plss Should a cursor be created in database or program unit in forms like in a package [message #306355 is a reply to message #306323] Thu, 13 March 2008 18:46 Go to previous message
solisdeveloper
Messages: 48
Registered: March 2008
Location: Mexico
Member
I've read that it's actualy better to place all your pl-sql and sql code in packages, stored procedures or triggers within the database server and all your form-oriented code within the Form.

This allows you to avoid network overload created by sending huge blocks of pl-sql code or large sql statements to the server. And you also get a bonus wich is the re-usability of the code.

And i totaly agree with LittleFood, you should not use an explicit Cursor-For-Loop if you are only retreiving a single record, you should instead use an implicit one (Select username INTO... From...)

Well at least this is how i've been working and so far it's been doing great! Smile

for more info check the book "Oracle Developer advanced forms & reports" from Osborne editions.

[Updated on: Thu, 13 March 2008 18:49]

Report message to a moderator

Previous Topic: Need help with first record in a block
Next Topic: Copy values from header to line in multiblock records
Goto Forum:
  


Current Time: Fri Dec 09 07:53:57 CST 2016

Total time taken to generate the page: 0.11244 seconds