SQL & PL/SQL

Hi all,

I'm getting SQLCODE = 100 returned from a database function following an attempted selection which I know should be returning one row of data.
I've proved this by doing a simple select..

SELECT xml_reference
        FROM mydmap
        WHERE source_table = 'PERSON' AND
              source_field = '<CHANGETYPE>';
              
XML_REFERENCE                           
--------------------------------------- 
employeeChangeType                      

1 rows selected 

I've also written a test function tries to emmulate the erroring function at the point of failure (the erroring function is 1500 lines long so a bit hefty to post here.)
Below is a snippet from the original. The problem occurs at the selection in line 134.

...
...
...
129   -- define change type item string
130   v_cChangeTypeMapping := '<CHANGETYPE>';
131
132   BEGIN
133      -- get employee change type xml reference
134      SELECT xml_reference
135        INTO v_cEmployeeChangeType
136        FROM mydmap
137         WHERE source_table = 'PERSON'
138                 AND source_field = v_cChangeTypeMapping;
139   EXCEPTION
140      WHEN OTHERS THEN
141         v_sys_error := SQLCODE;
142   END;
143
144   v_nError := v_sys_error;
145   v_nRowCount := SQL%ROWCOUNT;
146
147   IF v_nError <> 0
148     OR v_nRowCount = 0 THEN
149   BEGIN
150      -- RETURN;
151      RETURN v_sys_error ;
152
153   END;
154   END IF;
...
...
...

Here is my test function. This performs perfectly.

create or replace
function my_test return varchar2 as

v_returnValue VARCHAR2(500) := '';
v_cChangeTypeMapping VARCHAR2(50);


BEGIN
  v_cChangeTypeMapping := '<CHANGETYPE>';
  BEGIN
    SELECT xml_reference
        INTO v_returnvalue
        FROM mydmap
        WHERE source_table = 'PERSON' AND
              source_field = '<CHANGETYPE>';
  EXCEPTION
    WHEN OTHERS THEN
      v_returnValue := SQLCODE;
  END;
  RETURN v_returnvalue;
END my_test;

So now I'm confused :-|
Can anyone shed some light as to what might be going on?

Almost forgot.. I'm using Oracle 10g Express Edition and doing my debugging/coding from within SQL Developer.

Thanks in advance


Phil C.