A quick note on how I got round one this one.

Often enough, our requirement is that the latest record from another table is associated with the current row, and often enough we get the latest by ordering the dataset of the subquery. In T-SQL and MySQL, this is not so much of an issue.

I get this error when having to use an ORDER BY clause in a subquery within an Oracle 11g environment.

Consider the following:

Applies to:
- MS Windows 7 Enterprise

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
  1.  Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage 
  2.  3512 not found; No message file for product=NETWORK, facility=TNSMessage 3513 n 
  3.  ot found; No message file for product=NETWORK, facility=TNSMessage 3509 not foun 
  4.  d; No message file for product=NETWORK, facility=TNS 

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.

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:
Category: Oracle PL/SQL :: Article: 490

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.

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 to use the randomization features of Oracle PL/SQL.

I've been tasked with writing a stored procedure that scrambles data for developers working with some of our databases containing sensitive data.

SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;
  1.  SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL; 
Category: Oracle PL/SQL :: Article: 431

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.

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.

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).

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.

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.

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.

I need to refer to how to create a function in Oracle PL/SQL and sites on the net just attempt to overcomplicate everything and have forgotten how it is to be new to Oracle. I need a function in it's simplest form and if I want to torture my successors, I'll complicate the function myself.

Functions are supposed to return a single value, which is all I need in this case. In my example, I need to submit an Active Directory (AD) username and receive a student ID number instead, all with the aim to improve performance on some SSRS queries which accept either an ID number or an AD name as user parameters.

    [ (parameter [,parameter]) ]

    RETURN return_datatype




END [function_name];
  1.  CREATE [OR REPLACE] FUNCTION function_name 
  2.      [ (parameter [,parameter]) ] 
  4.      RETURN return_datatype 
  6.  IS | AS 
  7.      [declaration_section] 
  9.  BEGIN 
  10.      executable_section 
  12.  [EXCEPTION 
  13.      exception_section] 
  15.  END [function_name]
Category: Oracle PL/SQL :: Article: 409

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

display   where   case   first   table   version   find   joomla   value   server   list   files   client   database   time   script   error   creator   name   need   using   record   parameter   file   following   zoho   used   data   date   create   license   order   user   system   form   deluge   added   code   source   windows   function   page   mysql   work   would   note   field   uploaded   website   report   JoelLipman.Com


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.