Check any one Checkbox In Oracle Apex

Sometimes we have some requirements like the user can not check more than one option. So we can do the solution for this purpose using javascript.

1. Create a report as usual then example below code.

select EMPLOYEEID,

       EMPLOYEENAME,

       EMAIL,

       MOBILE,

       apex_item.checkbox(1,'#ROWNUM#') checkbox  -- code for creating check box in classic report 

  from EMPLOYEES ;

2. Use the blow javascript in  Function and Global Variable Declaration.

$('input[name="f01"]').on('change', function(){

    $('input[name="f01"]').not(this).prop('checked',false);

});

//Here f01 is the name of the check box column.  

XML Visualization And Download the XML into a txt File In Oracle Apex

 

1. Create a page and a region with  PL/SQL Dynamic Content type. Put the below code into PL/SQL Code 

DECLARE

v_text     CLOB;

BEGIN

HTP.p ('<span id="TEST_ID">');      

      FOR i

         IN (

     select to_char(XML)   "Information" 

FROM EMPLOYEES where EMPLOYEEID=1243 )

 LOOP

         v_text := i."Information";

         HTP.p ('<pre>' || replace(v_text,'&amp;','&') || '</pre>');

      END LOOP;

      HTP.p ('</span>');

END;

2. Put the like HTML link code above the code. 

HTP.p ('<a download="Information.txt" id="downloadlink"  ><b>Download</b><br><br><br<br></a> ');  

3.  Finally copy-pest the below JavaScript into page properties>  Function and Global Variable Declaration  Option. Make sure TEST_ID and downloadlink these two id is in the proper place.

(function () {

var textFile = null,

  makeTextFile = function (text) {

    var data = new Blob([text], {type: 'text/plain'});

    // If we are replacing a previously generated file we need to  

    // manually revoke the object URL to avoid memory leaks.

    if (textFile !== null) {

      window.URL.revokeObjectURL(textFile);

    }

    textFile = window.URL.createObjectURL(data);

    return textFile;

  };

  var create = document.getElementById('create'),

    textbox = document.getElementById('TEST_ID'); 

    var link = document.getElementById('downloadlink');

    link.href = makeTextFile(textbox.innerHTML.replace("&amp;", "&").trim());

    link.style.display = 'block';

})();

Language Change Using Google Translator

1. Create a global region on 0 page with a static content type.

2. Copy-pest the below code then hide or customize UI yourself.

<div id="google_translate_element"></div>

<script type="text/javascript">

function googleTranslateElementInit() { new google.translate.TranslateElement({pageLanguage: 'en'}, 'google_translate_element'); }

</script>

<script type="text/javascript" src="//translate.google.com/translate_a/element.js?cb=googleTranslateElementInit">

</script>

</body>

</html>

DBMS_REDACT Advanced Security Feature

How to leverage the data redaction from the Advanced Security feature in order to protect your sensitive data in your application even before it leaves your ATP

You have to execute the below code using the Admin user.

BEGIN

  DBMS_REDACT.ADD_POLICY(

  object_schema          => 'redact', -- (Schema Name)

  object_name            => 'credit_card', -- (Table Name)

  column_name            => 'card_val', --(Column Name)

  policy_name            => 'redact_REG_EXP',

   function_type       => DBMS_REDACT.PARTIAL,

   function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',  -- (Column Value Expression)

   expression          => '1=1',

   policy_description  => 'Partially redacts our credit card numbers', -- Description/Comments

   column_description  => 'card_val contains credit card numbers in VARCHAR2 format' -- Description/Comments

);

END; -- More Details

 

Interactive Report Upper Scroll Bar

1. Make an ID in the region Static ID 

2. Page CSS Inline 

#ID .t-fht-thead{

  overflow: auto !important;

}

API Calling By Oracle PL-SQL/APEX Using UTL_HTTP/ APEX_WEB_SERVICE and ACL Configuration

API Calling Using URL_HTTP

declare

    v_req       utl_http.req;
    v_res       utl_http.resp;
    v_buffer    varchar2(4000); 
    v_body      varchar2(4000) := '{"field":"value"}'; -- Your JSON
begin
    -- Set connection.
 v_req := utl_http.begin_request('http://yourapi_base_url/operation','POST');
    utl_http.set_authentication(v_req, 'username','password');
    utl_http.set_header(v_req, 'content-type', 'application/json'); 
    utl_http.set_header(v_req, 'Content-Length', length(v_body));
    
    -- Invoke REST API.
    utl_http.write_text(v_req, v_body);
  
    -- Get response.
    v_res := utl_http.get_response(v_req);
    begin
        loop
            utl_http.read_line(v_res, v_buffer);
            -- Do something with buffer.
            dbms_output.put_line(v_buffer);
        end loop;
        utl_http.end_response(v_res);
    exception
        when utl_http.end_of_body then
            utl_http.end_response(v_res);
    end;
end;

But if you have Oracle APEX installed, then you can try APEX_WEB_SERVICE package (much simpler).

declare
    v_response      clob;
    v_buffer        varchar2(32767);
    v_buffer_size   number := 32000;
    v_offset        number := 1;
begin
    -- Set connection and invoke REST API.
    v_response := apex_web_service.make_rest_request(
        p_url           => 'http://yourapi_base_url/operation',
        p_http_method   => 'POST',
        p_username      => 'username',
        p_password      => 'password',
        p_body          => '{"field":"value"}' -- Your JSON.
    );
    
    -- Get response.
    begin
        loop
            dbms_lob.read(v_response, v_buffer_size, v_offset, v_buffer);
            -- Do something with buffer.
            DBMS_OUTPUT.PUT_LINE(v_buffer);
            v_offset := v_offset + v_buffer_size;
        end loop;
    exception
        when no_data_found then
            null;
    end;
end;

If you get an ACL exception, then you have to create ACL to open TCP port to connect with REST API.

BEGIN
    DBMS_NETWORK_ACL_ADMIN.create_acl (
        acl          => 'acl.xml',
        description  => 'Connecting with REST API',
        principal    => 'YOUR_DATABASE_SCHEMA',
        is_grant     => TRUE, 
        privilege    => 'connect',
        start_date   => SYSTIMESTAMP,
        end_date     => NULL
    );
    
    DBMS_NETWORK_ACL_ADMIN.assign_acl (
        acl         => 'acl.xml',
        host        => 'localhost', -- Or hostname of REST API server (e.g. "example.com").
        lower_port  => 80, -- For HTTPS put 443.
        upper_port  => NULL
    );
    
    COMMIT;
end; 

I assume your REST API is protected by basic authentication scheme (user and password). To keep this example simple i used HTTP. If you have to connect via HTTPS, then you have to change TCP port in ACL and configure Oracle Wallet for your Oracle database instance.

Create Oracle Database Table and Load Data from Excel in Couple of Seconds

You can create a table and load data from excel with the appropriate column data type within 5 seconds.

Oracle Apex will read the whole excel and then create a table with the appropriate data type by respective data. Finally, it loads all data into the table from excel.