APEX : Send email with attachment (Part 2)

Please refer to my previous post related to this, here.

Moving forward, by now you should already completed doing these 2 things:

  1. User can insert any attachment into the form
  2. The attachment will be save into my own table within the Oracle database.

So now, the next step is to configure APEX to sent-out email together with the attachment.

The email process must be create at point [Processing > Processes] on [Page Processing] , right after the ‘CopyFile’ process (refer to my previous post)

  1. Right click on [Processes] > Create
  2. Select [PL/SQL] and Next
  3. Give a name to identify the procesess and click Next
  4. On the [Enter PL/SQL Page Process], paste below code;

DECLARE
    l_id number;
BEGIN
    l_id := APEX_MAIL.SEND( p_to        => ‘fred@flintstone.com’,
                            p_from      => ‘barney@rubble.com’,
                            p_subj      => ‘APEX_MAIL with attachment’,
                            p_body      => ‘Please review the attachment.’,
                            p_body_html => ‘<b>Please</b> review the attachment’ );
    FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM attachment_files
                WHERE ID = :P1_YOUR_ID ) loop
    APEX_MAIL.ADD_ATTACHMENT( p_mail_id    => l_id,
                                  p_attachment => c1.blob_content,
                                  p_filename   => c1.filename,
                                  p_mime_type  => c1.mime_type);
    END LOOP;
END;
BEGIN
wwv_flow_mail.push_queue(
   P_SMTP_HOSTNAME => ‘localhost’,
   P_SMTP_PORTNO => ’25’
);
END;

Please see the code and change whenever suit with your table name and page item. For example code below:

FOR c1 IN (SELECT filename, blob_content, mime_type
                 FROM attachment_files
                WHERE ID = :P1_YOUR_ID ) loop

The [attachment_files] comes from my table which I have created previously in so called Part 1.
You can have multiple ‘File Browse…’. You may also need to change the PL/SQL code if have more [File Browse]. Do comment below if you still need help.
I will try my best to help. 🙂

APEX : Send email with attachment

I have found a working solution for sending email with attachment using APEX application. But first, few items have to take note :-

  1. User can insert any attachment into the form
  2. The attachment will be save into my own table within the Oracle database.
User can insert any attachment
This will be require an item named ‘File Browse…’. Simply add the item into the page. I assume at this point, you already know how to it. Lets name it as [P1_ATTACHMENT]
Take a look at the [Settings] tab when u edit this item page. The storage type setting can be either two : 1.Table WWV_FLOW_FILES and 2.BLOB column specified in Item Source attribute
I will choose option No.1. Why I did not choose option No.2? As I had mentioned that the attachment will be save into my own table?
I am not expert, perhaps you can correct me if I am wrong, but I think that insert a attachment files into table will require some additional coding when we want to execute it in APEX environment.
One more reason is, I already has define one table to fetch row in the [Processes] in [After Header] section. So by adding another one more table (attachment table) it will make APEX get confused. Anyway, I have tried it. It does not work.
The attachment will be save into my own table within the Oracle database.
Next step is to save the file attachment to be save in my own attachment table. Lets name my table as ATTACHMENT_FILES. 

CREATE TABLE ATTACHMENT_FILES
(
  ID                 NUMBER,
  BLOB_CONTENT       BLOB,
  MIMETYPE           VARCHAR2(255 BYTE),
  FILENAME           VARCHAR2(255 BYTE),
  LAST_UPDATE        DATE,
  CHARSET            VARCHAR2(128 BYTE),
  UPLOADED_BY        VARCHAR2(10 BYTE),
  DOC_SIZE           NUMBER
)

Since I already have my table ready, I will now need to create one process to copy the file from WWV_FLOW_FILES into ATTACHMENT_FILES.
This process must be create at point [Processing > Processes] on [Page Processing]
  1. Right click on [Processes] > Create
  2. Select [PL/SQL] and Next
  3. Give a name to identify the procesess and click Next (lets name it as ‘CopyFile’)
  4. On the [Enter PL/SQL Page Process], paste below code;

begin
insert into ATTACHMENT_FILES( id, blob_content, mimetype, filename, last_update, doc_size )
select :P1_YOUR_ID, blob_content, mime_type, filename, created_on, doc_size from  wwv_flow_files where  name = :P1_ATTACHMENT;
delete from wwv_flow_files where name = :P1_ATTACHMENT;
end;

