Oracle Cursor Is Not Working as Expected

Here is the procedure,

CREATE OR REPLACE PROCEDURE provsnXmlCmprsn (
            encyNo SAS_PRO_CTL.AGENCYNO%TYPE, period SAS_PRO_CTL.PERIODE%TYPE) IS

xmlContent SAS_PRO_XML.XMLCONTENT%TYPE;
sasProvisionId SAS_PRO_CTL.SASPROVISIONID%TYPE;

CURSOR crsrXml IS
SELECT XMLCONTENT, c.SASPROVISIONID FROM SAS_PRO_XML x, SAS_PRO_CTL c
  WHERE x.SASPROVISIONID = c.SASPROVISIONID AND c.PERIODE = period 
                                    AND c.AGENCYNO = agencyNo ORDER BY XMLLINENO;

BEGIN
DBMS_OUTPUT.put_line('Params: ' || agencyNo || ', ' || period);

OPEN crsrXml;
LOOP
  FETCH crsrXml INTO xmlContent, sasProvisionId;
  EXIT WHEN crsrXml%NOTFOUND;
    DBMS_OUTPUT.put_line('XML Content Length: ' || LENGTH(xmlContent));
END LOOP;
CLOSE crsrXml;

END provsnXmlCmprsn;

The query in the cursor is retrieving 5 rows, whereas 1 row is expected, according to the condition and argument values. The same query results in 1 row, when run independently. And the surprising part is, the query in the cursor always return 5 rows no matter if the condition, c.PERIODE = period AND c.AGENCYNO = agencyNo, passed or not. Which clearly means that this query,

SELECT XMLCONTENT, c.SASPROVISIONID FROM SAS_PRO_XML x, SAS_PRO_CTL c
  WHERE x.SASPROVISIONID = c.SASPROVISIONID AND c.PERIODE = period 
                                    AND c.AGENCYNO = agencyNo ORDER BY XMLLINENO;

and this query,

SELECT XMLCONTENT, c.SASPROVISIONID FROM SAS_PRO_XML x, SAS_PRO_CTL c
  WHERE x.SASPROVISIONID = c.SASPROVISIONID ORDER BY XMLLINENO;

are behaving same inside the cursor. This, AND c.PERIODE = period AND c.AGENCYNO = agencyNo, part is not at all considered. Any idea what's going wrong?

Answers


One of your parameters has the same name as the column: AGENCYNO. Because of the way scoping works this evaluates to 1=1. This is why it is good practice to give parameters unique names, for example by prepending them with p_.

You should find that

AND c.PERIODE = p_period AND c.AGENCYNO = p_agencyNo

returns the desired one row. Strictly speaking you don't need to change the name of period to p_period because it is already distinguished from periode. But consistency is a virtue in software engineering.


Need Your Help

Alternative to maven assembly:single for packaging

java maven maven-assembly-plugin

What are the alternatives for packaging a standalone java application apart from creating a big fat assembly, which is horrible in some scenarios as

erl does not autocomplete code loaded using -pa

autocomplete module erlang loading erl

This is my first couple of hours of Erlang experimentation. So be gentle, please.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.