• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4541
  • Last Modified:

Oracle - PL\SQL for Oracle Forms in Developer 2000

-- PROBLEM:
---------------------
-- I am unable to retrieve the correct value from SQL%ROWCOUNT....
--
--
--WHAT IVE DONE
----------------------
-- When the code below runs the variable thisNumber is always set to the number 1
--even when there are no records that meet this sql select statments criteria....
--What I mean is that if I run the select statment manually in TOAD or SQL PLUS I get 0 records
--returned but when I run this form it will always set thisNumber to 1...  And the weird thing is that
--it sets the NT variable within the select into statement to 9, how can this be???
--
--Am i coding the select statement correctly?  if the DEV_ID in my where clause is of VARCHAR2 type
--will this be okay the way that I have it?  Or is some type of parenthesis needed?  Or is this okay in
--this case since I'm bringing the variable in through an input parameter to the function and it knows
--that its of varchar2 type so I don't need the parenthesis???
--




FUNCTION getNumOfDevicesWithCriteria( thisDeviceID in VARCHAR2,
                                             thisExemptnKey in NUMBER,
                                             thisNumber IN OUT NUMBER ) return boolean
          IS
                 NT    number;
          BEGIN  
                 select count(*) into NT
                 FROM ASR_EXEMPTED_DEVICES
                 WHERE DEV_ID = thisDeviceID and
                 EXEMPTN_KEY = thisExemptnKey;
             
                 thisNumber := SQL%ROWCOUNT;
             
                 return true;

END getNumOfDevicesWithCriteria;
0
wesunivofmd
Asked:
wesunivofmd
  • 5
  • 3
  • 3
  • +5
1 Solution
 
makhanCommented:
I suggest following changes.

use
  select count(1) into NT
instead of
  select count(*) into NT

also use an exception condition.

see the code below.

FUNCTION getNumOfDevicesWithCriteria( thisDeviceID in VARCHAR2,
                                             thisExemptnKey in NUMBER,
                                             thisNumber IN OUT NUMBER ) return boolean
          IS
                 NT    number;
          BEGIN  
              BEGIN
                 select count(1) into NT
                 FROM ASR_EXEMPTED_DEVICES
                 WHERE DEV_ID = thisDeviceID and
                 EXEMPTN_KEY = thisExemptnKey;
               thisNumber := SQL%ROWCOUNT;
               return true;
             EXCEPTION
               when no_data_found then
                  NT := 0;
                  return false;
             END;
END getNumOfDevicesWithCriteria;

0
 
alexfrlCommented:
count(1) without a group by statement always finds the data. there is alwais one row selected with one value >= 0
Your sql%rowcount in your case always returns 1 .
There is absolutely no need in getting rowcount because the necessary result is already in your NT variable.
0
 
wesunivofmdAuthor Commented:
-- made a mistake in my question.  My query statement is as follows...
-- Its a select * statement and it always returns 1 no matter if there are
-- 0, 1 or more records in the database....
-- For my situation there are zero records in the database that meet the
-- select query criteria and it is returning 1 saying that a record already
-- exists...


FUNCTION getNumOfDevicesWithCriteria( thisDeviceID in VARCHAR2,
                                             thisExemptnKey in NUMBER,
                                             thisNumber IN OUT NUMBER ) return boolean
          IS
                 NT    number;
          BEGIN  
                 select * FROM ASR_EXEMPTED_DEVICES
                 WHERE DEV_ID = thisDeviceID and
                 EXEMPTN_KEY = thisExemptnKey;
             
                 thisNumber := SQL%ROWCOUNT;
             
                 return true;

END getNumOfDevicesWithCriteria;
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
makhanCommented:
Use this code and check out the results.

FUNCTION getNumOfDevicesWithCriteria( thisDeviceID   in VARCHAR2,
                                      thisExemptnKey in NUMBER,
                                      thisNumber     IN OUT NUMBER ) return boolean
IS
NT    number;
BEGIN  
  select *
    FROM ASR_EXEMPTED_DEVICES
   WHERE DEV_ID = thisDeviceID
     and EXEMPTN_KEY = thisExemptnKey;

  thisNumber := SQL%ROWCOUNT;
  return true;

EXCEPTION
  when no_data_found then
    thisNumber := 0;
    return false;
END getNumOfDevicesWithCriteria;
0
 
makhanCommented:
Now there are a few more considerations....

Select * will need an

INTO clause
otherwise you will get error during the execution .


0
 
alexfrlCommented:
If you want to receive into "thisNumber" the number of rows suitable to your criteria and then to return TRUE
I suggest you to use the following:

FUNCTION getNumOfDevicesWithCriteria( thisDeviceID   in VARCHAR2,
                                      thisExemptnKey in NUMBER,
                                      thisNumber     IN OUT NUMBER ) return boolean is
BEGIN  
  select count(1)
    into thisNumber
    FROM ASR_EXEMPTED_DEVICES
   WHERE DEV_ID = thisDeviceID
     and EXEMPTN_KEY = thisExemptnKey;

   return true;
END;



0
 
