Forms :: Select Not Fetching Data Within A Cursor
Feb 8, 2011
SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd
[Code]..
The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.
why its not returning any data without the begin-end block.
The entire code has an outer begin-end block within which the cursor is also defined.
View 1 Replies
ADVERTISEMENT
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
Nov 3, 2011
I am working on forms 6i. I have one data block based on table EMP.
Datablock-A: Empno, Ename, Sal
I have FIND Screen: Empno, Ename, FIND Button.
I can search the data through FIND Screen and populate data in Datablock A.
I am using below logic to search data through FIND Screen.
When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.
In Pre-query trigger of DatablockA:
copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');
It's working fine. But below case is failing.
Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.
But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.
why it's failing to pull records if i try to query data in first case only.
View 2 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
Apr 21, 2011
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.
DECLARE
l_vin CLM_MAIN.vin%TYPE;
l_part CLM_MAIN.vin%TYPE;
l_clm_id CLM_MAIN.vin%TYPE;
[Code]....
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.
View 25 Replies
View Related
Feb 5, 2013
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]......
View 10 Replies
View Related
Sep 7, 2010
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;
[Code]...
However, it retrieves the first record only.
The results are displayed like this.
Loop: Result:
1 Full_Name
2 Empty
3 Empty
4 Empty
View 2 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
May 10, 2013
I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.
--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))
--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.
View 9 Replies
View Related
Jun 26, 2008
I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.
This is not happening every time. This problem starts happening only after heavy use of the unix process.
For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.
It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.
View 1 Replies
View Related
Jun 20, 2012
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
[Code]....
View 3 Replies
View Related
May 29, 2011
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)
and we have fetch the
1)today crushed weight
2)shift crushed weight
3)till date cane crushed,
4)total rotoon crushed,
View 5 Replies
View Related
Aug 22, 2010
I want to fetch the data through the cursor and cursor is getting the value of group_code through the variable 'a'. but when i am writing the code like this it is not coming.
My code is like this :
declare
a varchar2(400):='';
cursor c1 is select ref_no,ref_code,company_id from stock_detail where company_id=:global.company_id
[Code]....
View 2 Replies
View Related
Jun 16, 2011
In my select statement i am fetching the data from
OE_ORDER_HEADERS_ALL,OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS,WSH_SERIAL_NUMBERS.
I need appropriate links for that tables.
View 2 Replies
View Related
Mar 11, 2013
I am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.
SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);
Is there anything wrong in the query.
View 2 Replies
View Related
Oct 24, 2010
The below query take time to fetch the data about 25 seconds for 9 rows.
select uctb.member_code As Member_ID,
((uct.price * uct.quantity * i.multiplier) ) As Traded_Value_In_Lacs,
sum(uct.price * uct.quantity) As Total_No_of_Trades,
sum(uct.quantity) As Total_Volume_Of_Trades
[code]......
View 2 Replies
View Related
Mar 14, 2012
How can i get data from Ref cursor on Oracle forms?
View 1 Replies
View Related
Feb 7, 2010
i want to use cursor to get data from db to "control block "(db item =no ) this data had where clause depend on item on other block
this my code :
declare
cursor get_sol is
select SOL_STEP,PROB_ID
from MI_SOLUTION
where PROB_ID=:MI_FORM_PROB.PROB_ID;
begin
go_block('control');
[code]......
when am using when_validate_item trigger error raise :
FRM-40737:Illegal restricted procedure next_record in when_validate_item
that's the trigger ? or how to solve ? in case of execute query in what trigger i will write the same code to get data in case of execute query by user .
View 9 Replies
View Related
Mar 23, 2012
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.
View 1 Replies
View Related
Nov 30, 2012
I have a table structure and data as below.
create table production
(
IPC VARCHAR2(200),
PRODUCTIONDATE VARCHAR2(200) ,
QUANTITY VARCHAR2(2000),
PRODUCTIONCODE VARCHAR2(2000),
MOULDQUANTITY VARCHAR2(2000));
[Code].....
Now here i want to fetch data having condition as
PRODUCTIONDATE >= Monday of current week
so i would skip only first two rows and will have to get all rows.
I tried using below condition but it would give not give data for 2013 values.
to_number(to_char(to_date(PRODUCTIONDATE,'yyyymmdd'),'IW')) >= to_number(to_char(sysdate, 'IW'))
View 5 Replies
View Related
May 1, 2011
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.
View 8 Replies
View Related
Jul 29, 2013
i want to fetch the background color of my text field
i am using
Get_Item_Property('TEXT1',BACKGROUND_COLOR)='r0g127b127
is this valid? if yes then its not working here.
View 1 Replies
View Related
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
Nov 19, 2010
I am not an Oracle programmer but I have been given a task to produce a number of Text files from an Oracle table based on a selection from the table itself.The table consists of many records with a currency code and I need to extract the data into named files based on the currency code.My first idea was to use Cursors and try a select statement matching on the returned value of the cursor but no Joy I can't make it work - perhaps I need to take a new approach.
Current / latest attempt below
DECLARE
CURSOR tmp_splitter_cur IS
SELECT DISTINCT end_consumer_country
FROM tmp_splitter
WHERE 1 = 1;
currency_rec tmp_splitter_cur%ROWTYPE;
[code]....
View 6 Replies
View Related
Jul 9, 2012
How to make cursor to take a select statement
then check the rowcount
in certain case: assign another selete statement to the cursor
View 3 Replies
View Related
Aug 19, 2010
I've create two types:
create or replace type VAL_REC as object
(WAARDE number(20,5) ) ;
create or replace type VAL_TAB is VARRAY(200) of VALREC ;
create or replace type WSN_REC as object
( START_DATE date
, END_DATE date
, DATA VAL_TAB) ;
create or replace type WSN_TAB as table of WSN_REC ;
In a PL/SQL table collecton WSN_TAB is filled. Now I want to create a query (ref cursor) that gives the result:
START_DATE END_DATE WAARDE(1) WAARDE(2) WAARDE(3) etc.
2010-08-15 2010-08-16 20 20 40
2010-08-16 2010-08-17 23 45 40
View 16 Replies
View Related
Mar 16, 2010
I am new to oracle designer, forms. The requirement is to select a csv file in a form ,read the file and load selected columns from a csv file into a table.
I am using CLIENT_TEXT_IO. I want to know how to extract the data from selected columns from csv file and insert into a table if the lenth of the columns are of variable length.
Another condition is that if there are duplicate rows based on orderid then take the maximum order seq nbr.Do I need to use
temp table for this logic?
View 1 Replies
View Related
May 29, 2012
I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:
1. How to add item through block base on number of column which selected table has.
2. How to set value of table column in these mentioned column which added in the block.
3. And at the end retrieve the table data.
View 1 Replies
View Related
Jul 21, 2010
I've been examining som old queries in an existing db due to more and more problems regarding performance. The sql is used as backend for a java/jboss web application with the possibility for users to enter data. With more and more data, there starting to come complaints about the performance.
I stumbled upon a select query with an embedded cursor similar to this :
select id, name ...,
cursor(select id, sequence.... from table2),
cursor(select id, name.... from table3)
from table1
join table4 on (table1.id = table4.id)
where .....
The javacode is a prepared statement with the actual sql as a string and the content of the cursors saved in conjunction with each row.
when i use sqldeveloper to show the explain plan without the cursors, the cost is 2428
when i use sqldeveloper to show the explain plan with just 1 of the cursors, the cost is ~165000
Is there a better way to do this instead of cursors ?
View 2 Replies
View Related
Jan 22, 2012
I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.
CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....
View 5 Replies
View Related
