OAF Download Blob File Table Column

OAF Download Blob File Table Column

OAF Download Blob Table Column
1)Cretae custom table to store blob column.

CREATE TABLE xx_file_blobs
(
 file_id INTEGER
,Attached_file_Name VARCHAR2(50)
,Attached_file BLOB
,ContentType  VARCHAR2(50)
,creation_date DATE
,created_by    NUMBER
,last_update_date DATE
,last_updated_by INTEGER
,last_update_login INTEGER
) ;



2)In JDeveloper Create EO,VO and AM based on above table.

3)Create New Page .   Add AM to the page.
4) Create new region inside page with region style   advancedTable
5) Define View instance  XxFileBlobsVO1/<YourTableVO1>  on advancedTable
6) Create Column inside advTable for FileId.    Add sortable header and specify Prompt File Id for it.
    Add item to added Column.  Update View Attribute as FileId for item.

7) Create Column inside advTable for AttachedFile.    Add sortable header and specify Prompt AttachedFile  for it.
Add item to added Column. Assign
Id                            eg. AttachedFile
Item Style                      messageDownload
File MIME Type            Contenttype          //View Attribute which is picked from table column                        
Data Type                      BLOB
View Attribute as           AttachedFileName      // This will be name on Hyperlink.
File View Attribute         AttachedFile
Prompt                          File

Page xml For reference

<?xml version = '1.0' encoding = 'UTF-8'?>
<page xmlns:jrad="http://xmlns.oracle.com/jrad" xmlns:oa="http://xmlns.oracle.com/oa" xmlns:ui="http://xmlns.oracle.com/uix/ui" version="10.1.3_1312" xml:lang="en-US" xmlns:user="http://xmlns.oracle.com/jrad/user" xmlns="http://xmlns.oracle.com/jrad" file-version="$Header$">
   <content>
      <oa:pageLayout id="PageLayoutRN" windowTitle="Blob Attachment" title="Attachment Download" amDefName="xx.oracle.apps.ap.blobhandle.server.AppModule" controllerClass="xx.oracle.apps.ap.blobhandle.webui.AttachmentCO">
         <ui:corporateBranding>
            <oa:image id="corporateBrandingImage" source="/OA_MEDIA/FNDSSCORP.gif"/>
         </ui:corporateBranding>
         <ui:contents>
            <oa:stackLayout id="StackRN">
               <ui:contents>
                  <oa:advancedTable id="AdvTblRN" viewName="XxFileBlobsVO1">
                     <ui:contents>
                        <oa:column id="FileIdCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader1" prompt="File Id"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="FileId" viewAttr="FileId" prompt="File Id"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="AttachedFileNameCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader2" prompt="Attached File Name"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="AttachedFileName" viewAttr="AttachedFileName"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="AttachedFileCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader3" prompt="File"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageDownload id="AttachedFile" viewAttr="AttachedFileName" fileContentType="Contenttype" prompt="Attached File" dataType="BLOB" contentViewAttributeName="AttachedFile"/>
                           </ui:contents>
                        </oa:column>
                     </ui:contents>
                  </oa:advancedTable>
                  <oa:submitButton id="UploadAttachBtn" text="Upload Attachment" prompt="Upload Attachment"/>
               </ui:contents>
            </oa:stackLayout>
         </ui:contents>
      </oa:pageLayout>
   </content>
</page>


Note :-- Upload Attachment button navigates to Upload Page. (For Inofrmation only)

For more information
Page 350 Of OA Framework Developer Guide

Declarative Implementation

Perform the following steps to implement the File Download feature declaratively in an OA Extension page.

Step 1: Create a region in your page layout region, with the

Form property set to true for the page layout.

Step 2: In the new region, create an item of item style

messageDownload.

Note:

If you implement a messageDownload item in a table or advanced table region, the view object used to

render the table or advanced table must have a designated primary key, otherwise the messageDownload web

bean will repeatedly download content from the first row.

Step 3: In the OA Extension Property Inspector, set the following properties for the messageDownload item:

􀁹

View Instance - The view object instance of the underlying data source.

􀁹

