Oracle Apex Exam Flash Cards
APEX_APPROVALS
- APEX_APPROVALS API package allows you to manage approval and Human Tasks programmatically. - Package includes functionality to create new tasks and handle lifecycle management for existing tasks. - This package is part of Oracle APEX workflow functionality.
3 types of authorization schemas
- Administration Rights - Contribution Rights - Reader Rights
Where can you apply Authorization Scheme?
- An entire application - A page - Specific control such as a region, item, or button
Collections
- Use collections to temporarily capture one or more nonscalar Values. - Collections enable you to store rows and columns currently in session state - They can be accessed, manipulated, or processed during a user's specific session. - You insert, update, and delete collection information using PL/SQL API APEX_Collection.
Access Roles:
1) Administrator 2) Contributor 3) Reader
SQL Commands
-Enter and run SQL - Can create, edit, view, run, and delete database objects -Query Builder's graphical user interface (GUI) enables database developers to build SQL queries without manual SQL coding. -Access Query Builder via a code editor in many parts of APEX by clicking the respective hammer button when editing SQL attribute, or from SQL Workshop --> Utilities
When you run an application, the APEX engine relies on two processes:
Show Page and Accept Page
SQL Scripts
Store and run scripts - Set of SQL commands saved as a script file
Smart Filter
-Filters at the top of the page, report at the bottom - Each filter is displayed as a suggestion chip with a single count of how often the suggested value occurs. - Display as a Classic Report or Cards
Available Features:
-Install Progressive Web App (PWA)- Give your app the ability to be installed -About Page - Add about this application page -Access Control - Enables role-based user authorization -Activity Reporting - include user activity and error reports -Configuration Options - Enables or disables application features -Feedback - Allow users to provide feedback -Theme Style Selection - Update default application look and feel
Data Loading Page
-This allows users to load data using the page in the app. - consists of a single page with Native Data Loading page process, which enables users to upload data from a file or copy and paste, preview the data, and then upload the data.
APEX_MAIL contains 3 notable procedures:
1) APEX_MAIL.SEND - function which returns an ID and this submits the mail to the mailing queue, to be sent in some time (not immediate) 2) APEX_MAIL.PUSH_QUEUE - procedure using which you can deliver the messages stored in the APEX_MAIL_QUEUE view manually. Push the queue manually. 3) APEX_MAIL.ADD_ATTACHMENT - takes the ID that is returned by APEX_MAIL.SEND and using that ID, you can attach files as attachment to emails.
Synchronization types:
1) Append - typically used when no primary key has been defined in the data profile. 2) Merge - To use this option, the data profile must have a primary key defined. If a row for the given primary key value already exists, the row is updated. Otherwise, created. 3) Replace - Empty the local table before loading new data.
2 types of computations
1) Application level computation: 2) Page level computation: assign a value to an identified item only when a page is submitted or displayed
Session ID
a unique number assigned a specific user for the duration of that user's visit (session) - APEX establishes the identity of the user for each page request and the session ID to fetch session state from the database
How many panes for default page layout (developer)
3 panes: 1) Left pane - tabs for rendering dynamic actions, processing, shared components 2) Central Pane - tabs for layout messages, page search, help 3) Right Pane - property editor and attributes
page type: Model Dialog Page
after clicking edit icon next to a row, opens an overlay window which remains active and focused until closed. Can't access underlying normal page anymore until closed. Can be used when the requested information is essential to continue.
Task Parameters
attributes for a task that contain information about the task. Can be visible on task details page.
Application Process
blocks of PL/SQL logic that are set to run at specific points using processes from multiple pages of an application.
Task Settings
contains a general configuration options for the task, including default priority for tasks, the Task Details page URL, and task deadline settings
Where can we use Shared Components
Any page within the same application
Multiple users?
Apex Workspace enables multiple users to work within the same Oracle APEX installation while keeping their objects, data and applications private.
Region selections available (28)
Avatar Badge Breadcrumb Calendar Cards Charts Classic Report Column Toggle Report Comments Content Row Dynamic Content Faceted Search Form Help Text Interactive Grid Interactive Report List List View Map Media List Reflow Report Region Display Selector Search Smart Filters Static Content Timeline Tree URL
Authentication
Confirming user's identity before allowing access to the application. Apex engine uses APP_USER to track each user's login name in session state.
Object Browser:
Create database objects and view database object properties
RESTful Services
Declaratively specify RESTful services. Admin can disable.
Interactive Grid
End User can customize what data is displayed + add/modify/refresh data + Rearrange Data - Devs can make it Read Only or Editable. - for editable, there are "Save" and "Add Row" buttons - Selecting "Include Form" is not necessary because users can already edit data by default on the table itself, without the form. - Can be saved as public or private report - Users can download or email the report - Edit values, add columns, etc.
Human Task processes
Human Task Create and Human Task Manage are the page processes that create and act on the task instances in your pages.
Button selections available (6)
Icon Icon [Hot] Text Text[Hot] Text with Icon Text with Icon [Hot]
Creating new workspace options
New Database Schema or Existing Schema
Classic Report
Only display + sort ascending or descending
Progressive Web App (PWA)
PWAs leverage modern web APIs giving users an experience that looks and feels like a native app Advanced caching and improved performance Can be installed on your smartphone You can select when creating new app or enable for existing app Adds a new navigation bar entity named "Install App" Optimizes page loading speed on mobile devises. Improves page load rendering time with a new browser chace architecture for static files (APEX core files and app files) Customizable offline page when users are offline and cannot request the network Supports hooks into the service worker code for a fully customizable PWA experience.
Sessions State
enables developers to store and retrieve values for a user as the user navigates between different application pages.
REST Enabled SQL service URL format
http://host:port/ords/schema
Template Options
provide a declarative way for developers to apply different styles to components on an application page - Application components that support template options: Pages, Regions, Classic Reports. Breadcrumbs, lists, items and labels, and buttons. - Just like Theme Ruler, Live Template Options enables you to customize your application in real time. - Developers can click "Quick Edit" in Runtime Developer toolbar to access the Live Template Options dialog box.
Page Proces
specific event that runs when a page is loaded or submitted
Approvals
tasks for human approval - You can set up task approvers and administrators at design time or determine them dynamically at runtime based on data related to the task. -
Templates
templates define how pages, page controls, and page components appear.
https://apex.example.com/ords/mycompany/r/hr-app/update-employees?session=13766544332235 explain each component
***Hostname:port - Domain name of the server ***Ords - service name defined with configuring oracle rest data services (apex.example.com) ***Path prefix - URL path prefix used to access RESTful services. Defaults to Workspace name (mycompany) ***R - router shortcut. constant (r) ***App alias (hr-app) ***Page Alias (update-employees) ***Session ID/parameters (session=13766544332235 )
Unified Task List
- A page type in the Create Page wizard used to create a summary of tasks that function like an inbox. - The unified task list allows users to view, action, or edit task instances. - Can be in the same application as one of the task definitions, or a completely separate application in the same workspace. - If Unified Task List is in a separate application, you must configure SESSION SHARING so users can still open the task details.
REST Enabled SQL service reference requirements:
- A remote database that includes Oracle REST data services or ORDS 19.x or later on the front end. - An administrator must enable RESTful services, either at workspace level or instance levels.
Types of events that could trigger Dynamic Actions:
- Browser Events - Click, double-click, etc - Framework Events - After Refresh, before page submit, etc. - Component Events - Either an item, region or dynamic action that triggers - Custom Event - any custom event triggered from your application.
Smart Filter types:
- Checkbox group - Input Field - Radio Group - Range
Managing Data Synchronization:
- Data Synchronization enables developers to automatically sync the contents of a local table with the data from a REST service. - Can be triggered manually or on a schedule using a scheduler job. - Create job privilege must be granted to the applications parsing schema in order to execute REST source sync on schedule.
Rest Enabled SQL References
- Developers can create REST Enabled SQL References by defining a name, the endpoint URL, and authentication information within Shared Components - Create REST enabled SQL service references to help you execute SQL or PL/SQL defined on a remote Oracle database. - Oracle APEX installations that meet the minimum Oracle REST Data Services or ORDS requirements can execute any SQL through REST endpoint - Oracle APEX passes the SQL or PL/SQL query to ORDS over REST, and a self-describing JSON response is returned. -REST enabled SQL services are stored at the workspace level within APEX components - They are available to all applications within a workspace
Debugging Dynamic Actions
- Different than other debugging because most of processing is done on the client, not on the server. - Ensure the application containing the dynamic action has Debugging enabled. - Run the page containing the dynamic action - Open the browser's JavaScript console. - From the Developer toolbar, click Debug.
Access Control Page
- can be selected from list of features, during app creation. - administrative page where you can control user access to app components from the webpage of your application.
Interactive Report (as the user)
- Interact with report, create filters, charts, highlight, data computations, etc. - filter, select columns, add data computation formulas, charts, group by, pivot, download. - If you select "Include Form" page, the users will be able to maintain table values. "Edit" icon will appear next to rows -You can customize what is available for end user. Example disabling download or support hiding a column - Has a search bar at the top. You can select specific columns to search in. - You can create charts. - You can switch between chart view and report view. You can hit the "X" to close the chart too. Shows like a filter. - Public reports are visible to all users when private is visible to only the user who created it.-
Faceted Search
- Left side: Facets - Right side: either Classic Report or Cards - Batch Facet Changes - When enabled, apply button appears under facet and report does not auto refresh until apply is hit. Disabled by default. - There can be max 1 Search Type facet and displays at the top of Faceted Search region
Common Navigation Controls:
- Lists - Navigation Menu - top left hamburger - Breadcrumbs - can be list of links or a bar - Navigation Bar List - top right horizontal (can display as link, image or text - Search Configurations
Sending Emails with APEX on OCI
- Must use APEX_MAIL package - Only supported provider: Oracle Cloud Infrastructure Email Delivery service - UTL_SMTP package must be installed and functioning before using APEX_MAIL - You need to enable network services to send Outbound mail in APEX. - By default, the ability to interact with network services is disabled. - Sending emails involves following steps: 1) Generating Simple Mail Transfer Protocol (SMTP) Credentials for Email Delivery 2) Creating an approved sender from Email Delivery 3) Configuring the SMTP parameters in the Application Express instance 4) Sending a test email using the SQL Workshop.
Key Features of Data Load Definition:
- New app data loading supports CSV, XLSX, XML, and JSON formats - Column mapping occurs at design time, removing the burden for end users - Flexible column mappings based on simple names or regular expressions - Data conversion with transformation rules or lookup queries - Easy workflow for end users: upload the file, verify the preview, and load data - Can load CSV, XLSX, XML, and JSON data formats to tables or collections. - Configure data loading to append, merge, or replace data, with or without Error Handling. - Simple new Process Type Data Loading: Customize Data Loading pages as you wish - APEX_DATA_LOADING PL/SQL API available for custom processing - Max number of columns to load is 300; legacy data load support up to 45 columns.
Authorization Evaluation point operations:
- Once per session - Once per page view - Once per component - Always (No Caching)
Built-in authentication scheme
- Oracle APEX accounts - accounts created and managed in APEX User Repository. Your application is authenticated against these Oracle APEX accounts. - Custom Authentication - completely custom created from scratch - Database Accounts - authenticate using Database schema accounts. - HTTP Header Variables - authentication is done externally, and the username is stored in the HTTP Header Variable by the web server, which is then used to authenticate the user. - Open Door Credentials - anyone can access your application using a built-in login page that captures the username. - No Authentication - LDAP Directory - entered user and pass is authenticated with an authentication request sent to the LDAP server - Oracle Application Server / Single Sign-on Server Your site must have been registered as a partner application with the SSO server - SAML Sign-In - use third part identity providers which support SAML - Social Sign-In - APEX supports authentication with Google, Facebook, and other social networks that support OpenID Connect or OAuth 2
Charts
- Oracle APEX supports charts based on Oracle JavaScript Extension Toolkit, also knowns JET data visualizations - Oracle JET empowers developers by providing a modular open-source toolkit based on modern JavaScript, CSS3, and HTML5 design and development principles.
Plug-in:
- Plug in is an extension to the built-in types of APEX (Authentication scheme, authorization scheme, item, region, process, etc) - Use plug-ins for declarative use of new item, region, process and dynamic actions in your application. - You can create or import plug-ins
Saving Public Reports
- Public reports display in the "Saved Reports List" on the toolbar and are available to all users - Only the user who creates the Public Report can save, rename or delete it. - If no authorization scheme is selected, any user can save public reports for interactive grids. - By Default, all users can create Private Reports and Alternative Reports. Public has to be enabled by dev.
Universal Theme
- Responsive Design - Works on all devices - Versatile UI Components - Easy Customization without the knowledge of HTML, CSS or Java
REST Data Sources
- Rest Data Sources enable developers access to Representational State Transfer (REST) services, or generic JSON data feeds in applications. - A REST Data Source can contain one or many operations which are the references to a concrete external web service. - For REST services, an operation is a specific service handler (such as GET, PUT, POST, or DELETE) You can assign each database operation only once to your REST data source operation. - Oracle APEX provides direct integration of REST Data Sources in classic reports, interactive reports, CSS Calendar, and JET Charts. - REST Data Sources support various authentication types.
Interactive Report (as a Dev)
- Search bar can be enabled or disabled by the developer - You can customize the actions menu to enable/disable actions like as "aggregate", "highlight", "group By", etc - In attributes, You can enable/disable downloads and which formats are allowed to download (pdf, csv, html, etc.) - In attributes, you can also customize link column: You can link a row to a single row view or a custom target. You can also set the printing options, layouts, etc.
Search configurations
- Search configurations contain information about a searchable data source. They can be referenced from search regions to add declarative searches in your application. - Searches can be based on local data source, REST-enabled SQL or REST data source, or predefined Oracle Text or Oracle APEX list - 3 types of searches supported: 1) Standard - Defines a searchable data, such as a table, SQL query, or REST data source. 2) List - searches within a selected list from shared components. 3) Oracle Text - defines a searchable table or SQL query which already has an Oracle Text index created.
Show page vs Accept Page
- Show Page is a rendering process that assembles all the page attributes (including regions, items, and buttons) into a viewable HTML page - Accept Page performs forms page processing, including computations, validations, processes, and branching.
Managing Data Synchronization - Use Cases
- Some REST services can return large amounts of result data. If the reporting data needs large data sets, this can result in multiple HTTP requests and poor response times. In this case, Oracle recommends enabling Data Synchronization. - Provide efficient reporting on large data sets coming from a REST service - Collect data from REST Service for consumption by PL/SQL packages or other logic. - Collect data from a REST API over a longer period - Use intelligent caching for APEX components.
Task Participants
- Task initiators - who can start new task or provide more info about existing tasks - Potential owners - who can claim unassigned tasks. Task can have multiple potential owners. Users who initiate tasks, cannot approve their own task, even if they're listed as potential owners. - Actual owners - who can make changes to their assigned tasks, including requesting info, approving, rejecting. - Business Administrators - who have admin rights to renew expired tasks, reassign tasks, update priority.
Automations
- Typically a PL/SQL code - Automations are a sequential set of actions, triggered by query results that monitor data and then perform the appropriate actions. - Common automation use cases include: Approving specific requests or Sending email alerts - Automation executes as a query or a PL/SQL function based on a defined schedule. - Automations support Data Sources so the query can execute on a local database, a REST Enables SQL service, or REST Data Source.
Data Load Definition
- You can add Data Loading capability to an app using Data Load Definition - Data Load Definition comprises of a Data Load Definition, Data Profile, and Data Profile Columns. - Data can be loaded either to an existing table in your schema or to a collection. You can define SQL Expression, SQL Query, Lookups, or Transformation Rules for each data profile column. - When you create a Data Load Definition, the wizard prompts you to add a Data Loading page to your app. Can be added later too.
Options for saving customization to a report
- You can save all customizations you made as Default Report or Named Report - Default gives you options between primary and alternative
Key Feature of Automations
1) Define Automation Actions - conditions control whether or not the automation fires - u can use process type plugins as automation action 2) Trigger Automations Programmatically - The APEX_AUTOMATION package provides a PL/SQL interface for triggering an automation programmatically. 3) Review Automation Logs - View the Automation Execution Log and Log Messages view details about previous automation executions. - Automation History report lists history of recent automation modifications. - Automations execute regularly, after a few minutes after the next scheduled automation time.
3 types of forms:
1) Form - update a single row in a specified data source, which can be SQL query, or REST source module 2) Interactive Grid - presents set of data in a searchable and customizable form 3) Master detail - has 3 styles
Cards report layout styles:
1) Grid 2) Float 3) Horizontal (row)
Unified Task List REPORT CONTEXTS:
1) My Tasks - for users who are potential or actual owners of the tasks. - Shows all tasks assigned/unassigned to the user - Tasks that are approved do not show in this list 2) Admin Tasks - For users who are business administrators of the tasks - Shows all the tasks that are listed under a business administrator - Includes error, completed, and canceled tasks as well 3) Initiated by me
Execution points for page computations
1) New session 2) Pre rendering (before/after header, before regions) 3) post rendering (before/after footer, after regions, after submit) also: on New Instance or After Authentication
Types of pages:
1) Normal Page 2) Model Dialog Page 3) Non Model Dialog Page
Automation execution timing:
1) On Demand - executes when called explicitly using the APEX_AUTOMATION package. 2) Scheduled - executes automatically based on a selected frequency.
What are 2 types of items?
1) Page Items Placed on a page and have associated user interface properties, such as display only, label, and label template. - Examples include - Checkbox, Date Picker, Display as Text, file browse field, popup list of values, select list, or a text area. -Can be created by Rendering Tab on Page Designer, Right click on region node and "Create Page Item" or from the bottom gallery -> Items (next to regions/buttons) 2) Application Items Not associated with the page and therefore have no user interface properties. - Can be used as Global Items. Not displayed. Just global variables - Can be set using computations, processes, or bypassing values on a URL. - Used to maintain Session State. - Can be accessed through Shared Components
Pagination types:
1) Row Range X to Y of Z; 2) Row Range X to Y - Page - Scroll (default for Interactive Grid)
3 Authentication methods in APEX
1) Selecting a built-in authentication scheme 2) Creating a custom authentication scheme 3) Choosing to not require authentication
Steps before creating REST Enabled SQL Reference
1) Set up a remote Oracle database. 2) On the remote Oracle database, install Oracle REST Data Services (ORDS) 19.x or later. 3) Configure and enable the REST Enabled SQL service feature. 4) Activate REST Enabled SQL for the target schema on the remote database to be accessed by running ORDS.ENABLE_SCHEMA. 5) The REST Enabled SQL service is then available with a URL in the following format: http://host:port/ords/schema
3 styles of Master Detail form:
1) Stacked - 2 editable interactive grids on top of each other, based on two related tables or views. Users select a row in the top master grid to update the detail grid on the bottom. 2) Side by Side - Single page master detail, utilizing side by side layout and report regions with modulated windows. Left side contains master list to navigate to the master record. Right side contains the corresponding selected master record and the associated detailed report. 3) 2-page Drill Down - contains 2 pages based on 2 related tables or views. First page is an interactive report for the master table. Users click on the Edit icon and edit the details on the second page, which includes both a standard form for the master data/row selected and the editable interactive grid for details. Devs can only create this from create page wizard.
3 types of LOVs:
1) Static LOV - is based on the text value you enter (Display values and Return Values) 2) Dynamic LOV - is based on the SQL Query or the SQL Query returned by a Function Body 3) Cascading LOV - One item on a page determines the list of values for another item - Create 2 "select list" page items and link them together.
Lists types
1) static list - Define a list entry label and a target, which can be a page or a URL. 2) Dynamic List - Based on SQL query or a PL/SQL function executed at runtime. - Each link is called a List Entry. - You can control the appearance of a list through List Templates.
page type: Non Model Dialog Page
After clicking edit icon next to a row, opens a new Overlay window where user can interact both the new dialog page and the underlying normal page. Can be used when requested information is not essential to continue.
Item selections available (25)
Checkbox Checkbox Group Color Picker Date Picker Display Image Display Map Display Only File Browser Geocoded Address Hidden List Manager Markdown Editor Number Field Password Percent Graph Popup LOV Radio Group Rich Text Editor Select List Shuttle Star Rating Switch Text Field Text Field with Autocomplete Text area
Utilities:
Load and unload data; generate DDL -Data Workshop - Loading and unloading data from various file formats like XLSX, CSV, XML, and JSON -Only a single language can be loaded into the sample database objects. If you select a different language, the current language will be replaced -QuickSQL - Allows you to rapidly design and prototype data models using a markdown-like syntax that expands on standards-based Oracle SQL
Computation
Logic that assigns value to a Single Item
Schema
Logical container for database objects. A database schema will contain your applications' data.
Task Actions
Possible events that can happen when specific criteria are met. Available actions are: Claim Complete Delegate Update comment Update priority Release Cancel Create Request info Submit info
Dynamic actions
Provide a way to define complex client-side behavior declaratively without the need for JavaScript - works like excel IF functions - Example: when you select "Salesman", commission fields are enabled and editable. For other professions, they're not editable.
SQL Workshop
Provides tools for viewing and managing database objects
Quick SQL
Rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL.
Authorization
Restricting access to specific pages and components based on user privileges.
Team Development:
To track and manage issues in a conversational fashion
Runtime Views for Approvals - USER VIEW:
There are 5 types of public views available for Approval Components: 1) APEX_TASKS - for the properties for each task instance 2) APEX_TASKS_COMMENTS - for all comments entered on each task instance 3) APEX_TASKS_HISTORY - for chronological series of operations performed during the lifecycle of a task instance 4) APEX_TASKS_PARAMETERS - for the parameters defined for a task instance, along with the computer runtime values in string format. 5) APEX_TASKS_PARTICIPANTS - for the list of participants on each task instance.
Gallery
To access Starter Apps and Sample applications available in Oracle Apex GITHUB repository
App Builder
To create an application composed of a set of HTML pages and based on database objects
When enabling PWA, what should you turn on
When enabling for existing app, Edit applications Definition -> Properties -> Friendly URLs attribute must be ON and "Enable Progressive Web App" turned on. Muse run on secure https environment or localhost.
Syntax for Referring Item Values
You can reference Item Values stored in Session State in Regions, Computations, Validations, Processes, Branches SQL ---> :MY_ITEM PL/SQL ---> V('MY_ITEM') PL/SQL ---> NV{'MY_NUMERIC_ITEM') Static Text (Exact) ---> &MY_ITEM.
session
a logical construct that establishes persistence (or stateful behavior) across page views - Each session has a unique identifier or Session ID.\. -Any number of sessions can exist in the database at the same time - Sessions are completely different from Oracle database sessions used to service page requests. Database sessions often last a fraction of a second, fetching data - You can reference Item Values stored in Session State in Regions, Computations, Validations, Processes, Branches
Task Details Page
a page that shows details for a specific task, which can include metadata, history, comments, and actions.
Task Definitions
a shared component used to configure task parameters, participants, actions, and due dates.
