Oracle APEX Beginner

Oracle APEX BeginnerHands-On LabApril, 2019 (v19.1 OTN)Copyright 2018, Oracle and/or its affiliates. All rights reserved. 2

Getting StartedObtaining a WorkspaceCopyright 2018 Oracle and/or its affiliates. All rights reserved. 3

Step 1a Go to https://apex.oracle.com Click Get Started for FreeCopyright 2018 Oracle and/or its affiliates. All rights reserved. 4

Step 1b Click Request a Free WorkspaceCopyright 2018 Oracle and/or its affiliates. All rights reserved. 5

Step 2 What Type of Workspace - Click Application Development Enter your Identification details – First Name, Last Name,Email, Workspace{Note: For workspace enter a unique name,such as first initial and last name} Enter Schema details – Schema Name{Note: For schema name enter the same nameas you entered for workspace} Complete the wizardCopyright 2018 Oracle and/or its affiliates. All rights reserved. 6

Step 3 Check your emailYou should get an email from oracleapplication-express ww@oracle.comwithin a few minutes{Note: If you don’t get an email goback to Step 1 and make sure to enteryour email correctly} Click Create Workspace Click Continue to Log In Screen Reset your passwordCopyright 2018 Oracle and/or its affiliates. All rights reserved. 7

Building your first appCreating an App from a SpreadsheetCopyright 2018 Oracle and/or its affiliates. All rights reserved. 8

Step 1 – Logging In Log into your workspace on https://apex.oracle.com Click App Builder Click Create a New AppCopyright 2018 Oracle and/or its affiliates. All rights reserved. 9

Step 2 – Selecting App Type Click From a FileCopyright 2018 Oracle and/or its affiliates. All rights reserved. 10

Step 3 – Loading Sample Data Click Copy and Paste For Sample Data Set select Project and Tasks Click NextCopyright 2018 Oracle and/or its affiliates. All rights reserved. 11

Step 4 – Naming the Table Enter Table Name {SPREADSHEET} Click Load DataCopyright 2018 Oracle and/or its affiliates. All rights reserved. 12

Step 5 – Verifying Records Loaded Check that 73 rows are loaded Click Continue to Create Application WizardCopyright 2018 Oracle and/or its affiliates. All rights reserved. 13

Step 6 – Naming the App Enter Name{App from a Spreadsheet} Next to Features,click Check AllCopyright 2018 Oracle and/or its affiliates. All rights reserved. 14

Step 7 – Create Application Click Create ApplicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 15

Step 8 – App in Page Designer Your new application will be displayed in Page Designer Click Run ApplicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 16

Step 9 – Runtime App Enter your user credentials Play around with your new applicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 17

Using the Runtime EnvironmentImproving the AppCopyright 2018 Oracle and/or its affiliates. All rights reserved. 18

Step 1 – Sort the Interactive Report Click Spreadsheet Click Actions, select Data, select Sort For 1, select Start Date; For 2, select End Date; click ApplyCopyright 2018 Oracle and/or its affiliates. All rights reserved. 19

Step 2 – Add a Computation Click Actions, select Data, select Compute Column Label enter Budget V Cost Format Mask select 5,234.10 Computation Expression enter I – H Click ApplyCopyright 2018 Oracle and/or its affiliates. All rights reserved. 20

Step 3 – Add a Chart Click Actions, select Chart Label select Project Value select **Budget V Cost Function select Sum Sort select Label – Ascending Orientation select Horizontal Click ApplyCopyright 2018 Oracle and/or its affiliates. All rights reserved. 21

Step 3b – Add a ChartCopyright 2018 Oracle and/or its affiliates. All rights reserved. 22

Step 4 – Save Report Click Actions, select Report, select Save Report For Save, select As Default Report Settings Default Report Type, select Alternative Name, enter Date Review Click ApplyCopyright 2018 Oracle and/or its affiliates. All rights reserved. 23

Step 5 – Restrict the Status In the runtime environment,click the edit icon on a record A modal page will be displayed In the Developer Toolbar, clickQuick Edit Hover over the Status item(until a blue outline appears)and click the mouse Page Designer displayswith focus on the Status itemCopyright 2018 Oracle and/or its affiliates. All rights reserved. 24

Step 5b – Restrict the Status In Page Designer, within the Property Editor (right pane),for Type select Select List Under List of Values, for Type select SQL Query Next to SQL Query, click Code EditorCopyright 2018 Oracle and/or its affiliates. All rights reserved. 25

Step 5c – Restrict the Status Within the Code Editor, enter the following:select distinct status d, status rfrom spreadsheetorder by 1 Click Validate Click OK Display Extra Values, select No Null Value Display, enter - Select Status Click Save (In the toolbar - top right)Copyright 2018 Oracle and/or its affiliates. All rights reserved. 26

Step 6 – Run the App Navigate back to the runtime environment Refresh the browser Edit a record Click StatusCopyright 2018 Oracle and/or its affiliates. All rights reserved. 27

Step 7 – Add a Calendar Navigate back to the development environment In App Builder, navigate to the App Home Page Click Create PageCopyright 2018 Oracle and/or its affiliates. All rights reserved. 28

Step 7b – Add a Calendar Click Calendar Page Name, enter Calendar Breadcrumb, select Breadcrumb Click NextCopyright 2018 Oracle and/or its affiliates. All rights reserved. 29

Step 7c – Add a Calendar Navigation Preference, click Create a new navigation menu entry Click Next Table / View Name,select SPREADSHEET (table) Click NextCopyright 2018 Oracle and/or its affiliates. All rights reserved. 30

