Decode Function

DECODE

Syntax

Purpose

DECODE compares expr to each search value one by one.

If expr is equal to a search,

Then Oracle Database returns the corresponding result.

If no match is found, then

Oracle returns default. If default is omitted, then Oracle returns null.

The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_

DOUBLE) or character types.

The maximum number of components in the DECODE function, including expr,

Searches, results, and default, is 255.

DECODE ( expr , search , result

,

, default

)

Test Case

Create table plch_employees (

   Id    integer primary key

, name varchar2 (40)

, gender char (1)       

)

/

Insert into plch_employees values (100, 'Mr. John Doe','M')

/

Insert into plch_employees values (200, 'Mrs. Jane Doe','F')

/

Insert into plch_employees values (300, 'Ms. Julie Doe','F')

/

Insert into plch_employees values (400, 'Mr. Jack Doe','M')

/

Insert into plch_employees values (500, 'Dr. James Doe','M')

/

Insert into plch_employees values (600, 'Jonathan Doe','M')

/

Insert into plch_employees values (700, 'Jeff Jr. Doe','M')

/

Commit

/

select id, name, decode(gender, 'M', 'MALE', 'F', 'FEMALE', gender)

  from plch_employees;

Test Case:

/*

User have requirement ( if parameter value is 100 then query only show the result of those employee who have id no 100

Else query will show all employees

Expect 100 no ID)

*/

---we can handle it from our decode function 

Select id, name, and decode (gender, ‘M’, MALE, ‘F’, ‘FEMALE’, gender)

  From plch_employees

  Where decode (id, 100,'Y','F') =decode (:v_id, 100,'Y','F');

ABS function in SQL

Case Study:

Get only positive Values

Practice Table:

create table plch_employees (

   id    integer primary key

, name  varchar2(40)

, gender char(1)         

)

/

insert into plch_employees values (-100, 'Mr. John Doe','M')

/

insert into plch_employees values (-200, 'Mrs. Jane Doe','F')

/

insert into plch_employees values (300, 'Ms. Julie Doe','F')

/

insert into plch_employees values (-400, 'Mr. Jack Doe','M')

/

insert into plch_employees values (500, 'Dr. James Doe','M')

/

insert into plch_employees values (600, 'Jonathan Doe','M')

/

insert into plch_employees values (700, 'Jeff Jr. Doe','M')

/

commit

/

ABS

Syntax

Purpose

The ABS function returns the absolute value of a number, which will always be zero or a positive number.

This function takes as an argument any numeric data type or any nonnumeric data

type that can be implicitly converted to a numeric data type.

Examples

The following example returns the absolute value of -15:

SELECT ABS (-15) "Absolute"

FROM DUAL;

Absolute

----------

15

Print Report Directly oracle Forms

Oracle Forms : Print Report Directly

September 21, 2012

Usually we display Oracle Reports in Oracle Forms in PDF format and then the user print it through Adobe Reader  program or any other  PDF reader program.

But the requirement is to print Oracle Report directly without  displaying the report at screen.

To achieve this requirement in Oracle Forms 6i it is easy as you you set DESTTYPE parameter in report file to PRINTER and It will work correctly, But this feature not work with Oracle Forms 10g.

Some developers use java bean for this purpose but I will produce the below workaround to print report directly without using java bean.

1- Run Report and save it as PDF file at application server.

    I will share folder c:\temp at application server for everyone for read only and save reports PDF files in this folder. The share path of the folder is \\ApplicationServer_IP\Temp\

2- Silent Print of PDF file at user machine using PDF reader programs like Adobe Reader

3- Close PDF reader program after printing.

Some Developers may use ORARRP utility and do its required configuration at server and client machine for direct printing too.

Procedure Code

I create Procedure (Program Unit) in Oracle Forms to do the previous steps

PROCEDURE PRINT_DRIRECTLY (IN_SERVER_NAME  VARCHAR2, 

               IN_REP_NAME    VARCHAR2, 

               IN_OBJ_NAME    VARCHAR2) 

IS 

   LC$REP            VARCHAR2 (100); 

   LC$REP_STATUS     VARCHAR2 (20); 

   LC$LN$ADOBE_PATH  VARCHAR2 (500); 

   LN$PROCESS_ID     WEBUTIL_HOST.PROCESS_ID;

   REPID             REPORT_OBJECT; 

   LC$FILE_PATH      VARCHAR2 (1024); 

   LC$TEMP_PATH CONSTANT VARCHAR2 (256) 

      := '\\ApplicationServer_IP\Temp\' ; 

