Here is tthe code:
p varchar2(32767) := null;
l_length number := 1;
p_id => 1223125545905315 + wwv_flow_api.g_id_offset,
p_flow_step_id => 2,
p_process_name=> 'Fetch Row from QS_SPECIFICHHNO',
p_process_sql_clob => p,
p_process_error_message=> 'Unable to fetch row.',
p_runtime_where_clause=>'WHERE ( ( (HHNO) = P2_HHNO ) )',
I execute it as follows in SQL Developer:
select * from QS_SPECIFICHHNO
WHERE ( ( (HHNO) = &P2_HHNO ) )
No problem: I receive exactyl one row in my result set which is what I expect.
I think (definitely correct me if I am wrong) Apex is complaining about the following line of code:
It is complaining about a missing expession line 1 ccolumn 9 - |#OWNER#:
This is an Automatic Fetch query. P2_HHNO is a simple text field where the user enters a primary key value (Household Number)
The household number is used in the where clause of the QS_SPECIFICHHNO view.
Here is the view:
CREATE OR REPLACE FORCE VIEW "CASETRANS"."QS_SPECIFICHHNO" ("HHNO","CNAME","PDATE","TDATE","AGE","INWORKNO","ONWORKNO","ONCPS","ONYTHSRV","CPA","TMSUCCESS","VOL","D_N","DLQ","TRU","VPA","PA5","PA46","CPC","COOHPA4","COOHPA5","KINPA5","KINPA4","COOH","CKIN") AS
msaccess_utilities.datediff('d', PDate, TDate, 0) Age,
ORDER BY CaseTransfers.HHNo,
It looks like APEX is trying to concatenate the character F on the froont of the schema name. I am assuming #OWNER# is the schema name. But why it is doing this, I don't know. This seems to be the only place on page 2 where APEX is generating this "F|#OWNERNAME# string.
Hmmm... are you using the l_clob and l_length variables anywhere?
And I'm not sure, at least from what I see, that line 1 column 9 is necessarily referring to the | character. But neither do I know where else it could be. Is this something you could replicate on apex.oracle.com?
This is a Microsoft Access application that I migrated to APEX. All of the reports came over flawlessly - all I had to do was make minor visual changes to report titles. All of the functional forms (non-reports) gives the following error:
ORA-01403: no data found
When I debug they all fail at "...Process "Fetch Row from.... " and each failure has this F|#OWNER" specification in it.
You might be on to something regarding Microsoft Access artifacts. Perhaps swapping out the msaccess_utilities.datediff function with an Oracle function would make a difference. I'm surprised that even works, because I am unable to SELECT that function from DUAL. What version of APEX on what version of database are you using?
Is perfectly valid, it is an APEX DML string
F = Fetch
#OWNER# = the schema from the workspace
QS_SPECIFICHHNO = the table/view
P2_HHNO = The APEX item to match with the key
HHNO = The key in the table/view
The p_runtime_where_clause appears to be invalid;
a) The "WHERE" keyword is not necessary, also it should be '&P2_HHNO.' not '&P2_HHNO'
b) The record should have already matched using the DML string
Try deleting the runtime_where_clause, it should then work.