Home » SQL & PL/SQL » SQL & PL/SQL » Cursor not fetching special character (oracle 10g)
Cursor not fetching special character [message #576441] Tue, 05 February 2013 02:19 Go to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
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 Go to previous messageGo to next message
mvmkandan
Messages: 67
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 #576446 is a reply to message #576441] Tue, 05 February 2013 03:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Actually my cursor is not fetching with special character ..neither it is not mentioning as no data found..


I don't think cursor returns no_data_found exception

Quote:
I am not getting values after opening the cursor


May be EXIT or IFs are failing in the loop.

regards,
Delna

[Updated on: Tue, 05 February 2013 03:02]

Report message to a moderator

Re: Cursor not fetching special character [message #576448 is a reply to message #576445] Tue, 05 February 2013 03:05 Go to previous messageGo to next message
mvmkandan
Messages: 67
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 #576451 is a reply to message #576446] Tue, 05 February 2013 03:18 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
for opening the cursor itself failing Sad .

I am not getting this out put also dbms_output.put_line('cur_case_name_search -> count ' || cur_case_name_search%Rowcount);

I tried as below

open cur_case_name_search;
dbms_output.put_line('cur_case_name_search -> count ' || cur_case_name_search%Rowcount);
dbms_output.put_line('inside get_name_search_id when matching strategy 2 after opening cursor');
Loop
etc..
Re: Cursor not fetching special character [message #576452 is a reply to message #576451] Tue, 05 February 2013 03:21 Go to previous messageGo to next message
mvmkandan
Messages: 67
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 #576457 is a reply to message #576452] Tue, 05 February 2013 03:34 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi,I have corrected that also..but even though i am not getting.. Sad
Re: Cursor not fetching special character [message #576458 is a reply to message #576457] Tue, 05 February 2013 03:38 Go to previous messageGo to next message
mvmkandan
Messages: 67
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
Re: Cursor not fetching special character [message #576460 is a reply to message #576458] Tue, 05 February 2013 04:21 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
This is the output for the below query

SELECT a.*, DENSE_RANK () OVER (ORDER BY sc DESC) RANK
FROM uk_case_recommends_vw a
WHERE ROWNUM < 2 + 3

Persistent_id | casename |SC | Rank
-----------------------------------------------------
CSHEARING244442 | R v John Thomas W |36 | 1
CSHEARING248738 | R v W (John) |36 | 1
CSHEARING7852 | W(C) v W(R) |6 | 2
Re: Cursor not fetching special character [message #576462 is a reply to message #576460] Tue, 05 February 2013 04:35 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
What about the code output?

Veera
Re: Cursor not fetching special character [message #576469 is a reply to message #576462] Tue, 05 February 2013 05:23 Go to previous message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Getting errors.. Working on that..Sure will let you know. Thanks a lot.
Previous Topic: please help me to implement this logic.
Next Topic: exception with merge
Goto Forum:
  


Current Time: Thu Nov 27 01:25:58 CST 2014

Total time taken to generate the page: 0.28171 seconds