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. DATA Doctionary Table:
• USER_TAB_COLUMNS: All table names with column names and data types.
• USER_CONSTRAINTS: Table’s constraints name.
• USER_INDEXES: Table’s index name.
• TTTT
2. FUNCTION:
• GREATEST(): This function will return the greatest value in a list of expressions.
• LEAST(): This function will return the smallest value in a list of expressions.
• YYYYYYYYYY
3. Linewise Total Calculation: Using Analytical Functions, we can achieve this. SELECT EMPLOYEE_ID,SALARY,SUM(SALARY) OVER (ORDER BY EMPLOYEE_ID) LINE_TOTAL FROM EMPLOYEES.
4. Modify the order of columns without dropping the table: ALTER TABLE EMPLOYEES_TEST MODIFY MANAGER_ID INVISIBLE; Alter TABLE EMPLOYEES_TEST MODIFY MANAGER_ID VISIBLE;

5. Suppose we open and start working on a cursor, and in between some records were deleted by another user from the table. Are we still able to see all the selected rows of the cursor? Answer: Yes, we are able to see all the records that are selected in the cursor, because when we open a cursor, then it will load all data into memory, and data will be viewed from memory, not the base table. But if we operate any DML operation using cursor data, then the DML operation will fail because related data will not be available on the base table. We can also prevent DML operations during cursor execution by using the For Update clause within the cursor declaration.

6. Private Procedure Or Private Function: A private procedure or function is only defined within the package body, and no one will be able to access it. Only other package objects will be able to use this type of procedure or function. 

7. What is Pragma: Prgama is a precompiler directive to instruct the compiler to compile the program unit differently for a specific functionality at the runtime. List of pragma available in Oracle (based on 18C): AUTONOMOUS_TRANSACTION,COVERAGE,DEPRECATE,EXCEPTION_INIT,INLINE,RESTRICT_REFERENCES,SERIALLY_REUSABLE,UDF.
8. Table Level & Column Level Constraints: When we define a constraint in the column definition, then it will be column-level constraints, and when we define a constraint in the table level, then it will be table-level constraints. Composite constraints are now allowed in column-level constraints.
9. DELETE command will be fired by DML trigger, but Truncate command will not be fired by DML trigger. The Truncate command will be fired by DDL trigger. DDL trigger will be created on schema level, and we can specify it for specific tables by using the ORA_DICT_OBJ_NAME & ORA_DICT_OBJECT_TYPE function. The syntex will be:
   
   CREATE OR REPLACE TRIGGER T_TRUNCATE_TRG
   BEFORE TRUNCATE ON SCHEMA
   BEGIN
   IF ORA_DICT_OBJ_NAME ='Table_name' and ORA_DICT_OBJECT_TYPE ='TABLE THEN
   RAISE_APPLICATION_ERROR(-20001,'Truncation not allowed on Tables');
   END IF;
   END;
10. Hierarcial Query:
    SELECT FIRST_NAME, LAST_NAME, SYS_CONNECT_BY_PATH(LAST_NAME,'->') PATH, LEVEL,CONNECT_BY_ISLEAF FROM EMPLOYEES START WITH MANAGER_ID IS NULL CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
11. Different between Delete, Truncate, & Drop.
    Delete: Delete is a DML operation, and we can delete partial data by using the where clause. Deleted data can be recovered by using rollback. No impact on dependent object. This command is slower because it uses an undo segment, and triggers will get fired (on delete trigger). Deleted data is temporarily stored in the redo log.
    Truncate: Truncate is a DDL (autocommit) operation. All rows will be removed and we cannot use the where clause. Data lost forever and no impact-dependent object. The truncate command is faster, and no trigger will get invoked.
    Drop: DDL Command and removes the dependent object. Data can be recovered if the flashback option is on. A dependent object will have to be created. 
12. What is implicit cursor? Answer: An implicit cursor is a session cursor that is constructed and managed by Oracle. PL/SQL opens an implicit cursor every time when we run a SELECT or DML statement. We cannot control implicit cursor, but we can get information from its attributes.
    An explicit cursor is a session cursor that is constructed and managed by the user.
    Cursor Attributes are ISOPEN,FOUND,NOTFOUND,ROWCOUNT