So by now, you got the idea already right? First, we let the attachment to be saved into WWV_FLOW_FILES. Then we copied it over to our own table and delete the original data from WWV_FLOW_FILES to avoid consume space and storage.

To avoid any complex coding in PL/SQL, I believe this method is faster. It depends on your needs.

Hurmm..I will talk about sending email as Part 2 later on.

Update : This is the Part 2.

APEX : ORA-01008: not all variables bound

It is really frustrating if everything run not as per plan right?..:-)

Today I encounter error ORA-01008: not all variables bound, when executing a report from a SQL script. At first, I suspected because of a LEFT JOIN syntax problem.
What Oracle say about ORA-01008?
ORA-01008: not all variables bound
Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.
Action: In OCI, use an OBIND or OBINDN call to substitute the required values.
I not really understand it much. But below solution works well for me on my APEX application. And the report generation have no problem any more.
Initially, my SQL code was as below:

where employee = :P1_EMPLOYEE

Solution: 

where employee = v(‘P1_EMPLOYEE’)

Have a good day!

Error on APEX : ORA-20001: Unable to bind..

ORA-20001: Unable to bind :P1005_YOUR_PAGE_ITEM_NAME verify length of item is 30 bytes or less. Use v() syntax to reference items longer than 30 bytes. ORA-01006: bind variable does not exist


I encountered an error as above when testing my application on APEX. So, normally I will googling around to find the solution. But unfortunately, I do not found a solid solution to my problem, particularly on the above error.

So I try to solve the error myself. After took couple of hours play around with it, finally it is resolved.


Important thing to take note are:

  • This error is not come from the database. It is from application itself.
  • No need to check your database table and re-define your table structure. Such as change to data length to 30 bytes etc.


Solution:

The problem is due to the naming of the page item. So in APEX, it must not more than 30 bytes. So, I rename my page item to something more lesser in character. It works for me. I hope it is work for you too.


Change your character of P1005_YOUR_PAGE_ITEM_NAME to something lesser!




APEX – Grab value from Shuttle and put it into Textarea with carriage return

In some design, you may require to grab the value from Shuttle and put those value into a new Textarea or even in email HTML template. How am I going to do that?

What you need is a new Textarea so that the values you about to grab will be put inside. Lets put the Shuttle as P1001_X and assume the new Textarea as P1001_TXTAREA.

One solution can be used is a Computations (After Submit). You need to right click the Computations > Create > Item on This Page. Fill in those required value in that form and select [Item Value] in the Computation Type. In Computations field, put below code:

:P1001_TXTAREA := REPLACE( :P1001_X , ‘:’, (chr(13)||chr(10)) );

What above code will do is it will grab each value from Shuttle, together with new line feed and carriage return.

Information:
chr(13) : Carriage Return
chr(10) : New Line Fees

APEX – How to grab value from Shuttle and save it into database

So you are using Oracle APEX shuttle in your form and will give the ability to user to select multiple value from the list. The next challenge is to store the selected values into the Oracle database.

oracle apex shuttle

Oracle APEX Shuttle : How to do it?

In this post, I will talk about storing the multiple values into Oracle database in a way of storing the value one by one. Meaning that, if there are 4 active selection, the operation will be storing or insert the 4 value into Oracle table each row. So there will be 4 insert operation will be executed.

Please check below code:

declare
    tab apex_application_global.vc_arr2;
begin
    tab := apex_util.string_to_table (:PX_YOUR_ITEM);
    for i in 1..tab.count loop
        insert into YOUR_TABLE (YOUR_COLUMN_NAME, YOUR_SHUTTLE_COLUMN_NAME)
        values
(‘My List’, tab(i) );
    end loop;
end;

Please note that :PX_YOUR_ITEM is referring to your Shuttle Item name.

Where should I put those code?

1.Page Processing > Processing > Process, right click > Create

2.Select PL/SQL and you may procees the rest

3.When you see ‘Enter PL/SQL Page Process’, paste the code here. Please change accordingly follow your item page name and table/column in your database.

Part II : Create a page with multiple forms linked to multiple tables

Previously I has written about creating a page with multiple forms linked to multiple database. You can read it here. But, what I am going to post now is about trigger upon insert action, and how to pass the value into another page item.

Simple example is like this:

