11

The code below may return more than one row. Will sql%rowcount return the number of rows fetched?

select * from emp where empname = 'Justin' and dept='IT'
if sql%rowcount>0
    ...

This is my sample proc; am I using sql%rowcount in correct way?

CREATE PROCEDURE Procn(in_Hid IN VARCHAR2,outInststatus OUT VARCHAR2,outSockid IN NUMBER,outport OUT VARCHAR2,outIP OUT VARCHAR2,outretvalue OUT NUMBER)
AS
BEGIN
select INST_STATUS into outInststatus from TINST_child where INST_ID = in_Hid and INST_STATUS = 'Y';
if outInststatus = 'Y' then 
     select PORT_NUMBER,STATIC_IP into outport,outIP from TINST where INST_ID = in_Hid and IP_PORT_STATUS = 'Y';
    if sql%rowcount >= 1 then
       select SOCK_ID into outSockid from TINST where PORT_NUMBER = outport AND STATIC_IP = outIP;  
       outretvalue := 0;
    else
       outretvalue := -12;
    end if;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
   outretvalue := -13;
end if;
END;
Improve this question
7
  • 1
    If it's PL/SQL as tagged then you need to be selecting into something, in which case you'd get a no_data_found exception if the row count was zero, or too many rows if it was greater than 1, so you could use exception handling to deal with this; or be using an explicit cursor. I think more context is needed. Commented Nov 15, 2012 at 10:16
  • @alex i hav updated my code i posted a sample code am i using sql%count in a correct way? Commented Nov 15, 2012 at 10:36
  • No; select ... into (unless you have bulk collect into as Codo suggests) will always return exactly one row, or raise a exception. Imagine there were two matching rows; which of the two values for static_ip would be in outIP? Your code will work as long as there is one row, but fail when there isn't. Is it valid for there to be more than one row, and what should happen if there is? Commented Nov 15, 2012 at 10:45
  • Also, outInstatus can only be Y when you check it - if there is no matching rows from your first select than no_data_found will be raised before you get to the if. And although the construct isn't right anyway, if sql%rowcount was zero - which is can't be, you'd get no_data_found there instead - then you aren't setting outretvalue. I'm not entirely sure if you need to hit the table three times; that may suggest that the third select could return multiple rows too. I think you need to clarify what's supposed to be happening in various scenarios. Commented Nov 15, 2012 at 11:08
  • @alex 1.My first 'select' query is on one table and other two 'select' query on one more table. 2.Now i updated my above code to When no data found instead of else 3.If 2nd 'select' query returns more than one row i want to take the first one and process with it. Commented Nov 15, 2012 at 11:21

2 Answers 2

Reset to default
10

Yes, you can use SQL%ROWCOUNT. It's valid in PL/SQL.

However, in PL/SQL the result of your query needs to go somewhere e.g. into a PL/SQL table. PL/SQL will never send the result to the output (terminal, window etc.). So SELECT * FROM won't work.

Your code could look like this:

DECLARE
  TYPE emp_t ...;
  emp_tab emp_t;

BEGIN
  SELECT *
  BULK COLLECT INTO emp_tab
  FROM emp
  WHERE empname = 'Justin' AND dept='IT';

  IF sql%rowcount > 0 THEN
    .. do something ...
  END IF;
END;
/

Update:

The updated questions suggests that you're looking for something else.

Option 1: Use exceptions

If there are 0 rows or more than 1 row, these cases are handled separately (as errors):

BEGIN
  select PORT_NUMBER,STATIC_IP into outport, outIP
  from TINST
  where INST_ID = in_Hid AND IP_PORT_STATUS = 'Y';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    outretvalue := -12;
    RETURN;

  WHEN TOO_MANY_ROWS THEN
    outretvalue := -13;
    RETURN;
END;

Option 2: Use aggregations

Using aggregations, the query will always return exactly one row. If now source row matched the WHERE clause, then both result values will be NULL. If there WHERE clause matched more than one row, the maximum will be taken.

Note that this query might return a port number and an IP address that originally were not on the same row.

select MAX(PORT_NUMBER), MAX(STATIC_IP) into outport, outIP
from TINST
where INST_ID = in_Hid AND IP_PORT_STATUS = 'Y';

IF outport IS NULL OR outIP IS NULL THEN
    outretvalue := -12;
    RETURN;
END IF;

Option 3: Use ROWNUM

This query returns at most one row. If no row matched the WHERE clause, an exception is thrown and needs to be handled:

BEGIN
  select PORT_NUMBER, STATIC_IP into outport, outIP
  from TINST
  where INST_ID = in_Hid AND IP_PORT_STATUS = 'Y'
  AND ROWNUM = 1;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    outretvalue := -12;
    RETURN;

END;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

will sql%rowcount return >1 if more than one row fetched or it will say too many rows
@user1 - the bulk collect clause means multiple rows are stored in the emp_tab PL/SQL table. You will not get too_many_rows or no_data_found from this. When you do a bulk collect the sql%rowcount will have the number of rows; though I'd normally refer to emp_tab.count to see how many that contains - since sql%count refers to the last query it will only be useful briefly, whereas emp_tab.count will remain correct.
1

Based on your comment

If 2nd 'select' query returns more than one row i want to take the first one and process with it

... this ought to work, but perhaps not quite as you expect, as you haven't defined what the 'first one' means.

CREATE PROCEDURE Procn(in_Hid IN VARCHAR2, outInststatus OUT VARCHAR2,
    outSockid IN NUMBER, outport OUT VARCHAR2, outIP OUT VARCHAR2,
    outretvalue OUT NUMBER)
AS
BEGIN
    select INST_STATUS into outInststatus
    from TINST_child
    where INST_ID = in_Hid and INST_STATUS = 'Y';

    -- no need to check if outInstatus is Y, that's all it can be here

    -- restricting with `rownum` means you'll get at most one row, so you will
    -- not get too_many_rows. But it will be an arbitrary row - you have no
    -- criteria to determine which of the multiple rows you want. And you can
    -- still get no_data_found which will go to the same exception and set -12
    select PORT_NUMBER, STATIC_IP into outport, outIP
    from TINST
    where INST_ID = in_Hid and IP_PORT_STATUS = 'Y'
    and rownum < 2;

    -- no need to check sql%rowcount; it can only be 1 here

    -- not clear if this can return multiple rows too, and what should happen
    -- if it can; could use rownum restriction but with the same caveats
    select SOCK_ID into outSockid
    from TINST
    where PORT_NUMBER = outport AND STATIC_IP = outIP;   

    outretvalue := 0;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        outretvalue := -12;
END;

The exception handler applies to the whole block. If any of the select statements find no rows, the no_data_found exception will be handled by that block and will set outretvalue to -12.

If you want a different outretvalue for each select then you can wrap them in sub-blocks, each with their own exception handling section:

CREATE PROCEDURE Procn(in_Hid IN VARCHAR2, outInststatus OUT VARCHAR2,
    outSockid IN NUMBER, outport OUT VARCHAR2, outIP OUT VARCHAR2,
    outretvalue OUT NUMBER)
AS
BEGIN
    BEGIN
        select INST_STATUS into outInststatus
        from TINST_child
        where INST_ID = in_Hid and INST_STATUS = 'Y';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            outretvalue := -12;
    END;

    BEGIN
        select PORT_NUMBER, STATIC_IP into outport, outIP
        from TINST
        where INST_ID = in_Hid and IP_PORT_STATUS = 'Y'
        and rownum < 2;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            outretvalue := -13;
    END;

    BEGIN
        select SOCK_ID into outSockid
        from TINST
        where PORT_NUMBER = outport AND STATIC_IP = outIP;   
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            outretvalue := -14;
    END;

    outretvalue := 0;
END;

You only need to do that if the caller needs to know which select failed, and if you never really expect any of them to fail then it's probably more common not to catch the exception at all and let the caller see the raw no_data_found and decide what to do. Depends what the exception condition means to you and your application though.

Improve this answer

6 Comments

But my requirements totally differs from wat u explained 1.im checking if outInstatus is Y coz i need to allow to next step oly if its 'Y', it may also return 'N' 2.I cant restrict with rownum coz as my need multiple rows will come in 2nd SELECT QUERY and i need to select the 1st row in the result set randomly here no need to choose based on any criteria.
@user1 - 1) It can only be Y because of the where clause; INST_STATUS can't be both Y and N at the same time. 2) I don't follow I'm afraid; 'need multiple rows' and 'select 1st row randomly' (which this does) are contradictory?
sir, My question is in my above code if 1st SELECT query returns 'Y' then no pblm itll process if stmnt.If it returns 'NO DATA' it ll go to exception.If it returns 'N' where ll the condition ll go next according to the proc i worte?
@user1 - it cannot return N. The where clause has and INST_STATUS = 'Y', so you are filtering out any that are N.
@user1 - the exception applies to the whole block, so you will get -12 if any of the selects gets no rows.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.