alexfrlCommented:
ALSO! If you want to return the indication of "zero number of rows (or not) is found in your table"
you can replace the "return true;" command by "return thisNumber>0;" -> it returns TRUE when some rows are found and FALSE if no rows suit your criteria.
0
 
ora_user2003Commented:
Hi wes ,
   
     As far as I can see the procedure u have written is just seeing if there is any rows inthe table with the condition u are passing into it .If u tell me what u r trying to do with the result of this select may be I or somebody else will be able to help better

   As far as I can ssee the function is not required
you may be able to  take care of the situation by using

"where exists" clause in your select statement where u want to use this (if u are using this result along with a select statement .

or else u know what to do because
 since you are selecting count and it returns a row invariable the sql %rowcount will be 1 because it is the no. of rows returned





0
 
wesunivofmdAuthor Commented:
I've corrected the problem in my pl\sql program.  None of the solutions above solved my problem, maybe someone here can tell me why this might have happened, the best answer will get the points.  If no one knows I will give the points to the person that I thought gave the best response above.

When I ran a select count(*) sql query in TOAD manually just to figure out what the count of all the records were in my database table I got a number but when I coded the same exact select count(*) sql query in my pl\sql program and ran the program a different count was displayed on my form (specifically a 6 count record difference).  The 6 count record difference I found out were the same 6 records that I've been testing with, adding then deleting, till I got the program to work).   Since there was a 6 count record difference the first thing that came to mind was that the 6 records that I've been testing with had something to do with the problem, so I query'ed in my pl\sql program to see if these 6 records existed and they did, but when I manually query'ed for these 6 records in TOAD they did not...

Yes, I was connected to the same database in both cases and was running the exact same select count(*) query on the same table.  

So what I did was code and run a delete statement in the pl\sql program that deleted the 6 rows that it was saying existed that my manual sql query in TOAD didn't show as existing.  This still makes no sense to me but it has done the trick, now everything is working fine.  

This was a strange problem and if you have any ideas why and how this could have happened please give me some insight...

Some of the specifics of Forms I'm using is:
Oracle Developer 2000 w/Forms Version 5.0, Oracle8i Ent. Edition, PL/SQL V2.3

Thank you in advance...

0
 
alexfrlCommented:
You probably create the records in your form but do not commit after. So any other session doesn't see your rows
0
 
wesunivofmdAuthor Commented:
------------------------
QUESTION #1
------------------------

If this was the case, your saying its possible to:

(1) Insert 6 records in my database table with pl\sql
(2) Then delete those same 6 records with pl\sql

without doing a commit after step (1) and prior to step (2)?


------------------------
QUESTION #2
------------------------

(A)
Whats it mean to say: "commit the changes in your code, not the form data with
FORMS_DDL('COMMIT')"

(B)
And whats this for: "COMMIT_FORM"

(C)
How does using (A) differ from using (B)
0
 
alexfrlCommented:
1) When there are INSERT in DELETE in your FORM then after the completion of your DELETE there are no more records.
    If there is no COMMIT between INSERT and DELETE in your FORM then you cannot trace the changes from another session (for instance your Toad)

2) FORMS_DDL commits only the database transactions
    COMMIT_FORM post the form changes to the database: it inserts deletes and updates records, what your form doesn't do before the execution of COMMIT_FORM or POST commands and then it commits the changes as in FORMS_DDL

I mean FORMS_DDL is the second step of FORMS_COMMIT which is divided into 2 steps: POST and COMMIT;
0
 
Joseph MelnickCommented:
Hello,

There is a logic problem here.

The sql will always return exactly one row.

the variable NT is set to the count you want returned in thisNumber.

 so set:  thisNumber := NT;
not
  thisNumber := SQL%rowcount;

0
 
doryllisCommented:
I know this is closed but thought I'd offer a bit more clarification on jmelnicks statements above:

Rowcount will return the number of rows returned by the query

if you
 select record_number from table_1
it will return a count of the record numbers selected by the query

if you
  select count(record_number) from table_1
it will return a count of 1 because the count(anything) statement only returns one row.
0
 
kmosoCommented:
Your problem is that a SELECT COUNT query always returns a result not matter your conditions finds rows or not. Try SELECT count(1) FROM dual WHERE dummy = 'A' and you will see.

The value of SQL%ROWCOUNT is translated as "The number of records processes by the last SQL statement".

Hope this helps you!!
0
 
kamelleCommented:
There are many correct answers in this task. I'm not the hero, who brought the answer, but I will try to summeries:
There are two outputs - the returnvalue in boolean and the number of rows in thisNumber.

FUNCTION getNumOfDevicesWithCriteria( thisDeviceID in VARCHAR2,
                                             thisExemptnKey in NUMBER,
                                             thisNumber IN OUT NUMBER ) return boolean
          IS
                 NT    number;
          BEGIN  
              BEGIN
                 select count(*) into NT
                 FROM ASR_EXEMPTED_DEVICES
                 WHERE DEV_ID = thisDeviceID and
                 EXEMPTN_KEY = thisExemptnKey;

-- The next line will only processed when any row is found

               thisNumber := NT;
               return true;
             EXCEPTION
               WHEN no_data_found THEN
                  thisNumber := 0;
                  return false;
             END;
END getNumOfDevicesWithCriteria;
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now