Today I am going to upload two SQL scripts using which you could build Northwind & Pub databases against latest versions of MS SQL Server. The latest tested at my end is SQL Server 2014
I have packaged these scripts to avoid the error “Could not find stored procedure ‘sp_dboption’.” error that arises when Microsoft provided SQL scripts are executed against latest database technologies.
With my latest installations of 12c & 11g, I had issue getting the keys mapped properly & after loads and loads of googling I decided to add the same key to all available instances of cgicmd.dat one after another.
So I started with the first instance of cgicmd.dat file, that was found under
which is WLS_REPORTS temporary folder. I added the key, restarted the WLS_REPORTS server & the key was mapped as required!
This could be a nasty bug because I don’t remember adding the keys to multiple files while I tried out forms and reports 11g Release 2 almost 2 years back!
The most interesting part of the entire exercise is, once the key mapping happens successfully, you can remove the keys from cgicmd.dat file that in the temporary location and everything works! Could be the magic of cache
So if you come across key mapping issues, you know where to look at for a quick fix.
which is yet to receive any answers. We were in the same situation and started our experiments to find a solution.
Without boring you with details, here is the complete solution
Re-configure your listener, do not delete, just re-configure using the GUI. This will re-create the Windows service for listener.
Use oradim to create a new service (Please note, this scenario is strictly for creating services while they are missing, if you want to recreate services which are already available, YOU must drop them)
Then went ahead with instructions available with the oracle-base post for the 11gR2 installation.
Even though all the instructions were tailored for a successful installation of 11GR2 we were stuck as soon as we tried to start the installation process by calling
A number of Java exceptions where thrown and while digging around, came to a conclusion that, it was due to the exporting DISPLAY and later allowing access to the same.
We found a thread on stackoverflow, with a solution and the solution was
then as user oracle
$su – oracle
A number of .so files were upgraded to recent versions during the 6.2 to 6.4 upgrade process, hence the installer will prompt missing library files which you can safely ignore (We would recommend you to resolve such warning if you are doing a production installation, a test scenario could overlook these wanings)
It is painful, however, please do confirm all the recommended packages or new versions are already available with your linux installation prior going ahead with the installation.
We installed unixODBC package(s) using the add remove software, a newer version than the one suggested by Oracle and proceeded with the database installation.
Even though we were able to complete the installation successfully, left us with huge concerns like the time and efforts required, the level of knowledge about linux and complexities resolving the dependency which is almost nothing in the case of Windows environment.
Anyway, we hope you would enjoy another quality post by us!
Tested on 10g 10.2.0.3 database with default installation(no additional packages were installed to achieve the results)
There are many APIs (developed by 3rd parties) which will allow you to send SMS from a Oracle database on demand. Many times such APIs would become costly, depending upon your requirements.
Here, we are providing a zero cost solution, incase if your SMS gateway provider allows you to send SMS through a web service portal.
You may wrap the entire procedure and call it against a table trigger or through a button click available with user form(s)
[sourcecode language='sql' padlinenumbers='true']
SET serveroutput ON
SET Define OFF
SMS_MSG VARCHAR2(160) := 'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway';
--Based on your service provider, the following link format may differ from
--What we have specified below!
URL := 'http://yourwebsmsdomain.com/alerts/api/web2sms.php?username=demo&password=demo2&to=95xxxxxxx&sender=ODBSMS&message='||
--UTL_URL.Escape manages escape characters like SPACE between words in a message.
HTTP_REQ := UTL_HTTP.BEGIN_REQUEST(URL);
UTL_HTTP.SET_HEADER(HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);
-- Process Request
URL_TEXT := null;
UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);
WHEN OTHERS THEN EXIT;
Quest Toad may the best Oracle database tool developed by a third party. However, this costly tool may not be the best one for importing objects when you are in a process of migrating from an older version of database to relatively new one! (Or minimum our experiences prove this multiple occasions)
Oracle’s import tool (imp.exe on windows) is a console based utility and could be used without worrying the headaches usually Toad creates.
Below we are providing a “kind” of instructions about importing objects from a full database backup “.dmp” file which is created using Oracle’s export console utility
Prior importing objects, please make sure you have created the following with fresh installed database
Grants to the users (CONNECT, RESOURCE, DBA etc)
A parameter file, call it “myparam.txt” (preferably in the same directory where you store the “.dmp” file. We prefer to keep both the files with root of a partition itself)
(Sample parameter file)
Once you created the above objects, you may proceed towards importing the user objects (tables, views, functions, packages, procedures etc)
We are too busy now days. Issues @work keeping us working around the clock and giving us hardly any time to update this blog. Quite recently, we decided to expand this forum with more Oracle technology related posts, as we realize, we get maximum traffic towards the posts related to Oracle.
To begin with, we are providing a simple script for exporting the entire database using “system” user today for Microsoft Windows based implementation. This batch file exports a .dmp file to a user specified directory.
FOR /F "tokens=2-4 delims=/ " %%a IN ('date/t') DO exp system/password@connectionstring full = y file=d:\backup\exp_%%b%%a%%c.dmp
Save this script inside a .bat or .cmd file (eg: myorabackupdaily.bat or myorabackupdaily.cmd). An Administrator can easily create a scheduled job to insure that the batch file runs everyday at a particular time. Preferably during nights while transactions are none or less. Particularly, there is no need to shutdown the database in order to facilitate the export.
Once exported to .dmp file, it should bear a nomenclature as following:
exp -> Export
22012011-> Date stamp
hope this article/ batch file is useful for few out there.