Home » SQL & PL/SQL » SQL & PL/SQL » Cursor select (Oracle 10g)
Cursor select [message #338107] Sun, 03 August 2008 11:14 Go to next message
Adarshsk
Messages: 5
Registered: August 2008
Junior Member
I am having a stored procedure as shown below:

Here if i hardcode the drptmn_id as 1000,
its retreiving all the rows corresponding to 1000.
but if i use
the variable drptmn_id, the select statement is
retreiving all the rows....
drptmn_id evaluates to be 1000 only.....
pls help me.......
PROCEDURE sp_GetSubsForaDept(dprtmn_cd IN varchar2,
cur_subscrs OUT t_cursor) 
IS
dprtmn_id integer(10);
lv_cd varchar2(3);
v_name varchar2(100);
BEGIN
lv_cd := dprtmn_cd;

SELECT DPRTMN_ID INTO dprtmn_id FROM 
TMP_ENH_NXA_DPRTMN WHERE DPRTMN_CD = lv_cd;
[b]dbms_output.put_line(dprtmn_id);[/b]

OPEN cur_subscrs for
SELECT SBP.SBSCRP_NM FROM TMP_ENH_NXA_SBSCRP SBP WHERE SBP.DPRTMN_ID = dprtmn_id;

--To print the values in the cursor
LOOP
        FETCH cur_subscrs INTO v_name;
        EXIT WHEN cur_subscrs%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

END sp_GetSubsForaDept;

Output with 1000 hardcoded is 4 rows, which is correct,
but with the variable, its retrieving 15 rows,
i.e all the rows in the
table(where condition is not working)....
Thanks in Advance......

[Updated on: Sun, 03 August 2008 12:02] by Moderator

Report message to a moderator

Re: Cursor select [message #338110 is a reply to message #338107] Sun, 03 August 2008 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of writing in red size 3 and bold (which is too large for my screen and so unreadable) you should read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals) and don't use IM speak.

Regards
Michel
Re: Cursor select [message #338111 is a reply to message #338110] Sun, 03 August 2008 11:59 Go to previous messageGo to next message
Adarshsk
Messages: 5
Registered: August 2008
Junior Member
Sorry. now i have edited the code as per the standards,

Thanks and Regards,
Adarsh.S.K
Re: Cursor select [message #338112 is a reply to message #338111] Sun, 03 August 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I added code tags for you, do you see the differences in readability?

Quote:
Here if i hardcode the drptmn_id as 1000,

Where? Not in the code you posted.

Use SQL*Plus, copy and paste what you do, all what you do and don't forget to format it with code tags.

Regards
Michel
Re: Cursor select [message #338123 is a reply to message #338107] Sun, 03 August 2008 21:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
WHERE SBP.DPRTMN_ID = dprtmn_id;

You used the variable with the same name as a table column.
Rather than trying to fool Oracle with table alias (which may be useless as column name is probably found first anyway), rename the variable.
You shall introduce a naming convention, e.g. the one described in http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6729304326802.
You may adjust it or search for "Oracle naming conventions" to find the more fitting one.
Re: Cursor select [message #339350 is a reply to message #338123] Thu, 07 August 2008 05:47 Go to previous message
Adarshsk
Messages: 5
Registered: August 2008
Junior Member
Thanks a lot. Meanwhile sorry the delayed response from me..
Thanks
Previous Topic: Query approach
Next Topic: Can we say the connect string as a database?
Goto Forum:
  


Current Time: Thu Dec 08 12:40:54 CST 2016

Total time taken to generate the page: 0.19171 seconds