Interactive Grid Top Scroll Bar in Oracle Apex

Step 1: 

First, make a class name for the interactive grid.

Go to Page Properties >> Appearance >> CSS Classes >> CLASSNAME

Step 2: 

Goto Page Properties >> CSS Inline >> 

.CLASSNAME .a-GV-w-hdr{

    overflow-x: auto !important;}

Apex User Creation from Process

BEGIN

apex_util.create_user (  
p_user_name=> :NEW.USER_NAME,
p_first_name=> :NEW.FIRST_NAME,
p_last_name=> :NEW.LAST_NAME,
p_description=>  :NEW.DESCRIPTION,
p_email_address =>:NEW.EMAIL_ADDRESS,
p_web_password=>:NEW.TEMP_PASSWORD,--p_group_ids=>set_group_id,
 /*--For extra security, this resets the user's access to the APEX_BI schema only---*/
p_allow_access_to_schemas  => 'STARS_DEMO',
                /*--For extra security, this resets the user's default schema to APEX_BI--*/
p_default_schema =>  'STARS_DEMO',
               /*--For extra security, this resets the user's developer role to an end user Note:  In FETCH USER and EDIT USER p_developer_privs is named p_developer_role ---*/
p_developer_privs  => null,
p_account_locked   => 'N',
p_failed_access_attempts  =>0,
p_change_password_on_first_use=>'Y',
p_first_password_use_occurred =>'N');
/*--Email text defined --*/ 

End ;

An API call has been prohibited. Contact your administrator. Details about this incident are available via debug id "108075"

 

Whenever we try to call the workspace user to create API from the process we may face the below error. 

An API call has been prohibited. Contact your administrator. Details about this incident are available via debug id "108075"

Easy Solution: Just follow the below steps.

Excel File Upload Without Using Plugins

STEP-1 Creating Collection and Taking Excel File Value into Collection Table 

Begin
IF  APEX_COLLECTION.COLLECTION_EXISTS('COLLECTION_TABLE_NAME') THEN
    APEX_COLLECTION.TRUNCATE_COLLECTION('COLLECTION_TABLE_NAME');
END IF;
IF NOT APEX_COLLECTION.COLLECTION_EXISTS('COLLECTION_TABLE_NAME') THEN
    APEX_COLLECTION.CREATE_COLLECTION('COLLECTION_TABLE_NAME');
END IF; 

for r1 in (select *  from apex_application_temp_files f, table( apex_data_parser.parse(

p_content  => f.blob_content,
p_add_headers_row => 'Y',
-- p_store_profile_to_collection => 'FILE_PROV_CASH',
p_file_name=> f.filename,
p_skip_rows => 1 ) ) p   

--This line will skip excel the first row, as I contain heading only
where f.name = :P3_UPLOAD_FILE -- //Page Item name
)
loop
APEX_COLLECTION.ADD_MEMBER(P_COLLECTION_NAME => 'COLLECTION_TABLE_NAME',
P_C001            => nvl(REPLACE(r1.col001,'-',''),0),
P_C002            => nvl(REPLACE(r1.col002,'-',''),0),

P_C003            => nvl(REPLACE(r1.col003,'-',''),0),
p_C004            => nvl(REPLACE(r1.col004,'-',''),0),
p_C005            => nvl(REPLACE(r1.col005,'-',''),0), 
P_C006            => nvl(REPLACE(r1.col006,'-',''),0),
p_C007            => nvl(REPLACE(r1.col007,'-',''),0),
p_C008            => nvl(REPLACE(r1.col008,'-',''),0),
P_C009            => nvl(REPLACE(r1.col009,'-',''),0),
P_C010            => nvl(REPLACE(r1.col010,'-',''),0),
P_C011            => nvl(REPLACE(r1.col011,'-',''),0),
P_C012            => nvl(REPLACE(r1.col012,'-',''),0)
                                            );

END LOOP; 

end;

STEP-2 -- Transferring Collection Table to Database Table.

DECLARE
CURSOR C2 IS
SELECT C001, C002, C003, C004, C005, C006,
       C007, C008, C009, C010, C011, C012 ,C013
FROM APEX_COLLECTIONS 
WHERE
COLLECTION_NAME = 'COLLECTION_TABLE_NAME';

BEGIN

FOR I IN C2   LOOP

INSERT INTO DATABASE_TABLE
   (   COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME,
       COLUMN_NAME
   )
VALUES( I.C001,
        I.C002,   --collection data
        I.C003, 
        I.C004, 
        I.C005,
        I.C006,
        I.C007,    
        I.C008,
        I.C009, 
        I.C010,
        I.C011,
        I.C012,
        I.C013,
        :APP_USER
);
END LOOP;
--apex_collection.truncate_collection(p_collection_name => 'COLLECTION_TABLE_NAME'); 
END;

Multiple File Download into a ZIP File in Oracle Apex

*** Please Follow My Blog to Inspire Me More Posting. ***

Step 1. Create a Classic Report and add a checkbox 

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

SELECT 

ID,

APEX_ITEM.display_and_save (02,ID)ID,

