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 ;
}

Standard Password Validation SQL Block

Standard Password Validation SQL Block

Standard Password Format Like 

                       * One uppercase letter

                       * One lowercase letter

                       * One digit 

                       * One special character 

DECLARE

V_USERNAME    VARCHAR2(100);

V_OLD_PASSWORD VARCHAR2(60);

PASSWORD  VARCHAR2(60);

chars integer := 8;

letter integer := 1;

lower integer := 0;

upper integer := 1;

digit integer := 1;

special integer := 1;

digit_array varchar2(10) := '0123456789';

alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';

cnt_letter integer := 0;

cnt_upper integer := 0;

cnt_lower integer := 0;

cnt_digit integer := 0;

cnt_special integer := 0;

len integer := 0;

delimiter boolean := FALSE;

ch varchar2(100);

pw_lower varchar2(256);

BEGIN

V_USERNAME    := :P9991_USER_NAME;

V_OLD_PASSWORD := :P9991_OLD_PASSWORD;

PASSWORD := :P9991_NEW_PASSWORD;

len  := NVL (length(password), 0);

pw_lower := NLS_LOWER(password);

FOR i in 1..len LOOP

      ch := substr(password, i, 1);

      IF ch = '"' THEN

         delimiter := TRUE;

      ELSIF instr(digit_array, ch) > 0 THEN

         cnt_digit := cnt_digit + 1;

      ELSIF instr(alpha_array, NLS_LOWER(ch)) > 0 THEN

         cnt_letter := cnt_letter + 1;

         IF ch = NLS_LOWER(ch) THEN

            cnt_lower := cnt_lower + 1;

         ELSE

            cnt_upper := cnt_upper + 1;

         END IF;

      ELSE

         cnt_special := cnt_special + 1;

      END IF;

   END LOOP;

IF len>20 THEN

RETURN 'Password Must be less than 20 Characters';

ELSE 

IF V_OLD_PASSWORD = PASSWORD THEN

RETURN 'New password cannot be same as old Password' ;

ELSE 

        IF delimiter = TRUE THEN

        RETURN ('password must NOT contain a '|| 'double-quote character, which is '|| 'reserved as a password delimiter');

ELSE 

IF chars IS NOT NULL AND len < chars THEN

RETURN( 'Password length less than ' ||chars);

ELSE 

IF letter IS NOT NULL AND cnt_letter < letter THEN

RETURN( 'Password must contain at least ' ||letter || ' letter(s)');

ELSE 

IF upper IS NOT NULL AND cnt_upper < upper THEN

RETURN('Password must contain at least ' ||upper || ' uppercase character(s)');

ELSE 

IF lower IS NOT NULL AND cnt_lower < lower THEN

RETURN('Password must contain at least ' ||lower || ' lowercase character(s)');

ELSE

IF digit IS NOT NULL AND cnt_digit < digit THEN

RETURN('Password must contain at least ' ||digit || ' digit(s)');

ELSE

IF special IS NOT NULL AND cnt_special < special THEN

RETURN('Password must contain at least ' ||special || ' special character(s)');

ELSE

IF instr(pw_lower, NLS_LOWER(V_USERNAME)) > 0 THEN

RETURN('Password contains the username');

                                        ELSE 

RETURN NULL;

                                        END IF;

END IF;

END IF;

END IF;

END IF;

END IF;

END IF;

        END IF;

END IF;

END IF;

END;

Export Application Components Individually/All Together from a Oracle Apex Application

 We Can Export Application Components individually from Oracle Apex.

Application  >   Shared Components >  Export Application Components > 

Under Component Select Type of Component >  Selected/Check Component Name

like as 'Desktop Navigation Menu' >  Add to Export >  Next  >  Export Components.

Current version of data in database has changed since user initiated update process.

 Current version of data in database has changed since user initiated update process. 

Error occurred in Oracle Apex

This error can occur for many reasons, but one of the most common reasons is when the same row is updated by multiple parties at the same time.

In my case, I get the error when updating a flag in my table. Then I realized that when I update a flag a trigger gets fired and at the same row inside the trigger gets updated again. So I faced the problem.

Search by Keyword from All Oracle Database Objects

Search by keyword from Oracle Database Objects.

select owner, type, name, line, text 

from dba_source where 1 = 1

and text like '%TEXT YOU ARE LOOKING FOR%'

order by owner, type, name;

Error - ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (BLOB File to CLOB Variable Conversion)

Read a BLOB File the message and display the message into a variable.

Error - ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 9923, maximum: 2000)

You can get around the buffer issue by reading the BLOB value in chunks. In order to do that, you can define a user defined function (UDF). The following UDF reads the BLOB field one chunk at a time, converts that to VARCHAR2 and appends the results consecutively to return the result as a CLOB:

CREATE OR REPLACE FUNCTION VARCHAR2CLOB_FROM_BLOB(B BLOB)
RETURN CLOB IS
    c CLOB;
    n NUMBER;
BEGIN
    IF (b IS NULL) THEN 
        RETURN NULL;
    END IF;
    IF (LENGTH(b) = 0) THEN
        RETURN EMPTY_CLOB();
    END IF;
    DBMS_LOB.CREATETEMPORARY(c, TRUE);
    n := 1;
    WHILE (n + 32767 <= LENGTH(b)) LOOP
        DBMS_LOB.WRITEAPPEND(c, 32767, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 32767, n)));
        n := n + 32767;
    END LOOP;
    DBMS_LOB.WRITEAPPEND(c, LENGTH(b) - n + 1, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, LENGTH(b) - n + 1, n)));
    RETURN c;
END;
/

Enable / Disable Runtime Developer Toolbar

 

Check the screenshot path.

Available is for Disable.

Available with Developer Toolbar is for Enable.