Create an APEX Application Using RESTful service

 Create an APEX Application Using RESTful service

In this section, you will create a simple application using the Application Builder component of APEX to display the employees in the emps table. 

Click the Oracle logo to go to the APEX home page, then click App Builder

Create a simple application with home page. Click Create, Create a New App

Create the EMP DISPLAY application.

 Add the name: EMP DISPLAY, accept all other default values and click Create application.

Choose Shared Components > REST Data Sources

Add the name and URL for your empall module created earlier, click next

The next step will automatically split up the endpoint URL into a server-specific

and a service-specific part. Click Next

For authentication select No, click Discover

Employees from the emps table will be discovered. Click Create Web Source.

Return to the application  and click the Create Page button in order to add a new page. 

Choose Classic Report.

For Data Source, choose Web Source, select the Apex Restful Service service. Click Create.

The employees in the database table emp are displayed in the application

 using the REST Web Service.

Deleting Data Using RESTful Service

  Deleting  Data Using RESTful Service

 you will create a RESTful Service to delete data using the HTTP Method DELETE. 

In the emp/:empidTemplate, create a new DELETE Handler.

Specify the DELETE Method, and Source Type as PL/SQL. 

 Provide the Source Code for the PL/SQL.

BEGIN

    DELETE 

    FROM EMP 

    WHERE EMPNO = :empid;

    htp.prn('employee '|| :empid || ' Deleted');

END;

To test in Postman, click the plus icon to create a new tab.

Select DELETE, enter the correct URL adding /124.

 Click the Params tab. For KEY enter Content-Type, and for VALUE enter application/json

 

 

 Click Send.If the delete was successful, you will see the message below.

Updating Data Using RESTful Service

 Updating Data Using RESTful Service

In this section, you will create a RESTful Service to update data using the HTTP Method PUT.

 You will use the postman tool to test your RESTful Service, 

and to verify the results

Specify the PUT Method and Source Type as PL/SQL. 

Provide the Source Code for the PL/SQL

BEGIN

UPDATE EMP  

Set  SAL = SAL*2 

WHERE EMPNO= :empid;

htp.prn('Updated');

END;

Test in Postman, select PUT, enter the correct URL adding /124, click the Body tab,

 check raw, 

 Click Send.If the update was successful, you will see the message below.

 

Inserting Data Using RESTful Service

Inserting Data Using RESTful Service

you will create a RESTful Service to insert data using the HTTP Method POST.

You will use the postman tool to test your RESTful Service and to verify the results.

In the empall Template, create a new POST Handler.

BEGIN

INSERT INTO EMP

(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

VALUES

(:EMPNO,:ENAME,:JOB,:MGR,TO_DATE(:HIREDATE,'DD-MON-RR'),:SAL,:COMM,:DEPTNO);

htp.prn('employee '|| :EMPNO);

END;

In the postman tool to test the POST request, we have to set the Content-type to application/json. 

Click the Headers tab under the URL bar. 

Under the heading KEY, enter Content-Type, and for VALUE enter application/json.  

In the postman tool to test the POST request, click the Body tab, 

check raw, and enter the json code shown below. Click Send

{"EMPNO":124, "ENAME":"testinsert", "JOB":"IT_MANA", "MGR":7902, "HIREDATE":"25-JUN-15", "SAL":11, "COMM":22, "DEPTNO":10}

If the insert is successful, you will see the message below.

Check the new inserted record informationby using the GET handler created in the previous section, adding 124 to the end of the URL.

http://localhost:8080/ords/re/EMP_TEST/emp/124

 thanks

Creating Restful Web Service to Retrieving Data Using a Parameter

 Retrieving Data Using a Parameter

Perform the following steps to create a RESTful Service which retrieves the employee information based on a parameter id using the HTTP Method GET. 

In the EMP_TEST module, create a new Template emp/:empid following the steps shown in the previous section.

Add the Source code for the GET Handler. 

Test the new Web service from the Browser, 

adding a valid employee number to the end of the URL for your schema.

 7839 is the empno for “King”.

http://localhost:8080/ords/re/EMP_TEST/emp/7839

Test the new Web service from postman.

Creating the first Restful Web Service [ will retrieve all records in the emp table ]

 Creating the first Restful Web Service

The first Restful Web service will retrieve all records in the emp table.

Click Create Module button.

Provide the Module Name oa_test, and the Base Path /EMP_TEST/.

Change Paginationsize to 25.

Click “Create Module”

Click “Create Template”.

Create the Resource Template empall/.

Create the Resource Handler.

Click “Create Handler”

Create the Resource Handler.

Enter the following query in the “Source” area:

SELECT * FROM emp

Click “Create Handler

Copy the module URL.

Ensure the GET Resource Handler is selected,

and click the icon to copy the URL.

Test the Rest Web Service from the web browser.

Select GET, paste the URL,

and click Send to test the Rest Web Service from Postman.

Using RESTful Web Services in Oracle Application Express(APEX) 2 [ Register Schema with ORDS From Sql Developer]

  Register Schema with ORDS From Sql Developer

Open sql developer and open connection for your schema

your Sceham not registerd for ORDS Yet

Right Click on your connection > Rest Service > Enable Rest Service 

Click Enable Rest Service 

choose Your schema alias 

Press NEXT

You can see Code from SQl Tab 

Press Finish

we will find that our schema is register with ORDS

We can Use the Code Direct in SQL Developer 

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,

                       p_schema => 'RE',

                       p_url_mapping_type => 'BASE_PATH',

                       p_url_mapping_pattern => 're',

                       p_auto_rest_auth => FALSE);

    commit;

