Image view In BI Publisher Report In Oracle Apex

 1. First we need to create procedure which convert image to pdf. The procedure will like bellow.

CREATE OR REPLACE FUNCTION HRMS.PDF2IMAGE (p_blob IN BLOB)

   RETURN CLOB

IS

   l_clob   CLOB;

   l_step   PLS_INTEGER := 24573;--12000;

--        make sure you set a multiple of 3 not higher than 24573

BEGIN

   FOR i IN 0 .. TRUNC ( (DBMS_LOB.getlength (p_blob) - 1) / l_step)

   LOOP

      l_clob :=

            l_clob

         || UTL_RAW.cast_to_varchar2 (

               UTL_ENCODE.base64_encode (

                  DBMS_LOB.SUBSTR (p_blob, l_step, i * l_step + 1)));

   END LOOP;

   RETURN l_clob;

END;

2. We need to create sample query to bring data from database.

SELECT UNIQUE_ID, EMPLOYEE_NAME,

          (SELECT CODE_DESC

             FROM CODE_MASTER

            WHERE HARD_CODE = 'DSG' AND SOFT_CODE = DESIGNATION_CODE)

       || CHR (10)

       || (SELECT CODE_DESC FROM CODE_MASTER  WHERE HARD_CODE = 'CDV' 

         AND  SOFT_CODE = COMPANY_DIVISION_ID)

       || CHR (10)

       || 'ID: '

       || UNIQUE_ID

       || CHR (10)

       || 'Blood Group: '

       || (SELECT CODE_DESC

             FROM CODE_MASTER

            WHERE HARD_CODE = 'BLD' AND SOFT_CODE = BLOOD_GROUP)

          EMPLOYEE_DETAILS,

          'Date of Issue: '

       || TO_CHAR (SYSDATE, 'DD.MM.RRRR')

       || CHR (10)

       || 'Date of Expire: '

       || TO_CHAR (ADD_MONTHS (SYSDATE, 24), 'DD.MM.RRRR')

          ISSUE_DATE,

       PDF2IMAGE (PICTURE) PICTURE

  FROM EMPLOYEE_INFORMATION

 WHERE UNIQUE_ID =:P464_UNIQUE_ID;

3. In BI Publisher Image Filed Properties change with Following code

 <fo:instream-foreign-object content-type="image/jpg"  height="1.2  in" width="1  in" border="1  in" border-color="blue" >

<xsl:value-of select="PICTURE"/>

</fo:instream-foreign-object>.

4. Image Resize Procedure.

CREATE OR REPLACE PROCEDURE HRMS.RESIZE_IMAGE (P_UNIQUE_ID NUMBER)

IS

   V_PHOTO        BLOB;

   V_THUMD        BLOB;

   V_MIME_TYPE    VARCHAR2 (200);

   V_FILE_NAME    VARCHAR2 (300);

   V_IMAGE_SIZE   NUMBER;

   V_WIDTH        NUMBER := 360;

   V_HEIGHT       NUMBER := 410;

BEGIN

   SELECT TO_NUMBER (CEIL (SUM (DBMS_LOB.GetLength (picture) / 1024)))

     INTO V_IMAGE_SIZE

     FROM EMPLOYEE_INFORMATION

    WHERE UNIQUE_ID = P_UNIQUE_ID;

 IF V_IMAGE_SIZE > 20

   THEN

      SELECT PICTURE,

             PICTURE,

             MIME_TYPE,

             FILENAME

        INTO V_PHOTO,

             V_THUMD,

             V_MIME_TYPE,

             V_FILE_NAME

        FROM EMPLOYEE_INFORMATION

       WHERE UNIQUE_ID = P_UNIQUE_ID

      FOR UPDATE;

      -----------Scale The Image Size

      ORDSYS.ORDIMAGE.process (V_THUMD, 'maxScale=250 320');

      ORDSYS.ORDIMAGE.process (V_PHOTO,

                               'maxScale=' || V_WIDTH || ' ' || V_HEIGHT);

      UPDATE EMPLOYEE_INFORMATION

         SET PICTURE = V_PHOTO,

             MIME_TYPE = V_MIME_TYPE,

             FILENAME = V_FILE_NAME

       WHERE UNIQUE_ID = P_UNIQUE_ID;

   END IF;

   COMMIT;

END;

/

Copy Item Value From One Item to Another Item In Oracle Apex

 We can copy one apex items value to another apex items by using javascritp. The javascript code will be like this.

First need to creat Dynamic action on selected item. The action of Dynamic action will be Execute Javascript code.

var x = $v("P392_TAX_YEAR");   

 $s('P392_SECTION_ID', x);

The Execute options=> Fire on initialization will be no.

Oracle Database Interview Question

1. Different between Delete,Truncate & Drop.
   Delete: Delete is a DML operation and we can delete partial data by using where clause. Deleted data can be recovered by using rollback.No impact on dependent object.This command is slower, because it use undo segment and triggers will get fired (on delete trigger). Deleted data is temporary store in then redo log.
   Truncate: Truncate is DDL (Autocommit) operation.All rows will be removed and cannot use where clause.Data lost forever and no impact dependent object.Truncate command is faster and no trigger will get invoked.
   Drop: DDL Command and removes the dependent object. Data can be recovered if Flashback option is on. Dependent object will have to be created.
   
   
2. What is SQL?

3.  

4. 
   

 

Protect Restfull API with Basic Authentication

 1. Create new module from Restfull service.

2. Create Template..

3. Create Authentication User from ==> Manage Users & Group ==> Create User

   

4. From Privilage Create New privilage.

 

Now API is protected and when we want to call API then we need to use Basic Authentication.