Do you already own Nokia’s New 8810 Banana Phone?

Seriously, if I want to have second phone, Nokia 8810 Banana is one of my options.

Back in 1999, Nokia was one of the world’s leading phone suppliers. Nokia is bringing back one of its retro classics — the Nokia 8110. The model made its way onto The Matrix film, and later garnered a cult following for those who wanted a phone just like Neo’s.

The Nokia 8110 runs on the Smart Feature OS which won’t allow access to Android apps, but will include its own app store for the device offering Facebook and allowing the possibility of other third parties creating custom apps.

The 8110 will include LTE support across Europe with dual-SIM versions available. The phone has a 2.4 inch display and 2-megapixel camera at the rear. Sure to delight fans, owners will have access to a revamped Snake game and be able to boast about a 25-day battery standby.

What is Big Data? Is Big Data a product? You will find out in less than a minute..

No. It is not a product. It is not a software.

I just found out about Big Data couple a week ago during a company briefing. I heard about it so long time ago and sadly my view about Big Data is a product/software is totally untrue. So, what is Big Data?

Put simply, big data is larger, more complex data sets, especially from new data sources. These data sets are so voluminous that traditional data processing software just can’t manage them. But these massive volumes of data can be used to address business problems you wouldn’t have been able to tackle before. – Oracle

Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. But it’s not the amount of data that’s important. It’s what organizations do with the data that matters. Big data can be analyzed for insights that lead to better decisions and strategic business moves. – SAS

Big data is a term used to refer to the study and applications of data sets that are so big and complex that traditional data-processing application software are inadequate to deal with them. Big data challenges include capturing data, data storage, data analysis, search, sharing, transfer, visualization, querying, updating, information privacy and data source. There are a number of concepts associated with big data: originally there were 3 concepts volume, variety, velocity. Other concepts later attributed with big data are veracity (i.e., how much noise is in the data) and value. –Wikipedia

How to send email from Oracle database using procedure/PLSQL

It has been a long time I did not post to this blog. Happy New Year 2017 anyway!

Recently, I am working on something related to my work. I have to monitor Oracle job scheduler, their job status etc. One of the options came across my mind is to email me the job status everyday from Oracle database.

Below script is working on my machine, which running Oracle XE. If error accrued during execute the script, it may come from insufficient privileges on certain package. Please grant where necessary.

Here is the script:

DECLARE
V_FROM      VARCHAR2(80) := ‘YOUR_EMAIL@GMAIL.COM’;
V_RECIPIENT VARCHAR2(80) := ‘RECIPIENT_EMAIL@GMAIL.COM’;
V_SUBJECT   VARCHAR2(80) := ‘TEST EMAIL’;
V_MAIL_HOST VARCHAR2(30) := ‘YOUR_MAIL_HOST’;
V_MAIL_CONN UTL_SMTP.CONNECTION;
CRLF        VARCHAR2(2)  := CHR(13)||CHR(10);
V_CONTENT   VARCHAR2(300);

BEGIN
             
    SELECT ‘THIS IS EMAIL TESTING FROM ORACLE DATABASE.’  
    INTO V_CONTENT
    FROM DUAL;
                                         
BEGIN
V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, V_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
  ‘DATE: ‘   || TO_CHAR(SYSDATE, ‘DY, DD MON YYYY HH24:MI:SS’) || CRLF ||
  ‘FROM: ‘   || V_FROM || CRLF ||
  ‘SUBJECT: ‘|| V_SUBJECT || CRLF ||
  ‘TO: ‘     || V_RECIPIENT || CRLF ||
  CRLF ||
  V_CONTENT || CRLF ||      –MESSAGE BODY  
  CRLF        

);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
  RAISE_APPLICATION_ERROR(-20000, ‘UNABLE TO SEND MAIL’, TRUE);
END;
           
END;

Hope this help.

How to convert VCF to CSV and vice versa

