I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question.
select submission_identifier,
submitted_by_person_id,
submission_date,
form_version_date_time,
form_id,
func_get_open_days_2(
submitted_by_person_id,
submission_date,
form_version_date_time,
form_id)` open_days
from form_submission
where submission_identifier = 2070;
submission_identifier is the PK for the form_submission table, so naturally, this query returns one result.
As you can see, the function takes 4 parameters. I'm assuming the values in those parameters are the results of the query AFTER the where clause has been evaluated. Here is a snippet of the function.
create or replace function func_get_Open_Days_2
( SUBMITTED_BY_PERSON_ID in number,
SUBMISSION_DATE in date,
FORM_VERSION_DATE_TIME in timestamp,
FORM_ID in number
)
RETURN number
IS
n1 number:=0;
n2 number:=0;
cursor records is
SELECT
SUBMITTED_BY_PERSON_ID,
SUBMISSION_DATE,
FORM_ID,
FORM_VERSION_DATE_TIME,
AUDIT_DATE_TIME,
STATUS_DATE,
SUBMISSION_STATUS_CODE,
CREATE_USER_ID
from form_state_audit_trail t
where
t.submitted_by_person_id = SUBMITTED_BY_PERSON_ID and
t.form_id = FORM_ID and
t.submission_date = SUBMISSION_DATE and
t.form_version_date_time = FORM_VERSION_DATE_TIME and
t.submission_status_code NOT IN (3469, 3462);
order by t.status_date asc;
rec records%ROWTYPE;
BEGIN
dbms_output.put_line('submitted by person id ='||submitted_by_person_id);
dbms_output.put_line('form id ='||form_id);
dbms_output.put_line('submission date ='||submission_date);
dbms_output.put_line('form_version_date_time ='||form_version_date_time);
-- go through all the durations in audit_trail table
FOR rec in records
LOOP
I have tried hard-coding the return values and putting them in this cursor select query. I receive 1 result.
This suggests to me that the "records" array or list should only be 1 result long. However, I am getting numerous results as it stands now. "records" are being returned which suggest the where clause is not being evaluated using the 4 values/results I'd expect I'm not sure what I'm doing wrong. The the "dbms_output.put_line" are the BEGIN but before the LOOP confirm the expected values are reaching the function.
FORM_ID
within the body of function asfunc_get_Open_Days_2.FORM_ID
, etc.