Saturday, August 15, 2015

Installing APEX5 with ORDS3 on Apache-Tomcat

I was facing few problems like 404 page not found etc. so after going through the documentation and OTN forum I finally succeeded in setting up the Oracle XE+ Apex 5 + ORDS 3+Tomcat on my laptop. I thought of sharing it to everyone.

I assume that you have installation files of following with you.


  • Oracle XE 11g installation files
  • APEX 5.0.1 zip file
  • ORDS 3.0.1.177.18.02.zip
  • apache-tomcat-8.0.22.zip
  • Java Run time 


Here are the different directory/ paths that I have used, in case you have a different path for any installation make changes accordingly
.
  • Oracle XE - c:\oralexe
  • APEX set up files - C:\Tools\apex501

Step 1 – Install Oracle XE

I am not going through the installation of Oracle XE, the only thing you need to know is system password.
For sqlplus - in most cases you can also log in as sysdba without password in sqlplus using “/ as sysdba”

Step 2 – Install APEX


  1. Unzip the apex_5.0.1_en.zip file in C:\Tools\apex501
  2. Go to command prompt, Change directory to C:\Tools\apex501
  3. Log on to database using sqlplus as sysdba
  4. Create necessary Table spaces using following commands

    CREATE TABLESPACE APEX DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\APEX.DBF' SIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

    CREATE TABLESPACE APEX_FILES DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\APEX_FILES.DBF' SIZE 300M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
  5.            Finally start the apex installation using 

@apexins APEX APEX_FILES temp /i/


The installation will take 15 minutes to 30 minutes depending on your PC configuration. Make sure that you see these lines finally, if you see this means your APEX installation was successful.
Session altered.
timing for: Complete Installation
Elapsed: 00:12:36.80
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row selected.
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

Step 3 Install JRE

Install JRE and note down the path. On my laptop it is
C:\Program Files (x86)\Java\jre1.8.0_45\bin\java.exe

Step 4 Install Apache Tomcat server

Download latest version, I am using apache-tomcat-8.0.22.exe as of this writing. When you run the
Set-up make sure to use “Run as administrator” option.

1.       On Welcome screen click next
2.       Next screen is about the license agreement, click on “I Agree” button
3.       On the “Choose Options” button, again there is no need to change any selections so just click next button.
4.       The next screen is configuration. Here you need to specify the ports, the HTTP port is where the apex will be configured, I have used 8180 it was used by some other server on my laptop but you can use default 8080 if there is no conflict.




Also enter user administrator name in user name box and set password, make sure to note it down so you don’t end up missing it. Once done click next.


5.       The next screen is “Java Virtual Machine”. Apache set up will look for Java Virtual machine, most probably it will detect the java and populate the path properly, if not then refer step 3, and enter the java home like this  
C:\Program Files (x86)\Java\jre1.8.0_45
Then click next.
6.       Next screen is “Choose Install Location”, no change needed. So click next.
7.   Click on install, finally you will see the “completing wizard” screen like this.




Keep the “Run Apache Tomcat” selected and click Finish.
Tomcat server will now start showing the start  progress.




When it is complete you will see the icon in the taskbar like this.




Check if the server is running, open browser and point to locahost:8180 , if you get following page then it means all is fine. 





Step 5 Prerequisites before setting up ORDS

Login in into database using system as sysdba
  
1.       EXEC DBMS_XDB.SETHTTPPORT(0);
Confirm if this is 0.
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

2.       Unlock APEX_PUBLIC_USER account and set the new password
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER identified by secret;
ALTER USER APEX_PUBLIC_USER identified by secret;
3.       Unlock APEX_REST_PUBLIC_USER  and set the password
ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK;
ALTER user APEX_REST_PUBLIC_USER identified by secret;

Step 6 ORDS Configuration.


1.   Extract the ORDS zip file in C:\Tools\ORDS

2.   Make a directory to hold the configuration. If you have any failures during the installation, remember to delete the contents of this directory before trying again.

C:\Tools\ORDS-Config

