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.
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.
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]......
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.
/* 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)
I am having a scenario where i have a ref cursor opening and fetching though dynamic sql query. And those values which i get from ref cursor i want to use them for other parameter cursor in for loop.
for example
PROCEDURE script ( p_account_no IN VARCHAR2, p_from_date IN DATE, p_to_date IN DATE, p_subledger_code IN VARCHAR2, p_posted IN VARCHAR2, v_alloc_unalloc IN OUT alloc_unalloc, -- ref cursor declared in package specification. [code]..........
I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.
OPEN p_cur FOR v_query USING p_ht_nm, p_ht_treat_source, var_cycle (1), var_cycle (2), var_cycle (3), var_cycle (4), var_cycle (5), var_cycle (6), var_cycle (7),
[code]...
We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.
QUERY: /* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */ INSERT INTO z_ca_get_lot_id SELECT DISTINCT attrbts.lot_id FROM (SELECT DISTINCT lot_id FROM z_alv_cert_lot_attrbts_syn WHERE NAME = 'Heat' AND UPPER (text_value) = :p_ht_nm) attrbts,
I have two different database servers where I need to migrate table data from one schema to another schema in batch wise for eg say 100 rows. I used BULK COLLECT with LIMIT. But to access BLOB data from table I have facing errors. What could be other approache to do the same.
here pc_work is a table containing BLOB data in sourse schema. I am fetch data from this table to table t1_test_work using dblink but not working
[ declare type array is table of test_work%ROWTYPE; L_DATA array; cursor C is select * from pc_work@prpctrg; begin open C; LOOP
here i have a table called cn_wghmtdt_trn and some table columns are shift_date(date),net_wt(nothing but crushing weight or cane wt),crop_type_code(rotoon or plant),shift_code(its like 1/2/3 but its default 1)
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.
--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.
I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.
Table is P_Order Columns are DELIVERY_POSTCODE and TEL_NO... Condition DELIVERY_POSTCODE has more than 3 TEL_NO
I have dcs_sku table .The record count is 50 thousand in that table.My requirement is to fech every row,create an xml out of it and post the data to some third party.As the count is very huge,I can't select the entire record and do the operation at a time.way which I will run the sql query in a loop,which will fetch 1st from rown 1 to row 1000,next 1001 to 2000,2000 no 'n' row...
I tried the below query:
select * from dcs_sku where rownum between 1 and 200...This gave me the 1st 200 rows and worked fine.
but the moment I changed the query to :
select * from dcs_sku where rownum between 201 and 300:::No result was coming up.
I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.
Here is the code I've used.
PROCEDURE get_details IS
iLoop number := 1;
CURSOR c is
SELECT FULL_NAME FROM GRP_EMPLOYEE WHERE EMPLOYEE_NUMBER = :USER_ID;
-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;
User complaints fetching data from table A is timing-out ( as per application standards). I decided to collect latest statistics(dbms_stats) on index used by query which is running for more than 12 hours.not.It is partition table and got close to 10 millions records.
1) I checked dba_waiters - no rows 2) I checked v$locked_object/v$locks - no rows 3) I'm unable to run explain-plan for any criteria on that table 4) I'm unable to open table data-tab in Toad/ Sql developer. 5) I'm unable to query a single partition; no error but it keeps on running.
