Muhammad Abdul Qaium is a Database Engineer/Oracle Apex Developer/BI Developer (in Connecticut, USA) who is an Oracle Certified Cloud Architect Professional, OCI Autonomous DB specialist as well as Oracle Business Intelligence Foundation Suite 11g Certified Implementation Specialist with extensive expertise in Database design , PL/SQL, Oracle Apex, Microsoft SSIS, ETL, Power BI, Qlik Sense, BI Publisher, Jasper Report, OBIEE. Contact: qaiuminfo@gmail.com
Run Time Validation Or Validation From Dynamic Action By JavaScript in Oracle Apex
STEP-1: CREATE JS FUNCTION ON PAGE LEVEL JUST PASTE THE BELOW CODE
function myerror(){
var myval = apex.item('P6_ERR').getValue();//document.getElementById('P6_ERR').value;
apex.message.clearErrors();
apex.message.showErrors(
[
{
"type": "error",
"location": "page",
"message": myval
}]
);
}
STEP-2: CALL THE FUNCTION ON ONCHANGE EVENT ON YOUR SPECIFIC ITEM ON CUSTOM ATTRIBUTE
onchange="myerror();"
STEP-3: CREATE A DYNAMIC ACTION ON CLICK OR AS YOU WISH PASTHE BELOW SAMPLE CODE
EVENT: CLICK
ACTION: EXECUTE PL/SQL CODE
BEGIN
IF LENGTH(:P6_NEW)>=10 THEN
:P6_ERR :='Length must not exceed 10 digit....';
--RAISE_APPLICATION_ERROR(-20001,'Length must not exceed 4 digit....');
elsif LENGTH(:P6_NEW)>=5 THEN
:P6_ERR :='Length must not exceed 4 digit....';
end if;
exception
when others then
:P6_ERR :=SQLERRM;
--RAISE_APPLICATION_ERROR(-20001,'Length must not exceed 4 digit');
END;
STEP-1: CREATE JS FUNCTION ON PAGE LEVEL JUST PASTE THE BELOW CODE
function myerror(){ var myval = apex.item('P6_ERR').getValue();//document.getElementById('P6_ERR').value; apex.message.clearErrors(); apex.message.showErrors( [ { "type": "error", "location": "page", "message": myval }] ); }
STEP-2: CALL THE FUNCTION ON ONCHANGE EVENT ON YOUR SPECIFIC ITEM ON CUSTOM ATTRIBUTE
onchange="myerror();"
STEP-3: CREATE A DYNAMIC ACTION ON CLICK OR AS YOU WISH PASTHE BELOW SAMPLE CODE
EVENT: CLICK
ACTION: EXECUTE PL/SQL CODE
BEGIN IF LENGTH(:P6_NEW)>=10 THEN :P6_ERR :='Length must not exceed 10 digit....'; --RAISE_APPLICATION_ERROR(-20001,'Length must not exceed 4 digit....'); elsif LENGTH(:P6_NEW)>=5 THEN :P6_ERR :='Length must not exceed 4 digit....'; end if; exception when others then :P6_ERR :=SQLERRM; --RAISE_APPLICATION_ERROR(-20001,'Length must not exceed 4 digit'); END;
Work With apex_collection in Oracle Apex
1. Create apex_collection :
apex_collection.create_collection('NAME');
2. Existence Check:
apex_collection.collection_exists ('NAME');
3. Add Member:
apex_collection.add_member (p_collection_name => 'NAME',
p_c001 => :P2_ITEM1,
p_c002 => :P2_ITEM2,
p_c003 => :P2_ITEM3);
4. Collection to Database table data transcfer:
BEGIN
FOR i IN ( SELECT c001 AS a, c002 AS b, c003 AS c
FROM apex_collections
WHERE collection_name = 'NAME'
ORDER BY 1)
LOOP
INSERT INTO DATABASE_TABLE (COLUMN1, COLUMN2, COLUMN3)
VALUES (i.a, i.b, i.c);
END LOOP;
COMMIT;
END;
5. Truncate Collection:
apex_collection.truncate_collection(p_collection_name => 'NAME');
6. Delete Collection Member :
apex_collection.delete_member(
p_collection_name => collection name,
p_seq => member sequence number);
7. Delete All Collecion Members:
apex_collection.delete_members(
p_collection_name => collection name,
p_attr_number => number of attribute used to match for the specified
attribute value for deletion,
p_attr_value => attribute value of the member attribute used to match for deletion);
8. Delete Collection :
apex_collection.delete_collection (
p_collection_name =>'NAME');
1. Create apex_collection : (Collection Name Must be in Capital letter) apex_collection.create_collection('NAME');
BEGIN FOR i IN ( SELECT c001 AS a, c002 AS b, c003 AS c FROM apex_collections WHERE collection_name = 'NAME' ORDER BY 1) LOOP INSERT INTO DATABASE_TABLE (COLUMN1, COLUMN2, COLUMN3) VALUES (i.a, i.b, i.c); END LOOP; COMMIT; END;
apex_collection.delete_member( p_collection_name => collection name, p_seq => member sequence number);
7. Delete All Collecion Members:
apex_collection.delete_members( p_collection_name => collection name, p_attr_number => number of attribute used to match for the specified attribute value for deletion, p_attr_value => attribute value of the member attribute used to match for deletion);
8. Delete Collection :
Re Assign Oracle Table Column Without Delete Data From Table Using (Rebuild form TOAD)
***Table ->altermode->Rebuild-> Script-> Re Assign Column -> Finally Execute.***
-- **********************************************************************
-- Note: This rebuild script is not meant to be used when a possibility *
-- exists that someone might try to access the table while it is *
-- being rebuilt! If you need online table rebuilding and you *
-- are on Oracle 10g or newer, use the dbms_redfinition wizard *
-- under database -> optimize. (Requires DB Admin module) *
-- *
-- Locks are released when the first DDL, COMMIT or ROLLBACK is *
-- performed, so adding a "Lock table" command at the top of this *
-- script will not prevent others from accessing the table for *
-- the duration of the script. *
-- *
-- One more important note: *
-- This script will cause the catalog in replicated environments *
-- to become out of sync. *
-- **********************************************************************
-- Table Rebuild script generated by Toad
--
-- Original table: TEST1
-- Backup of table: TEST1_X
-- Date: 11/05/2019 12:48:57
***Table ->altermode->Rebuild-> Script-> Re Assign Column -> Finally Execute.***
-- **********************************************************************
-- Note: This rebuild script is not meant to be used when a possibility *
-- exists that someone might try to access the table while it is *
-- being rebuilt! If you need online table rebuilding and you *
-- are on Oracle 10g or newer, use the dbms_redfinition wizard *
-- under database -> optimize. (Requires DB Admin module) *
-- *
-- Locks are released when the first DDL, COMMIT or ROLLBACK is *
-- performed, so adding a "Lock table" command at the top of this *
-- script will not prevent others from accessing the table for *
-- the duration of the script. *
-- *
-- One more important note: *
-- This script will cause the catalog in replicated environments *
-- to become out of sync. *
-- **********************************************************************
-- Table Rebuild script generated by Toad
--
-- Original table: TEST1
-- Backup of table: TEST1_X
-- Date: 11/05/2019 12:48:57
1.Create report and take a column as link.
2.Create another region for modal and put a static ld like ‘m’
3.Then Link -> Target -> URL type and wright the code like this one.
Here P1_ID is the item where you want to assign the value and #ID# is report value.
And ‘m’ is the static id of modal region and this region template should be Inline Dialog .
javascript:$s('P1_ID','#ID#','#ID#'); javascript:openModal('m');