END;

----

thanks

Using RESTful Web Services in Oracle Application Express(APEX) 2 [ Register Schema with ORDS From APEX ]

Register Schema with ORDS From APEX

Login into Oracle Application Express (APEX).

On the Workspace home page, click SQL Workshop.

Click RESTful Services. RESTful Services

If your schema is not registered with ORDS RESTful Data Services, 

click Register Schema with ORDS

ORDS Schema Attributes dialog (Note: your schema name will be different). 

Click Save Schema Attributes.

ORDS RESTful Services dashboard:

Using RESTful Web Services in Oracle Application Express(APEX) 1 [ Verify the emp table ]

Verify the emp table 

If the EMP table does not exist, create the EMP table and data manually.

 Choose SQL Commands.

Using RESTful Web Services in Oracle Application Express(APEX) Intro

 Using RESTful Web Services in Oracle Application Express(APEX)  Intro

We will covers creating a RESTful Web Service declaratively using Oracle Application Express's SQL Workshop tool to connect to a database table, and then consuming this service by creating an application and adding a Web Service reference to the RESTful Web Service. 

A client java program is then created and executed that will consume the RESTful Web Service to return data stored in an APEX database table to the java application.

Web Services enable applications to interact with one another over the web in a platform-neutral, language independent environment. 

In a typical Web Services scenario, a business application sends a request to a service at a given URL by using the HTTP protocol. 

The service receives the request, processes it, and returns a response.

Once you have defined a RESTful Web Service, you can call it with a unique Uniform Resource Identifier (URI).

 The Web Service uses the URI to call methods such as GET, POST, PUT, and DELETE.

Change Autharization, boint and assigned button for Process on runtime in APEX

 Change Autharization, boint and assigned button

for Process on runtime in APEX

Make Item for process

select PROCESS_NAME,ID from APEX_200200.wwv_flow_step_processing

                   where FLOW_ID = :P10_FLOW_ID 

                    and FLOW_STEP_ID =:P10_STEP_ID

Process for Autharization 

BEGIN

UPDATE APEX_200200.wwv_flow_step_processing -- Process table

    SET  SECURITY_SCHEME = :P10_SECURITY_ID -- Authorization schema

WHERE FLOW_ID = :P10_FLOW_ID -- APP ID

    and FLOW_STEP_ID = :P10_STEP_ID  -- PAGE ID

    and ID = :P10_PROCESS_ID; -- process id

end;

Process for process point 

BEGIN

UPDATE APEX_200200.wwv_flow_step_processing -- Process table

    SET  PROCESS_POINT = 'BEFORE_HEADER' -- PROCESS POINT

WHERE FLOW_ID = :P10_FLOW_ID -- APP ID

    and FLOW_STEP_ID = :P10_STEP_ID  -- PAGE ID

    and ID = :P10_PROCESS_ID; -- process id

end;

 

Process for assigned button 

BEGIN

UPDATE APEX_200200.wwv_flow_step_processing -- Process table

    SET  PROCESS_WHEN_BUTTON_ID = :P10_BUTTON_ID --when button

WHERE FLOW_ID = :P10_FLOW_ID -- APP ID

    and FLOW_STEP_ID = :P10_STEP_ID  -- PAGE ID

    and ID = :P10_PROCESS_ID; -- process id