3.   Edit the following file
C:\Tools\ORDS\params\ords_params.properties

And add following entries, please change according to your set up but if you are using default set up then this should be same. If required changed according to your set up.
db.hostname=localhost
db.port=1521
db.servicename=XE
db.sid=
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=
rest.services.ords.add=true
schema.tablespace.default=APEX
schema.tablespace.temp=TEMP
standalone.http.port=8180
standalone.static.images=
user.tablespace.default=USERS
user.tablespace.temp=TEMP

   Then set up the config directory using following command,


Note : For following command your Firewall might intervene

“C:\Program Files (x86)\Java\jre1.8.0_45\bin\java.exe" -jar C:\Tools\ORDS\ORDS.war configdir C:\Tools\ORDS-Config

The above command will end with this message

INFO: Set config.dir to C:\Tools\ORDS-Config in: C:\Tools\ORDS\ords.war

     Configure ORDS using the following command.


 For simplicity in all the steps below wherever a password is asked , I have entered secret, you can use different passwords if you wish to and yes this is not recommended for production.

“C:\Program Files (x86)\Java\jre1.8.0_45\bin\java.exe" -jar C:\Tools\ORDS\ORDS.war

Enter the database password for ORDS_PUBLIC_USER: secret
Confirm password:secret
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step [1]:1
Enter the database password for APEX_PUBLIC_USER:secret
Confirm password:secret

Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:1
Enter the database password for APEX_LISTENER:secret
Confirm password:secret
Enter the database password for APEX_REST_PUBLIC_USER:secret
Confirm password:secret
Aug 14, 2015 9:06:30 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Aug 14, 2015 9:06:30 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.1.177.18.02
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2


Step 7 Deploy the war file to tomcat server.


Copy Ords.war file to C:\Program Files\Apache Software Foundation\Tomcat 8.0\webapps

Once you copy the war file, Tomcat will expand it and create ords folder in webapss. There is no action for you but this is something you need to know, in case anything goes wrong in future as a clean-up you can delete it.

Step 8- Copy Images folder.


Copy content of imgaes folder
C:\Tools\apex501\images

to C:\Program Files\Apache Software Foundation\Tomcat 8.0\webapps\i folder. ( Note the folder name is “i”)

Step 9 Check the instance is running.


Go to localhost:8180/ords/apex

Step 10 Reset the admin password.


  1. Goto command prompt and change folder to  C:\Tools\apex501
  2. log in to oracle as sysdba
  3. type @apxchpwd.sql and hit enter , enter the new password as directed.
  4. Go to http://localhost:8180/ords/apex_admin
  5. Enter user name and password.


You are into APEX as an instance administrator, create workspaces and users as required.
Happy APEXing !


Oracle APEX and Reporting options


Old Post - moved to this blog...

I am currently learning APEX the new (or not-new)development tool from Oracle.

By now I know how to create basic forms and simple reports etc. To make the learning better I started developing a software for school management.

With Oracle XE and APEX together you can develop a software that will be completely free of cost. There is a cache, however the reports that you can develop are limited to banded data only. If you want complex reports or if you want to generate PDF or DOC you need to look elsewhere. After searching on web I found following options are available.

1. Use Oracle BI Publisher. - NOT Free.
2. Use OC4J for PDF generation - not very clear on the license of this. I suppose this is not free.
3. BIRT
4. JasperReports.

Currently I am checking BIRT.

Thanks to Vis Naidu for his detailed write up here

I will share my experiences with BIRT in my next posting.

--Sanjeev

Thursday, November 29, 2012

Adding Wrap This Code context Menu to files

I was working on some packages and I wanted to wrap this code before I could deliver, the parameters that we need to pass to the wrap utility are not something easy to remember.

So I decided to make it easy. What I finally achieved is this..
The right click menu of PKS and PKG files now show a nice option called "Wrap This Code".



If you think this is handy, save following registry entries in .reg files and import them into registry. 
You might want to do some minor changes , for example if you are using some different extensions or you have different path for the wrap.exe etc.


Tuesday, November 06, 2012

Oracle APEX Reporting options

