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.
The below sql is executed by reading the flat file row by row which is having more than 2 million rows.
SELECT CUST_SEQ_R, BANK_BRANCH_C INTO v_CustNo, v_BrnachCode
FROM TABLE1
WHERE ACCOUNT_NO = v_Acc_No
AND VIST_NO = v_VistnO
AND CUST_JOIN_DATE = to_Date(v_Cust_Date,'yyyymmdd');
The procedure opens the file, reads one record, parses it, then it executes few select statements then it loops back to the top where it reads one more record and runs through each activity again. How to read batch of records rather than one record at a time. Is something like creating staging table or by using bulk collect.
v_Acc_No,v_VistnO,v_Cust_Date are variables that read from below file.
test data:
BNC192363A1291197OQ6HA0H05557004559020080528
BNC194566A1280615VT8FA0H05917004016020080530
BNC192588A1263301VB2LE0G11065005707020080519
BNC178369A1295822MM4XB0G11820006839020080512
I have a table with these rows,
Name No
one1
two2
Without using plsql looping constructs, how to achieve this in a simple sql statement?
I am using an external table with numbers and joining the external table to fetch data and Insert into another table.I am altering the table within procedure to pass csv file name as parameter as It'll change each time. It's working fine but in case if my grouping id is having two gids, then Header from csv file is also inserted as row in to coupon_personlization_members.
I gave skip 1 in external table but for second GID, header is inserted as row.I don't want header to be inserted as row but header will be there on csv file.
Here is my procedure :
CREATE OR REPLACE
PROCEDURE proc_coupon_load(
p_grouping_id NUMBER,
p_file_name VARCHAR2)
[code]..
I am Using the Check Box in the Detailed Block & I Have Assigned values 'Y' and 'N' when Checked & Unchecked If there are 3 Values Retrieved in the Detailed Block, Among that 2 Values are Checked the Cursor is Directly going the 2nd Value and Displaying the 2nd Value 2 Times rather than Displaying the 1st Value.
Below Attached is the Code Which i am Using.
GO_BLOCK ('ITEM_RESULT');
L_ITEM_CODE := :ITEM_RESULT.ITEM_CODE;--71720;--
first_record;
loop
message (' item id is '|| L_ITEM_CODE || '-' || :ITEM_RESULT.CHECKBOX);
if L_ITEM_CODE is not null and :ITEM_RESULT.CHECKBOX = 'Y' then
begin
[code]....
How can I loop through unchecked checkboxes using this construction: FOR i IN 1 .. apex_application.g_f01.COUNT (its for checked)
View 5 Replies View RelatedI have two blocks, both are multi record block. 1st block is control block and second is database block. Both the blocks have same fields(Example: Location,Location_name,Location_Type). In the first block(Control Block) I have check box. My goal is when I check the checkbox and click on add button all the records which are selected in first block should go to second block.
View 3 Replies View RelatedI am trying to improve a procedure which is looping through a query to make inserts.
FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....
Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
Using Apex 4.2
BackgroundCreated a Tabular Form based on a view. Removed all the default multi-row updates and deletions, buttons and processes.I am using a tabular form because of the fact that I can gain access to a row selector.The row data itself is all read only so in effect this is a report with a row selector.This is because I can't find a way to select a row an interactive report, well I can and did but this was a work around. ProblemI have created a button to process the selector to run the below code:
begin
kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'Selected Check Boxes:'||wwv_flow.g_f01(1)) ;
kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'Selected Check Boxes:'||wwv_flow.g_f02(1)) ;
for i in 1..wwv_flow.g_f01.count
loop
kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'i:'||i) ;
kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'WF ID:'|| wwv_flow.g_f02 (wwv_flow.g_f01.count)) ;
end loop;
end;
I just wanted to see what would be output. For one row this works fantastically and I can find the ID based on the rowWhen I select more than one row the loop run's through for the number of rows but then also runs for the number of rows selected.So if I selected threee rows this above procedure would run three times, each times looping three times.I have set the execution scope to: For Created and Modified Rows in the button condition that appears.RequiredI only want to loop through the selected rows once per button clickORKnow which row I am currently looping through.
I created a view in DB2 .I copied the code of view from DB3 and created in DB1. The view got created but with compilation errors.
SQL>
SQL> CREATE OR REPLACE FORCE VIEW bala.cee_efacts_pie_order_v (cenumber,
2 installationid,
3 areanumber,
4 clli,
5 servicedate,
6 forecasttype,
7 jobid,
8 shippriority,
[code]....
I checked the status of view in user_objects , it was invalid.How to make it valid ?
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 Relatedi 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;
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.
DB Version : 11.1.0.7 OS: AIX
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]....
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies View RelatedI 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.
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 RelatedI 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.
what is use of cursor variables?
View 1 Replies View RelatedIam 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.
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.
limitations of REf cursor? and What is the difference in declaring as SYS_REFCURSOR and REFCURSOR?
View 1 Replies View Relatedprocedure 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 ?
