How to download an excel file with the correct extension

Recently one of my friends asked me about, did you notice any issue with downloading excel files from one of the web applications that we have used?

I said, no actually, since I haven't recently tried it.:D 

And his problem was that, excel files were not downloading with the correct extension in some browsers. So that he cannot open it with MS-EXCEL. I was so sure about that, since it worked for me before. Anyway since he asked I have checked it again.

Oh no, the issue is there too. :o

I have checked with other browsers as well. Oh yeah, it was working fine with IE. And sadly not with firefox. Since I knew that there were no any recent changes with the code and this functionality had worked fine before, I wanted to inspect the elements in the web application. So I have monitored it and found out the fact.

I knew that inorder to set up the file extension for a .xls format in the response header the value for the 'content-type' attribute should set as "Content-Type:application/vnd.ms-excel". And it was Ok. 

And I have checked the content-disposition and I have noticed that the file name is there, but not the extension. So I have added the change and then the application worked fine. I just added the ".xls" extension to the end of the file name for the value of content-disposition attribute in the code level. And this is how it appeared after the changes.

Since this solution worked fine and actually it saved my time and his too, I wanted to share this with others. So I hope this will help for someone else too.!! :)

Cheers

"do good and good will come to you!!"

Oracle DB Triggers

Recently I have come a cross a task of writing a DB Trigger. My requirement was to save some intermediate data in to a new table. Instead of changing the code for saving those data to a new table, I used the method of recording those data using a db trigger to the relevant table. That was again a new experience for me. So I am sharing the things I have learnt with you all !!:)

Do you know about db triggers ? What is a db trigger ?

I am using one of the available definitions in the web for define the db trigger. 

"Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers." Hope you can get the idea.

When I surf the web for more information I have found out more facts about triggers.

What are the parts of  a typical db trigger ?

Mainly there are 3 parts of the db trigger.
1. Triggering Statement
2. Trigger Restriction
3. Trigger Action

What is Triggering Statement ?

A triggering statement is the SQL statement that causes a trigger to be fired. This Triggering Statement can be an INSERT, UPDATE or DELETE statement on a table.

What is Trigger Restriction ?

A trigger restriction specifies a Boolean expression that must be TRUE for the trigger to fire. 

What is Trigger Action?

A trigger action is the procedure (PL/SQL) that contains the PL/SQL code to be executed when a triggering statement is issued.

What are the available types of Triggers ?

There are 2 types of Triggers.  So I have to find out which one suites for me in this case. I searched for them. 

There are two types of triggers as follows.

1. Row Triggers
2. Statement Triggers

What is Row Level Trigger?

A row trigger is fired each time the table is affected by the triggering statement.

When to use Row Level Triggers?

A row trigger is useful if the code in the trigger action depends on data provided by the rows that are affected.

What is Statement Level Trigger?

A statement trigger is fired once on behalf of the triggering statement. There is no concern about the number of rows in the table that the triggering statement affects

When to use Statement Level Triggers?

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.

According to my requirement. I had to use the Row Level Trigger. Because I wanted to apply the code inside the triggering action to be executed for each row.

Next was to choose one from BEFORE or AFTER Triggers. Because when defining triggers we can specify the time, that is when to fire the trigger action.

What is this BEFORE Trigger?

As it name implies, Before trigger executes the trigger action before the triggering statement. Actually this types of triggers helpful to derive specific column values before executing the triggering statement.

What is AFTER Trigger?

After trigger executes the trigger action after the  triggering statement.  

According to my requirement I have to choose the After Trigger. So I have designed the skeleton of my trigger as follows.

CREATE OR REPLACE

    TRIGGER MY_FIRST_DB_TRIGGER AFTER

    INSERT OR

    UPDATE OR DELETE  ON TRIGGER_ON_TABLE 

    FOR EACH ROW

    DECLARE        

    -- define parameters here
