PL/SQL cursor: Fetch count into an integer, bad syntax?

0
votes

EDIT: Please see the edit below for the full code, the first one works. This might be a different problem that what was is in the title.

I am trying to put a cursor's count result into an integer, but the integer always stays at 0.

DECLARE    
   v_count int := 0;

CURSOR logins IS
   SELECT count(*)
   FROM big_table;

BEGIN
OPEN logins;
FETCH logins into v_count;

IF v_count > 10 THEN
   DBMS_OUTPUT.PUT_LINE ('Hi mom');
END IF;

CLOSE logins;
END;

This is only a sample of the code in which I want to do this, this fetch is actually in a LOOP.

I tried putting a "SELECT count(*) INTO v_count..." before the IF statement. It works, but it is awfully slow.

Thank you!

EDIT: As pointed out by A.B.Cade, this sample works. So my problem might be elsewhere in my code, you'll find the whole thing below: 

DECLARE
    v_hour int := 0;
    v_maxConn int;
    v_count int;
    --set the time parameters
    --v_day = the first day you loop in
    v_day int := 16;
    v_month varchar2 (2) := 10;
    v_year varchar2 (4) := 2013;
    v_lastDay int := 16;

CURSOR logins IS

        SELECT count(*)
        FROM (
            SELECT (SELECT user_function_name
            FROM apps.fnd_form_functions_vl fffv
            WHERE (fffv.function_id = a.function_id)) "Current Function",
               first_connect,
               last_connect,
               user_name, session_id,
               apps.fnd_profile.value_specific
                                    ('ICX_SESSION_TIMEOUT',
                                     a.user_id,
                                     a.responsibility_id,
                                     a.responsibility_application_id,
                                     a.org_id,
                                     NULL
                                    ) TIMEOUT,
               counter "How many hits a User has made",
               a.limit_connects "No of hits allowed in session"
            FROM icx.icx_sessions a, fnd_user b
            WHERE a.user_id = b.user_id
            AND last_connect > SYSDATE - 30
            AND b.user_name NOT LIKE 'GUEST'
             )
        WHERE to_date(v_year || '-' || v_month || '-' || v_day || ' ' || v_hour || ':00:00','YYYY-MM-DD HH24:MI:SS') between first_connect and last_connect
        ;

    out_menu varchar2(500);
    out_path varchar2(50) := '/usr/tmp/QA';
    file_out utl_file.file_type ;

BEGIN
file_out := utl_file.fopen(out_path,'debug_rapport.txt','W');
OPEN logins;

LOOP EXIT WHEN v_day > v_lastDay;
    v_maxConn := 0;

    LOOP EXIT WHEN v_hour > 23;

        FETCH logins into v_count;

        IF v_count > v_maxConn THEN
           v_maxConn := v_count;
        END IF;

        out_menu := 'Debug: ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') || ' -> dateLoop: ' || to_date(v_year || '-' || v_month || '-' || v_day || ' ' || v_hour || ':00:00','YYYY-MM-DD HH24:MI:SS') || ' -> v_maxConn: ' || v_maxConn || ' -> hour:' || v_hour;
        utl_file.put_line(file_out,out_menu);

        v_hour := v_hour + 1;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE (v_year || '-' || v_month || '-' || v_day || ';' || v_maxConn);

    v_hour := 0;
    v_day := v_day + 1;

END LOOP;

CLOSE logins;

END;

The code is to see concurrent connections within each hour of a given day stretch. It works if I replace the cursor code in the BEGIN statement with the SELECT statement that is found within the cursor.

Here is the output from my "Debug" file when I use the cursor:

Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:0
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:1
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:2
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:3
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:4
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:5
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:6
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:7
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:8
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:9
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:10
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:11
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:12
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:13
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:14
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:15
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:16
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:17
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:18
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:19
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:20
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:21
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:22
Debug: 2013-10-24 10:19:32 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:23

And here is the output when I replace the cursor with "SELECT count(*) INTO v_count [...]" :

