Home » SQL & PL/SQL » SQL & PL/SQL » Runtime Select
Runtime Select [message #266988] Wed, 12 September 2007 03:36 Go to next message
tessdeveloper
Messages: 17
Registered: August 2006
Location: Ireland
Junior Member
Hi there,

When I step through a package I'm having a problem getting values inserted into two variables below, I'm getting 0's.

SELECT NVL(SUM(v.numb2),0)
, NVL(SUM(v.numb1),0)
INTO l_var_2
, l_var_1
FROM view_x v
WHERE v.ref_nbr = v_ref_nbr;

however when I execute the statement on its own using the value for v_ref_nbr that is used when stepping through the package I get the correct values - 142681.29 and 130556.15. Why do I only get 0 values when stepping through the packages
Re: Runtime Select [message #266994 is a reply to message #266988] Wed, 12 September 2007 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Then, execute "set role none;" and your query.
What do you get?

Regards
Michel
Re: Runtime Select [message #267025 is a reply to message #266994] Wed, 12 September 2007 05:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
are you REALLY sure about the contents of v_ref_nbr?
Re: Runtime Select [message #267031 is a reply to message #267025] Wed, 12 September 2007 05:19 Go to previous messageGo to next message
tessdeveloper
Messages: 17
Registered: August 2006
Location: Ireland
Junior Member
yep, v_ref_nbr does get populated correctly when running the package. I'm wondering is it a just a problem with being able to select from a view while stepping through the code? it shouldn't really be a problem.
Re: Runtime Select [message #267035 is a reply to message #266988] Wed, 12 September 2007 05:27 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Post the full PL/SQL that you are running, not just a fragment.
Re: Runtime Select [message #267078 is a reply to message #267031] Wed, 12 September 2007 07:55 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
tessdeveloper wrote on Wed, 12 September 2007 05:19
yep, v_ref_nbr does get populated correctly when running the package. I'm wondering is it a just a problem with being able to select from a view while stepping through the code? it shouldn't really be a problem.


are you saying you only see this issue when running in debug?

If that's the case I'd say it's a flaw with the debugger
Re: Runtime Select [message #267099 is a reply to message #267078] Wed, 12 September 2007 08:28 Go to previous messageGo to next message
tessdeveloper
Messages: 17
Registered: August 2006
Location: Ireland
Junior Member
Here is the code that it steps through. I'm beginning to think its a problem with the debugger though.

FUNCTION RMI_Validate_Confirmation (p_csv_header_id IN PLS_INTEGER,p_file_date IN DATE)
RETURN PLS_INTEGER
IS

l_details_count NUMBER := 0;
l_var_1 CSV_CONFIRM_MAIL_DETAILS.CHECKSUM_1%TYPE;
l_var_2 CSV_CONFIRM_MAIL_DETAILS.CHECKSUM_2%TYPE;
l_conf_count NUMBER := 0;

CURSOR c_conf_details
IS
SELECT tx_ref_nbr
, checksum_1
, checksum_2
FROM CSV_CONFIRM_DETAILS cmh
WHERE conf_header_id = p_csv_header_id;

BEGIN
SELECT count_detail_records
INTO l_conf_count
FROM CSV_CONFIRM_HEADERS cmh
WHERE conf_header_id = p_csv_header_id
AND file_identifier = l_file_identifier;
END;

FOR l_conf_details_rec IN c_conf_details LOOP

l_details_count := l_details_count + 1;

BEGIN
SELECT NVL(SUM(v.numb2),0)
, NVL(SUM(v.numb1),0)
INTO l_var_2
, l_var_1
FROM view_x v
WHERE v.tx_ref_nbr = l_conf_details_rec.tx_ref_nbr;
END;

[Updated on: Wed, 12 September 2007 08:50]

Report message to a moderator

Re: Runtime Select [message #267101 is a reply to message #266988] Wed, 12 September 2007 08:33 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The easy way to confirm that would be to put some dbms_output.put_line statements to display the results. If they give you what you expect, then it must be the debugger.
Re: Runtime Select [message #267103 is a reply to message #267099] Wed, 12 September 2007 08:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This code is not complete. It lacks _at least_ a declare, begin and end
Re: Runtime Select [message #267119 is a reply to message #267103] Wed, 12 September 2007 09:15 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...And it is NOT formatted.

Regards
Michel
Previous Topic: Query Please
Next Topic: using Collection variable???
Goto Forum:
  


Current Time: Sat Dec 10 16:46:15 CST 2016

Total time taken to generate the page: 0.09750 seconds