Home » SQL & PL/SQL » SQL & PL/SQL » Rowcount with dynamic cursor
Rowcount with dynamic cursor [message #304989] Fri, 07 March 2008 07:38 Go to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Hello,

I was writing a package with procedures to test the functions and procedures of a database.

pkgcustomer.get(id) gives me a cursor with only one row.

Example:
select pkgcustomer.get(2) from dual;

gives me '1 rows selected'

Quote:

PKGCUSTOMER.GET(2)
------------------
ID LABEL NAME
---------------------- ------------
2 pol


1 rows selected




exemple 2:

select pkgcustomer.get(5999) from dual;

gives me: 1 rows selected

Quote:

PKGCUSTOMER.GET(5999)
---------------------
ID LABEL NAME ----------------------------------


1 rows selected



When I do:

select count(*)
from 
  (select pkgcustomer.get(2)
  from dual);


I get the result: 1


When I do:
select count(*)
from 
  (select pkgcustomer.get(5999)
  from dual);


I get the result: 1


When I do:
select count(*)
from
  (select pkgcustomer.get(2)
  from customer);

I get the result: 15 (15 rows in customer)

How can I fix it? I want to count the rows in the cursor, but when there is no record in the cursor the count must be 0.


A piece of code of my package to count the records in customer (p_command is pkgcustomer.get(2) for example):
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (SELECT ' || p_command || ' FROM DUAL)' INTO l_counter;
      
        /*
        OPEN c_testresult FOR 'SELECT ' || to_char(p_command) || 
          ' FROM DUAL';
          
          LOOP
          
            FETCH c_testresult INTO rec_testresult;
            
            EXIT WHEN c_testresult%NOTFOUND;  
            
            l_counter         := l_counter + 1;

          END LOOP;
          
          l_testresult        := to_char(l_counter);
          
        CLOSE c_testresult;
        */

[Updated on: Fri, 07 March 2008 07:38]

Report message to a moderator

Re: Rowcount with dynamic cursor [message #304998 is a reply to message #304989] Fri, 07 March 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good luck to get an answer with what you posted.

Regards
Michel
Re: Rowcount with dynamic cursor [message #305006 is a reply to message #304998] Fri, 07 March 2008 08:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I like your post. I started and then stopped.

Regards

Raj
Re: Rowcount with dynamic cursor [message #305288 is a reply to message #304989] Mon, 10 March 2008 05:42 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Here is a new try to explain my problem:

have the function: pkgcustomer.get(id).

To count the rows from this function I do:

select count(*)
from
  (select pkgcustomer.get(2)
  from dual);



So, this gives me 1.

The id '2' exist

When I give an id that does nog exist (for example 5999), I also get '1' for the rows he count.

Can someone help me to get the correct count from a function?

I also did:

OPEN c_testresult FOR 'SELECT pkgcustomer.get(2) FROM DUAL;';
          
          LOOP
          
            FETCH c_testresult INTO rec_testresult;
            
            EXIT WHEN c_testresult%NOTFOUND;  
            
            l_counter         := l_counter + 1;
 
          END LOOP;
          
          l_testresult        := to_char(l_counter);
          
        CLOSE c_testresult;



I hope that someone can help me...

Regards
Re: Rowcount with dynamic cursor [message #305295 is a reply to message #305288] Mon, 10 March 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't think so.

Regards
Michel
Re: Rowcount with dynamic cursor [message #305303 is a reply to message #305288] Mon, 10 March 2008 06:13 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
To count the rows from this function I do:

Are you to trying to count the number of rows returned by this function or you are trying to display the value what this function is returning? Please bear in mind a function can always return only one value but there is an exception to this. Unless and until the function is pipelined which I really doubt your function is. Again when I say Function can return only one value is the "returned value". I am not counting the formal parameters defined as part of the function.

Still you have not clearly explained what your problem is and what you are trying achieve. You have re-iterated what you said in your previous post in a different way. All we need is an explanation in plain english words along with a test script and expected result.

HTH

Regards

Raj
Re: Rowcount with dynamic cursor [message #305309 is a reply to message #304989] Mon, 10 March 2008 06:41 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
SELECT <anything> FROM dual;

As dual contains exactly one row, this query returns one row. So COUNT is always 1.

As you did not post the function declaration (at least its RETURN datatype), I can only guess what are you tryng to achieve.
However I think it would be useful for you to read (and go into) this answer to your previous thread.
Re: Rowcount with dynamic cursor [message #305313 is a reply to message #304989] Mon, 10 March 2008 06:52 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
This function returns a cursor and I want to see how many rows this function returns. An other function can return 2 rows in a cursor. So the count can be 1, 2, 3 or even more.
When you do a select you always have to add a table, so I use the dummy table dual. Sometimes a function doesn't returns rows and when I do that function on the table dual, I also get the result 1...

I don't may use a framework from the internet, I have to write one by myself (traineeship)...

Regards
Re: Rowcount with dynamic cursor [message #305318 is a reply to message #304989] Mon, 10 March 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't count the number of rows of a cursor from SQL, you have to fetch and count them one by one (or array by array).

Regards
Michel

[Updated on: Mon, 10 March 2008 07:15]

Report message to a moderator

Re: Rowcount with dynamic cursor [message #305320 is a reply to message #305318] Mon, 10 March 2008 07:27 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
The code to fetch the rows:

OPEN c_testresult FOR 'SELECT pkgcustomer.get(2) FROM DUAL;';
          
          LOOP
          
            FETCH c_testresult INTO rec_testresult;
            
            EXIT WHEN c_testresult%NOTFOUND;  
            
            l_counter         := l_counter + 1;
 
          END LOOP;
          
          l_testresult        := to_char(l_counter);
          
        CLOSE c_testresult;


When I change the '2' by 5999, the amount of rows should be '0'.

Regards
Re: Rowcount with dynamic cursor [message #305330 is a reply to message #305320] Mon, 10 March 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The correct way is:
SQL> create or replace function f (nb integer) return sys_refcursor is
  2    l sys_refcursor;
  3  begin
  4    open l for 'select * from emp where rownum <= '||nb;
  5    return l;
  6  end;
  7  /

Function created.

SQL> def n=1
SQL> declare
  2    l_res varchar2(10);
  3    l_cnt integer := 0;
  4    l_cur sys_refcursor;
  5    l_rec emp%rowtype;
  6  begin
  7    l_cur := f(&n);
  8    loop 
  9      fetch l_cur into l_rec;
 10      exit when l_cur%notfound;
 11      l_cnt := l_cnt + 1;
 12    end loop;
 13    close l_cur;
 14    l_res := to_char(l_cnt);
 15    dbms_output.put_line ('res='||l_res);
 16  end;
 17  /
res=1

PL/SQL procedure successfully completed.

SQL> def n=2
SQL> /
res=2

PL/SQL procedure successfully completed.

SQL> def n=0
SQL> /
res=0

PL/SQL procedure successfully completed.

Regards
Michel
Re: Rowcount with dynamic cursor [message #305331 is a reply to message #305330] Mon, 10 March 2008 08:09 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
The functions are already written and an example of a function is: pkgcustomer.get(id) of pkgcustomer.getbyname(name).

This return rows and I want to count the amount of rows... I don't may rewrite the functions...

Regards
Re: Rowcount with dynamic cursor [message #305334 is a reply to message #305320] Mon, 10 March 2008 08:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
  1  declare
  2  c_testresult sys_refcursor;
  3  rec_testresult number;
  4  l_testresult varchar2(100);
  5  l_counter number;
  6  begin
  7  open c_testresult for 'select 1 from dual;';
  8  loop
  9    fetch c_testresult into rec_testresult;
 10    exit when c_testresult%notfound;
 11    l_counter := l_counter + 1;
 12  end loop;
 13  l_testresult := to_char(l_counter);
 14  close c_testresult;
 15* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 7


So the code snippet you have posted is not the one you are running. So post a working version something like this :
  1  create or replace function test_func(p_table_type varchar2)
  2  return number
  3  is
  4  v_result number;
  5  begin
  6  select count(*) into v_result from cat where table_type = p_table_type ;
  7  return v_result;
  8* end;
  9  /


SQL> /

Function created.

  1    declare
  2    c_testresult sys_refcursor;
  3    l_testresult  number;
  4    begin
  5       open c_testresult for q'{select test_func('TABLE') from dual}';
  6       loop
  7          fetch c_testresult into l_testresult;
  8          exit when c_testresult%notfound;
  9       end loop;
 10       close c_testresult;
 11       dbms_output.put_line( ' Test_func output is : ' || l_testresult);
 12*    end;
SQL> /
Test_func output is : 12

PL/SQL procedure successfully completed.


  1    declare
  2    c_testresult sys_refcursor;
  3    l_testresult  number;
  4    begin
  5       open c_testresult for q'{select test_func('N/A') from dual}';
  6       loop
  7          fetch c_testresult into l_testresult;
  8          exit when c_testresult%notfound;
  9       end loop;
 10       close c_testresult;
 11       dbms_output.put_line( ' Test_func output is : ' || l_testresult);
 12*    end;
SQL> /
Test_func output is : 0

PL/SQL procedure successfully completed.

From this you can see your function definition what it is doing.

Regards

Raj
Re: Rowcount with dynamic cursor [message #305335 is a reply to message #305331] Mon, 10 March 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You never gave the code of your function, you just said it returns a cursor, I gave you the way to handle this.
My function "f" is your function "pkgcustomer.get".
This "OPEN c_testresult FOR 'SELECT pkgcustomer.get(2) FROM DUAL;';" is plain wrong if your function really returns a cursor.
Read the example, study it, understand it and adapt it to your case.

Regards
Michel
Re: Rowcount with dynamic cursor [message #305336 is a reply to message #305334] Mon, 10 March 2008 08:23 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
This is my procedure to test a function (the function will be written in p_command):

 PROCEDURE start_command (p_testtype       IN VARCHAR2,
                           p_command        IN VARCHAR2,
                           p_testvalue      IN VARCHAR2, 
                           p_id_command     IN NUMBER,
                           p_id_run         IN NUMBER)
  IS
  
    l_testresult              VARCHAR2(100);
    l_testresult_number       NUMBER;
    c_testresult              t_cursor;
    rec_testresult            c_testresult%type;
    l_counter                 NUMBER := 0;
    x                         NUMBER;
  
  BEGIN
      
        OPEN c_testresult FOR 'SELECT ' || to_char(p_command) || 
          ' FROM DUAL';
          
          LOOP
          
            FETCH c_testresult INTO rec_testresult;
            
            EXIT WHEN c_testresult%NOTFOUND;  
     
            l_counter         := l_counter + 1;

          END LOOP;
          
          l_testresult        := to_char(l_counter);
          
        CLOSE c_testresult;

  END start_command;
Re: Rowcount with dynamic cursor [message #305342 is a reply to message #305336] Mon, 10 March 2008 08:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
the function will be written in p_command

How do we know what the function returns without you telling us ?

Regards

Raj
Re: Rowcount with dynamic cursor [message #305343 is a reply to message #305342] Mon, 10 March 2008 08:38 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
The function returns a cursor (dynamic cursor)

Regards
Re: Rowcount with dynamic cursor [message #305344 is a reply to message #305343] Mon, 10 March 2008 08:44 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't know what to say. Please read the guidelines provided by Michael especially this one.
Quote:
You never gave the code of your function, you just said it returns a cursor, I gave you the way to handle this.
My function "f" is your function "pkgcustomer.get".
This "OPEN c_testresult FOR 'SELECT pkgcustomer.get(2) FROM DUAL;';" is plain wrong if your function really returns a cursor.
Read the example, study it, understand it and adapt it to your case.

Regards
Michel


All the best.

Regards

Raj

[Updated on: Mon, 10 March 2008 08:45]

Report message to a moderator

Re: Rowcount with dynamic cursor [message #305347 is a reply to message #305330] Mon, 10 March 2008 08:52 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
I've done what you said... I don't may change any functions of the company.

p_command would be a function

I have written c_testresult := p_command;

When I try to compile, I get the next error on that line:

Quote:

PLS-00382: expression is of wrong type



Regards
Re: Rowcount with dynamic cursor [message #305363 is a reply to message #305347] Mon, 10 March 2008 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
c_testresult := p_command;

This is not what I posted.
Your "c_testresult" is my "l_res".

Regards
Michel
Re: Rowcount with dynamic cursor [message #305366 is a reply to message #305363] Mon, 10 March 2008 09:35 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
your l_res is a dynamic cursor, just like my c_testresult...
Re: Rowcount with dynamic cursor [message #305370 is a reply to message #305366] Mon, 10 March 2008 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sorry my l_res is your l_testresult.
Please give DISTINCT names to your variable.

Regards
Michel


[Updated on: Mon, 10 March 2008 09:40]

Report message to a moderator

Re: Rowcount with dynamic cursor [message #305373 is a reply to message #305370] Mon, 10 March 2008 09:47 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
You write: l_cur := f(&n);
I write: c_testresult := p_command;

Your l_cur is a dynamic cursor.
my c_testresult is also a dynamic cursor.
Re: Rowcount with dynamic cursor [message #305375 is a reply to message #305373] Mon, 10 March 2008 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
But p_command is a string not a cursor:
p_command        IN VARCHAR2


Regards
Michel

Re: Rowcount with dynamic cursor [message #305378 is a reply to message #305375] Mon, 10 March 2008 09:58 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Yes,

my p_command is your f(&n);

So you have to read it like: c_testresult = pkgcustomer.get(500);
Re: Rowcount with dynamic cursor [message #305380 is a reply to message #305378] Mon, 10 March 2008 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
c_testresult = pkgcustomer.get(500);

This is correct as "pkgcustomer.get(500);" returns a cursor.
Quote:
c_testresult = p_command;

This is wrong as p_command is a string.

Regards
Michel
Re: Rowcount with dynamic cursor [message #305383 is a reply to message #305380] Mon, 10 March 2008 10:09 Go to previous messageGo to next message
xenium
Messages: 25
Registered: February 2008
Location: Leuven Belgium
Junior Member
Hello,

It is a package and I have to fill any function, so I need to use the p_command.

Regards
Re: Rowcount with dynamic cursor [message #305387 is a reply to message #304989] Mon, 10 March 2008 10:40 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
ok, in that case:
1. take the anonymous pl/sql block Michel provided you
2. create it as a function returning NUMBER (number of fetched rows) with one VARCHAR2 parameter (name of tested procedure/function)
3. call it from your code as
p_command := '<your_new_function> ( ''pkgcustomer.get(2)'' )'

And, yes, for each different l_rec structure, you have to create new function; so I would introduce a package to hold all of them.
Previous Topic: SQL query for update
Next Topic: Columns to Rows(New Rows)
Goto Forum:
  


Current Time: Wed Feb 12 05:57:18 CST 2025