Scripts, systems & field notes.

Oracle PL/SQL 06 Dec 2013 30203

Oracle: order by subquery missing right parenthesis

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

Read note →

Oracle PL/SQL 04 Mar 2013 55085

Fix Oracle Tnsping 3511 without Windows Registry

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

Read note →

Oracle PL/SQL 22 Aug 2012 36119

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.

Read note →

Oracle PL/SQL 21 Aug 2012 18150

DBMS Random Reference

How to use the randomization features of Oracle PL/SQL.

Read note →

Oracle PL/SQL 24 Jan 2012 41621

No rows returned in Oracle causes SP to fail

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

Read note →

Oracle PL/SQL 16 Jan 2012 39072

Basic Oracle Stored Procedure Structure

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

Read note →

Oracle PL/SQL 15 Jan 2012 29600

Basic Oracle Function Structure

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. How? Functions are supposed t...

Read note →

Oracle PL/SQL 30 Aug 2011 19735

Oracle SQL - Convert given list (CSV) into Rows

Just a quick note as to something that worked for me. Taken from the Oracle forums @ https://forums.oracle.com/forums/thread.jspa?messageID=6861756 Situation I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.

Read note →