Oracle PLSQL: Save Image in directory/folder using

In PL/SQL you can use this procedure to save image in directory:

CREATE OR REPLACE PROCEDURE SaveFile(
   FileContent IN OUT NOCOPY BLOB
   , FolderName IN VARCHAR2
   , FileName IN VARCHAR2)
IS

    BUFFER RAW(1024);
    OFFSET PLS_INTEGER := 1;
    FileLength PLS_INTEGER;
    amount PLS_INTEGER := 1024;
    fhandle UTL_FILE.FILE_TYPE;

BEGIN

    FileLength := DBMS_LOB.GETLENGTH(FileContent);
    fhandle := UTL_FILE.FOPEN(FolderName, FileName, 'wb');          
    LOOP
        EXIT WHEN OFFSET > FileLength;
        DBMS_LOB.READ(FileContent, amount, OFFSET, BUFFER);
        UTL_FILE.PUT_RAW(fhandle, BUFFER, TRUE);
        OFFSET := OFFSET + amount;
    END LOOP;
    UTL_FILE.FCLOSE (fhandle);

EXCEPTION 
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(fhandle) THEN
            UTL_FILE.FCLOSE(fhandle);
        END IF;
    RAISE;

END SaveFile;

Oracle Forms: How to show long label on push button as wrapped content

Label on the push button is "How to fit long label". But as you can see it does not fit.
How to make it wrap content somehow displayed in 2 rows:

Solution:

#Create a button

#Create a "WHEN-NEW-FORM-INSTANCE" or "PRE-FORM" trigger with following code:

#Compile and run the form

Oracle 12c new feature using WITH Clause

With Clause improvement:

In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement and use it as an ordinary function. Using this construct results in better performance as compared with schema level functions.

Example:

SQL> WITH
FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
    BEGIN     RETURN n+1;    END;
SELECT f_test(1)
FROM dual;

Oracle 12c Forms / Reports Post Installation Configuration Steps.

How To Configure Oracle Forms And Reports Application.

After Installation of Oracle 12c Forms And Reports.
We will configure the application.
Follow below steps to configure and run the 12c application.

---------------------FOR FORMS------------

Step-1) Change in file Default.env  (default environment file)

Location of this file in the server will be:-

E:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.\config

                                             In Linux, it will be

/u01/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain/config/fmwconfig
/servers/WLS_FORMS/applications/formsapp_12.2.1/config

1-a) In this file Enter the Location for.FMX/.PLX/
                                                            image-default.env

Step-2) Change in file Formsweb.cfg (formsweb configuration file)

Location for Formsweb.cfg

E:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig

\servers\WLS_FORMS\applications\formsapp_12.2.1\config

                                             In Linux, it will be

/u01/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain/config/fmwconfig

/servers/WLS_FORMS/applications/formsapp_12.2.1/config

2-a) In this file create the named configurations (sets of parameters) in the last of the file.
                                             image-Formsweb.cfg
                                   

2-b) In this file change the name of a first form, database connection string as required.
        You can change the set of the parameter as required like
        width, Hight, colour scheme etc.

Step 3) After doing the above changes Restart the WLS_FORMS by a console.
            to open console use url
            http://server_ip:7001/console
       
                                 

Then after Shutdown click on Start and Start the WLS_FORMS.
Then run the forms using URL
http://server_ip:7777/forms/frmservlet?config=named configuration
i.e.
http://172.31.43.17:7777/forms/frmservlet?config=jois

It will download a jnlp and then run that jnlp using java web start and see the impact on the application.
                                                  image-application
                                         

-------------------FOR REPORTS-------------------------------

Step 4) we know character mode reports are not compatible with the standard report calling methods of oracle like using Rp2rro we cannot run these reports
but for normal reports, we can use this normal means default mode reports.


After configuring the Rp2rro we have to change on some files of Oracle server to run these reports.
the configuration of rp2rro I will show this you on another blog.

change in reserve.conf file (report server configuration file)
Location for this file:-

E:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration
                                                  in Linux it will be
/u01/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_12.2.1/configuration

4-a) Enter the directory of your reports in this file.
                       

4-b) Restart The WLS_REPORTS by a console.
   

Then run the application and run the reports.

This is the basic configuration for running forms and reports on the application.
Rest all the configurations for application will be published in future.

Oracle Forms: Check / Create / Read File using text_IO

--Create File
Declare
  l_Out_File Text_Io.File_Type;
  l_In_File  Text_Io.File_Type;
  Linelog    Varchar2(32767);
