MS SQL Northwind & Pub databases | ‘sp_dboption’

November 6, 2017

Hi guys

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.

You may download the SQL scripts from this link

MD5: 5831113f5ec890986312d2ed532a13e2

SHA-1: 57484de9bea37c5e0b8ba84454e8093612d96df6

regards,

rajesh

Advertisements

Oracle Weblogic | Reports 11g | cgicmd.dat | Key mapping issues

January 16, 2016

 

Hi guys

I’ve been installing and configuring Weblogic 10.3.6 & 12c (recently) and came across a peculiar issue, where the reports server failing to map the keys I’ve specified with cgicmd.dat file.

The best I could recollect from my memories was, I only added the keys to a single instance of cgicmd.dat file, as there are many instances of the same file once after classic domains are created.

Under linux installations you will find the file in 3 different locations once after the domain created and WLS_REPORTS server started.

 

[root@wls Middleware]# find /u01/Oracle/Middleware/ -name cgicmd.dat
/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/cgicmd.dat
/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/cgicmd.dat
/u01/Oracle/Middleware/Oracle_FRHome1/reports/conf/cgicmd.dat

[root@wls Middleware]#

image

Under usual scenarios you need to add the keys ONLY to the cgicmd.dat file present in

/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/

folder.

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

/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/cgicmd.dat

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 Winking smile

So if you come across key mapping issues, you know where to look at for a quick fix.

for Windows7bugs

rajesh


Oracle services disappear from Windows Services panel after upgraded to Windows 8.1

October 31, 2013

Just a week back we upgraded two of our development laptops to Windows 8.1 64bit PRO, which were having Oracle 10g 10.2.0.3.0 locally installed.

Once after the upgrade, we realized that the Windows services panel totally missing the entries for Oracle services (yes WTF from our end as well)

There is a question thrown at http://stackoverflow.com/questions/19533791/not-able-to-see-oracle-services-in-windows-services-after-upgrading-to-win-8-1

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.

image

image

image

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)

From an elevated command prompt

 

set ORACLE_SID=<SID>

D:\oracle\product\10.2.0\db_1>oradim -new -sid %ORACLE_SID% -intpwd PASSWORD -st
artmode M -pfile D:\oracle\product\10.2.0\admin\sct\pfile\init.ora

There is a high possibility that you may find the init.ora file like following

D:\oracle\product\10.2.0\admin\sct\pfile>dir
Volume in drive D is New Volume
Volume Serial Number is 866B-F095

Directory of D:\oracle\product\10.2.0\admin\sct\pfile

10/31/2013  12:31 PM    <DIR>          .
10/31/2013  12:31 PM    <DIR>          ..
02/18/2013  09:24 AM             2,613 init.ora
02/18/2013  09:24 AM             2,613 init.ora.11820139272
10/31/2013  12:31 PM               745 sqlnet.log
3 File(s)          5,971 bytes
2 Dir(s)  97,706,209,280 bytes free

D:\oracle\product\10.2.0\admin\sct\pfile>

just copy the init.ora.NNNNNNNNNNN file to init.ora

Now start registry edit. This is required for the situation when you will notice the database service you have just created starts, however will not mount the database.

Navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraDb10g_home1

image

(We are using Windows 8.1 64bit)

By the right side details panel, find the key “ORA_<SID>_AUTOSTART” which will have a value “FALSE”. Change this value to “TRUE” and get back to Windows services panel and start the database service.

That’s all folks

if you are not cursed, the Oracle database service must start, mount the database without any troubles.

referenced threads: https://forums.oracle.com/thread/541840

http://www.dba-oracle.com/tips_oradim_utility.htm

For Windows7bugs

raj


Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6

September 22, 2013

 

We always refer www.oracle-base.com for exclusive tips on installing various Oracle products and we would suggest, it is one of the best repositories you can also depend upon for.

Recently we were having a requirement to migrate our legacy 10.1.2.x Oracle database to 11gR2 and we followed this thread by www.oracle-base.com to.

Our scenario was involving a VM instead of physical server which was hosted on Windows 8 64 Professional edition using Oracle’s VirtualBox 4.2.18