I am currently learning APEX the new (or not-new)development tool from Oracle.

By now I know how to create basic forms and simple reports etc. To make the learning better I started developing a software for school management.

With Oracle XE and APEX together you can develop a software that will be completely free of cost. There is a cache, however the reports that you can develop are limited to banded data only. If you want complex reports or if you want to generate PDF or DOC you need to look elsewhere. After searching on web I found following options are available.

1. Use Oracle BI Publisher. - NOT Free.
2. Use OC4J for PDF generation - not very clear on the license of this. I suppose this is not free.
3. BIRT
4. JasperReports.

Currently I am checking BIRT.

Thanks to Vis Naidu for his detailed write up here

I will share my experiences with BIRT in my next posting.

--Sanjeev

Friday, July 06, 2007

My _VIMRC file for PL/SQL Development


Please find the file here

I tried to paste the contents here but somehow all words enclosed in the <> gets removed. So I uploaded it as file.

Note :
whenever we save file in VIM , a copy is created with filename~,
by default it is in the same directory from where original file is opened. To put all such backup files in specific dir create desired dir and set it as below

set bdir=c:\\vim\\temp
I have commented this line in the file cause if this line is not commented and this directory is not present, VIM can not save files.
So if you want to enable this feature first you need to create this dirctory.


Please do leave your comments or suggestions here or you may mail me at
sanjeev.blog@gmail.com

Regards
Sanjeev

Sunday, July 01, 2007

Finding Details of Oracle Errors.

Download

When we get any oracle error and we want to know more details about it we have to search through oracle documentation. That is time consuming most of the times. Here is an attempt to make that task simple.

I have created a Excel file with a database of most ORA/PLS errors. Just enter the type of error (ORA, PLS etc) and the error number and click on GO. You will get the Error, Cause, and Action.

You will see a dialog box for entering the error type and code like this.


For example I entered this….

Error Type : ORA

Error number : 01234

You will get the Error message, its cause and action like this.


Please do leave your comments or suggestions here or you may mail me at sanjeev.blog@gmail.com

Regards

Sanjeev

Generate Excel workbook with multiple worksheets from PL/SQL

Version 0.62

I am in apps environment and so assumed the presence of fnd_file package.

I am uploading a version for non-apps environment.

Please find the code Non-apps_gen_xl_xml_package.

The Apps version is here

Please do leave your comments or suggestions here or you may mail me at sanjeev.blog@gmail.com

Regards
Sanjeev

Thursday, June 28, 2007

Create Excel Workbook by PL/SQL

Further to my previous post..

I have uploaded the actual package code here
Compile the code on 9i or above and it should compile w/o error.

Package Name : gen_xl_xml
Version : 0.62

You can download samples for demo of how to create a simple and multi-sheet workbook from here

Here I explain how to use this package...

1. First step is to open the file. You do this by using the procedure

If you not using the package in Oracle Applications then use
create_excel - You need to pass the directory object and the file name to create.

If you are under Oracle Applications then use
create_excel_apps - This does not have any parameter as it writes to the out file.
You can not call both procedures at a time.

2. Then you can create styles , worksheet by using the respective procedures.

3. To Write the cells you call write_cell_char and write_Cell_num procedures
4. If you just want to apply style but no contents then use write_cell_null
5. Use set_row_height and set_column_width to set the height and width of row and column respectively.
6. At then end you call close file method. At this point the package will actually write to the file.


Debugging :

There is a package level variable debug_flag which is false by default.

If your excel file is not opening or there are any errors you may set it on and try running from sqlplus to see all the steps that the package executes like creating worksheet , setting row height etc.


Known Limitations.

1. As of now Rows have to be inserted in ascending order.
2. Not all attributes in style are available now. For Example : Borders.
3. Currently formulas are not supported. You may try passing formula strings , I have not checked this but excel's XML structure does not write the formulas that way.
4. Date format is not supported as of now.
5. Column height changes has to be inserted in the ascending order. For example you can not call height change for column c after column e.