View Attribute - The view attribute that maps to a column in the underlying data source.

􀁹

File View Attribute - The view attribute that maps to the column that stores the file content.

􀁹

File Name Override - The file name to save to when you select the File Download link and choose the

Save option in the File Download window to save the file. The default file name that appears in the File

352

Name field of the Save As dialog window is derived from the value returned from the view attribute

specified by the View Attribute property. The value of the File Name Override property overrides that

default file name and is especially useful if the view attribute returns instructional text, such as "Click on

this link to download the file". If the File Name Override property is not defined, then the file name to

save to is the value returned from the view attribute.

􀁹

File MIME Type - The MIME type of the file. See the Runtime Control example below if you do not want

to specify a static value for this property.

􀁹

Data Type - The data type of the File View Attribute. The BLOB datatype is supported for File

Download.

􀁹 Prompt - The text prompt that proceeds the File Download link.
   

n_to_nth convert number to char plsql

CREATE OR REPLACE PACKAGE n_to_nth

IS

   FUNCTION n_to_nth (n_in IN NUMBER)

      RETURN VARCHAR2;

   /* Alias for n_to_nth */

   FUNCTION nth_from_n (n_in IN NUMBER)

      RETURN VARCHAR2;

   /* Convert day of month to word */

   FUNCTION dd_to_nth (dd_in IN NUMBER)

      RETURN VARCHAR2;

END;

/*****************************************************************************************/

CREATE OR REPLACE PACKAGE BODY n_to_nth

IS

   /* Utilize date functions to conver "1" to "1st" and first" etc. */

   FUNCTION n_to_nth (n_in IN NUMBER)

      RETURN VARCHAR2

   IS

   BEGIN

      RETURN CASE

                WHEN n_in IS NULL

                THEN

                   NULL

                WHEN n_in = 0

                THEN

                   NULL

                ELSE

                   LOWER (

                      TO_CHAR (TO_DATE ('1-1-' || n_in, 'dd-mm-yyyy'),

                               'FMYYYYth'))

             END;

   END;

   FUNCTION nth_from_n (n_in IN NUMBER)

      RETURN VARCHAR2

   IS

   BEGIN

      RETURN n_to_nth (n_in);

   END;

  

   FUNCTION dd_to_nth (dd_in IN NUMBER)

      RETURN VARCHAR2

   IS

   BEGIN

      RETURN CASE

                WHEN dd_in IS NULL

                THEN

                   NULL

                ELSE

                   LOWER (

                      TO_CHAR (

                         TO_DATE ('2010-01-' || LPAD (dd_in, 2, '0'),

                                  'YYYY-MM-DD'),

                         'DDSPth'))

             END;

   END;

END;

/*******************************************************************************/

BEGIN

   DBMS_OUTPUT.put_line (n_to_nth.n_to_nth (1));

   DBMS_OUTPUT.put_line (n_to_nth.n_to_nth (3));

   DBMS_OUTPUT.put_line (n_to_nth.n_to_nth (100));

   DBMS_OUTPUT.put_line (n_to_nth.dd_to_nth (1));

   DBMS_OUTPUT.put_line (n_to_nth.dd_to_nth (3));

   DBMS_OUTPUT.put_line (n_to_nth.dd_to_nth (27));

   DBMS_OUTPUT.put_line (INITCAP (n_to_nth.dd_to_nth (27)));

END;

Example of Oracle Pipelined Function

Create Type Loop_Type is table of Varchar2(400)


Create or Replace Function  Find_Loop_F (P_End_loop in NUmber Default 10) Return Loop_Type PIPELINED
Is
    begin
        For  op in  1..P_End_loop loop
            Pipe row  (op);
            Pipe row  ('Value' );
        end loop;
        --Return;
end;


select * from (Table (Find_Loop_F(200)))

Send Email from PLSQL

