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
ADVERTISEMENT
May 10, 2013
create or replace package cognos_pk as /* Creates Package Header*/
TYPE project_type IS record( /* A record declaration is used to */
c1 NUMBER /* provide a definition of a record */
); /* that can be used by other variables*/
TYPE project_type1 IS REF CURSOR return project_type; /* Variable declaration */
procedure conosg_sp (result1 out project_type1); /* SP declaration */
end;
[code]....
View 2 Replies
View Related
Apr 16, 2010
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
Table1
Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74
Output
Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
View 9 Replies
View Related
May 4, 2012
I tried to find about sql loader, tried googling and ended up with Orafaq .
1) Is there a way that you can skip few records in the control file.
Assume the control file is loading a file with three records.
CREATE TABLE emp_tab
(
Emp_id NUMBER(15,0),
Name CHAR(25),
Age NUMBER(15,0)
);
The text file is like this name.txt
1;sam;19;
2;jai;22;
;pam;33;
LOAD DATA
INFILE 'C:
ame.txt'
BADFILE 'C:
ame.bad'
DISCARDFILE 'C:
ame.dsc'
APPEND
INTO emp_tab
fields terminated by ";"
TRAILING NULLCOLS
( Emp_id ,
name,
age)
I want to skip the record 3 in the text file as it has no id, is there a way to do this.Can we skip a record based on a condition?
2) What needs to be included in the control file in order to get a return code?
3) Assume the return code = 0 for success and return code = 1 for failure, what will be the return code if 60 out 100 records are loaded and 40 are discarded and written to .bad file?
4) SQL loader does a auto commit, meaning the moment you run the control file, the records are inserted and commited, is there a way to avoid it ?
View 3 Replies
View Related
Aug 3, 2011
I have data file emp.dat in that i have 10000 records. My requirement is i want to skip last 100 records when i am loading it into EMP table using SQL *LODER.
View 6 Replies
View Related
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
Apr 8, 2013
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
View 3 Replies
View Related
Jan 13, 2011
I have a snippet of code
EXCEPTION
WHEN OTHERS THEN
H_Report_Error('sql',Sqlerrm);
end;
And I'm trying to figure out how to catch a error from a loop such as:
OPEN My_cursor FOR SQLCODEBLOCK
LOOP
FETCH a_variable INTO ResultCount;
EXIT
WHEN My_cursor % NOTFOUND ;
BEGIN
--Stuff done
End;
End Loop;
So right now I have a error in the SQLCODEBLOCK, but I can't catch that error. I've tried putting the exception in various places but it wont compile.
View 6 Replies
View Related
Oct 31, 2013
Oracle 11g For a procedure, if I use the hard-code procedure
sp_test( p_rc out sys_refcursor, p_1... p_2 ... ) is begin open p_rc for select * from TEST_TABLE... But now, I want to use the dynamically constructed sql, such as if condition1 then v_sql := ... elsif condition2 then v_sql := ... execute immedite v_sql
How can I return the the cursor p_rc in this case?
View 2 Replies
View Related
Mar 1, 2012
open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';
NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?
View 2 Replies
View Related
Apr 18, 2013
I would like to mention that I won't be able to give the data,specific information(like query or table names) about the issue as this concerns the privacy of some multinational company(where I work).
We have windows server and we are currently using Oracle 10g.
Issue-
We have a ProC, which has a SQL statement where it is trying to OPEN a cursor. The cursor query is huge and fetches data from 3 tables. 3 tables have 5k, 600k and 1.7 millions records respectively but the 'where' condition in cursor query makes the total count to 3k lines only.
Now the problem is , we have a schedule reboot of the server every Saturday night and the next day(Sunday night) when the program runs it gets stuck in the query --> OPEN <cursor> . We wait for 5 hours but this OPEN cursor query never executes or completes, that means our program gets stuck here.Eventually we have to kill the program.We have tried running it after killing as well but it again gets stuck.
Now the interesting part. On Monday business happens as usual but in the night when we again run this program it runs within minutes. The records in the tables are more or less same. Open cursor does not get stuck at all. It runs every night properly but every Sunday night it gets stuck. note that the program does not run on Saturday nights. So on Sunday night it runs for the first time after server reboot.
So there is something with Open cursor statement(with so much data) after server reboot. What exactly Open cursor does in memory or database.
View 11 Replies
View Related
Jul 27, 2012
I have the following code for a Apex(Application Express) project I am developing.
declare
mail_id varchar2(100);
min_skill_cnt number;
skill_cde varchar2(30);
total_leave number;
toal_emp number;
cursor cur is
select S_EMP_EMAIL
from EMP_SKILLS_INFO where SKILLCODE='MGR' and S_EMP_EMAIL = lower(:APP_USER) ;
cursor minskill is
select skill_code,MINRQMT_AM
from skills_code_info
where skill_code in (select skillcode from emp_skills_info where S_EMP_EMAIL = lower(:APP_USER));
cursor leavecnt(v_skill IN VARCHAR2) is
select count(*) from emp_leave_info
where leave_date = :P24_LEAVE_DATE
and emp_email IN (select S_EMP_EMAIL from EMP_SKILLS_INFO where SKILLCODE = v_skill);
cursor empcnt(v_skills IN VARCHAR2) is
[code]...
Ideally this should send email to managers when a particular skill is running short when employee applies for leave. I am getting error that cursor is already open when I run this code. I am not sure which cursor or where it is picking open cursor command.
View 2 Replies
View Related
Feb 26, 2013
Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?
DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......
View 4 Replies
View Related
Sep 1, 2012
So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
V_SQL := 'REPLACE(V_DATA,'TEST',CUR_DATA.'||V_FIELD||');';
EXECUTE IMMEDIATE (V_SQL);
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
View 6 Replies
View Related
Apr 13, 2012
I getting a error Ora-06511 when-new-item-instance trigger raised unhandled exception.
below is the coding for the trigger when-new-item-instance : where the cursor is open? how to close it?
if :blk_master.action_status = 'A' then
declare
doc_slno number;
[Code].....
View 3 Replies
View Related
May 28, 2013
In my environment found maximum open cursor exceeds error. So how can I found the open cursor list and how can I close that cursor without restarting. Any SQL commands to close the open cursor.
View 4 Replies
View Related
Aug 29, 2010
Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.
I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).
View 5 Replies
View Related
Jul 20, 2011
I am creating a db function to select * from table_name to be used by the application for retrieving lookup data. I found many examples for how to return cursor as a result of executing a dynamic sql but don't know the optimum way.
following is my current trial:
CREATE OR REPLACE PACKAGE types AS
TYPE Cursortype IS REF CURSOR;
END;
/
CREATE OR REPLACE FUNCTION F_GEN_SELECT_CURS
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2)
/*This function maps S_ACC_MAJOR_ACCOUNT_LIST CoolGen service
[code].......
View 5 Replies
View Related
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
Aug 21, 2012
I'm currently using Oracle 11.2G and I was wondering if you can declare a dynamic cursor as a strong ref cursor type or can it only be declared as weak type ref cursor? Now, if it can only be declared as a weak type ref cursor, is there any way to avoid having to explicitly declare the type and field names being returned by the cursor? Meaning, is there anyway to avoid have to do something like this...
TYPE example_rec IS RECORD
(
col1 VARCHAR2,
col2 NUMBER,
col3 NUMBER,
etc...
)
Instead, I was wondering if I could just set the fields and their types by setting them as a TABLE OF the ref cursor? The reason I'm wondering if this is possible is because I have a lot of fields being returned in my cursor, and I was hoping on avoiding having to type each one out and its type.
View 12 Replies
View Related
May 24, 2005
I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)
[Code].....
View 4 Replies
View Related
Oct 11, 2013
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....
View 7 Replies
View Related
Nov 11, 2012
Just explaining what I am trying to achieve:
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is
[Code].....
View 5 Replies
View Related
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
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
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
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
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
View Related
Feb 25, 2013
i have a ref cursor and i have used 'open cursor for' statement:
CREATE OR REPLACE PACKAGE aepuser.pkg_test
AS
TYPE cur1 IS REF CURSOR;
PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1);
END;
[code]...
then i want to know that- will oracle automatically deallocate the memory occupied by records in cursor area?if yes, then when it will be free , in case of 'open cursor for' ?
View 7 Replies
View Related
Jan 8, 2009
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
View 5 Replies
View Related
