Oracle Builds a Bridge to Salesforce.com with New Adapter:
Security Risk in Starbucks App a ‘Wakeup Call’ for Consumers:
WinJS Takes the Next Step:
Code First Goodies in Entity Framework 6:
Entity Framework and slow bulk INSERTs:
Windows Azure Scheduler:
MakerBot CEO Bre Pettis Discusses new 3D Printers:
As I was connecting to Oracle today I got a warning saying that my password would expire within 10 days. What threw me off was that it suggested I’d change it using the PASSWORD command within SQL*Plus. I’ve always thought of SQL*Plus of being a DBA type tool and I’ve always used Oracle SQL Developer instead (not the best of tools but it gets the job done). I knew there had to be a way that you could do this from within Ora Sql Develper, and there was! The answer is below.
alter user username identified by “oldpassword” replace “newpassword”
The advantage of this approach is that you could potentially handle it from within a store procedure. Now I know what you are saying, that this could be a security issue and depending on how it is implemented I would agree. Nevertheless, I would say that being able to do it without having to install yet another tool is a pretty cool thing.
Yet another simple approach to a simple need.
I recently had the need to access data in Oracle from SQL Server 2005. I was surprised to find out that there weren’t that many places in the web that would explain how this is done. I had to go through post after post gathering crumbs of information until I finally got it to work.
The concept is rather simple:
- You link the Oracle server
- You then query it like you would any other linked server
- select * from [ServerName].[TableName]
The problem, however, is the gotchas along the way.
There are two stored procedures that help in the first step of linking the servers: sp_addlinkedserver and sp_addlinkedsrvlogin. They do what their name implies, one creates the server record in the system table and the other one creates a log in with which you will be accessing the linked server’s data.
The stored procedure:
Create PROCEDURE [dbo].[Create_ServerLink]
ServerName: the name of the linked server. It is the name to be used in the statements to call data from oracle.
Schema: the name of the schema
Database: the name of the database
declare @ServerExits bit
set @ServerExits = (select count(*) from sys.sysservers where srvname = @ServerName)
if @ServerExits = 0
EXEC sp_addlinkedserver @ServerName , @Database, ‘OraOLEDB.Oracle’, @Database
EXEC sp_addlinkedsrvlogin @ServerName, ‘FALSE’,NULL, @UserName, @Password
Once linked you would access the server in the following manner:
select * from LinkedServerName..SchemaName.TableName
So if your linked server is called OracleData, the Schema name is Orion and the table name is Products then you would have the following:
select * from OracleData..Orion.Products
- You can link a server by going to the Server Objects\Linked Servers in SQL Server Management Studio, but I think this way is much better because you can add this stored procedure to any other stored procedure that uses oracle data. This is specially useful in an evironment where schemas are added constantly (like where I work).
- At first I used the MSDAORA provider which ended up being dirt slow. After doing some research I found that the OraOLEDB.Oracle driver is much faster.
Remember to let me know if this helped you in any way!