end;

  

thanks 

 

How to Change Autharization, Build Option ,custom attribute and Server side condtion for button on runtime in Oracle APEX

How to Change Autharization, Build Option ,custom attribute and Server side condtion for button on runtime in Oracle APEX

Make Item for button

select BUTTON_IMAGE_ALT   ,ID from APEX_200200.wwv_flow_step_buttons

                   where FLOW_ID = :P8_FLOW_ID 

                    and FLOW_STEP_ID =:P8_STEP_ID

                    and BUTTON_PLUG_ID =:P8_REGION_ID

Process for Autharization 

select BUTTON_IMAGE_ALT   ,ID from APEX_200200.wwv_flow_step_buttons

                   where FLOW_ID = :P8_FLOW_ID 

                    and FLOW_STEP_ID =:P8_STEP_ID

                    and BUTTON_PLUG_ID =:P8_REGION_ID

Process for Build Option

BEGIN

UPDATE APEX_200200.wwv_flow_step_buttons -- buttons

SET REQUIRED_PATCH = :P8_BUILD_OPTION  

 WHERE FLOW_ID = :P8_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P8_STEP_ID  -- PAGE ID

 and BUTTON_PLUG_ID =:P8_REGION_ID -- region  

 and ID = :P8_BUTTON_ID; -- button id 

end;

 

Process for custom attribute

BEGIN

UPDATE APEX_200200.wwv_flow_step_buttons -- Buttons Table

SET   BUTTON_CATTRIBUTES = 'style="color:red;"'

 WHERE FLOW_ID = :P8_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P8_STEP_ID  -- PAGE ID

 and BUTTON_PLUG_ID =:P8_REGION_ID -- region region

 and ID = :P8_BUTTON_ID; -- button

end;

 Process for Server side condtion

BEGIN

UPDATE APEX_200200.wwv_flow_step_buttons -- button

SET  BUTTON_CONDITION_TYPE = 'EXISTS',

     BUTTON_CONDITION        ='select 1 from dual where 1=2'

 WHERE FLOW_ID = :P8_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P8_STEP_ID  -- PAGE ID

 and BUTTON_PLUG_ID =:P8_REGION_ID -- region region

 and ID = :P8_BUTTON_ID; -- button id 

end;

thanks 

How to Change Autharization, Build Option ,Read only and Server side condtion for Item on runtime in Oracle APEX

 How to Change Item 

Autharization, Build Option ,Read only and Server side condtion 

on runtime in Oracle APEX

Make Item for Items 

select NAME,ID from APEX_200200.wwv_flow_step_items

                   where FLOW_ID = :P9_FLOW_ID 

                    and FLOW_STEP_ID =:P9_STEP_ID

                    and ITEM_PLUG_ID =:P9_REGION_ID

Process for Autharization 

BEGIN

UPDATE APEX_200200.wwv_flow_step_items -- ITEMS

SET  SECURITY_SCHEME = :P9_SECURITY_ID -- Authorization schema

WHERE FLOW_ID = :P9_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P9_STEP_ID  -- PAGE ID

 and ITEM_PLUG_ID =:P9_REGION_ID -- region region

 and ID = :P9_ITEM_ID; -- item id 

end;

Process for Build Option

BEGIN

UPDATE APEX_200200.wwv_flow_step_items -- Region

SET REQUIRED_PATCH = :P9_BUILD_OPTION  

 WHERE FLOW_ID = :P9_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P9_STEP_ID  -- PAGE ID

 and ITEM_PLUG_ID =:P9_REGION_ID -- region region

 and ID = :P9_ITEM_ID; -- item id 

end;

Process for Read only

BEGIN

UPDATE APEX_200200.wwv_flow_step_items -- ITEMS

SET  READ_ONLY_WHEN_TYPE = 'EXISTS',

     READ_ONLY_WHEN          ='select 1 from dual'

 WHERE FLOW_ID = :P9_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P9_STEP_ID  -- PAGE ID

 and ITEM_PLUG_ID =:P9_REGION_ID -- region region

 and ID = :P9_ITEM_ID; -- item id 

end;

 Process for Server side condtion

BEGIN

UPDATE APEX_200200.wwv_flow_step_items -- ITEMS