You are created 2 separate form in one page in APEX environment. One Submit button operation, all the data will be insert accordingly into their respective table, let say Table1 and Table2. Table1 has an auto generate ID by trigger on insert operation. And this Table1.ID was require to be pass to Table2. This scenario can be said as one to many relational table inside database.

I have tried to use the DML operation to pass the primary key into another page item unfortunately has failed. The workaround of it was to use ‘RETURNING’ clause in PL/SQL.

Check-out SQL below:

insert into Table1 ( column1, column2, column3) values
( :P1_Data1, :P1_Data2, :P1_Data3)
returning YourPrimaryKeyColumnName into :P1_YourTargetPageItem;

I took a lot of time searching how to do it. If you need more details on this, just let me know here.

APEX – Create a page with multiple forms linked to multiple tables and 1 Submit Page Button for INSERT operation

I am having a problem to update into Oracle table. To be exact, I have created 2 separate form into 1 page. And those forms are actually binded with different table as well. My operation or design will be, when I click a ‘Submit’ button, all the data entered by user will be inserted into their respective table. Well, I got an error. What has been wrong?

Oracle APEX limitation?

After ‘googling’ (I wonder if this word has in dictionary..) around, I found that this is a known limitation for APEX. Well, obviously Oracle need to check further for this. You can only have one automated row fetch (DML) process per page. However, it does not means that we cannot add other form on the page. There are few a solution for that which works well for me.

Create Separate Region for separate Form

1. On Page Rendering, locate Region > Body and right click. Click ‘Create’ to create new region. This one will be your 1st Form.
2. Create Region, select ‘Form’ and press ‘Next’
3. Create Form, select ‘Table’ and press ‘Next’. Please just proceed until the wizard complete. The 1st Form will be straight forward.

The Important on Next Sequence Form

You may now see the new region successfully created from the wizard. The Form will populated text field or other field as per your selection.

APEX-Page Definition

Now, the next step we need to do will bit different.

4. Repeat same step 1 and 2, but for 3 select ‘SQL Query (fetch row) instead of ‘Table’

image

5. Press ‘Next’ until you found ‘SQL SELECT Statement’ page tab. Here will be SQL to populate the page item into the page. You can type it manually, or click [Query Builder] link.

6. The new page items will be populated according to your SQL. You can see the Form already created for you.

Setting the INSERT Operation

You may notice that there are few button automatically created inside the page. For me, I do not want it so many, so I created a new region for only 1 button. This button will do action INSERT for those 2 form above for 2 different table. Just make sure that the button action is ‘Submit Page’.

image

7. Since the 1st Form using Automatic Row Processing(DML), I just need to set the INSERT operation for Table #2 in Form #2. Go to Page Processing > Processes and right click on it.

image

8. Select PL/SQL and Next. Give your process a name and Next.

9. When reach at [*Enter PL/SQL Page Prosess] , enter you INSERT SQL statement. Remember to use page items in this SQL. For Example:

insert into Table2 (column1, column2, column3) values ( : P1_Item1, : P1_Item2, : P1_Item3);

Hopefully above steps works for you. If you got comment, leave your comment below.

Obtain machine IP Address using Oracle SQL command

I never know this function has exist until I was required to explore Oracle APEX to design a website. Here is the code, which can get an IP address of machine and the value can be store into a database or pass it into other variable for processing, or whatever your programming do.

select sys_context(‘userenv’,’ip_address’)  from dual

Have fun.

Oracle Application Express (APEX) and ASP.NET

I am digging around of the benefit using Oracle Application Express (APEX) compare to ASP.NET in building my website for current job needs. Because I am getting the direction from superior to study and start using APEX instead of ASP.NET for my web development.

At first, when I look into Oracle website about APEX, I can imagine how easy my work will be. I mean the website will be easy to create, offer easy connectivity to Oracle database but still, I will need to connect my website to MSSQL server to store data input by user.

Anyway,let look at what other people has say about Oracle Application Express (APEX) versus ASP.NET (Visual Studio).

APEX is a framework that uses the database and PL/SQL to produce web pages. If you can figure out what the output to the browser will need to be you can create it in APEX. If you find any part of the framework inhibiting you can write PL/SQL procedures and expose them to the web server directly but still take advantage of the security, logging, session state, etc that the APEX system manages for you.

You should know PL/SQL, SQL, HTML, JavaScript and CSS. Sure the interface looks like a big data entry application but the data you enter will mostly be code snippets in each of these languages.

