Oracle/PLSQL Topics: Question & Answer

We've added this category to include some of our general questions from viewers.

LONG Data Types:

Determine the length of a LONG field

Retrieve the value of a LONG field

String Data Types:

Dealing with apostrophes/single quotes in strings

Test a string for a numeric value

Test a string for an alphabetic value

Test a string for an alphanumeric value

Sort a varchar2 field as a numeric field

Extract arithmetic operators from a string

Parse a string value and then return a substring

Difference between an empty string and a null value

Extract the directory path from a full file path

Extract the filename (including suffix) from a full file path

Extract the filename suffix from a full file path

DATE Data Types:

Insert a date/time value into an Oracle table

Retrieve the total elapsed time in minutes between two dates

Cursors:

Cursor within a cursor

Procedure that outputs a dynamic PLSQL cursor

Cursor with variable in an "IN CLAUSE"

Errors:

ORA-04098 Error

Avoid "data not found" error in PLSQL code

Retrieve Top, Middle, or Bottom N records of a query:

Retrieve Top N records from a query

Retrieve Bottom N records from a query

Retrieve Middle N records from a query

Retrieve second highest value from a table

Retrieve third highest value from a table

Retrieve second lowest value from a table

Retrieve third lowest value from a table

SQLPlus:

Execute an SQL script file in SQLPlus

Prompt user for a parameter value in SQLPlus

Miscellaneous:

Format a number in Oracle

Calculate the average between two dates

Return error message when return datatype is set to NUMBER

Rollback behavior and DDL's

Procedure to return a monthly period

Execute a function that is defined in a package

Add days to a date (skipping Saturdays and Sundays)

Retrieve Oracle version information

Retrieve the name of the Oracle instance currently connected to

Retrieve primary key information

Retrieve the value that occurs most in a column

GROUP BY Clause and sorting

Insert multiple rows with a single INSERT statement