Step 7d – Add a Calendar Display Column, select TASK NAME End Date Column, select END DATE Click CreateCopyright 2018 Oracle and/or its affiliates. All rights reserved. 31

Step 8 – Link the Calendar to the Update Form In the Rendering tab, under Calendar, click Attributes In the Property Editor (right pane), click View / Edit Link Page, select 3 Set Items – Name, select P3 ID; Value, select ID Clear Cache,enter 3 Click OK Click Save and RunCopyright 2018 Oracle and/or its affiliates. All rights reserved. 32

Step 8b – Link the Calendar to the Update FormNote:You may need tonavigate to themonth of Mayto see calendarentries.Copyright 2018 Oracle and/or its affiliates. All rights reserved. 33

Defining new data structuresUsing Quick SQLCopyright 2018 Oracle and/or its affiliates. All rights reserved. 34

Original Spreadsheet TableCopyright 2018 Oracle and/or its affiliates. All rights reserved. 35

Napkin Design – Improved data model for ProjectsCopyright 2018 Oracle and/or its affiliates. All rights reserved. 36

Step 1 – Open Quick SQL Go back to your development environment - App Builder Click SQL Workshop Click SQL Scripts Click Quick SQLCopyright 2018 Oracle and/or its affiliates. All rights reserved. 37

Step 2 - Enter shorthand for Team Members table Enter the Table Name {Team Members} Indent 2 or more spaces and enter the column namesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 38

Step 3 - Enter details for Projects table Enter the Table Name in the first column {Projects} Indent 2 or more spaces and enter the column namesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 39

Step 4 – Review Help Click Help Click TableDirectivesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 40

Step 4b – Review Help Click ColumnDirectivesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 41

Step 4c – Review Help Click Data TypesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 42

Step 5 - Improve the Shorthand Close Help Enter /insert xx for tables Enter /nn for mandatory columns Enter /references team membersfor project lead column Enter num for budget column Enter /vc30 and /check ASSIGNED,IN-PROGRESS, COMPLETED forstatus columnCopyright 2018 Oracle and/or its affiliates. All rights reserved. 43

Step 6 - Enter details for a child table Enter the Table Name indented{Milestones} Indent 2 or more spaces andenter the column namesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 44

Step 7 - Enter details for another child table Enter the Table Name indented{Tasks} Indent 2 or more spaces andenter the column namesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 45

Step 8 – Complete the Shorthand Copy the following URL into a new window in your browser:{Remember you are on Slide 50 if you click the link icksql-5095785.txt Copy and Paste the full scriptinto the Quick SQL Shorthand pane{on the left} Click Generate SQLteam members /insert 10username /nn /upperfull nameemail /nnphone numberprofilephoto fileprojects /insert 20name /nnproject lead /nn /references team membersbudget numstatus vc30 /nn /check ASSIGNED, IN-PROGRESS, COMPLETEDcompleted datedescriptionmilestones /insert 30name /nndue date /nndescriptiontasks /insert 100name /nnassignee /nn /references team membersmilestone id /references milestonesstart date /nnend datecost numdescriptionis complete yn /check Y, Nview project tasks projects tasksCopyright 2018 Oracle and/or its affiliates. All rights reserved. 46

Step 9 – Update the Settings Click Settings Object Prefix, enter hol On Delete, select Restrict Primary Keys, select12c Identity Data Types Date Data Type, select TIMESTAMPWITH LOCAL TIME ZONE Audit Columns, check Include Row Version Number, check Include Click Save ChangesCopyright 2018 Oracle and/or its affiliates. All rights reserved. 47

Step 10 – Save, Review, and Run the Script Click Save SQL Script For Script Name, enter hol Click Save Script Click Review and Run{Note: The script will be displayed inthe Script Editor within SQL Scripts} Click Run Click Run NowCopyright 2018 Oracle and/or its affiliates. All rights reserved. 48

Creating an app on the tables from Quick SQLUsing the Create Application WizardCopyright 2018 Oracle and/or its affiliates. All rights reserved. 49

Step 1 – Start the Create App Wizard Click Create App from ScriptCopyright 2018 Oracle and/or its affiliates. All rights reserved. 50

Step 2 – Name the App and Update Appearance For Name, enter Projects Click Appearance For Theme Style, select Vita-SlateCopyright 2018 Oracle and/or its affiliates. All rights reserved. 51

Step 3 – Add Features For Features, click Check AllCopyright 2018 Oracle and/or its affiliates. All rights reserved. 52

Step 4 – Create Application Click Create ApplicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 53

Step 5 – App in Page Designer Your new application will be displayed in Page Designer Click Run ApplicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 54

Step 8 – Runtime App Enter your credentials Play around with your new applicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 55

Improving the AppUpdating a PageCopyright 2018 Oracle and/or its affiliates. All rights reserved. 56

Step 1 – Restart the Create App Wizard From the development environment,click App Builder, and then select Create Click New ApplicationCopyright 2018 Oracle and/or its affiliates. All rights reserved. 57

Step 2 – Load Blueprint In the Create App Wizard, click Load Blueprint For Projects, click LoadCopyright 2018 Oracle and/or its affiliates. All rights reserved. 58

Step 3 – Add a Page Click Add Page Click Interactive Grid For Page Name,enter Milestones For Table or View, selectHOL MILESTONES Click Add PageCopyright 2018 Oracle and/or its affiliates. All rights reserved. 59

Step 4 – Reorder a Page Click and hold the mouse when hovering over the hamburger forthe Milestones – Interactive Grid page Move it up until the page is under Projects Release the mouseCopyright 2018 Oracle and/or its affiliates. All rights reserved. 60