Fix Oracle Tnsping 3511 without Windows Registry
- MS Windows 7 Enterprise
What?
So I can ping the server the Oracle 11g database sits on. I've installed the Oracle 11g client tools and can connect using sqlplus. But if I tnsping any oracle database, I get something along the lines of:
- Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage
- 3512 not found; No message file for product=NETWORK, facility=TNSMessage 3513 n
- ot found; No message file for product=NETWORK, facility=TNSMessage 3509 not foun
- d; No message file for product=NETWORK, facility=TNS
Why?
We NEED several instances of Oracle installed as we have different products/services and these run off various instances of Oracle 9i, 10g, and 11g. This article was written for those that need to support these kind of systems and not as an invite for suggestions on how my business should be run.
How?
So there are a variety of solutions that will fix this. However we have certain restrictions in my work environment thanks partly to group policies:
Data Randomization Function in Oracle PL/SQL
This article describes a function that will return random data based on a given value. The function intends to determine the data type and return data that is absolutely irrelevant to the original data but the same length and the same type of data.
Why?
The joy of working with the plethora of applications out there result in me using MySQL, Oracle PL/SQL and Transact SQL in my day job. The equivalent functions have to be written up all in the name of "seamless integration"...
How?
DBMS Random Reference
How to use the randomization features of Oracle PL/SQL.
Why?
I've been tasked with writing a stored procedure that scrambles data for developers working with some of our databases containing sensitive data.
How?
- SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;
No rows returned in Oracle causes SP to fail
We have a stored procedure which contains two select queries. The first query will retrieve a student ID number where the input parameter is the student's username. The second query will return data using the student ID number found in the first query. The stored procedure compiles successfully and without any warnings.
So What?
The problem is that if the student does not have a username but has an ID number, then the first query returns NO ROWS and then the second query errors and the whole stored procedure fails. The same problem happens when using these as a subquery which has an empty result set.
Furthermore
I am using this stored procedure in a SQL Server Reporting Services (SSRS) environment and don't want to return any rows if there are no matches (not a blank row either) as one of my reports uses a row counter to display a "No data found" message when no rows are returned. This does not affect the above problem as the stored procedure simply fails and discontinues processing the overall report.
How?
Browsing the web for a solution, most suggest using NVL() but this only replaces a NULL value with a string of your choice; and even if you NVL all returned fields, no rows are returned, and not a row of NULL/blank/empty values. Sounds confusing? That's just me, the answer was using two NVL functions which allowed the stored procedure to return no rows without erroring (ie. where rownum/rowcount = 0).
Basic Oracle Stored Procedure Structure
For a basic Oracle function, visit my article Basic Oracle Function Structure. For a more advanced version which uses cursors to work with SSRS, see my article Oracle Stored Procedures in SSRS.
What?
I asked someone for a simple, easy and basic Oracle stored procedure as my MySQL and T-SQL stored procedures work slightly differently and are easier to pull off. After much umming and aah-ing, I have written this article as a note for me to demo a working stored procedure and how to use cursors. I'm told I need to use this for SQL Server Reporting Services (SSRS) which is why I'm going down this route. For an even simpler stored procedure, I'd imagine you just get rid of the cursor.
Why
A standalone query is faster in the eyes of SSRS, however within SSRS we don't have the capability to run standard PL/SQL commands... unless they're run from within a stored procedure. Our aim is to run a small query first to return the ID of the student, and then to use this number in a second query which we're hoping will be quicker than a straightforward table join.