Following the straight forward instructions we installed Oracle Linux 6.2 X86_64 (64bit) and added Oracle’s own public repository for yum repository (instructions here)

Next we ran an update cycle, and our distro was upgraded to 6.4 from 6.2 and the first issue we realized was X terminal. To resolve, we did the following few activities

Uninstalled the VirtualBox addons

Re-configured the X terminal

Re-installed VirtualBox addons

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

./runInstaller

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

as root

set DISPLAY

$DISPLAY=:0.0

$export DISPLAY

$xhost +

then as user oracle

$su – oracle

$DISPLAY=:0.0

$export DISPLAY

$cd /stage/database

$./runInstaller

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!

regards,

admin


Oracle Database, Send SMS through SMS Gateway

July 3, 2013

 

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)

SET serveroutput ON
SET Define OFF

DECLARE
    HTTP_REQ      UTL_HTTP.REQ;
    HTTP_RESP     UTL_HTTP.RESP;
    URL_TEXT      VARCHAR2(32767);
    URL VARCHAR2(2000);
    
     SMS_MSG VARCHAR2(160) := 'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway';
    
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
--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(SMS_MSG,TRUE); 
--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
    LOOP
        BEGIN
            URL_TEXT := null;
            UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);
            DBMS_OUTPUT.PUT_LINE(URL_TEXT);

            EXCEPTION
                WHEN OTHERS THEN EXIT;
        END;
    END LOOP;

    UTL_HTTP.END_RESPONSE(HTTP_RESP);
END;

Enjoy another quality solution from us :)

Regards,

Admin


Windows 7 64bit, Classic ASP, Visual Studio connection to Oracle Database

October 23, 2012

References

26.12.2013 Update

We realized, over a 64bit OS you may need to change the default application pool to accommodate 32bit applications. Please refer to this post

https://help.webcontrolcenter.com/KB/a1114/how-to-enable-32-bit-application-pool-iis-7-dedicatedvps.aspx

 

 

http://www.connectionstrings.com/

http://stackoverflow.com/questions/234657/binding-asp-net-gridview-to-an-oracle-sys-refcursor

So, you are still using classic ASP or develop 32bit .NET applications using Visual Studio and Oracle databases on a Windows 7 64bit box and completely lost as connection cannot be established?

Here are few workaround solutions

Install the latest Oracle clients (both 64bit and 32bit)

If you installed Oracle client in the following folders (example)

D:\app\yourusername\product

right click on the folder, under security tab check whether authenticated users have read privileges.

If not, provide the basic privileges (Read & Execute, list folder contents, Read) Refer the image

image

Now add the user “IIS_ISURS” user and provide the same rights mentioned above

RESTART your Windows 7 “64bit” box now!

You can establish the connection from classic ASP like following

<%
    Dim adoCon                'Holds Connection
    Dim rsViewRecords        'Holds Recordset

    Set adoCon = Server.CreateObject("ADODB.Connection")
    if Err.Number <> 0 then 
        Response.Clear() 
        response.Write "<hr>ORASESSION Error<br>" & err.number & "  --  " & err.Description & "<hr>"
        response.End
    end if 

    adoCon.Open "provider=oraoledb.oracle;data source=test;user id=apps;password=apps;plsqlrset=true"

    Set rsViewRecords = Server.CreateObject("ADODB.Recordset")

    %>

 

Regards,

Admin


Oracle imp VS Quest Toad’s import utility Wizard

May 19, 2012

 

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

image

Prior importing objects, please make sure you have created the following with fresh installed database

  1. Tablespaces
  2. Users
  3. Grants to the users (CONNECT, RESOURCE, DBA etc)
  4. 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)
  5. image(Sample parameter file)

Once you created the above objects, you may proceed towards importing the user objects (tables, views, functions, packages, procedures etc)

Now open command prompt and type the following

C:\> imp system/<<password>> PARFILE=paramtext.txt LOG=C:\19052012imp.log

Now sit back while Oracle does the import job for you!

 

Regards,

Admin