13. What is bulk bind?
    Answer: The PL/SQL engine executes all the procedural code, and the SQL engine will execute all the SQL statements. Assigning values to PL/SQL variables that appear in SQL statements is called binding.
    Bulk binding is a process in which instead of doing the binding one by one because of CONTEXT SWITCHING, the entire information from the SQL statement will be binded back to the PL/SQL variable either with a single switch or with fewer switches, and this concept is called bulk binding.
    Bulk binding binds an entire collection of values at once. To implement bulk binding, Oracle provides two statements. One is called a FORALL statement, and another is called a BULK collection clause.
14. What is sort merge join? 
    Answer: A sort-merge join is a join algorithm used in Oracle to combine data from two tables based on a common join key. It's a variation of a nested loop join. 
15. What is trigger?
16. What is materialised view? can we use materialized view without materialized view log?
17. In which case can we use Pragma Autonomous transactions?
18. What is a save exception?
    Answer: Save Exception used in FORALL statement. Using Save Exception PL/SQL does not raise an exception; instead, it saves information about the failure. After the FORALL statement is complete, PL/SQL raises a single exception for the FORALL statement.
19. What is Dynamic SQL? Using EXECUTE IMMEDIATE, we can use dynamic SQL.
20. When do we use ref Cursor?
21. What is the virtual column and syntax for that column?
22. What is the difference between a nested array and a V-array?
    
23. Did DML operations allow in materialised view?
    Answer: Users can perform DML operations on a writeable materialised view, but if we refresh the materialised view, then these changes are not pushed back to the master and the changes are lost in the materialised view itself.

 

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.

 

Oracle Analytical Question

Index:
Scenario -1 

• Suppose I have a table EMP with three columns (EMP_ID, EMP_NAME, SALARY), and I create a unique composite index on this table's two columns (EMP_ID, EMP_NAME). Now I write a query in which the query's where clause contains two columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'). Which types of index scans use Oracle?

        Answer: Oracle will use INDEX UNIQUE SCAN.-TABLE ACCESS BY INDEX ROWID

• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_ID=100 ). Which types of index scans use Oracle?
  
  
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_NAME='BASIR'). Which types of index scans use Oracle?

      Answer: Oracle will use TABLE ACCESS FULL 

• If I write a query in which the query's where clause contains three columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'  AND SALARY=3000). Which types of index scans use Oracle?

        Answer: Oracle will use INDEX UNIQUE SCAN.- TABLE ACCESS BY INDEX ROWID

Scenario -2

• Suppose I have a table EMP with three columns (EMP_ID, EMP_NAME, SALARY), and I create a unique composite index on this table's two columns (EMP_ID, EMP_NAME). Now I write a query in which the query's where clause contains two columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'). Which types of index scans use Oracle?
        Answer: Oracle will use INDEX RANGE SCAN.
        TABLE ACCESS BY INDEX ROWID BATCHED
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_NAME='BASIR'). Which types of index scans use Oracle?
      Answer: Oracle will use TABLE ACCESS FULL 
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_ID=100 ). Which types of index scans use Oracle?
      Answer: Oracle will use INDEX RANGE SCAN.-
                TABLE ACCESS BY INDEX ROWID BATCHED


Index fast full scan :
If we create primary key constraints or create a unique composite index and specify that particular column as a not null. Then if we write a query about which column is part of the composite index, then it will use-Index fast full scan.
Suppose: I have an EMP Table with three columns (id not null, name, salary)
Create a unique composite Index (ID, Name). 
My query was SELECT ID, NAME FROM EMP. Then it will use Index fast full scan.

SQL Loader & External File:

SQL Loader: SQL loader is a utility provided by Oracle to load data from external files into the Oracle database. This is a command line utility. To do this, first, we need to create database directories. Then need two files one is a data file, and another is a control file. The control file will have information about the name of the source file and metadata information about the source file. For example, The name of the source file, how the source file is formatted, and what should be a target table.

The sample Data File is source_data.txt, and the data format

ID, NAME, ADDRESS, SALARY

1001,Hasan Mahmud,Mirpur-1 Dhaka,100000,

1002,Rasel Hasan Mahmud,Mirpur-1 Dhaka,500000,

1003,Kabir Hasan Mahmud,Mirpur-1 Dhaka,800000,

Control file: control_file.txt

OPTIONS(SKIP=1)

LOAD DATA

INFILE 'SampleFile.txt'

TRUNCATE INTO TABLE EMP_DTL

FIELDs TERMINATED BY ','

(ID, NAME, ADDRESS, SALARY)

SQL Loader Command: Got to Directory Location. Then run this command

sqlldr hrms/hrms@orcl control=control_file.txt