Nowadays, most contact information for individual store inside their mobile phone and gadgets. Android device for example, storing their contact information locally and when user do contact backup, the file produced will be in VCF formatted data.

Now, consider this scenario; I do have *.VCF in hands and I would like to manage my contact information manually. Manually meanings I wanted to sort, remove duplicate, change their first name or last name, change email address, change mobile number etc. For me, manage it all in Excel is the most convenience way. So , I need that file to be converted to Excel readable file.

The solution

The are many online solution available, no need to install seldom use software or whatever. Do it online because everyone now is connected to internet. One example is vcftocsv.com . The application provided by the website is to convert CSV to VCF and VCF/LDIF to CSV. Just read the instruction inside the website and you will be good.

Again,  here is the link: vcftocsv.com

Hope this will help.

Cheers!

Floating ads using CSS

In the events that you wanted to have a floating ads on your blogspot website, the use of CSS most probably will help a lot. You may refer to the screenshot below, or the live demo of it at this site.
Pretty simple actually.
  1. Click [Add a gadget] from your blogger layout page. In does not matter where it is located.
  2. Choose [HTML/Javascript] from the list of gadget available.
  3. Paste the code given below.
<style type=”text/css”>
.advt {
color:black;
font-size:10px;
position:fixed;
left:25px;
margin-bottom:25px;
bottom:0;
}
</style>
<script type=”text/javascript” src=”http://code.jquery.com/jquery-1.8.3.min.js”></script>
<script type=”text/javascript”>
$(document).ready(function() {
$(“#advt”).click(function() {
});
$(“#closeadvt”).click(function(event) {
event.stopPropagation();
$(“#advt”).fadeOut();
})
});
</script>
<div align=”center” id=”advt” class=”advt”><span id=”closeadvt” style=”cursor:pointer;”>Close X</span>
<p></p>
<a href=”http://etoro.tw/1D3PSrW”>    
<img src=”http://3.bp.blogspot.com/-7dJNg81KFiA/VMRKMJ8QIEI/AAAAAAAAAt4/1j3wk2nXqVw/s1600/etoro_copy_trader1.png” />
</a>
</div>
The reason why it is does not matter is because the floating ads will be sitting at the bottom left of your blogspot website. (It also can be use other then blogspot as well if your know how to code it)
Please change these parameter accordingly:
1. To change your preferred link –> please find code <a href=”http://etoro.tw/1D3PSrW”>
2. To change the image –> please find code <img src=”http://3.bp.blogspot.com/-7dJNg81KFiA/VMRKMJ8QIEI/AAAAAAAAAt4/1j3wk2nXqVw/s1600/etoro_copy_trader1.png” />
3. To change the wording –> please find “Close X”
4. To move the floating ads to top-left –> please find code [bottom:0;], change to [top:0;]
Please change the CSS if you know what you doing. Nothing harm to playing around with the CSS. Have fun!

Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

For some of us whom frequently using MS Access in daily use, encountered an error might be very troublesome.

The problem is like this : I am using MS Access for data analysis and checking and have a connection over the network to Oracle database. My connection was using Microsoft ODBC Driver for Oracle. For some security reason, I need to change the password now. The password changed with no problem but encountered error ORA-12154 when trying to relink the tables.

Error as below:

ODBC–call failed
Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

Generally, ORA-12154 indicated that the specifying TNS alias is not resolving. Few questions needed to clarify;

  1. Are you using a DSN for your connection to Oracle database? 
  2. If so, what is the TNS alias that you specifying? 
  3. Does that alias exist in your tnsnames.ora file? 
  4. Are there multiple tnsnames.ora files on your machine?

Your should have your tnsnames.ora file when you installing the Oracle client software/driver. If you wondering what is the version of the Oracle client you have installed, the simplest way is to get DOS command prompt and type ‘tnsping [service_name]’ where [service_name] is TNS alias specified in your DNS.