DECLARE
    v_from_name         VARCHAR2(100) := 'admin@falickfaisal.com';
    v_to_name           VARCHAR2(100) := 'falickfaisal@gmail.com';

    v_subject           VARCHAR2(100) := 'This is an awesome mail';
    v_message_body      VARCHAR2(100) := 'Hey you!! Bla Bla Bluu';
    v_message_type      VARCHAR2(100) := 'text/plain';

    v_smtp_server       VARCHAR2(200)  := 'smtpout.asia.secureserver.net';
    n_smtp_server_port  NUMBER        := 25;
    conn                utl_smtp.connection;

    TYPE attach_info IS RECORD (
        attach_name     VARCHAR2(40),
        data_type       VARCHAR2(40) DEFAULT 'text/plain',
        attach_content  CLOB DEFAULT ''
    );
    TYPE array_attachments IS TABLE OF attach_info;
    attachments array_attachments := array_attachments();

    n_offset            NUMBER;
    n_amount            NUMBER        := 1900;
    v_crlf              VARCHAR2(5)   := CHR(13) || CHR(10);
    base64username VARCHAR2(40) := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('admin@falickfaisal.com'))); 
base64password  VARCHAR2(40):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('123456789')));



BEGIN

  -- Fill data for example
    attachments.extend(5);
    FOR i IN 1..5
    LOOP
        SELECT 'test' || to_char(i) || '.txt','text/plain','test' || to_char(i)
        INTO attachments(i)
        FROM dual;
    END LOOP;



  -- Open the SMTP connection ...
    conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);
    utl_smtp.helo(conn, v_smtp_server);
    UTL_SMTP.command(conn, 'AUTH', 'LOGIN');
    UTL_SMTP.command(conn, base64username );
    UTL_SMTP.command(conn, base64password );

    utl_smtp.mail(conn, v_from_name);
    utl_smtp.rcpt(conn, v_to_name);

  -- Open data
    utl_smtp.open_data(conn);

  -- Message info
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_to_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="SECBOUND"' || v_crlf || v_crlf));

  -- Message body
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || v_crlf || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || v_crlf));

  -- Attachment Part
    FOR i IN attachments.FIRST .. attachments.LAST
    LOOP
    -- Attach info
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type
                            || ' name="'|| attachments(i).attach_name || '"' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
                            || attachments(i).attach_name || '"' || v_crlf || v_crlf));

    -- Attach body
        n_offset := 1;
        WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)
        LOOP
            utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));
            n_offset := n_offset + n_amount;
        END LOOP;
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || v_crlf));
    END LOOP;

  -- Last boundry
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND--' || v_crlf));

  -- Close data
    utl_smtp.close_data(conn);
    utl_smtp.quit(conn);

END;

WorkFlow : ORA-04061: existing state of has been invalidated ORA-04061: existing state of package

RA-04061: existing state of has been invalidated ORA-04061: existing state of package

Compile Package for Error  ORA-04061 in WorkFlow

alter package      Wf_Engine_Util  compile;
alter package      wf_engine       compile;
alter package      wf_notification compile;
alter package      wf_standard     compile;

Print Function for PLSQL Testing

For tracing PLSQL code , Print function  show output in DBMS Output as per Arguments.

1) For implementation of  this function simply add G_Debug_Enabled Variable and Function Specification in your package Specification. For enable tracing set G_Debug_Enabled as True before execute your code.

  G_Debug_Enabled Boolean default false;
    procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                );

procedure test_Print_function;

2) Add Function Body in Package Specification Body

procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                )

    is

    begin

            if G_Debug_Enabled then

               Dbms_output.put_line('$ '||pString||' : '||pText_1||'  => '||pValue1||' '||pText_2||' => '||pValue2||' '||pText_3||' => '||pValue3||' '||pText_4||' => '||pValue4||' '||pText_5||' => '||pValue5||' '||pText_6||' => '||pValue6||' '||pText_7||' => '||pValue7||' '||pText_8||' => '||pValue8||' '||pText_9||' => '||pValue9);

              end if;

      end;

procedure test_Print_function

  is

  begin

          print('Function Test','TransactionID',10,'EmployeeName','mFaisal');

    end;

3) Now test test_print_function

begin

  -- Call the procedure

  PKG_wf_api.G_Debug_Enabled:=true;

  PKG_wf_api.test_print_function;

end;

Output: 

$ Function Test : TransactionID  => 10 EmployeeName => mFaisal