Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

yet another Apex 21.2 bug? - Validation on Tabular forms

massi_cicJan 11 2022

Hi all,
we have an application written in apex 4.2 where we have a page containing a tabular form with a validation on data, type: Function body (returning error text)
Tabular form source query:

select ID_PROCESS,
TICKET_NUM_PROBLEM,
QUESTION,
dbms_lob.substr(ANSWER,4000,1) ANSWER
from PROCESS_QUESTIONNAIRE
where ticket_num_problem=replace(:P86_TICKET_NUM_PROBLEM,'=',':')

Validation source code:

DECLARE
sid_chk varchar2(40); sid_hidden varchar2(500); 
tmp boolean default true;  n_riga integer;

BEGIN
   FOR i IN 1 .. apex_application.g_f01.COUNT
   LOOP							
        sid_hidden:=apex_application.g_f01(i);
        if sid_hidden is null then 
        tmp := false;
        n_riga := i;
        end if;
   end loop;
    if tmp then 
       return null;
    else 
       return 'Please fill all fields';
    end if;
END;

The problem is that when importing the application in apex 20.2, everything is OK, but importing in apex 21.2.1 , when validation fires and shows the error 'Please fill all fields', the page tries to re-display the tabular forms giving the following error:

report error:
ORA-20001: Error fetching column value: ORA-01403: no data found

Looking into debug log , the code is the following:
MicrosoftTeams-image (1).pngAny help would be appreciated :)
Max

Comments

Jason_(A_Non)
Answer
This works on 10.2.0.3 
select xmlelement("PUDOOUTPUT",xmlelement("PKG",xmlelement("SHIPMENTID",123456),
xmlelement("ERROR",'NO DATA'),
xmlelement("ERRORTEXT",'Error: Cannot find shipper alias in SPL_SHIPPER_ACCOUNTS.')))
from dual;
I suspect the issue is because you have "i.shipmentid" in your SQL statement as you have to table with an alias of "i" in your SQL statement. Do you mean something like l_shipmentid, as a variable from your PL/SQL code?
Marked as Answer by sreese · Sep 27 2020
678345
Hi,I need to generate XML FIle.Since i m new to XML.
I had toregister the valid schema in my DB now i want to generate the XML file and with the refernce to this schema that xml file should be validated one against that schema.

things i hav done till now
->i hav registered the scehma

and the sample XML file provided by client
<?xml version="1.0"?>
<MEDMLDATA xmlns="PhaseForward-MedML-Inform4">
<!-- Insert Trial/Study Information -->
<STUDYVERSION UUID="id"
STUDYNAME="name"
VERSIONDESCRIPTION="text" />

<!-- Insert Site Personnel Information -->
<USER USERNAME="mmeyer"
USERTYPE = "SITE"
ACTIVESTATE = "TRUE"
DELETESTATE = "FALSE"
FIRSTNAME = "Marianne"
LASTNAME = "Meyer"
DISPLAYNAME = "Marianne Meyer"
TITLE = ""
HOMESCREENURL = "./Custom/HomeDefault.html"
PHONE = "(555) 555-1212"
FAX = "(555) 555-1212"
ALTPHONE = ""
EMAIL = "email@domain.test"
ADDRESS = ""
ADDRESS2 = ""
CITY = ""
STATE = ""
ZIPCODE = ""
COUNTRY = ""
BEEPER = ""
USERMUSTRESETPASSWORD = "FALSE"
DESCRIPTION = ""
PASSWORD="changeme"/>

<USER USERNAME="john"
USERTYPE = "SITE"
ACTIVESTATE = "TRUE"
DELETESTATE = "FALSE"
FIRSTNAME = "John"
LASTNAME = "Meyer"
DISPLAYNAME = "John Meyer"
TITLE = ""
HOMESCREENURL = "./Custom/HomeDefault.html"
PHONE = "(555) 555-1212"
FAX = "(555) 555-1212"
ALTPHONE = ""
EMAIL = "email@domain.test"
ADDRESS = ""
ADDRESS2 = ""
CITY = ""
STATE = ""
ZIPCODE = ""
COUNTRY = ""
BEEPER = ""
USERMUSTRESETPASSWORD = "FALSE"
DESCRIPTION = ""
PASSWORD="changeme"/>

