| Cursor not fetching special character [message #576441] |
Tue, 05 February 2013 02:19  |
 |
vnithya
Messages: 20
Registered: December 2012
Location: Chennai
|
Junior Member |
|
|
Hi All,
Please help..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 casename 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..
Please 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
o_pid_rank_tab.count >= ukcc_constants.c_casename_search_threshold + 1;
v_prev_score := v_case_name_row.sc;
if nvl(v_persistentid, '~') != v_case_name_row.persistentid
then
if check_duplicates(v_case_name_row.persistentid,o_pid_rank_tab)
then
o_pid_rank_tab.extend;
o_pid_rank_tab(o_pid_rank_tab.count) := ukcc_pid_score_type(v_case_name_row.persistentid,
v_case_name_row.sc);
end if;
end if;
end loop;
close cur_case_name_search;
|
|
|
|
| Re: Cursor not fetching special character [message #576445 is a reply to message #576441] |
Tue, 05 February 2013 03:00  |
mvmkandan
Messages: 66
Registered: May 2010
Location: Trivendrum
|
Member |
|
|
Hope this will be fine now..
Hi All,
Please help..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 ukcc_case_casenames my casename may be with special character or normal .
For Example - ('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..
Please 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 o_pid_rank_tab.COUNT >=
ukcc_constants.c_casename_search_threshold + 1;
v_prev_score := v_case_name_row.sc;
IF NVL (v_persistentid, '~') != v_case_name_row.persistentid
THEN
IF check_duplicates (v_case_name_row.persistentid, o_pid_rank_tab)
THEN
o_pid_rank_tab.EXTEND;
o_pid_rank_tab (o_pid_rank_tab.COUNT) :=
ukcc_pid_score_type (v_case_name_row.persistentid,
v_case_name_row.sc
);
END IF;
END IF;
END LOOP;
CLOSE cur_case_name_search;
This is correct..
|
|
|
|
|
|
| Re: Cursor not fetching special character [message #576448 is a reply to message #576445] |
Tue, 05 February 2013 03:05 |
mvmkandan
Messages: 66
Registered: May 2010
Location: Trivendrum
|
Member |
|
|
LOOP
FETCH cur_case_name_search
INTO v_case_name_row;
DBMS_OUTPUT.PUT_LINE('v_case_name_row.RANK ' || v_case_name_row.RANK);
DBMS_OUTPUT.PUT_LINE('v_matchingvalue ' || v_matchingvalue);
DBMS_OUTPUT.PUT_LINE('o_pid_rank_tab.COUNT ' || o_pid_rank_tab.COUNT);
DBMS_OUTPUT.PUT_LINE('ukcc_constants.c_casename_search_threshold + 1' || ukcc_constants.c_casename_search_threshold + 1);
EXIT WHEN cur_case_name_search%NOTFOUND
OR v_case_name_row.RANK > v_matchingvalue
OR o_pid_rank_tab.COUNT >=
ukcc_constants.c_casename_search_threshold + 1;
Check the output of the given DBMS_OUTPUT lines, It may be getting failed....
Veera
|
|
|
|
|
|
| Re: Cursor not fetching special character [message #576452 is a reply to message #576451] |
Tue, 05 February 2013 03:21 |
mvmkandan
Messages: 66
Registered: May 2010
Location: Trivendrum
|
Member |
|
|
SELECT
/*+first_rows(4) index(u ukcc_case_casename_idx)*/
persistentid,
casename,
FROM ukcc_case_casenames
In this query, after the casename comma (,) is there. so the view may be in invalid stage... check the view is in valid or not.
If it is valid, execute the query to open the cursor alone...
Regards
Veera
[Updated on: Tue, 05 February 2013 03:21] Report message to a moderator |
|
|
|
|
|
| Re: Cursor not fetching special character [message #576458 is a reply to message #576457] |
Tue, 05 February 2013 03:38 |
mvmkandan
Messages: 66
Registered: May 2010
Location: Trivendrum
|
Member |
|
|
please Execute the below code and give me the output.
DECLARE
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;
BEGIN
DBMS_OUTPUT.put_line ('BEFORE OPEN CURSOR');
OPEN cur_case_name_search;
DBMS_OUTPUT.put_line ('AFTER OPEN CURSOR');
LOOP
DBMS_OUTPUT.put_line ('BEFORE FETCH CURSOR');
FETCH cur_case_name_search
INTO v_case_name_row;
DBMS_OUTPUT.put_line ('AFTER FETCH CURSOR');
EXIT WHEN cur_case_name_search%NOTFOUND
OR v_case_name_row.RANK > v_matchingvalue
OR o_pid_rank_tab.COUNT >=
ukcc_constants.c_casename_search_threshold + 1;
DBMS_OUTPUT.put_line ('v_case_name_row.RANK ' || v_case_name_row.RANK);
DBMS_OUTPUT.put_line ('v_matchingvalue ' || v_matchingvalue);
DBMS_OUTPUT.put_line ('o_pid_rank_tab.COUNT ' || o_pid_rank_tab.COUNT);
DBMS_OUTPUT.put_line
( 'ukcc_constants.c_casename_search_threshold + 1'
|| ukcc_constants.c_casename_search_threshold
+ 1
);
v_prev_score := v_case_name_row.sc;
IF NVL (v_persistentid, '~') != v_case_name_row.persistentid
THEN
IF check_duplicates (v_case_name_row.persistentid, o_pid_rank_tab)
THEN
o_pid_rank_tab.EXTEND;
o_pid_rank_tab (o_pid_rank_tab.COUNT) :=
ukcc_pid_score_type (v_case_name_row.persistentid,
v_case_name_row.sc
);
END IF;
END IF;
END LOOP;
CLOSE cur_case_name_search;
END;
--------------
Reply me what is the output of the below query.
SELECT a.*, DENSE_RANK () OVER (ORDER BY sc DESC) RANK
FROM uk_case_recommends_vw a
WHERE ROWNUM < 2 + 3
Regards
Veera
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
.