SET  DISPLAY_WHEN_TYPE = 'EXISTS',

     DISPLAY_WHEN        ='select 1 from dual where 1=2'

 WHERE FLOW_ID = :P9_FLOW_ID -- APP ID

 and FLOW_STEP_ID = :P9_STEP_ID  -- PAGE ID

 and ITEM_PLUG_ID =:P9_REGION_ID -- region region

 and ID = :P9_ITEM_ID; -- item id 

end;


thanks 

How to change Region read only on runtime in Oracle APEX

 1: Make P6_REGION_ID

that will contain all Region available for your page 

use this code as source 

select 

PLUG_NAME,

ID

 from APEX_200200.wwv_flow_page_plugs

                    where FLOW_ID =:P6_FLOW_ID

                    and   PAGE_ID =:P6_STEP_ID

  and make process to update Region read only

 BEGIN

UPDATE APEX_200200.wwv_flow_page_plugs -- Region

SET PLUG_READ_ONLY_WHEN_TYPE = 'EXISTS' ,

    PLUG_READ_ONLY_WHEN = 'SELECT 1 from dual where 1 =2 '

WHERE FLOW_ID = :P6_FLOW_ID -- APP ID

 and PAGE_ID = :P6_STEP_ID  -- PAGE ID

 and ID = :P6_REGION_ID -- REGION ID

; end;

thanks

How to change Region Serveer Side COndition on runtime in Oracle APEX

1: Make P6_REGION_ID

that will contain all Region available for your page 

use this code as source 

select 

PLUG_NAME,

ID

 from APEX_200200.wwv_flow_page_plugs

                    where FLOW_ID =:P6_FLOW_ID

                    and   PAGE_ID =:P6_STEP_ID

  and make process to update Region Server side condition 

 BEGIN

UPDATE APEX_200200.wwv_flow_page_plugs -- Region

SET PLUG_DISPLAY_CONDITION_TYPE = 'NOT_EXISTS' ,

    PLUG_DISPLAY_WHEN_CONDITION = 'SELECT 1 from dual where 1=2'

WHERE FLOW_ID = :P6_FLOW_ID -- APP ID

 and PAGE_ID = :P6_STEP_ID  -- PAGE ID

 and ID = :P6_REGION_ID -- REGION ID; 

end;

--
thanks

How to change Region Bulid Option on runtime in Oracle APEX

1: Make P6_REGION_ID

that will contain all Region available for your page 

use this code as source 

select 

PLUG_NAME,

ID

 from APEX_200200.wwv_flow_page_plugs

                    where FLOW_ID =:P6_FLOW_ID

                    and   PAGE_ID =:P6_STEP_ID

and select list item  P6_BUILD_OPTION

that will contain all build Option available for your APP

use this code as source 

select PATCH_NAME,ID from APEX_200200.wwv_flow_patches

                    where FLOW_ID =:P6_FLOW_ID

 

and make process to update Region Build Option

 BEGIN

UPDATE APEX_200200.wwv_flow_page_plugs -- Region

SET REQUIRED_PATCH = :P6_BUILD_OPTION  

WHERE FLOW_ID = :P6_FLOW_ID -- APP ID

 and PAGE_ID = :P6_STEP_ID  -- PAGE ID

 and ID = :P6_REGION_ID -- REGION ID; 

end;

--
thanks

How to change Region Authorization Scheme on runtime in Oracle APEX

1: Make P6_REGION_ID

that will contain all Region available for your page 

use this code as source 

select 

PLUG_NAME,

ID

 from APEX_200200.wwv_flow_page_plugs

                    where FLOW_ID =:P6_FLOW_ID

                    and   PAGE_ID =:P6_STEP_ID

and select list item  P6_SECURITY_ID

that will contain all Authorization available for your APP

use this code as source 

select NAME,ID from 

APEX_200200.wwv_flow_security_schemes

 where FLOW_ID =:P7_FLOW_ID

and make process to update Region Authorization 

BEGIN

UPDATE APEX_200200.wwv_flow_page_plugs 

SET PLUG_REQUIRED_ROLE = :P6_SECURITY_ID 

WHERE FLOW_ID = :P6_FLOW_ID 

 and PAGE_ID = :P6_STEP_ID  

 and ID = :P6_REGION_ID

end;

--
thanks

How to change Build Option for Page on runtime in Oracle APEX

    first make select list item  P7_BUILD_OPTION

that will contain all Build Option available for your APP

use this code as source 

  select patch_name||' ('||wwv_flow_lang.system_message(patch_status)||')' d, id r 

  from APEX_200200.wwv_flow_patches where FLOW_ID = :APP_ID

and make process to update Page Build Option

BEGIN

UPDATE APEX_200200.wwv_flow_steps -

SET REQUIRED_PATCH = :P7_BUILD_OPTION  

 WHERE FLOW_ID = :P7_FLOW_ID 

 and ID = :P7_STEP_ID ; 

end;

--
thanks

How to change Page Read only on runtime in Oracle APEX

Make process to change Page Read only on runtime

BEGIN

UPDATE APEX_200200.wwv_flow_steps -- PageS

SET READ_ONLY_WHEN_TYPE = 'EXISTS' ,

READ_ONLY_WHEN = 'select 1 from dual where 1=1'

WHERE FLOW_ID = :P7_FLOW_ID -- APP ID

 and ID = :P7_STEP_ID  -- PAGE ID

; 

end;

--
thanks

How to change Page to Public or Not on runtime in Oracle APEX

    first make select list item  P7_PAGE_IS_PUBLIC_Y_N

 

use this code as source 

and make process to change Page to Public  or Not

BEGIN

UPDATE APEX_200200.wwv_flow_steps -- PageS

SET PAGE_IS_PUBLIC_Y_N = :P7_PAGE_IS_PUBLIC_Y_N -- Authorization schema

WHERE FLOW_ID = :P7_FLOW_ID -- APP ID

 and ID = :P7_STEP_ID  -- PAGE ID

; 

end;

--
thanks

How to change Page Authorization Scheme on runtime in Oracle APEX

   first make select list item  P7_SECURITY_ID

that will contain all Authorization available for your APP

use this code as source 

select NAME,ID from 

APEX_200200.wwv_flow_security_schemes

 where FLOW_ID =:P7_FLOW_ID

and make process to update Page Authorization 

BEGIN

UPDATE APEX_200200.wwv_flow_steps -- PageS

SET required_role = :P7_SECURITY_ID -- Authorization schema

WHERE FLOW_ID = :P7_FLOW_ID -- APP ID

 and ID = :P7_STEP_ID  -- PAGE ID

; 

end;

--
thanks

How to change Authorization Scheme on runtime in Oracle APEX

  first make select list item  P4_AUTHORIZTION_SCHEMA

that will contain all Authorization available for your APP

 

use this code as source 

 

select a.d, a.r from (

    select APEX_200200.wwv_flow_lang.system_message('MUST_NOT_BE_PUBLIC_USER') d,'MUST_NOT_BE_PUBLIC_USER' r, 1 

    from dual

    union all

    select APEX_200200.wwv_flow_escape.html(substr(name,1,50)),to_char(id), 1

    from APEX_200200.wwv_flow_security_schemes where flow_id = :APP_ID

    union all

    select '{'||APEX_200200.wwv_flow_lang.system_message('NOT_W_ARGUMENT',APEX_200200.wwv_flow_escape.html(substr(name,1,50)))||'}' d,

     '!'||to_char(id) r, 2

    from   APEX_200200.wwv_flow_security_schemes

    where flow_id = :APP_ID

    order by 3,1) a

and make process to update Application Authorization 

UPDATE APEX_200200.wwv_flows 

SET SECURITY_SCHEME =:P4_AUTHORIZTION_SCHEMA

where ID = :APP_ID;

--
thanks

How to change Parsing Schema on runtime in Oracle APEX

  first make select list item  P4_AUTHORIZTION_SCHEMA

that will contain all DB schema available for your workspace

use this code as source 

select APEX_200200.wwv_flow_escape.html(schema) d, schema r

from   APEX_200200.wwv_flow_company_schemas

where  security_group_id = :flow_security_group_id

order by 1

and make process to update Application Authentication 

UPDATE APEX_200200.wwv_flows 

SET SECURITY_SCHEME =:P4_AUTHORIZTION_SCHEMA

where ID = :APP_ID;

--
thanks

How to change Authentication Schemes on runtime in Oracle APEX

 first make select list item  P2_AUTHENTICATION_ID
that will contain all Authentication available for your APP

use this code as source 

select name as d,

       id   as r

  from APEX_200200.wwv_flow_authentications

 where flow_id           = :APP_ID

   and security_group_id = :WORKSPACE_ID

 order by 1

and make process to update Application Authentication 

UPDATE APEX_200200.wwv_flows 

SET AUTHENTICATION_ID =:P2_AUTHENTICATION_ID

where ID = :APP_ID;

--
thanks