It scales as well as the database does. It typically uses Apache as a web server but is only used to serve static files and pass requests back to the database, where the web pages are created by the PL/SQL code in the APEX schema. You can use AJAX to minimize the size of the traffic traveling up and down the pipe. You can set caching for specific items, lists, page regions, pages, etc. 

Since most things are pretty simple to do with the framework, naturally there will be some things that are a little more complicated to do within the framework. The color coding example given above might be something you do with CSS or maybe you would need to turn to print statements to produce the output you need. The thing is to learn the how the framework makes life easier and then when you hit a limit you can easily resort to more direct methods.

Coming from VB.Net you will miss the step by step debugging and the drag and drop. You will never miss the fact that some part of the page lifecycle will do a bind and reset the values you bound to an object in another part of the page.

This fella have a huge migration project. What has he say about APEX?

I am involved in a huge project to migrate a 5000 module Oracle Forms application to Apex. This is an extreme use of Apex, but it’s working just fine. It is a complete myth that Apex is suitable only for small internal apps built by DBAs, interns or end users: it is certainly suitable for those too (and more suitable than most other tools), but it can also be used to build extremely sophisticated applications. 

To build a sophisticated application (rather than a default out-of-the box Apex one) you will need someone on the team with Javascript skills, and someone with CSS skills. But most developers will just need PL/SQL initially. 

Is it scalable? Yes: probably more scalable than most other solutions! Apex adds very little overhead to the database server, and only the most minimal of application servers is required. “Facebook size”? I don’t know for sure but I don’t see why not, assuming you have an Oracle database on a machine large and powerful enough to handle “Facebook size” data and transaction volumes. Like any Oracle project, scalability is impeded mostly by bad database designs and poorly written SQL, not by the tool.

The good side of APEX has been discussed too.

The Good

  • Incredibly easy to generate a respectable web app with basic CRUD data entry, simple reporting and populate it with data. If you’re the IT guy who’s been tasked with consolidating a company’s mess of Excel/Access dbs into a central DB/web environment then you should take a look at APEX, it very well suited for this task. If you expect the scope to grow to something of even moderate complexity then I would move straight to a more flexible framework.
  • If you are a DBA/PLSQL guru but have no experience with traditional web development you’ll be well prepped to expose existing business logic in a web app without stuffing around with HTML/CSS/JavaScript if you dont want to.
  • APEX support forum has a ton of info and is well staffed by APEX devs.

Take a look into the bad site of APEX compare to ASP.NET.

The Bad

  • My experience with Apex began to go downhill when apps moved beyond CRUD data entry and required more dynamic and event driven behaviours.
  • The web based GUI is not cool. Debugging is painful.
  • When you (inevitably) need to do anything outside the limited scope of the framework, you’ll have to get your hands dirty with PL/SQL. Writing business logic against the database is fine, but generating HTML from PL/SQL procedures felt uncomfortably archaic in 2007.
  • Given the large number of sneaky places you can hide page and redirection logic, the program flow is both difficult to visualise and not naturally conducive to modular, separable and reusable code. OOP developers will be not be impressed. It’s possible to have well structured maintainable applications with APEX but its harder than it should be. This is worlds away from MVC.
  • Unacceptable number of framework bugs in the versions I used. I’d hope this has improved with recent versions, but the paradigm of integrating the IDE into the APEX platform itself caused me some of the darkest, soul destroying debugging sessions of my life. As an example, I was trying to reproduce an intermittent bug that would cause a user to lose their session data. Using the session information popup I saw that occasionally the session data would change when it shouldnt have. I spent 2 days trying to find the error in my code with no luck. Near delirious, I noticed by pure chance that I could reproduce errorous session data in the debug window but the application itself wouldnt go into an error state. My heart sunk when I realised what might be happening. Oracle later confirmed that I’d found a bug in APEX that caused the session information window to intermittently show me data from a prior session. I’d wasted 2 days debugging a session related bug with a buggy session debug window. That was the last Apex app I built.
  • PL/SQL is not and will never be the Next Big Thing in web development. After working with APEX for a while I realised it wasnt going to make me a better web developer. Mastering APEX is really about PL/SQL. Thats fine if you plan to focus your career on Oracle technology, just be aware that APEX is so tangential to the direction of mainstream web technologies that the portable set of skills you can take from APEX to other web frameworks is minimal.