Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32

3782197Feb 19 2019 — edited Mar 13 2019

The base procedure mail_attach_binary(created below) to e-mail binary files

from a directory location on the database (/home/alert)

My Question is from here how can at

how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is  > 32000 bytes?

--SPEC

PROCEDURE mail_attach_binary

    (recipients VARCHAR2,

     cc VARCHAR2 DEFAULT NULL,

     subject VARCHAR2,

     message VARCHAR2 DEFAULT NULL,

     att_filename VARCHAR2 DEFAULT NULL,

     att_file_loc  VARCHAR2);    

END SPP_EMAIL;

--BODY

PROCEDURE mail_attach_binary

    (recipients     VARCHAR2,

     cc             VARCHAR2,

     subject        VARCHAR2,

     message        VARCHAR2,

     att_filename   VARCHAR2,

     att_file_loc   VARCHAR2) AS

--file attachment paramaters    

    v_bfile         BFILE;

    v_clob          CLOB;

    destOffset      INTEGER:=1;

    srcOffset       INTEGER := 1;

    lang_context INTEGER := DBMS_LOB.default_lang_ctx;

    warning INTEGER;

   

--    v_mime_type    VARCHAR2(30) := 'application/pdf';

  BEGIN

 

    setup_smtp_server;

--Get the file to attach to the e-mail

    v_bfile := BFILENAME (att_file_loc, att_filename);

    DBMS_LOB.OPEN (v_bfile);

    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);  

    DBMS_LOB.LOADCLOBFROMFILE(

        dest_lob => v_clob,

        src_bfile => v_bfile,

        amount => DBMS_LOB.GETLENGTH(v_bfile),

        dest_offset => destOffset,

        src_offset => srcOffset,

        bfile_csid => DBMS_LOB.default_csid,

        lang_context => lang_context,

        warning => warning);

    DBMS_LOB.CLOSE(v_bfile);    

   

    EXCEPTION WHEN

      INVALID_ARGUMENT THEN

      alert('EMAIL',1000,'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');

  END mail_attach_binary;

Comments

Scott Wesley
Answer

You may get something out of this reference

Grassroots Oracle: Tutorial: Include action button in report

Marked as Answer by Schuriik · Sep 27 2020
Pavan Badi

Hi,

You can use html directly into to sql report query to get multiple buttons like below,

select

'<input type="button" value="Button 1">'||'<input type="button" value="Button 2">'||

from dual;

Tim Halbach

Hi Schuriik,

here is an example and a package of what you can use to render a button in a report as simple as possible.

https://thalbachdevelop.blogspot.de/2017/02/apex-rendering-button-in-report-column.html

The post from Scott was my ambition to render the button into the report.

You should also read Scott's post to understand the mechanics behind it.

If you have multiple buttons you can concatinate them into a sql query.

e.q.

Select

thdevelop_util_pkg.apex_item_button(p_item_label     => 'FIRST_BUTTON',

                              p_item_id     => 'first_button'

                             ) ||

thdevelop_util_pkg.apex_item_button(p_item_label     => 'SECOND_BUTTON',

                              p_item_id     => 'second_button'

                             );

from dual;

then you have to set on the coloumn security Escape special character to no.

Regards Tim

Schuriik

Hi Scott, one more question: I created 2 buttons in the SQL report with following code: "'<a data-id="#ID#" class="test t-Button" </a>Start' || ..."

Now I want to save the ID of the row in my item P1_TEST via javascript like shown in your example:  $s('P1_TEST', $(this.triggeringElement).data('id'));

But afterwards the value of P1_TEST is always "#ID#", no matter which row I click. Can you tell me how I can get the right ID?

When I create a button with link like shown in your example its working fine and I get the right ID of every row. But that way I cant create 2 buttons in 1 column.

Scott Wesley

#ID# must be the alias of a column in your report.

Check that

a) you have such a column

b) it's not conditionally hidden

1 - 5

Post Details

Added on Feb 19 2019
5 comments
4,196 views