Debug: 2013-10-24 10:00:40 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:0
Debug: 2013-10-24 10:00:54 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:1
Debug: 2013-10-24 10:01:09 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:2
Debug: 2013-10-24 10:01:23 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:3
Debug: 2013-10-24 10:01:37 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:4
Debug: 2013-10-24 10:01:50 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:5
Debug: 2013-10-24 10:02:05 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:6
Debug: 2013-10-24 10:02:20 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:7
Debug: 2013-10-24 10:02:33 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:8
Debug: 2013-10-24 10:02:47 -> dateLoop: 13-10-16 -> v_maxConn: 0 -> hour:9
Debug: 2013-10-24 10:03:00 -> dateLoop: 13-10-16 -> v_maxConn: 1 -> hour:10
Debug: 2013-10-24 10:03:15 -> dateLoop: 13-10-16 -> v_maxConn: 1 -> hour:11
Debug: 2013-10-24 10:03:28 -> dateLoop: 13-10-16 -> v_maxConn: 1 -> hour:12
Debug: 2013-10-24 10:03:41 -> dateLoop: 13-10-16 -> v_maxConn: 1 -> hour:13
Debug: 2013-10-24 10:03:54 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:14
Debug: 2013-10-24 10:04:08 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:15
Debug: 2013-10-24 10:04:22 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:16
Debug: 2013-10-24 10:04:35 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:17
Debug: 2013-10-24 10:04:47 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:18
Debug: 2013-10-24 10:05:00 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:19
Debug: 2013-10-24 10:05:13 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:20
Debug: 2013-10-24 10:05:25 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:21
Debug: 2013-10-24 10:05:38 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:22
Debug: 2013-10-24 10:05:51 -> dateLoop: 13-10-16 -> v_maxConn: 2 -> hour:23

You see v_maxConn doesn't stay at zero, therefore v_count is being written into. It takes 5 minutes for only one day, though.

Your help is greatly appreciated!

3
sqloracleplsqlcursor
thats the only way you can store the count in a variable though it's awfully slow. – Teja
Your code seems to work fine sqlfiddle.com/#!4/07131/1 (I've put it in a function for the fiddle) – A.B.Cade
If you want to check if count(*)>10, then use SELECT count(*) FROM bigtable WHERE rownum <= 11, this query reads only 11 rows instead of all rows from the table, and should perform better. – krokodilko
@kordirko, basically you're right but as OP says This is only a sample of the code in which I want to do this, this fetch is actually in a LOOP, we don't really know what he's trying to do – A.B.Cade
@A.B.Cade Thanks for pointing that the sample works, I tried in the actual database and it does. I edit the OP with the full query. – davidb

3 Answers

0
votes

I modified your code a little, this works.. 

DECLARE    
   v_count int := 0;

CURSOR logins IS
   SELECT count(*)
   FROM big_table;

BEGIN

OPEN logins;

loop

FETCH logins into v_count;



IF v_count >= 10 THEN

   DBMS_OUTPUT.PUT_LINE ('Hi mom');

   exit;

END IF;

end loop;

CLOSE logins;

END;

you forgot loop, end loop, and statement to exit, otherwise there will be infinity loop... hope this helps. IF there will be less than 10 rows, it will be infinite loop.

0
votes

You're doing a strange thing - you're looping on v_hour and v_date but you're refetching the same opened cursor which contains only one record.

So basically what's happening is that you're getting the same value (0) over and over again

Here is a sqlfiddle demo with a sample which is pretty much like your case.

OPEN logins;

for v_i in 1..10 loop
FETCH logins into v_count;

IF v_count > 10 THEN
   v_res := v_res ||v_i || '. v_count=' || v_count || ', ';
END IF;

end loop;
CLOSE logins;

If you really want to do that, then you need to add a parameter to the cursor and reopen it in every iteration:

Here is another sample that works

for v_i in 1..10 loop
OPEN logins(v_i);
FETCH logins into v_count;

IF v_count > 10 THEN
   v_res := v_res ||v_i || '. v_count=' || v_count || ', ';
END IF;
CLOSE logins;
end loop;

But then, this is like quering count(*) in every itteration (which might get slow)

It's hard to say but it looks as if you can get the values in one query-

Something like this

0
votes

The problem was that I didn't fully understand how cursors worked. I replaced the cursor with a temporary table in which I put my static query : 

CREATE TABLE apps.audit_custom as
select [...]

Then I query this table in my LOOP, as such:

SELECT count(*) into v_count
        FROM apps.audit_custom
        WHERE to_date(v_year || '-' || v_month || '-' || v_day || ' ' || v_hour || ':00:00','YYYY-MM-DD HH24:MI:SS') between first_connect and last_connect;

Finally, I drop the table when I'm done.

It's fastest way I found to make my idea work.

Thanks for helping guys!