The ouput of it may look like this:

C:Usersjcave>tnsping fuddy_duddy
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 – Production on 07-OCT-2
010 08:10:51
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:oracleproduct11.1.0db_1NETWORKADMINsqlnet.ora
TNS-03505: Failed to resolve name

The “Used parameter files” line shows you the directory where the TNS related files are stored. A tnsnames.ora and a sqlnet.ora file should be in that directory and it alwasys be there by default. Perhaps you should check the TNS alias name and confirm with your DNS setting.

How to load image files into BLOB column using PL/SQL

Assuming that you have a list of physical images needed to be loaded into Oracle database. And also you have a free text list of the files, perhaps in form of CSV file format.

From above, you may already get the bigger picture of the scenario which you need to execute.

Lets consider this task needed to be execute in Oracle using Oracle procedure.

The PL/SQL

I already prepared the PL/SQL example for this purpose. Check the code below;

CREATE OR REPLACE PROCEDURE PROC_LOAD_IMAGES (FILE_DIR in VARCHAR2 , MYFILE_NAME in VARCHAR2) AS
  — FILE_DIR is the directory of the flat file and images
  — MYFILE_NAME is  the flat file name
  F UTL_FILE.FILE_TYPE;
  V_LINE VARCHAR2 (1000);
  V_CACC_OLD_NUM  VARCHAR2(24 BYTE);
  V_IDENT_TYPE    NUMBER(1);
  V_ID_NUMBER     VARCHAR2(20 BYTE);
  V_FILE_NAME     VARCHAR2(80 BYTE);
  V_FILE_BLOB     BLOB;
  V_CLASS         VARCHAR2(1 BYTE);
  lFile           BFILE;
           
BEGIN
    F := UTL_FILE.FOPEN (upper(FILE_DIR), MYFILE_NAME, ‘R’);
    IF UTL_FILE.IS_OPEN(F) THEN
        LOOP
            BEGIN
            UTL_FILE.GET_LINE(F, V_LINE, 1000);
            IF V_LINE IS NULL THEN
            EXIT;
            END IF;
                –If using piping, change to ‘[^|]+’
                V_CACC_OLD_NUM := REGEXP_SUBSTR(V_LINE, ‘[^|]+’, 1, 1);
                V_IDENT_TYPE := REGEXP_SUBSTR(V_LINE, ‘[^|]+’, 1, 2);
                V_ID_NUMBER := REGEXP_SUBSTR(V_LINE, ‘[^|]+’, 1, 3);
                V_FILE_NAME := REGEXP_SUBSTR(V_LINE, ‘[^|]+’, 1, 4);
                V_CLASS := REGEXP_SUBSTR(V_LINE, ‘[^|]+’, 1, 5);
             
                INSERT INTO BS_SIGNATURE_IMAGES (CACC_OLD_NUM, IDENT_TYPE, ID_NUMBER, FILE_NAME, CLASS) VALUES( V_CACC_OLD_NUM, V_IDENT_TYPE, V_ID_NUMBER, empty_blob(), V_CLASS) RETURNING FILE_NAME INTO V_FILE_BLOB;
                lFile := BFILENAME(upper(FILE_DIR), V_FILE_NAME);
             
                IF dbms_lob.fileexists(lFile) = 1 THEN      
                    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
                    DBMS_LOB.OPEN(V_FILE_BLOB, DBMS_LOB.LOB_READWRITE);
                    DBMS_LOB.LOADFROMFILE(DEST_LOB => V_FILE_BLOB,
                                          SRC_LOB  => lFile,
                                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));
                    DBMS_LOB.CLOSE(lFile);
                    DBMS_LOB.CLOSE(V_FILE_BLOB);
                END IF;
             
                COMMIT;
                             
            EXIT;
            END;
        END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/

The important portion UTL_FILE function. This is the function to read the file and responsible to load the files into BLOB format into the database.

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!