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;
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.
-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;
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).
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.
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
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]....
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.
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.
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.
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 :
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.
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.
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.
procedure vieworderstatus(in_CALL_ID IN NUMBER , in_DAYS_OF_HISTORY in number , out_SUPLY_REORDR_STATUS_LST OUT supplyreordrlist , out_ERR_CODE OUT NUMBER, out_ERR_MESSAGE out varchar2);
This procedure takes retrievs cust_id for given input call_id. The procedure then retrieves order information for that customer and returns using the out ref cursor. I need to test the scenario where the user passes invalid call_id.
The following query returns no data for call_id = 12345 ( invalid call_id) select fk_cust_id into v_cust_id from xcom_call where call_id = in_CALL_ID;
in such cases; I just need to return null ref cursor and log the error but right now when I pass invalid call_id ; I am getting "invalid cursor" error.How do I handle this ?
I am an experienced SAS programmer jumping into PL/SQL for the first time and have already encountered a problem.Let's assume I have 7 records (shown below). (In reality, I have millions of records, but the concept's the same.) The Value field is only populated when it changes. Therefore, I am forced to "fill in the gaps" as I read the data file. It's fairly straightforward. I carry the value foward one record at a time, using it if the Value field in not populated. The ANSWER I want is also shown.
In reading through a PL/SQL book, I realized that only 2 chapters are relevant to what I do. My guess is this solution involves cursors; probably a Cursor FOR loop,
I have a table with more than 200 columns. I would like to declare a cursor to query all the columns for each record (if c1%found then ....). As the table has more than 200 columns is it a good practice to declare a cursor
insert into cte with cteas as ( select hit from radial,gib where no=id ) [code]........
i want to update the values based on with clause so i used like above.but it is time consuming so i planned to use cursor . is it effective or any other methods avail for the above. How to write cursor and use for the above ?
I have come across a code where i need to know exact logic for the use of FOR UPDATE and COMMIT used in a cursor.The code is like :
Declare Cursor c1 is select * from emp FOR UPDATE; Z c1%rowtype; Begin Open C1; Fetch c1 into Z; dbms_output.put_line(z.ename); Commit; Fetch c1 into Z; dbms_output.put_line(z.ename); end;
When i run above code, i get error as below, but first value from table emp i.e. emp.ename is displayed :
Error at line 1 ORA-01002: fetch out of sequence ORA-06512: at line 9
But when i run by removing FOR UPDATE, i get first 2 values displayed without any error message:
Declare Cursor c1 is select * from emp; Z c1%rowtype; Begin Open C1; Fetch c1 into Z; dbms_output.put_line(z.ename); Commit; Fetch c1 into Z; dbms_output.put_line(z.ename); end;
I have the following tables: First one (PART) is the master and other 2 are children.I need to create query that would link both children to the parent. So I was thinking about using embedded refcursor (since my function returns a ref cursor).
on using this ? Is this ok or any other better way of doing this ?
TABLE PART PART_ID, PART_NAME TABLE ABC ABC_ID,PART_ID
I'm modifying a package which contains a function with different parameters (say cno, gno, pname etc) need to be passed though it and that function is called by the cursor later in the program. I need to create a cursor (select statement) for every class (a program set)...i.e. multiple select statements for a program set with different values. The parameters needed to be passed correspond to same columns in different tables...so do i have to refer those tables everytime i'm passing the parameters using a select statement? The Program accepts the parameters as an array (list of parameters) and returns a cursor and the program handles one program set (class) at a time. That parameter list will be in a loop..i.e. it will be repeated for every certain combination (say cno - pname combination). So ... How should i proceed and create a select statement with different parameters declared in the function of type string making it dynamic and returning them using ref cursor?
I have a table where each record has a numerical x-coordinate value as one of its fields. I want to loop through a group of the records that have another field in common in a given order. In a nested loop, I would like to subtract the coordinate of the outer loop from the coordinate of the inner loop for all records in the inner loop that appear later in the sequence. The result is a list of the distances between all coordinates.
Example: x-coordinate 3 4 6 7
It would look like this: 4-3, 6-3, 7-3, 6-4, 7-4, 7-6
I can do this using two nested cursors that select the same thing basically. But the table being selected from is pretty large and it takes forever to keep selecting from the huge table when the inner cursor could just copy the results of the other cursor and repeatedly iterate through them.
Is it possible to copy a cursor's results into another cursor or reset the cursor index back to the beginning so that it doesn't have to do the select statement every time?