Begin
  l_Out_File := Text_Io.Fopen('Text.txt', 'a'); -- w is write, r is read, a is append
  Text_Io.Put_Line(l_Out_File,
                   To_Char(Sysdate, 'dd-mon-rrrr hh24miss');
  Text_Io.Fclose(l_Out_File);
Exception
  When Others Then
    If Sqlcode = -302000 Then
      -- file problems
      Message('SystemError: File not found.');
      Message('SystemError: File not found.');
      Raise Form_Trigger_Failure;
    Else
      -- other problems
      Message('LogicError: ' || Sqlerrm);
      Message('LogicError: ' || Sqlerrm);
      Raise Form_Trigger_Failure;
    End If;
End;


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


--Read File
Declare
  l_In_File Text_Io.File_Type;
  Linelog   Varchar2(32767);
Begin
  l_In_File := Text_Io.Fopen('Text.txt', 'r'); -- w is write, r is read, a is append
  Begin
    Loop
      Text_Io.Get_Line(l_In_File, Linelog);
    End Loop;
  Exception
    When Others Then
      Text_Io.Fclose(l_In_File);
  End;
  Message(Linelog);
  Message(Linelog);
Exception
  When Others Then
    If Sqlcode = -302000 Then
      -- file problems
      Message('SystemError: File not found.');
      Message('SystemError: File not found.');
      Raise Form_Trigger_Failure;
    Else
      -- other problems
      Message('LogicError: ' || Sqlerrm);
      Message('LogicError: ' || Sqlerrm);
      Raise Form_Trigger_Failure;
    End If;

End;

Oracle 12c Middle-ware services execution sequence.

Services in >> \Middleware\Oracle_Home\user_projects\domains\base_domain\bin
1) startNodeManager.cmd
2) startWeblogic.cmd
3) startManagedWebLogic.cmd WLS_FORMS

4) Launch Forms Builder Middleware\Oracle_Home\bin\frmbld.exe

Service in >> \Middleware\Oracle_Home\user_projects\domains\base_domain\bin
5) startManagedWebLogic.cmd WLS_REPORTS

6) Launch Reports Builder Middleware\Oracle_Home\user_projects\domains\base_domain\reports\bin\rwbuilder.bat

Oracle Forms: Dynamic Change in mode of buttons according to certain events

Example


Create Block Named as BLK_BUTTONS
Create following Buttons in BLK_BUTTONS
PB_ENTER_QUERY
PB_EXECUTE_QUERY 
PB_CANCEL_QUERY
PB_SAVE
PB_DELETE
PB_ADD
PB_CLEAR
PB_SCROLL_UP
PB_SCROLL_DOWN 
PB_NEXT_RECORD 
PB_PREVIOUS_RECORD 
PB_PREVIOUS_SCREEN 
PB_NEXT_SCREEN 
PB_EXIT



Form-Level:
UserDefined-Trigger:
  if :system.mode in ('ENTER-QUERY') then
  disable all buttons except execute query, cancel query and exit form
  else
  enable all buttons except cancel query

  if :system.trigger_record = '1' then
  disable previous record, first record 
  enable next record, last record

  if :system.last_record = 'TRUE' then
  disable next record, last record
  enable previous record, first record

WHEN-NEW-RECORD-INSTANCE
  execute_trigger('UserDefined-Trigger');
  
  
-----------------------------------------------------------------------------------------
-- run toolref button commands
  if upper(substr(:system.trigger_item,1,9)) = 'BLK_BUTTONS' then
    if :system.trigger_item = 'BLK_BUTTONS.PB_ENTER_QUERY' then
      do_key('enter_query');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_EXECUTE_QUERY' then
      do_key('execute_query');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_CANCEL_QUERY' then
      do_key('exit_form');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_SAVE' then
      do_key('commit_form');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_DELETE' then
      do_key('delete_record');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_ADD' then
      do_key('create_record');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_CLEAR' then
      do_key('clear_form');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_SCROLL_UP' then
      do_key('scroll_up');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_SCROLL_DOWN' then
      do_key('scroll_down');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_NEXT_RECORD' then
      do_key('down');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_PREVIOUS_RECORD' then
      do_key('up');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_PREVIOUS_SCREEN' then
      do_key('previous_block');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_NEXT_SCREEN' then
      do_key('next_block');
    elsif :system.trigger_item = 'BLK_BUTTONS.PB_EXIT' then
      do_key('exit_form');
    end if;

  end if;  -- BLK_BUTTONS commands