SQL & PL/SQL :: Reporting Using Cursor

Nov 8, 2010

I am using Oracle 11G. I have set of tables in a schema - DDXX_UTIL.

Table Names wll be with the high level qualifiers DDXX_REJ_EMP*

DDXX_REJ_EMPLOYEE
DDXX_REJ_EMPLOYEE_DEPEND
DDXX_REJ_EMPLOYEE_ADDRESS
DDXX_REJ_EMPLOYEE_SAL
DDXX_REJ_EMPLOYEE_EXP

My requirement is to get the number of rejections happened on that day after the batch cycle for the tables with the high level qualifier DDXX_REJ_EMP* and list out each table followed by the rejection cause and count.

Expected Result
-----------------------------------------------------------------
TABLE NAME : DDXX_REJ_EMPLOYEE
REJECTION_CAUSE COUNT
ID LIST MISSING 25
MANDAORY FIELDS IS NULL 56

TABLE NAME : DDXX_REJ_EMPLOYEE_DEPEND
REJECTION_CASUE COUNT
ID LIST MISSING 25
MANDAORY FIELDS IS NULL 56

Given below is the create statement for one of the table.

CREATE TABLE DDXX_UTIL.DDXX_REJ_EMPLOYEE
(
REJECTION_CAUSE VARCHAR2(510 BYTE),
INTEG_REJ_DATE VARCHAR2(10 BYTE)
);

SAMPLE_DATA
DDXX_REJ_EMPLOYEE
REJECTION_CAUSE INTEG_REJ_DATE
INVALID ID 31-OCT-10
INCORRECT FIELD 31-OCT-10
INVALID ID 31-OCT-10
INCORRECT FIELD 31-OCT-10

I need to group by the rejection cause and integ_rej_date for all the tables.

View 5 Replies


ADVERTISEMENT

OEM Grid Control 10g Reporting

Aug 19, 2011

I am using OEM Grid control 10g for monitoring/reporting purposes and a bit new to OEM.I am able to generate database alerts sent via email say for DB_abc1 and also generate reports.I installed Oracle 10g agent on a another box and lets say with DB_abc2 running on it,and did not realise that there was a database control already installed on that box, so I

1.Uninstalled database control on DB_abc2 box.
2.Uninstalled Oracle 10g agent and re-installed it on DB_abc2 box.
2.Was able to configure the DB_abc2 database from OEM Grid control gui ie the dbsnmp user password and was able to connect to it.

But when I put in alert for things like ORA-XXXX errors I am not getting any emails, also when I put in database usage reports(to be sent via emails) the reports are not picking up any data, they are empty.

I checked the agent on the box in question and the heart beat is OK, and I manually uploaded using "emctl upload" and it is sucessful.The only alerts I am getting for DB_abc1 is the agent up/down thats it.

DB_abc1 is a 9208 database and DB_abc2 is 10201 database.

View 2 Replies View Related

SQL & PL/SQL :: How To Show Only Address For Yes Tax Reporting Site

Aug 22, 2013

I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful.

View 7 Replies View Related

Using Total Recall With Streams - Reporting Database?

Jun 2, 2010

this Topic was already discussed in "Physical standby on oracle 11g - Reporting needs" but without finishing.

View 5 Replies View Related

SQL & PL/SQL :: To Create Columns From Distinct Rows In Reporting

Feb 22, 2010

I have a table PRODUCT with following structure:

Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....

There may be any number of records in the table. Now I need to print distinct products in different columns such as.

AF LO SK
100 50 47

There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?

View 2 Replies View Related

PL/SQL :: Extract Values From Text In Reporting Environment

Jun 26, 2012

I need write a query based on a bunch of user supplied IDs. The IDs will be pasted as plain text, one per row, by end user in a memo field in the reporting environment, and I need to do something like this:

SELECT PHONE_NUMBER FROM TELEPHONE
WHERE
ID IN('MEMO_ID1', 'MEMO_ID2',.....)

Reporting environment does not provide any tools to automatically convert plain text into IDs.

View 9 Replies View Related

Performance Tuning :: Copy Data From Transaction (DB1) To Reporting (DB2)

Nov 26, 2010

We are copying our transaction tables data into another database for our reporting applications (say every day midnight refresh will happen).

The Transaction Database has some 30tables. Existing system is following below points and it is taking 2hours to complete.

1) Truncate data from reporting database (or schema)

2) Direct path Insert into reporting database (or schema) as select * from transaction tables.

3) Rebuild index and Enable constraints.

Note: Each tables data will vary from 30lakhs to 50lakhs. Dump/import/export is not advised by the client.

I want to cut down the time i.e., below 2hours. Instead of above method. Can go for a field in each table specifying the time of each records update/insert operation and then pick the modified records only and copy into reporting db.

