SQL & PL/SQL :: Identify / Count Records In Ref Cursor Without Actually Fetching?

Jan 22, 2013

/* 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)

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Fetching Records From Cursor

Jun 21, 2012

I have created one global temporary table in which I inserted 2 rows.

I am fetching the rows by using following cursor :

declare
cursor c1 is
select TTD_TRV_MODE
from global_tra_trv_dtl;
v_trv_mode varchar2(10);
Begin
open c1;

[code]....

But instead of 2 rows , 3 rows are getting fetched.

View 6 Replies View Related

SQL & PL/SQL :: Cursor Not Fetching Values

Apr 21, 2011

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a cursor in my procedure. When I OPEN, FETCH, it doesnt give me any values. But instead of cursor,if I use the sql , used in the same cursor, then i am getting the value.

DECLARE
l_vin CLM_MAIN.vin%TYPE;
l_part CLM_MAIN.vin%TYPE;
l_clm_id CLM_MAIN.vin%TYPE;

[Code]....

Clm_main has a UNIQUE constrains, of VIN, PART. So when i OPEN the cursor, its not finding clm_id , eventhough it exist in the table. so it takes it as claims_cur%NOTFOUND, an tries to INSERT in the table. But since the record with that VIN and PART already exists, it throws exception that ORA-00001: unique constraint (CLM_MAIN_UK) violated.

View 25 Replies View Related

SQL & PL/SQL :: Cursor Not Fetching Special Character

Feb 5, 2013

This is the cursor which fetches from View uk_case_recommends_vw and this view calls the table ukcc_case_casenames below is the query

cursor cur_case_name_search is
select a.*,DENSE_RANK() OVER (ORDER BY sc desc) rank
from uk_case_recommends_vw a
where rownum < 2 + 3;
SELECT
/*+first_rows(4) index(u ukcc_case_casename_idx)*/
persistentid,
casename,
FROM ukcc_case_casenames

Here in this table my case name may be with special character or normal .

For Ex ('R V Wilson') - With Special character
('R V Wilson') - Without Special character

Actually my cursor is not fetching with special character ..neither it is not mentioning as no data found. Find the piece of code below.. I am not getting values after opening the cursor.

open cur_case_name_search;
loop
fetch cur_case_name_search into v_case_name_row;
exit when cur_case_name_search%notfound or
v_case_name_row.rank > v_matchingvalue or
[code]......

View 10 Replies View Related

Forms :: Fetching Value From Dynamic Cursor?

Dec 13, 2011

I have used a dynamic cursor for fetching value from different tables hence table name assigned dynamically during run time but i face an error ORA-06562 type of argument must match type of column and bind variable with error ORA-06212 But i frequently check the table structure and Declared variable there is no mismatch between them.

Code----------

Declare
C_YEAR number(38);
C_LOC VARCHAR2(200);
C_INVNO VARCHAR2(200);
C_INVDT DATE;
C_CTRT VARCHAR2(200);
C_GLCD VARCHAR2(200);

[code]....

View 2 Replies View Related

Forms :: Select Not Fetching Data Within A Cursor

Feb 8, 2011

SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd

[Code]..

The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.

why its not returning any data without the begin-end block.

The entire code has an outer begin-end block within which the cursor is also defined.

View 1 Replies View Related

Fetching Last N Records?

Jun 5, 2012

oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE TABLE MACHINE_M (
IDVARCHAR(20)NOT NULL,
NAMEVARCHAR(20)
)

[Code]....

Now when there records in READING_DWR are 1 lakh, i am getting the o/p in 10 sec.
However when there are bulk records, it is taking 3 to 4 min.

Is there any way to improve my query performance?

View 1 Replies View Related

Select Query Not Fetching Records In 11g?

May 10, 2013

I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.

--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))

--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');

SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0

When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.

View 9 Replies View Related

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Row Count With Sys_ref Cursor

Jul 22, 2011

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]......

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records On Each Date?

Jan 26, 2011

PART_REF REGISTERED_BYREGISTERED_DATE
DCA544519 SVK 1/3/2011
DCA544520 SJA 1/3/2011
DCA544539 SJA 1/3/2011
DCA544572 THS 1/3/2011
DCA544608 GKA 1/3/2011
DCA544610 GKA 1/3/2011
DCA544611 THS1 1/3/2011

I just want a to get the total number of records registerd on particular date by particular user...

View 4 Replies View Related

SQL & PL/SQL :: Count Records Per Every 10 Minutes

Jun 6, 2011

How to count records per every 3 minutes ? we don't want SPs to get answer. Instead of we want single query to get this output.

The sample data has been enclosed with it.

View 13 Replies View Related

SQL & PL/SQL :: Get Count Of Records By Month

Aug 17, 2011

I have the following table. In this table i'm trying to get the count of records by month when its value (SIGNAGE_ONE_LANE_ROAD_AHEAD) is 'Yes' and its giving me the wrong count.

AUDIT_DATE SIGNAGE_ONE_LANE_ROAD_AHEAD SIGNAGE_LANE_CLOSED_AHEAD

1/5/2011 9:30 N/A N/A
2/9/2011 11:50 Yes Yes
2/21/2011 10:00 Yes Yes
2/22/2011 7:00 N/A N/A
3/7/2011 13:35 Yes N/A
3/8/2011 9:15 N/A N/A
3/9/2011 8:40 N/A N/A
3/10/2011 10:00 N/A N/A
[code]....

View 15 Replies View Related

SQL & PL/SQL :: How Many (count Of) Records Got Updated

Dec 8, 2011

I Have a table with 100records.after sometime i updated some records . But after that i want to know how many (count of) records got updated

View 3 Replies View Related

PL/SQL :: Get Records Count Of All Tables

Sep 26, 2012