BEGIN 

   LC$FILE_PATH := LC$TEMP_PATH || 'ReportName.pdf'; 

   REPID := FIND_REPORT_OBJECT (IN_OBJ_NAME); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_FILENAME, IN_REP_NAME); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_SERVER, IN_SERVER_NAME); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_EXECUTION_MODE, BATCH); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_COMM_MODE, SYNCHRONOUS); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_DESTYPE, FILE); 

  

   --File name must be unique otherwise it will override the old file with same name 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_DESNAME, LC$FILE_PATH); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_DESFORMAT, 'pdf'); 

  

   LC$REP := RUN_REPORT_OBJECT (REPID); 

  

   LC$REP_STATUS := REPORT_OBJECT_STATUS (LC$REP); 

  

   WHILE LC$REP_STATUS IN ('RUNNING', 'OPENING_REPORT', 'ENQUEUED') 

   LOOP 

    LC$REP_STATUS := REPORT_OBJECT_STATUS (LC$REP); 

   END LOOP; 

  

   IF LC$REP_STATUS = 'FINISHED' 

   THEN 

    IF WEBUTIL_FILE_TRANSFER.IS_AS_READABLE (LC$FILE_PATH) 

    THEN 

      -- Try to get the correct instaled version of Adobe Reader program 

  

      FOR I IN REVERSE 5 .. 13 

      LOOP 

       BEGIN 

         LC$LN$ADOBE_PATH := 

          CLIENT_WIN_API_ENVIRONMENT. 

          READ_REGISTRY ( 

             'HKEY_LOCAL_MACHINE\SOFTWARE\Adobe\Acrobat Reader\' 

            || I 

            || '.0\Installer', 

            'Path', 

            TRUE); 

       EXCEPTION 

         WHEN NO_DATA_FOUND 

         THEN 

          --If path not exists in Registery, it will raise NO_DATA_FOUND EXCEPTION 

  

          NULL; 

       END; 

  

       IF LC$LN$ADOBE_PATH IS NOT NULL 

       THEN 

         EXIT; 

       END IF; 

      END LOOP; 

  

      BEGIN 

       LN$PROCESS_ID := 

         WEBUTIL_HOST. 

         NONBLOCKING ( 

            '"' 

          || LC$LN$ADOBE_PATH 

          || 'Reader\AcroRd32.exe" /H /P ' 

          || LC$FILE_PATH); 

      EXCEPTION 

       WHEN OTHERS 

       THEN 

         NULL; 

      END; 

  

      --sleep for sometime until printing is finished 

  

      DBMS_LOCK.SLEEP (3); 

  

      -- Teminate Adobe Reader Program Process 

  

      IF NOT WEBUTIL_HOST.ID_NULL (LN$PROCESS_ID) 

      THEN 

       WEBUTIL_HOST.TERMINATE_PROCESS (LN$PROCESS_ID); 

      END IF; 

    ELSE 

      MESSAGE (

            LC$FILE_PATH || ' is unreadable path at application server');

    END IF; 

   END IF; 

END PRINT_DRIRECTLY; 

Use Guidelines

1- You pass three parameters to procedure

     a- IN_SERVER_NAME  : Report service name

     b- IN_REP_NAME : physical file path of report file (rdf , rep files)

     c- IN_OBJ_NAME : Report object name in your form

2- I expect that the installed program at client machine is Adobe reader if there are another program you use, then you should modify the code in below lines

LN$PROCESS_ID := 

         WEBUTIL_HOST. 

         NONBLOCKING ( 

            '"' 

          || LC$LN$ADOBE_PATH 

          || 'Reader\AcroRd32.exe" /H /P ' 

          || LC$FILE_PATH); 

3- You should make report file name (Generated PDF file after running report) unique over the application, You should modify the below code to make it unique.

LC$FILE_PATH := LC$TEMP_PATH || 'ReportName.pdf'; 

call report from oracle

Posted by MuhammadFaisal May 10, 2019

Just create a program unit in your form ,  just copy paste this code.

PROCEDURE run_report_object_proc (

   report_id             report_object,

   report_server_name    VARCHAR2,

   report_format         VARCHAR2,

   report_file_name      VARCHAR2,

   report_otherparam     VARCHAR2,

   reports_servlet VARCHAR2)

IS

   report_message   VARCHAR2 (1000)  := '';

   rep_status       VARCHAR2 (1000)  := '';

   vjob_id          VARCHAR2 (4000) := '';

BEGIN

  

   SET_REPORT_OBJECT_PROPERTY (report_id, report_comm_mode, synchronous);

   SET_REPORT_OBJECT_PROPERTY (report_id, report_filename, report_file_name);

   SET_REPORT_OBJECT_PROPERTY (report_id, report_server, report_server_name);

   SET_REPORT_OBJECT_PROPERTY (report_id, report_destype, CACHE);                       

   SET_REPORT_OBJECT_PROPERTY (report_id, report_desformat, report_format);               

   SET_REPORT_OBJECT_PROPERTY (report_id, report_other, 'paramform=no '||report_otherparam);

                                                         

   report_message := RUN_REPORT_OBJECT (report_id);

   rep_status := REPORT_OBJECT_STATUS (report_message);

   IF rep_status = 'FINISHED' THEN

     

      vjob_id := SUBSTR (report_message, LENGTH (report_server_name)+2, LENGTH(report_message));

           

      web.show_document (reports_servlet||'/getjobid'||vjob_id||'?server='||report_server_name, '_blank');

                                                                                           

   ELSE

message ('Report failed with error message '||rep_status);

   END IF;

  

END;

And create a object in form

Named ‘report’

After this write this code on button where you have to call report

:global.reportserver:='rep_appsrv1_FRHome1'; /* your report server name */

declare

report_id report_object := find_report_object('report');

v_report_other varchar2(4000);

reports_servlet VARCHAR2(1000) := '/reports/rwservlet';

begin

v_report_other := 'p_deptno='||:control.deptno;  /* ur parameters * /

run_report_object_proc(report_id, :global.reportserver, 'pdf', 'urreport.rdf', v_report_other,reports_servlet);

end;

How To Convert Base64 in Java

//Default Package Name 

//Import Class

import java.util.Base64;

public class decodeBase64

{

  public static void main(String[] paramArrayOfString) {

//Get First Parameter Value only     

String str1 = paramArrayOfString[0];

   //Get Byte of Base64

    byte[] arrayOfByte = Base64.getDecoder().decode(str1);

    String str2 = new String(arrayOfByte);

   

    System.out.println(str1 + " = " + str2);

  }

}