Home » SQL & PL/SQL » SQL & PL/SQL » Issue with SELECT query of CURSOR
Issue with SELECT query of CURSOR [message #421563] Wed, 09 September 2009 04:34 Go to next message
MaryAngeline
Messages: 3
Registered: October 2008
Junior Member
Hi,

I have declared a CURSOR as follows in my Por*C code.

DECLARE sample_dt_cursor CURSOR
FOR
SELECT LPAD(TO_CHAR(TO_DATE(sample_dt,'DD-MON-YY'),'MMDDYY'),6,0),
sample_id
FROM SAMPLE_TABLE
WHERE sample_ind= 'N'
AND upper(sample_id) = upper(:db_sample_id)
AND sample_dt <> TRUNC(SYSDATE);

When I run the select query in the sqlplus prompt, I get the Left padded value of the sample_dt (090809 - MMDDYY). But when I fetch the data from the cursor in the code, I'm not getting the left padded value(90809 - MMDDYY).

Can anyone help me resolve this issue ? I'm new to cursors.

Thanks,
Mary Angeline
Re: Issue with SELECT query of CURSOR [message #421565 is a reply to message #421563] Wed, 09 September 2009 04:38 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Show us more detail please. Show us exactly what you are doing with the cursor, along with an explanation of what your end result is meant to be i.e. what are tyou ctually trying to do. Also tell us what datatype sample_dt is (as there is definitely something wrong with
LPAD(TO_CHAR(TO_DATE(sample_dt,'DD-MON-YY'),'MMDDYY'),6,0)

depending upon what your answer to the final question is
Re: Issue with SELECT query of CURSOR [message #421567 is a reply to message #421563] Wed, 09 September 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you get?
The returned value is (or should be) the same.

Please 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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Issue with SELECT query of CURSOR [message #421569 is a reply to message #421563] Wed, 09 September 2009 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK I (think I) understand your problem (but as you didn't show what you get I can't be sure).
You LPAD with NUMBER 0 not CHARACTER '0'. In C it means "end of string" so you get no value.

Regards
Michel

[Updated on: Wed, 09 September 2009 04:45]

Report message to a moderator

Re: Issue with SELECT query of CURSOR [message #421582 is a reply to message #421563] Wed, 09 September 2009 05:08 Go to previous messageGo to next message
MaryAngeline
Messages: 3
Registered: October 2008
Junior Member
Hi,

The sample_dt is of DATE type and when I fetch the value of left padded sample_dt from the CURSOR, I'm getting, say, 90809 instead of 090809.

I need the output of LPAD to return a 6digit number which will represent MMDDYY. The pbm is, for the monthe of Jan-Oct, I'm getting only 5digit number since the month is represented as a single number.

Thanks,
Mary Angeline
Re: Issue with SELECT query of CURSOR [message #421585 is a reply to message #421582] Wed, 09 September 2009 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
MaryAngeline wrote on Wed, 09 September 2009 11:08
The sample_dt is of DATE type


Then remove the to_date, it's just a bug waiting to happen.
Re: Issue with SELECT query of CURSOR [message #421586 is a reply to message #421582] Wed, 09 September 2009 05:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What data type are you fetching this cursor back into?

I think you're fetching it back into a variable of type 'NUMBER' as the converson from Varchar2 to Number would remove any leading zeroes.

Previous Topic: SQL result divided by another SQL result (merged 3)
Next Topic: CASE WHEN
Goto Forum:
  


Current Time: Thu Dec 08 22:10:43 CST 2016

Total time taken to generate the page: 0.07942 seconds