I am trying to get the record count of all tables using dynamic query. I don't know how to put the value in placeholder. I tried the below code.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
CURSOR table_list
IS
select OBJECT_NAME from user_objects
where object_type in ('TABLE')
[code].......          

View 4 Replies View Related

PL/SQL :: Get Count Of Particular Table Records

Mar 11, 2013

We have table, which maintain log record of gl table. I don't know how much data exist in that table but problem is taking too much time while counting whole records.

View 11 Replies View Related

SQL & PL/SQL :: Get Number Of Records (not A Simple Count())

Mar 4, 2010

using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;

---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---

the output will be as below;

fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF

However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Item List

Apr 17, 2012

One of the tables is having

SQL> select count(1) from item_list;

COUNT(1)
----------
14356529

To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%

I tried finding the fragmentation using the below query.

select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"
from dba_tables
where table_name like ITEM_LIST%'
and owner = 'TEST_USR'
and blocks != 0
order by 4 desc

TABLE_NAME table size kb actual data in table kb
------------------------------ ------------- -----------------------
wasted space kb percent fragmented
--------------- ------------------
ITEM_LIST 5163360 4318302.27
845057.73 16.3664306

View 15 Replies View Related

SQL & PL/SQL :: How To Get Distinct Count Of Records From CSV File

Mar 18, 2010

the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id

"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"

I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4

View 15 Replies View Related

SQL & PL/SQL :: Total Records Count Calculation

Jan 14, 2013

We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .

View 7 Replies View Related

PL/SQL :: How To Count Duplicate Records In A Table

Jun 15, 2012

query to count the duplicate records and to display duplicate records in a table. Iam using oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Table With Different Contents

Mar 31, 2010

I am having a table with contents like

item id, name
100,s_enter
100,p_enter
200,s_enter

i would like the output to be

Variable name cnt_s_enter=1 (this is for 200)
cnt_s_p_enter =1 (this is becoz 100 is both in s_enter and p_enter)

I am working with basic case but thats not working.

select
sum(case when name='s_enter' then 1 else 0 end),
sum(case when name in (s_enter,p_enter) then 1 else 0 end)
from table

View 3 Replies View Related

Getting Count Of Records / Returning Zero For Null Results?

Jul 23, 2009

I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.

This is the pre-existing query:

SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')

The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.

View 14 Replies View Related

Reports & Discoverer :: Count Records With Not Null Column

Jun 25, 2013

i want to count group above report records! want to count department group employees there in time is not null!

my report query

SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....

im doing it with formula column!

function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......

View 18 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

Get Count Of Records Grouped By Another Field From Transactions Table

Nov 5, 2009

I have a table that cannot be changed with a field called transaction_reference in the transactions table. This field contains any number of some values in a look-up table called codes.

The table codes contains 'AA', 'BB', 'CC'.

A typical transaction_reference field may look like 'CC BB' or 'AA' or 'AA CC' or 'AA CC BB' - any number, any order.My goal is to get a count of records grouped by another field from the transactions table.

Transactions table example:
transaction_id | transaction_reference | family
---------------------------------------------
1 | AA BB | foo
2 | BB CC | bar
3 | BB | hello
4 | AA CC BB | foo
5 | BB AA | bar

So the results should look like:

family | code | count
foo | AA | 2
foo | BB | 2
foo | CC | 1
bar | AA | 1
bar | BB | 2
bar | CC | 1
hello | AA | 0
hello | BB | 1
hello | CC | 0

If the counts of 0 (like the third to last and last line above) don't show up I'm ok with that.I put together an explode function like this one here but I'm really not sure where to go from here. I can split the transaction_reference, but I'm not sure what to compare it to or how.

I realize that a field in the transactions table for AA, BB, and CC would be ideal, but I can't do that... the powers that be won't let me change the table.

for each exploded segment from transaction_reference
look for it in the codes table
if it exists, add 1 to the count

View 7 Replies View Related

SQL & PL/SQL :: Can FOR Not Be Used To Loop Through Records Of A Ref Cursor

Jul 1, 2011

I wrote the following block :

set serveroutput on
declare
rec employees%rowtype;
cur SYS_REFCURSOR;
begin
open cur for 'select * from employees where rownum<:a' using 4;
for i in cur
[code]....

It gave errors if we execute is as such, but worked when I commented out the for loop and instead de-commented the simple loop. Does that mean that FOR cannot be used to loop through the records of a ref cursor ?

View 14 Replies View Related

SQL & PL/SQL :: Retrieve Records From Sys_ref Cursor

Nov 25, 2010

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.

View 6 Replies View Related

Dividing The Cursor Records Using Commit Limit

Sep 18, 2011

I have to optimize a batch job which returns > 1 lakh records . I have a commit limit being passed . I am planning to divide the cursor records for processing as follows. If the cursor suppose returns 1000 rows and the commit limit passed is 200 , then i want to fetch 200 records first , bulk collect them into associative arrays and then bulk insert into target table.

After this is done, i will fetch the next 200 records from the cursor and repeat the processing. I would like to know how i can divide the cursor records, and fetch "limit" number of records at a time and also be able to go to the next 200 recs of the cursor next time.

View 1 Replies View Related

SQL & PL/SQL :: How To Skip Some Records In Open Dynamic Cursor

Jan 3, 2011

After opening a dynamic cursor, usually fetch hit record into some variables. However, if I do not want to "FETCH INTO " operate Just only skip this record.

DECLARE
TYPE weak_cur_type IS REF CURSOR;
weak_cur_1 weak_cur_type;
weak_cur_2 weak_cur_type;
vs_dsql VARCHAR2(2048);
vd_create_time DATE;
vn_count NUMBER(8);
vn_total_amount NUMBER(13);

[Code]...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved