SQL Application Express REST API client

The Application Express REST API client sample demonstrates calling an SQL query with a bind variable passed on the URL.

  • Client creation using com.oracle.ceal.ApexRestClient

    apexClient=new ApexRestClient(apexRestUrl, proxy Host, proxy Port,cloud  
    identityDomain, cloud username, cloud password, 
    ignoreSSLCertificationPathErrors)
    • Apex REST URL in the format: https://servername

      Example: https://server.analytics.us2.oraclecloud.com/apex

    • proxy host:

      • Leave empty if not using a proxy

      • If using a tool like Fiddler for HTTP captures, specify localhost.

      • If you need to go through a proxy to connect to Oracle cloud services, specify the proxy host.

    • proxy port:

      • Leave empty if not using a proxy

      • If using Fiddler, use 8888.

      • Otherwise, enter your proxy port.

    • Cloud identity domain: this is provided with your cloud login. You can also find this in the APEX URL.

    • ignoreSSLCertificationPathErrors (true or false): Set this to true if connecting through a proxy like Fiddler.

  • Calling an SQL Query defined in APEX / SQL Workshop / RESTful web services

    The REST web service must be created first. For more information, read this Oracle By Example: http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/13_2/dbservice/restfulws/restfulws.html

    apexClient.launchSQLQueryUsingGETAndVariableOnUrl("<module name>/<uri>", "<bind variable>")

    Example:

    apexClient.launchSQLQueryUsingGETAndVariableOnUrl("bics/test", "7839")

    This example uses the following definition of the REST service in APEX:

    RESTful Service Module: bics/ URI Template: test/{ID} Method: GET Source Type: Query Format: JSON Requires Secure Access: YES Source: Select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from EMP where EMPNO = :ID

    The URL call will be in the following format:

    https://<SERVER>.oraclecloudapps.com/apex/bics/test/7839

    The response will be in the following format:

    Response Content-Type:application/json

    {"next":{"$ref":"https://<SERVER>.oraclecloudapps.com/apex/bics/test/7839?page=1"},"items":[{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"1981-11-17T00:00:00Z","sal":5000,"deptno":10}]}
  • Calling a PL/SQL defined in APEX / SQL Workshop / RESTful web services

    A method is available, but all the configuration work must be done in APEX.

    apexClient.launchProcUsingGET("<module name>/<uri>")

    The definition of the REST service in APEX is for this example:

    RESTful Service Module: bics/ URI Template: plsql/ Method: GET Source Type: PL/SQL Requires Secure Access: YES Source:

    DECLARE
    		prevdeptno   number;
    		deptloc      varchar2(30);
    		deptname     varchar2(30);
    		CURSOR getemps IS select * from emp 
    				where ((select job from emp where ename = :empname)  IN ('PRESIDENT', 'MANAGER')) 
                 			or deptno = (select deptno from emp where ename = :empname) 
                 			order by deptno, ename;
    		BEGIN
    			sys.htp.htmlopen;
    			sys.htp.headopen;
    			sys.htp.title('Departments');
    			sys.htp.headclose;
    			sys.htp.bodyopen;
     			for emprecs in getemps
    			loop
    			if emprecs.deptno != prevdeptno or prevdeptno is null then
            					select dname, loc into deptname, deptloc 
           	     				from dept where deptno = (select deptno from emp where ename = emprecs.ename);
              			if prevdeptno is not null then
                 				sys.htp.print('</ul>');
              end if;
              			sys.htp.print('Department ' || deptname || ' located in ' || deptloc || '<p/>');
              			sys.htp.print('<ul>');
              end if;
    			sys.htp.print('<li>' || emprecs.ename || ', ' || emprecs.job || ', ' || emprecs.sal || '</li>');
    			prevdeptno := emprecs.deptno;
    			end loop;
    			sys.htp.print('</ul>');
    			sys.htp.bodyclose;
    			sys.htp.htmlclose;
    		END;
    
    		URL call will be in the form: https://<SERVER>.oraclecloudapps.com/apex/bics/plsql/
    		Response will be in following format for this specific plsql example
    				Response Content-Type:text/html; charset=UTF-8
    				<HTML>
    				<HEAD>
    				<TITLE>Departments</TITLE>
    				</HEAD>
    				<BODY>
    				</ul>
    				</BODY>
    					</HTML>