View 4 Replies View Related

Replication :: Two Schemas To Be Moved / Replicated To New Reporting Database

Oct 19, 2011

I would like to know the Replication method which is fast and the best approach,we need two schemas to be moved/replicated to a new reporting database.It appears that data is to be flown in one way,do we proceed with Materialized view replication or please clarify about Oracle Streams and Advanced replication. what are the factors to decide the replication method.

View 3 Replies View Related

Security :: Audit User Connection On Reporting Database?

Jun 9, 2011

I want to audit user connection on my reporting database, and send a report to application team on monthly basis, with a list of users who are not connected for a month and remove them.

What would be best method, i know there is LOGON trigger, or database level auditing.

View 14 Replies View Related

PL/SQL :: Create Reporting View Based On Date Column

Jul 30, 2012

My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.

Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:

It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01

So i started out my creating like this:

select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union

[Code]....

.and so on till i get 2010-01.

there has to be a better way to do this.

View 7 Replies View Related

Reports & Discoverer :: Reporting Against Ap Invoice Hold Release Name In Oracle 11.5.10

May 9, 2012

why we seem to be unable to report against the ap invoice hold release date using Discoverer in Oracle 11.5.10? the person who wrote our current report used a decode statement to look at the last update date of the release lookup code to create a release date, but i am trying to recreate this in a different tool (Qlikview) and just wanted to understand why we seem to be unable to report on the field as is!

View 4 Replies View Related

Data Guard :: Implement Logical Standby Database For Reporting Purposes

Apr 14, 2010

I working on a Production Environment, where our Database is running on a IBM-AIX platform.I need to implement Logical standby database for reporting purposes, in order to improve the performance.Now the transaction and the reporting is done at the same site itself.I want to know that, operating system and platform architecture (RAM size, no. of CPUs, ORACLE_HOME etc..) on the primary and standby systems must be same or can be different. complete configuration for Dataguard. My os version is,

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

View 1 Replies View Related

Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array

Sep 7, 2007

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.

View 1 Replies View Related

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

Feb 25, 2011

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

View 3 Replies View Related

SQL & PL/SQL :: Converting Numeric Cursor To Ref Cursor?

Sep 11, 2011

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?

View 1 Replies View Related

SQL & PL/SQL :: Open Ref Cursor From Explicit Cursor

Nov 23, 2011

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>

View 5 Replies View Related

PL/SQL :: Can User Exist When Cursor Will Using For Cursor

Aug 7, 2013

Can i user exist when cursor will using For Cursor .

View 15 Replies View Related

PL/SQL :: Cursor In Another Cursor

Aug 31, 2012

i have a program like this

DECLARE
V_VARIABLE1 VARCHAR2(10);

CURSOR_ABC IS select...............;
BEGIN
OPEN CURSOR_ABC;
LOOP
FETCH CURSOR_ABC INTO V_VARIABLE1;
EXIT WHEN CURSOR_ABC%NOTFOUND;

WHAT IF I WANT TO INSERT ANOTHER SAME KINDA CURSOR LOOP HERE LIKE*

CURSOR_XYZ IS select...............;
BEGIN
OPEN CURSOR_XYZ;
LOOP
FETCH CURSOR_XYZ INTO V_VARIABLE2;
EXIT WHEN CURSOR_XYZ%NOTFOUND;
END LOOP;
CLOSE CURSOR_XYZ;
END LOOP;
CLOSE CURSOR_ABC;

View 3 Replies View Related

Passing Ref Cursor

Oct 14, 2010

We receive hand punches (clock data) every day. Normally a person badges in(hand punch) which creates a row in the clock_tran_processed table. The information from that hand punch is the employee id (emp_id) the date hand punch occurred and a work_summary id (wrks_id). At the end of the day, the employee badges out (hand punch out) and another entry in the clock_tran_processed table is created. The new row will have the emp_id (employee name), date the hand punch occurred and the same work summary id from the morning.

Normally hand punches should occur in pairs. One in, one out... or one in, out for lunch, in for lunch, out for day. I am seeing intervals of three and five. Meaning the employee clocked in twice and out once, or in once and out twice. This shouldn't happen.

I am writing a report that will show number of clocks per for all employees that have three(3) or five(5) clock entries.I wrote a ref cursor that gives all the employees that have a count of 3 or 5 and the employee id (emp_id). I need to pass that employee id to another query that will then get me the dates of the clocks.Here is the ref cursor thus far (I am printing those with a count of 3 and 5).

CODEDECLARE

TYPE ClockTran_Refcur IS REF CURSOR;
ClockTran_cur ClockTran_Refcur;
NumClock_num number :=0;
NumClock_name clock_tran_processed.emp_id%TYPE;
[code]...