<!-- Insert Site Information -->

<SITE NAME= "(01) Massachusetts General Hospital" MNEMONIC="01"
ADDRESS = "1 Main Street"
ADDRESS2 = ""
CITY = "Boston"
STATE = "MA"
ZIPCODE = ""
COUNTRY = "USA"
PHONE = "(555) 555-1212"
ALTPHONE = ""
FAX = ""
EMAIL = ""
TIMEZONE="(GMT-05:00) Eastern Time (US & Canada)"
SITEDATEFORMAT = "MONTH_DAY_YEAR"
STARTDATE = "April 1, 2005">
</SITE>

<SITE NAME= "(02) Boston General Hospital" MNEMONIC="01"
ADDRESS = "1 Main Street"
ADDRESS2 = ""
CITY = "Boston"
STATE = "MA"
ZIPCODE = ""
COUNTRY = "USA"
PHONE = "(555) 555-1212"
ALTPHONE = ""
FAX = ""
EMAIL = ""
TIMEZONE="(GMT-05:00) Eastern Time (US & Canada)"
SITEDATEFORMAT = "MONTH_DAY_YEAR"
STARTDATE = "April 1, 2005">
</SITE>

<!-- Insert SITE and User Mapping Information -->

<SITEGROUP SITENAME="(01) Massachusetts General Hospital">
<USERREF USERNAME="mmeyer"/>
</SITEGROUP>
<SITEGROUP SITENAME="(02) Boston General Hospital">
<USERREF USERNAME="John"/>
</SITEGROUP>

<!-- Insert User ROle Information -->
<RIGHTSGROUP GROUPNAME="CRA">
<!-- Insert Rights -->
<RIGHTREF RIGHT="Print"/>
<RIGHTREF RIGHT="Monitor"/>
<RIGHTREF RIGHT="Canned Reports"/>
<RIGHTREF RIGHT="View CRF"/>
<RIGHTREF RIGHT="View Signature History for CRF"/>
<RIGHTREF RIGHT="View Signature History for CRB"/>
<RIGHTREF RIGHT="Data Export Listings"/>
<RIGHTREF RIGHT="Freeze a CRF"/>
<RIGHTREF RIGHT="Unfreeze a CRF"/>
<RIGHTREF RIGHT="Mark and Unmark a CRF as SVed"/>
<RIGHTREF RIGHT="Freeze a CRB"/>
<RIGHTREF RIGHT="Unfreeze a CRB"/>
<RIGHTREF RIGHT="Change Query State from Candidate to Open"/>
<RIGHTREF RIGHT="Change Query State from Candidate to Deleted"/>
<RIGHTREF RIGHT="Change Query State from Answered to Closed"/>
<RIGHTREF RIGHT="Change Query State from Open to Closed"/>
<RIGHTREF RIGHT="Change Query State from Reissued Candidate to Closed"/>
<RIGHTREF RIGHT="Enter Query in Candidate State"/>
<RIGHTREF RIGHT="Enter Query in Open State"/>
<RIGHTREF RIGHT="Re-issue Query in Candidate State"/>
<RIGHTREF RIGHT="Re-issue Query in Open State"/>
<RIGHTREF RIGHT="Navigate by Visit"/>
<RIGHTREF RIGHT="Navigate by Form"/>
<RIGHTREF RIGHT="Reordering of Patients"/>

<!-- Insert Users for role -->
<USERREF USERNAME="mmeyer"/>
<USERREF USERNAME="john"/>
</RIGHTSGROUP>

</MEDMLDATA>

now i need to generate XML based on the above SAMple XML file,can anybody tell me how to generate XML file based on this..and after generating it should be validated against the registerd schema.
1 - 2

Post Details

Added on Jan 11 2022
#apex-discussions, #apex20-2, #bug, #validation
4 comments
557 views