External Table: External table is another way to read information from external files to Oracle Database. For this, first, we need to create directories and put source files here. Another one is to create an external table. This particular file reads data from an external file. Actually, the external table will not store any data. When we call an external table then, it will read data from external sources and show this data.Table Syntex:

CREATE TABLE EMP_DTL_EXTERNAL

(

   ID        NUMBER,

   NAME      VARCHAR2 (100),

   ADDRESS   VARCHAR2 (100),

   SALARY    NUMBER

)

ORGANIZATION EXTERNAL

   (TYPE ORACLE_LOADER

         DEFAULT DIRECTORY DB_BACKUP

            ACCESS PARAMETERS

               (RECORDS DELIMITED BY NEWLINE

 FIELDS TERMINATED BY ',')

         LOCATION ('SampleFile.txt'))

         REJECT LIMIT UNLIMITED;

SELECT * FROM EMP_DTL_EXTERNAL

Linux Command Line

 Shell: It's a CLI stand for command-line-interface.

Terminal: A terminal is a tool in which you can pass your shell commands. To open the terminal press ctrl-alt-T.

Oracle Apex Important Question

Oracle Apex Important Question :

1. Application Processes: Application Processes execute at the same point for every page in the application. However, we can apply conditions for specific pages to control when the process executes. If we want to show some value on every page, then we create an application process.
   
   
2. Application Computation: Application Computation are units of logic that set the value of a single page or application level item and are run at the same point across multiple pages in an application. Like page-level computation, application computation can be based on static value, item value, PLSQL or SQL. Suppose Application Name.
   
   
3. Authentication Schema:  An authentication scheme defines what is required for an authentication process. This includes the following: The login module stack determines whether a user is granted access to an application. The user interfaces are used to gather the information required to authenticate a user.

• Authentication is the process of establishing user's identities before they can access an applications.
• Although we can define multiple authentication schemes for your application, only one scheme can be current at a time.
• The purpose of authentication is to determine the application user identity.
• Create custom authentication scheme. Create a custom authentication method we must provide a PL/SQL function the Application Express engine executes before processing each page request.The function will return boolean values.

1. 
   
2. Authorization Schemes: An authorization scheme extends the security of your application's authentication scheme. You can specify an authorization scheme for an entire application, page, or specific control, such as a region, item, or button.

     5.  Apex Collection : 

• The apex collection is a temporary structure, similar to a temporary table. In  Apex Collection we can store data temporarily and can be posted after the performing validation or calculation during specifice user session. 
• Collection in Oracle Apex are the temporary storage for the current session, In which you can add the data, access the data, and can do other lots of things.
• Collections enable we to temporary capture one or more nonscalar values. We can use collections to store rows and columns currently in session state so they can be accessed,manipulated, or processed during user's specific session. 
• Every collection contains a named list of data elements (or members) which can have up to 50 character attributes(VARCHAR3(4000)), five number attribures, five date attributes one XML type attribute, one large binary attribute(BLOB). We can insert,update,and delete collection information using PL/SQL API APEX_COLLECTION.

         apex_collection.collection_exist(p_collection_name=>'test');
         apex_collection.create_collection(p_collection_name=>'test');
         apex_collection.truncate_collection(p_collection_name=>'test');

         apex_collection.add_member(p_collection_name=>'test');

     6.  What is page 0 in oracle apex?

Page zero acts as a template page for the application. A global page is a special page. Unlike all other processes, validations or branches. It function as a master pages.

•  Page Zero acts as a template for the application.
• All components of page Zero will be rendered on all the pages in the application.
• The following component can be used on page zero: Computation,Branches,Regions(forms,reports,lists and HTML),Items,Dynamic action.
• Page Zero has no processing part.

Page Item: 

• Page items are created on page level.
• An item is part of an HTML form.
• An item can be a text field,text area,password,select list,check box,and so on.
• Item attributes affect the display of items on a page.
• These attributes can impact where a label display, how large an items is.

Application Item :

• Application Item are created on application level.
• We have to give name of the Application Item & Scope--Application/Global.
• If the oracle apex session is shared by more than one application and the Global item's value should be the same for all applications.
• Application items maintain sesson state.
• Application items can be set using computations,processes or by passing values on a URL.
• Application can share the same session if their authentications have the same session cookie attributes. the scope attribute of application items has to be the same across these application.
• Application level items do not display, but are used as global variavles to the application.
• Appllication item can not be use in Javascript to get the value.
• We can use application item as a global variable.

Global Page items:

• The Global page of application functions as a master page.
• We can add several globel page for each user interface.
• The application express engine renders all components we add to a Global page on every page within our application.

Did DML Operation allowed in Materialized view?

Answer: Users can perform DML operations on a writeable materialized view, but if we refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself.

Faceted Search : Faceted search allows we to filter different columns on the left side of the page and report on the right side of the page, to get only the data that we need.

Error Log: Create Error Log Table Statement

begin
  dbms_errlog.create_error_log (dml_table_name => 'dest');
end;

Create Error Log Table With custom table name:

begin
  dbms_errlog.create_error_log (dml_table_name     => 'dest',
                                err_log_table_name => 'dest_err_log');
end;

Insert Statement:

INSERT INTO TABLE_NAME

                  (UNIQUE_ID, MOBILE_NUMBER, MOBILE_EXCESS_BILL, CREATED_BY)

          VALUES (i.UNIQUE_ID, i.MOBILE_NUMBER, i.MOBILE_EXCESS_BILL, :APP_USER)

         LOG ERRORS INTO ERR$_MOBILE_BILL_EXCESS_EXCEL REJECT LIMIT UNLIMITED;

UPL Structure in Apex:  The sample structure will be :

http://www.gplhrms.com:8080/ords/f?p=100:1:3211767652771:::::

PC Address/Port Number/ords/f?p=Application ID/Page Number/Session ID

Insert data into multiple table using Interactive Grid in Oracle Apex:

Answer: Yes, Possible to insert multiple table from interactive Grid 

but we should get rid of the automatic row processing (created by Apex, by default) and write your own PL/SQL code which would do inserting/updating. Something like an instead-of trigger on a view. Example:

Begin 
  case when :apex$row_status = 'C' then 
         insert into emplocations (employee_id, location_id)
         values (:employee_id, :location_id);
       when :apex$row_status = 'U' then
         update emplocations set
           employee_id = :employee_id,
           location_id = :location_id
           where rowid = :rowid;
       end;
End;  

Instead of Trigger on Views: INSTEAD OF triggers control insert, update, merge, and delete operations on views, not tables. They can be used to make no updateable views updateable and to override the default behavior of views that are updateable. Example:

  CREATE [OR REPLACE] TRIGGER trigger_name

   INSTEAD OF operation (Insert/Update/Delete)

   ON view_name

   FOR EACH ROW

   BEGIN

     ------------The code goes here...

   END;

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

We can copy one apex page item value to another apex item by using Javascript. The code will be like this:

First, you need to create Dynamic Action on the selected item. The Action of Dynamic Action will be to execute JavaScript code.

var x=$v("p2_name");

$s('P2_TARGETED_ITEM',X);

The Execute option will be=> Fire on initialization=>on

Add the check box in the classic report: To add a checkbox to the Classic report, use the apex_item.checkbox API function.

1. select  
2.     APEX_ITEM.CHECKBOX(p_idx=>1, p_value=>DEPTNO)  as select_dept,  
3.     DEPTNO as DEPTNO,  
4.     DNAME as DNAME,  
5.     LOC as LOC  
6. from DEPT  

Then you can access checked values (for example in a page process)

1. declare  
2. v_deleted_depts number := 0;  
3. begin  
4. FOR i in 1..APEX_APPLICATION.G_F01.count  
5. LOOP  
6.   v_deleted_depts := v_deleted_depts + 1;  
7.   delete from dept where deptno = APEX_APPLICATION.G_F01(i);  
8. END LOOP;  
9. :P1_DEPTCOUNT := v_deleted_depts;  
10. end;  

How can we debug PL/SQL code in Apex?

By using APEX_DEBUG

APEX_DEBUG.ENABLE(apex_debug.c_log_level_info);

apex_debug.message(p_message=>'clip enable debug.SQL below);

Error Show From Apex:

APEX_ERROR.ADD_ERROR(P_MESSAGE => 'Sorry, You are not Eligable for Medicine Application.', p_display_location => apex_error.c_inline_in_notification );

Most developers know and already have used the $s, $v and $x functions to work with form items in Javascript. $s sets an item value, $v retrieves it and $x determines whether an item with the given name exists on the current page.

apex.item("P2_NAME").getvalue().

apex.item("P2_NAME").setvalue("Sample")

Login One application to another application without any authentication In Oracle Apex:

For this, we need to setup in both application

=> shared component=> Authentication Schema=>Current Authentication=>Session Sharing

 Then select=> Type is workspace sharing or custom