Is there a way to tell the record count when working with a sys_refcursor?
procedure Login (SSO in varchar2,
UserRole out sys_refcursor) is
begin
begin
open UserRole for
select descr
[code]......
I wrote the function witch returns some information.
function get_cust_info (v_msisdn integer) RETURN sys_refcursor
IS
curs sys_refcursor;
BEGIN
open curs for 'select first_name, last_name, street, town
from the_table where MSISDN = :1' using v_msisdn;
RETURN curs;
end
How to call this function to write result into table? I just want to write function which returns more Varchar.
/* Formatted on 22/01/2013 19:32:50 */
CREATE OR REPLACE PROCEDURE test_rdm_miles (
p_ref_cursor OUT SYS_REFCURSOR
p_success NUMBER)
IS
BEGIN
OPEN p_ref_cursor FOR
SELECT 5168 mem_uid,
[code]....
I have a Procedure with out parameters as a REF CURSOR and response message as p_success.This ref cursor will be returned to the calling service. Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it. Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.
Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.
one more solution is
OPEN
FETCH
CLOSE
OPEN (AGAIN) { this will lead to redundancy)
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
Is it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?
parse and execute the sql string first with dbms_sql and then convert it to ref cursor?
I want to return ref cursor based on explicit cursors
create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;
[Code].....
The above procedure has compilation errors when I am trying to open ref cursor
LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>
Can i user exist when cursor will using For Cursor .
View 15 Replies View RelatedWhen we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.
SQL> select * from t1;
A B C
---------- -------------------- --------------------
1 2 3
2
5
SQL> select rownum,a.* from t1 a;
ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
I have one tbale like
TABLE A with columns bu_code and bu_cty
bu_code bu_cty
12 ES
12 ES
13 AP
13 AP
so now i want to know the count for bu_code
like my output shd be
bu_code(12) ---count is 2
bu_code(13)------count is 13
I have this query and I want to get the COUNT:
SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;
This returns one column which is the n_p; how do I get the count of n_p?
say there is astring "mumbojumbo "i need the count of given string in it
ex:when o is given count shuld be 2 when m is given count shuld be 3
is there any pre defined function for counting a given string ...
I have a table with the following fields :
RGS_CAN_PERIOD NUMBER(4)
RGS_ID_ELECTOR NUMBER(
FBD_NID VARCHAR2(14)
RGS_SURNAME VARCHAR2(40)
RGS_O_NAME VARCHAR2(40)
RGS_ALIAS VARCHAR2(30)
RGS_ADDR VARCHAR2(75)
RGS_ID_REG_AREA VARCHAR2(3)
[Code]..
I need a report as hereunder :
Reg. Area Age <=19 20 <= Age <= 24 25 <=Age <= 29 Total No. of Voters
xxxx 10 15 7 32
yyyy 5 7 3 15
I have work out a script but the age is not in a range
select *
FROM (select rgs_id_reg_area,
count(decode(fbd_age,19,fbd_age)) Age19,
count(decode(fbd_age,20,fbd_age)) Age20
FROM rubyvoterstat where vote ='Y'
GROUP by rgs_id_reg_area)
order by rgs_id_reg_area
I have the following table with insert below.
CREATE TABLE ATTENDANCE
(
"REG_NO" NUMBER(7,0),
"COURSE_SEQ_NO" NUMBER(4,0),
"SECTION" VARCHAR2(1 BYTE),
"SEMESTER_CODE" NUMBER(1,0),
[code]...
How can I have the following output?
course_seq_nosectiontotal_recordscount_1count_0
322X20137
I tried decode but it could not find its way
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc
I need suing group by for getting count.I have a table with columns below
SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT
178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461
I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT
I want to get
1789 3 4
I tried this query but this is not working
[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from
my_table sub
group by sys_audit_id [/code]
I need to get an accumulated value for a count. E.g. The table has purchased date, purchased item, purchased item type. The count of purchased item groyup by purchased type on every purchased date. Now, we got the count value (purchased item). But, I want the accumulated count value on every purchased date. So that I will get that how many items has been purchased on a particular date.
View 5 Replies View RelatedI pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.
My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:
SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12
-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL
Here is the query in it's current state:
-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)
[Code]....
So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.
I'm trying the various joins, but none of them seem to be working.
I've tried the old 8i (+) join as follows:
AND c.fol_id = d.doc_fol_id(+)
I've tried the inner join:
AND c.fol_id(+) = d.doc_fol_id
...and I've tried the 9i method (left outer and full outer) using the following types of notations:
folder c full outer join documend d on c.fol_id = d.doc_fol_id
...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)
we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.
Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?
I have a table employee
name title
A manager
B sales
c manager
D engineer
E marketing
I want to know how many people has title as manager?
the answer is 2 how to do the count, I can't do count(title), right ?
SQL> select * from test11;
A B C
--------------------------------------
AA 123
BB 100 200
CC 300
AA
How to count no null in the above table
oracle SQL,
Student(unique) has five races in a table
I need a select statement with student_id - 1 column
I need to count the number columns with values 'Yes' in them
the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn
The 3rd column should contain the max of the race having 'yes' in them
i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column
i was just looking to find out how it is possible to select * from a sub select that has data coming from 2 tables.
For example:
select *, count(*) AS Count
from
(
select ....does not work, but i am not sure how i can select all fields from the sub query.
I want the number of UNITS with the Status EN/PL for AREA 3/6
ID 1 has area 3 and 6 but only EN so count 1 (no PL)
ID 2 has area 6 but DR so no count
ID 3 has area 3 and 6 for EN and PL so 1 for each
ID 4 has area 6 three times and 3 once so count.
CREATE TABLE DAN_T1
(
ID varchar(8),
AREA varchar(8),
UNIT varchar(8),
STATUS varchar(8)
)
[Code]...
WANT:
IDENROLLEDPLANNED
11 0
20 0
31 1
41 3
In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).
example
----------
jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5
feb ------------do---------------------------------
like this for all 12 months at a single query.
I have written a script to get count of each tables of a schema.
DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;
[Code].....
I am trying to run a query that will count the number of each occurrence of a combined month and day. Birthdays are formatted as: 1947-07-26 00:00:00
I need to query everyone in the system who share the same month & day, but not year & time. For example:
1927-07-26 00:00:00
1927-07-26 00:00:00
1987-07-26 00:00:00
1965-07-26 00:00:00
1981-03-14 00:00:00
would give me a Count of 4 for 7/26 and a Count of 1 for 3/14
