Home » Developer & Programmer » Forms » ora 01403 (10g)
ora 01403 [message #333517] Sat, 12 July 2008 01:17 Go to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi ,
I'm trying to create a form for user privileges
with two tables :
/*
users & passwords
*/
CREATE TABLE  user_prv (
empno NUMBER (4) UNIQUE ,
pword VARCHAR2(100),
CONSTRAINT user_emp_fk FOREIGN KEY (empno) REFERENCES emp(empno)
);
 -----------
/*
 users' forms privileges 
*/
CREATE TABLE  form_prv (
empno NUMBER(4) ,
frm_no NUMBER(4),
frm_name VARCHAR2 (200) ,
frm_stat VARCHAR2(3),
CONSTRAINT empno_user_fk FOREIGN KEY (empno) REFERENCES user_prv (empno)
);

at WHEN-NEW-RECORD-INSTANCE I use the following code to set a button enabled or not :
DECLARE
   v_frmno   NUMBER;
BEGIN
------------------------------------>>>>>>>
   SELECT DISTINCT empno
              INTO v_frmno
              FROM form_prv
             WHERE form_prv.empno = :user_prv.empno
               AND form_prv.frm_no IS NOT NULL;
------------------------------------>>>>>>>
   IF :user_prv.empno = v_frmno
   THEN
      SET_ITEM_PROPERTY ('Pb_new', enabled, property_false);
   ELSE
      SET_ITEM_PROPERTY ('Pb_new', enabled, property_true);
   END IF;
END;


My question is how to get only one value for previous select statement.

thanks

[Updated on: Sat, 12 July 2008 01:20]

Report message to a moderator

Re: ora 01403 [message #333519 is a reply to message #333517] Sat, 12 July 2008 02:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Normally, if a select returns more rows than you wish/need, especially if the rows are different (and they seem to be, because you used distinct), you are missing a where-clause.
Since we don't have your data nor your logic, we cannot tell you what extra where condition is needed. Think for yourself: which one of the returned records do I want, and what do I base that on.
The answer to that is your missing where-clause.
Re: ora 01403 [message #333591 is a reply to message #333517] Sun, 13 July 2008 06:00 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi,
I'm trying to give access privilege for for each form depending
on the user previlege
table user_prv
==============
empno		pword
------  	------
1		**		
2		**	
3		**

table form_prv 
==============
empno  frm_no	frm_name     frm_stat
------	-----	--------    -----------
1	1	emp		yes
1	2	dept		yes	
1	3	bouns		no
2	1	emp		yes
2	2	dept		yes	
2	3	bouns		yes

./fa/4615/0/
in the given code & image for users 1 & 2 ( button create user will be disable, as they are already exist)
when it is user 3 ( button create user will be enabled)

my target is to tell the form at each record if this user (Master record)has detailed records or not?
thanks
  • Attachment: 1.JPG
    (Size: 24.31KB, Downloaded 387 times)
Re: ora 01403 [message #333599 is a reply to message #333591] Sun, 13 July 2008 08:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think I must be misinterpreting your problem. Here is how I understand what you are saying:

Why is it of interest whether the user has any details or not? Either the user exists, or the user does not exist.
If the user does not exist (i.e. in the parent table), the user must be created.
For a given user, you want to link that user to some form(s).
Either a specific user already is linked to a specific form, or not.

Now your query checks if there is a detail record present for the given user (regardless of which form the user is linked to). Then you say, that if you find such a link, you want to disable linking the user again. This implies that you have a (functional) one-to-one relationship between your users- and your forms-table.
Then how can there be more than one row returned?

I guess, it's back to my previous post: if you get more rows from a query than you expect/want, you have to add a where-clause.
Re: ora 01403 [message #333609 is a reply to message #333517] Sun, 13 July 2008 10:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
looking at it another way, consider the difference between these two code snippets and tell us if either of both is what you are thinking of?

BEGIN
------------------------------------>>>>>>>
   for r1 in (
              SELECT DISTINCT empno
              FROM form_prv
              WHERE form_prv.empno = :user_prv.empno
              AND form_prv.frm_no IS NOT NULL
             ) loop
------------------------------------>>>>>>>
      IF :user_prv.empno = r1.empno
      THEN
         SET_ITEM_PROPERTY ('Pb_new', enabled, property_false);
      ELSE
         SET_ITEM_PROPERTY ('Pb_new', enabled, property_true);
      END IF;
   end loop;
END;


DECLARE
   v_frmno   NUMBER;
BEGIN
------------------------------------>>>>>>>
   begin
      SELECT DISTINCT empno
                 INTO v_frmno
                 FROM form_prv
                WHERE form_prv.empno = :user_prv.empno
                  AND form_prv.frm_no IS NOT NULL
                  and rownum = 1
   exception
      when no_data_found then null;
   end;
------------------------------------>>>>>>>
   IF :user_prv.empno = v_frmno
   THEN
      SET_ITEM_PROPERTY ('Pb_new', enabled, property_false);
   ELSE
      SET_ITEM_PROPERTY ('Pb_new', enabled, property_true);
   END IF;
END;


These variations of your code will avoid the "TOO MANY ROWS" and "NO DATA FOUND" errors. Each does so in its own way. Neither may be correct for your needs, or both might work. We can't really tell yet, but you should be able to.

Good luck, Kevin
Re: ora 01403 [message #333620 is a reply to message #333609] Sun, 13 July 2008 12:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The first snippet will not enable the button, as the code will not iterate through the loop.

Furthermore, I stil am curious for the exact functionality required by the original poster, as I stated in my previous replies.

[Updated on: Sun, 13 July 2008 12:04]

Report message to a moderator

Re: ora 01403 [message #333682 is a reply to message #333609] Mon, 14 July 2008 03:00 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi,
I used
SELECT count( empno)
              INTO v_frmno
              FROM form_prv
             WHERE form_prv.empno = :user_prev.empno
               AND form_prv.frm_no IS NOT NULL;
   IF  v_frmno >0
THEN
      SET_ITEM_PROPERTY ('Pb_new', enabled, property_false);
.
.

also
Kevin Meade wrote on Sun, 13 July 2008 10:21
   begin
      SELECT DISTINCT empno
                 INTO v_frmno
                 FROM form_prv
                WHERE form_prv.empno = :user_prv.empno
                  AND form_prv.frm_no IS NOT NULL
                  and rownum = 1
   exception
      when no_data_found then null;
   end;

works exeactly.
thanks everybody

Re: ora 01403 [message #333848 is a reply to message #333517] Mon, 14 July 2008 10:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK

As long as you know what you are doing with it. I only offered these up for clarification, not as any replacement to Frank's comments.

Good luck, glad you are happy. Kevin
Re: ora 01403 [message #333915 is a reply to message #333517] Mon, 14 July 2008 20:08 Go to previous message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi
dear Kevin Meade
thanks for your concerning, actually your post
.
.
and rownum = 1
   exception
      when no_data_found then null;
   end;
.
.
clarify my way to get the answer
the point was (enable or disable) of a button is depending on the existence of data . So, " It doesn't matter the kind of data retrieved, as it is retrieved - of course - the data is exist"

thanks again Smile

[Updated on: Mon, 14 July 2008 20:19]

Report message to a moderator

Previous Topic: Get tab page name
Next Topic: problem with global value while inserting from calling form
Goto Forum:
  


Current Time: Tue Dec 06 04:38:25 CST 2016

Total time taken to generate the page: 0.14094 seconds