;

   BEGIN

        

   IF inserting THEN    

     

     -- logic that should execute for the inserting event
        --INSERT INTO NEW_TABLE (column_1, column_2)
           --VALUES (:NEW.ID, :NEW.NAME );

    ELSIF updating THEN

   

    -- logic that should execute for the updating event

    ELSE

   

     -- logic that should execute for the deleting event

   

    END IF;

    END MY_FIRST_DB_TRIGGER;

How to access the column values in Row Triggers?

Within a trigger body of a row trigger, the PL/SQL code have access to the old and new column values of the current row affected by the triggering statement. So that you can access the new value for the row is being created as :NEW and the already available column value as :OLD. 

Actually in this case,
* If the trigger has fired by an INSERT statement has meaningful access to new column values only and the old values are null. Because there is only the :NEW value for the row being created.
* If the trigger has fired by an UPDATE statement has access to both old and new column values.
* If the trigger has fired by a DELETE statement has access to :OLD column values only.

Cheers

"do good and good will come to you !!"

dbms_scheduler.create_job

Do you know that you can create Schedulers running in db level? Yes. We can create them. Recently I have come a cross a situation, where I had to create a oracle db level scheduler. Requirement was to periodically searching for modified data using a complex query and inserting them to another table. The same thing can be achieved using a application level scheduler job, but we moved to the db level solution. And it was a new experienced for me. So I am ready to share the knowledge I gathered with that task with you !!

BEGIN

dbms_scheduler.create_job (

job_name=> 'Rep_data_pumping',

job_type=> 'STORED_PROCEDURE',

job_action=> 'pumpRepData',

number_of_arguments=>0,

start_date=> TO_DATE('2015/02/07', 'yyyy/mm/dd'),         

repeat_interval=> 'SYSTIMESTAMP + INTERVAL '30' MINUTE',

end_date=>NULL,

job_class=> 'DEFAULT_JOB_CLASS',

enabled=>TRUE,

auto_drop=>FALSE,

comments=>     'pump rep data periodically');

END;

/

This creates the job named Rep_data_pumping which executes the stored procedure named pumpRepData.  And this will be starting on February 07th and then it runs in every 30 minutes. And this job is enabled. You can use the following commands for different actions as described. 

1. How to run a job manually?

call the dbms_scheduler.run_job procedure and the job will start immediately. 

BEGIN
dbms_scheduler.run_job(job_name=> 'Rep_data_pumping');

END;

/

2. How to stop a running job?

call the  dbms_scheduler.stop_job procedure and the job will stop the running job.

BEGIN
dbms_scheduler.stop_job(job_name=> 'Rep_data_pumping');

END;

/

3. How to disable the job?

call the  dbms_scheduler.disable procedure and the job will disable. One thing to note, if the job is running you will get an error. You must first stop the job and then try to disable the job

BEGIN
dbms_scheduler.disable(job_name=> 'Rep_data_pumping');

END;

/

4. How to enable the disabled job?
call the  dbms_scheduler.enable procedure and the job will enable.

BEGIN
dbms_scheduler.enable(job_name=> 'Rep_data_pumping');
END;
/

5. How to drop a job?
call the  dbms_scheduler.drop_job procedure and the job will drop. And again if the job is runing you will get an error. First stop the job and then drop it.

BEGIN
dbms_scheduler.drop_job(job_name=> 'Rep_data_pumping');

END;

/

Hope you can schedule the next job in db level!! :) :)

Cheers!!

'do good and good will come to you'

Why PATH :..;%JAVA_HOME%\bin;?

It is all about setting up PATH variable for JAVA_HOME with some interesting facts. I had some questions about things related to this topic long time ago. And I wanted to share the things I have found out related to the topic with you all.

1. What is this variable 'PATH' ?

• It is the system variable that operating system uses to locate the required executables from the command line. 
• The value assign for that is series of directories separated by semicolons. 