How do I pass that variable to the get the clktranpro_time's? I am using "Easy Oracle PL/SQL Programming" but I am not seeing this type of example(pgs 140-148).

View 5 Replies View Related

Cursor Pin S Wait On X

Apr 17, 2013

In our environment we have db link to fetch data from other database. Whenever we try to fetch data using the dblink we receive the wait event "cursor: pin S wait on X" and we do not get any result. The db link works fine. what could be the issue.

DB Version : 11.1.0.7 OS: AIX

View 1 Replies View Related

Cursor - Pin S Wait On X

Nov 14, 2006

i am using the Oracle 10 g 10.2 with the windows 2003 3 sessions from a single User shows the currenncy on the top activity graph of the enterprice manager they are not getting so much resources of system....when i go in the detail of the session i get

cursor: pin S wait on X

i am unable to kill these sessions how could i get rid of these sessions

View 2 Replies View Related

SQL & PL/SQL :: Error While Using Cursor

Apr 10, 2013

My task has more lines of code,so i tried to present here only few lines of code where i am getting the error.The following cursor needs to select data from two tables with some conditions that are included in cursor and place those in other table.I used BULK collect.

CURSOR c_arch_trk (p_run_date DATE,
p_nbr_days_arch1 NUMBER,
p_nbr_days_arch2 NUMBER )
IS
SELECT a.SIS_PGM_START_DATE,a.SIS_PGM_END_DATE,a.PGM_MSTR_NBR,a.PGM_TRK_NBR,a.CNTL_LOCN,a.CMPNY_VNDR_NBR,a.AGRMNT_MSTR_NBR,
a.SLS_CONT_NBR,b.PGM_NAME,b.PGM_BASIS,b.AGRMNT_CNTL_LOCN
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Collection Over Cursor?

Jun 30, 2010

What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?

View 1 Replies View Related

SQL & PL/SQL :: Passing Value From One Cursor To Other One

May 24, 2011

I would like to know whether a value obtained from one cursor can be passed to the other cursor as a parameter and by passing it i want to retrieve a list of records and print only the records obtained from the second cursor where the value is passed.

View 3 Replies View Related

SQL & PL/SQL :: Use REF Cursor In Query

Aug 20, 2013

I have a function :

FUNCTION WP19DUMMY(vname IN VARCHAR2) RETURN SYS_REFCURSOR
IS
tmpcur SYS_REFCURSOR ;
BEGIN
OPEN tmpcur FOR SELECT IVALUE FROM GENPARAMS WHERE NAME = vname;
RETURN tmpcur;
END WP19DUMMY;

I want to use the returned REF CURSOR in a SQL query. So I ran the following @ SQL prompt :

SELECT WP19DUMMY('TD') FROM DUAL;

and I am gettng the following result :

WP19DUMMY('TD
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1

IVALUE
-------------
0

BUT I do not want such a result. I want a result that will only return the IVALUE.

View 6 Replies View Related

SQL & PL/SQL :: Use Of Cursor Variables?

Mar 11, 2012

what is use of cursor variables?

View 1 Replies View Related

SQL & PL/SQL :: Looping Through A Ref Cursor

Feb 16, 2008

I have a procedure which has an out value of a ref cursor (made up of different columns from different tables). I would like to call that procedure from another procedure and loop through each row.

I know how to call my stored procedure but I don't know how to loop through it.

View 5 Replies View Related

SQL & PL/SQL :: Varray To Cursor

Mar 31, 2011

Iam passing the varray to cursor as show below.

type t is varray(50) of number;
t1 t;
cursor c1(t3 t) is select name from temp2 where id in(t3(1),t3(2));
name1 varchar2(20);
begin
t1 :=t(501,384,3242,106);
[code]......

my question varray size is 50, while creating the cursor i have to pass the 50 values like t3(1),t3(2).....t3(50). I have tried to use t3.first..t3.last in IN condition but throwing some error, any other method/hints to pass the values dynamically to cursor IN condition.

View 5 Replies View Related

SQL & PL/SQL :: Ref Cursor As OUT Parameter

Aug 8, 2012

CREATE OR REPLACE PACKAGE test_package IS
TYPE refcur IS REF CURSOR;
END test_package;
CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT test_package.refcur)
AS
BEGIN
OPEN o_cursor FOR
SELECT * FROM emp;
END get_info;

What is the advantage of using refcursor variable as OUT parameter in procedure. Instead of that why cannot we use variables or TYPE variables. use ref cursor as OUT parameter in procedure.

View 1 Replies View Related

SQL & PL/SQL :: Limitations Of Ref Cursor?

Dec 31, 2011

limitations of REf cursor? and What is the difference in declaring as SYS_REFCURSOR and REFCURSOR?

View 1 Replies View Related







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