Saturday, September 05, 2015
Text Size

Oracle: order by subquery missing right parenthesis

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:

  1.  SELECT 
  2.       ps.person_id, 
  3.       ps.person_name, 
  4.       ( 
  5.       select  
  6.            pa.person_reference as personid, 
  7.            pa.person_text as txt 
  8.       from  
  9.            Person_Academia pa 
  10.       where  
  11.            pa.type = 'USER'  
  12.            AND pa.code = 'SPECIAL1' 
  13.            pa.person_reference = ps.person_reference 
  14.       order by 
  15.            pa.last_modified_date desc 
  16.       ) 
  17.  FROM 
  18.       Person_Staff ps 
  20.  -- In oracle, yields "missing parenthesis" error. 
So you can see my parentheses are fine.

Working Solution:

  1.  SELECT 
  2.       ps.person_id, 
  3.       ps.person_name, 
  4.       ( 
  5.            select txt from 
  6.            ( 
  7.                 select  
  8.                      pa.person_text as txt,  
  9.                      pa.person_reference as personid, 
  10.                      row_number(over (order by pa.last_modified_date desc) r 
  11.                 from  
  12.                      Person_Academia pa 
  13.                 where  
  14.                      pa.type = 'USER'  
  15.                      AND pa.code = 'SPECIAL1' 
  16.            ) 
  17.               where personid = ps.person_id 
  19.       ) as AcademicInfo 
  20.  FROM 
  21.       Person_Staff ps 

Fix Oracle Tnsping 3511 without Windows Registry

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:

  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:

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.

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


DBMS Random Reference

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.


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

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

Help Develop!

Thank You! :c)

Connect: Google+

Visitors of the World

Latest Posts