FILE_NAME,

FILE_MIMETYPE,

FILE_CHARSET,

FILE_LASTUPD_DTTM,

FILE_UPLOAD_DATE,

FILE_UPLOAD_STATUS,

apex_item.checkbox(01,'#ROWNUM#') CheckBox

FROM TABLE

WHERE 1=1 AND

ID=:P5_ID; 

Step 2. Create a process 

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

declare

var_zip blob; 

var number ;

begin

-- Create/clear the ZIP collection

apex_collection.create_or_truncate_collection(

p_collection_name => 'ZIP');

if APEX_APPLICATION.G_F01.COUNT < 1 then 

raise_application_error(-20001,'Please Select to Download!');

end if;

for i in 1..APEX_APPLICATION.G_F01.COUNT  loop -- Loop through all the files in the database

 var:=APEX_APPLICATION.G_F01(i);

   begin

        for var_file in (select FILE_BLOB , FILE_NAME

                         from TABLE

                         where 1=1

                         AND ID=UPPER(APEX_APPLICATION.G_F02(var)))

        loop      

            -- Add each file to the var_zip file

            apex_zip.add_file(

                p_zipped_blob => var_zip,

                p_file_name   => var_file.FILE_NAME,

                p_content     => var_file.FILE_BLOB );

        end loop;  

    exception when no_data_found then

        -- If there are no files in the database, handle error

        raise_application_error(-20001, 'No Files found!');

    end;

    end loop;

    -- Finish creating the zip file (var_zip)

    apex_zip.finish(

        p_zipped_blob => var_zip);

    -- Add var_zip to the blob column of the ZIP collection

    apex_collection.add_member(

        p_collection_name => 'ZIP',

        p_blob001         => var_zip);

end;

Step-3  Create a Ajax Callback Process  with this name ---download_zip_file  

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

declare

  var_mimetype varchar2(50) := 'application/zip';

  var_name varchar2(100) := to_char(current_date, 'DD-MON-YYYY') || '_File.zip';

  var_blob blob;

begin

    -- Get the BLOB from the ZIP collection

    select blob001 into var_blob from apex_collections where collection_name = 'ZIP' and seq_id = 1;

    sys.htp.init;

    sys.owa_util.mime_header( var_mimetype, FALSE );

    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( var_blob));

    sys.htp.p('Content-Disposition: attachment; filename="' || var_name || '"' );

    sys.htp.p('Cache-Control: max-age=3600');  -- tell the browser to cache for one hour, adjust as necessary

    sys.owa_util.http_header_close;

    sys.wpg_docload.download_file( var_blob );

    apex_application.stop_apex_engine;

    apex_collection.truncate_collection(p_collection_name => 'ZIP');

    

exception when apex_application.e_stop_apex_engine then

    NULL;

end; 

Step 4. Create a Branche on After Processing

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

Target-> Type-> Page in this application 

page -> same page 5

Advanced-> Request-> APPLICATION_PROCESS=download_zip_file  -- download_zip_file is the Ajax Callback process (Name must be same)

Column Freeze In Interactive Report Oracle Apex

Here we are freezing the first 5 columns from an interactive report in Oracle Apex.

Step-1. Copy the below code and pest into Page -> Execute when Page Loads

$('.a-IRR-table tr th:nth-child(2), .a-IRR-table tr td:nth-child(2)').css("left",$(".a-IRR-table tr th:nth-child(1)").width());

$('.a-IRR-table tr th:nth-child(3), .a-IRR-table tr td:nth-child(3)').css("left",$(".a-IRR-table tr th:nth-child(1)").width()+$(".a-IRR-table tr th:nth-child(2)").width());

$('.a-IRR-table tr th:nth-child(4), .a-IRR-table tr td:nth-child(4)').css("left",$(".a-IRR-table tr th:nth-child(1)").width()+$(".a-IRR-table tr th:nth-child(2)").width()+$(".a-IRR-table tr th:nth-child(3)").width());

$('.a-IRR-table tr th:nth-child(5), .a-IRR-table tr td:nth-child(5)').css("left",$(".a-IRR-table tr th:nth-child(1)").width()+$(".a-IRR-table tr th:nth-child(2)").width()+$(".a-IRR-table tr th:nth-child(4)").width());

Step-2 Copy the below code and pest into Page-> Inline

Finally, use left: size as you want. 

.a-IRR-table tr th:nth-child(1), .a-IRR-table tr td:nth-child(1)
{
position: sticky;
left: 0px !important;
}
.a-IRR-table tr th:nth-child(2), .a-IRR-table tr td:nth-child(2)
{
position: sticky;
}
.a-IRR-table tr th:nth-child(3), .a-IRR-table tr td:nth-child(3)
{
position: sticky;
}
.a-IRR-table tr th:nth-child(4), .a-IRR-table tr td:nth-child(4)
{
position: sticky;
left: 186px !important ;
}
.a-IRR-table tr th:nth-child(5), .a-IRR-table tr td:nth-child(5)
{
position: sticky;
left: 286px !important ;
}