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. 

RMAN Backup and Archivelog / Noarchivelog

RMAN Backup:

Step-1 : Go to

C:\oracle\app\user\product\12.1.0\dbhome_1\BIN>

open CMD on this path.

Step-2:

Assign database name to ORCLE_SID.

C:\app\user\product\12.1.0\dbhome_1\BIN>set ORACLE_SID=orcl

Check :

C:\app\user\product\12.1.0\dbhome_1\BIN>echo %ORACLE_SID%

Step-3:

C:\app\user\product\12.1.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jul 22 13:56:48 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1613180020)

RMAN>

Step-4:

RMAN> backup database ;

Starting backup at (Date)

using target database control file instead of recovery catalog

...................................................

Finished backup at (Date)

----------------------------------------------

Common Error:

RMAN> backup database ;

Starting backup at 22-JUL-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=364 device type=DISK

RMAN-00571: ========================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS 

RMAN-00571: =============================================

RMAN-03002: failure of backup command at 07/22/2022 13:40:26

RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

Solution:

 Archivelog On

1. Shutdown the Database

SQL>shutdown immediate;

database closed

database dismounted

Oracle instance shut down

2. Open the Database Up-to mount stage.

SQL> Startup Mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 1610609384 bytes

Fixed Size 9028328 bytes

Variable Size 402653184 bytes

Database Buffers 1191182336 bytes

Redo Buffers 7745536 bytes

Advertisements

REPORT THIS AD

3. Enable the Archive log

alter database archivelog;

4. Check destination parameter and change value for archive generation.

-- check

show parameter log_archive_dest

–change value

alter system set log_archive_dest=’D:\Oracle’ scope=both;

5. Open the database.

alter database open;

6. Verify the archive generation by checking the location.

alter system switch logfile;

Disable the Archive Mode:

Rollback Process: noarchivelog

Shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

Oracle Apex Password Expired: ORA-28001: the password has expired

java.sql.SQLException: ORA-28001: the password has expired

Local Apex password expired solution:

------------------------------------

APEX_200200

ALTER USER APEX_200200 IDENTIFIED BY "Password";  

ALTER USER system ACCOUNT UNLOCK;       

APEX_LISTENER

ALTER USER APEX_200200 IDENTIFIED BY "Password";

ALTER USER APEX_200200 ACCOUNT UNLOCK;

YOUR_SCHEMA

ALTER USER YOUR_SCHEMA IDENTIFIED BY "Password";

ALTER USER YOUR_SCHEMA ACCOUNT UNLOCK;

APEX_PUBLIC_USER

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY "Password";

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

APEX_REST_PUBLIC_USER

ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY "Password";

ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK;

APEX_INSTANCE_ADMIN_USER

ALTER USER APEX_INSTANCE_ADMIN_USER IDENTIFIED BY "Password";

ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK;

ORDS_PUBLIC_USER

ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY "Password";

ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK;

Find Number of Inserted Row.

sql%Rowcount is working to print number of inserted row. like below.

dbms_output.put_line('No Of rows'||sql%Rowcount);