Rowcount with dynamic cursor [message #304989] |
Fri, 07 March 2008 07:38  |
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 #305288 is a reply to message #304989] |
Mon, 10 March 2008 05:42   |
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 #305303 is a reply to message #305288] |
Mon, 10 March 2008 06:13   |
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   |
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   |
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 #305320 is a reply to message #305318] |
Mon, 10 March 2008 07:27   |
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 #305331 is a reply to message #305330] |
Mon, 10 March 2008 08:09   |
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   |
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 #305336 is a reply to message #305334] |
Mon, 10 March 2008 08:23   |
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 #305344 is a reply to message #305343] |
Mon, 10 March 2008 08:44   |
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   |
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 #305387 is a reply to message #304989] |
Mon, 10 March 2008 10:40  |
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.
|
|
|