Note : This code is not using any version specific features but if you are using 8i I think directory object is not there rather you have to use path set by utl_dir.

I request you to use this code and pass on to whoever requires it. If you get any errors please write to me and I will try to sort them out. I plan to fix the above limitations as I go ahead.

Regards
Sanjeev

Wednesday, January 17, 2007

VIM and PL/SQL Programming

I have been using VIM since last 5 years for doing most of my PL/SQL programming tasks and over this period I found some tweaks /scripts that will help me speed up my work and do it effectively.

Before I could go ahead I must thank VIM and the great mailing list of VIM which is very responsive and helpful. Most of the settings in vimrc I did were with the help of people on VIM list.


All these tweaks and scripts can be divided into three broad categories.
1. Changes to _VIMRC
2. Scripts
3. Extrernal settings.

1. Changes to _VIMRC

Not in any specific order.

First of all I will list down what functionality I achieved and then I will show you the steps.

1. All files with extensions PLS, PLB, BUF, SQL, CODE, PKG should use same syntax coloring as SQL. Reason is straight forward, as some projects use their own extensions for packages etc. These are the files extensions I chose for myself you can add your own extensions.

2. For all above type of files when ever I will do some foldings in VIM , those folds shall be remembered when I re-open those files.

3. As PL/SQL is case in sensitive I want search mechanism also to be case in-sensitive too.

4. Added my own custom dictionary where I can add all my frequently used table names and columns etc. With VIM's powerful word completion feature this will help me to improve my typing speed and also reduce number of errors/typo too.

5. Added some shortcuts

F9 : quit w/o saving and
F12 : quit with save. This is useful when you are working in sqlplus. After ed , doing changes simply press F12 and you are back to SQLplus.


F3 : Change case under cursor, I am not 100% happy with this , Will further modify it.
F4 : Copy word under cursor to clipboard.
F8 : Copy full path of file to clipboard. This is useful when you open file in VIM and frequently do changes and test it. Simply press F8 and go to SQLplus. Type @ and control-V.

6. Personally I like that VIM window is opened maximized. So added one line for that.

7. I dont use VIM's built in File navigator frequently but in case I use I have set it to show full details rather than file list.

8. A small visual change, I have set some colors for the SELECTION text.

9. By default VIM searches word under cursor when you press * key. A very useful feature.
I found one setting in tips that allows to search for multiple selected words/ text to be searched. A very useful enhansement.

10. Setting to hilight current line.

11. Menus that allows me to see the files from Unix directly
There is nothing new in this for VIM users but I think I have made it more convinient for myself, I am working on Oracle Apps. and many times I need to read the LOG and out files from Unix server, and I have the request id.

I have created some menus so that I can select proper sub-menu from it and pater/type the request id there. The file will be read for me in the VIM.

12. QuickSQL Menus. - I don't write triggers everyday and so when time comes I have to look for manuals to see the sytax etc. This is one example. So I cam up with this idea of adding menus which when selected will insert the syntax text in the editor.


Please find my _vimrc here


2. Scripts

In this section I will list all the VIM scripts that use.

1. FavMenu.vim - Adds a favourites menu to VIM. I like to keep added files like _VIMRC, TNSNAMES.ora etc. and of course the code files that I am currently editing.

2. showpairs.vim - This shows matching brackets

3. MultipleSearch.vim - Allows to search multiple words in different colours.

4. filemenu.vim - This script creates a menu of files of given type. I have one script called desc.sql which actually creates .desc files when I run desc command. Then this filemenu script adds a menu called desc in VIM. So I have all .desc files available at the click of a mouse.

5. FeralToggleCommentify.vim - This allows me to comment lines by pressing ALT-C, If line is commented it will get uncommented.

6. mru.vim - This is script to create a most recently used file list.

7. sql_iabbr.vim - Converts the sql key words into capital as you type.

8. sql_indent.vim - Automatically indents the code as you type.

9. sql.vim - I have changed the default coloring to suite to my likings.

Please do leave your comments or suggestions here or you may mail me at sanjeev.blog@gmail.com


Regards
Sanjeev