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.