2. Is it necessary to setup PATH variable once we have installed the jdk ? and Why ?

• Actually it is not necessary, but it is convenient for us :)
• If you do not set the PATH variable, you need to specify the full path to the executable every time you run it.
• If you do set the PATH variable you can conveniently run the executables from any directory without typing the full path in the terminal.  

3. Why we set the path for the JAVA to the 'bin' directory in jdk installation ?

• Because Java compiler and interpreter executables are placed in the 'bin' directory. So that Windows can find them. 

4. How can we setup PATH variable for java in Windows??

• Select Start -> Computer -> System Properties -> Advanced system settings -> Environment Variables -> System variables 
• You can introduce new System variable as JAVA_HOME and give the path to the jdk or you can edit the PATH variable it self. I used the first method.

• Then select the PATH system variable and add a semicolon to the end of the existing value and then put the %JAVA_HOME%\bin as follows.

• Finally check that you have setup it correctly, type the java -version in the terminal and check the installed jdk version details. 
• Now you are done with the setting up PATH for the JAVA_HOME.Go home safely and play with JAVA !! ;)

Cheers!!

'do good and good will come to you'

How to display output using DBMS_OUTPUT.PUT_LINE() in SQL Developer

This was a problem I have faced sometimes back, where I wanted to display output messages from the DBMS_OUTPUT.PUT_LINE() function, when I executing a PLSQL function using the SQL Developer. 

The line I used is as follows,

DBMS_OUTPUT.PUT_LINE('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

Once I called the function no output displayed anywhere in the SQLDeveloper. I was thinking what happened to the output. But I wanted to see the output so badly. So I searched for that and found the solution for that. So I am sharing it with you all.:)

1. Step: You have to 'Enable' the "Dbms output" functionality in the SQLDeveloper. 

Select View -> Dbms Output, click 'Enable'.

Now you can see the Dbms Output is enabled as follows.

2. Step: Run the script and see the output. :)

And also this panel gives you the chance of changing the buffer size. The default buffer size is 20000 characters and you can increase it. 

Hope this will help anyone who are wondering about what happened to the output line you put in 

DBMS_OUTPUT.PUT_LINE("You just wait output. I can see you now :D :D!!! ")

cheers !!

'do good and good will come to you'

Reply for "In Appreciation"

I would like to share my first blog post with the thoughts, which are freely flown through my mind. Hope this is a good day to start writing freely, because today is the 67th Independence Day of my country.:)

I thought to start my blog with one of the nice memories of my work place.  Actually this was a good starting for the year 2015. Even though I wanted to start posting to the blog since long time ago, it delayed with different reasons. And this is the trigger which fired to motivate me for start this as well.

It was one of the memorable days for me, hopefully for other team members as well. It was 2nd of January 2015 and was a normal working day. Most importantly a beautiful Friday ;) We got an email saying that we all have a meeting at 10.30 am. And we went to the conference room at 10.30. All of the members of my team were sitting around the table and curiously waiting for the presentation. And our most senior personal of the team started the presentation with an introduction. He wanted to appreciate each individual for the contribution they have put for the success of the project throughout the last year.  And he said that he is going to use 3 words for each and he will not repeat those for any other.

It was a presentation with one slide for each with a fun picture of that person and the 3 words that describes that person.  It was so nice to hear such a senior person describing about each and every junior members of the team and appreciating their efforts.  Not only it was a presentation with full of fun but also it was so touching meeting as well. It was really a wonderful way of appreciating others and thanking others. So this is how he described me with one of my crazy photographs. :D

Finally that wonderful presentation ended up with a piece of delicious cake that he bought himself for all of us. I left the conference room with the determination of adding different 3 words for me at the end of this year !!.

Even though I have different milestones to achieve throughout this year, I am keeping that promise to myself, because I know that will anyway help for my career path and also my personal life in future wherever I go. 

Thank you !!

Cheers !!
'do good and good will come to you'