Insert multiple rows with a single INSERT statement

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?

Answer: You can insert multiple rows using the following syntax:

INSERT ALL
  INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
  INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
  INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

Example #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Example #2

You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.

Read More

GROUP BY Clause and sorting

Question: When you use a GROUP BY clause with one or more columns, will the results be in the sorted order of GROUP BY columns (by default) or shall we use ORDER BY clause?

Answer: You may get lucky and find that your result set is sorted in the order of the GROUP BY columns, but we recommend always using the ORDER BY clause whenever sorting is required.

Example #1

SELECT department, depart_head, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department, depart_head
ORDER BY department;

This example would sort your results by department, in ascending order.

Example #2

SELECT department, depart_head, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department, depart_head
ORDER BY department desc, depart_head;

This example would first sort your results by department in descending order, then depart_head in ascending order.

Read More

Retrieve primary key information

Question: How do I determine if a table has a primary key and if it has one, how do I determine what columns are in the primary key?

Answer: You can retrieve primary key information with the following SQL statement:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

If you knew the table name that you were looking for, you could modify the SQL as follows:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Make sure to type the table_name in uppercase, as Oracle stores all table names in uppercase.

Let's quickly explain the output from this query.

table_name is the name of the table (stored in uppercase).

column_name is the name of the column that is a part of the primary key. (also stored in uppercase)

position is the position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is very important.

status indicates whether the primary key is currently enabled or disabled.

owner indicates the schema that owns the table.

Read More

Retrieve the name of the Oracle instance currently connected to

Question: How can I get the name of the Oracle database instance that I'm connected to through an SQL statement?

Answer: You can retrieve the instance name using the sys_context function.

To retrieve the Oracle instance name, you execute the following SQL statement:

select sys_context('USERENV','DB_NAME') as Instance
from dual;

It should return something like this:

INSTANCE
--------------------------------------------------------------------------------------
DBPROD

Read More

Retrieve Oracle version information

Question: How do I check the Oracle version from the command prompt?

Answer: Version information is stored in a table called v$version. In this table you can find version information on Oracle, PL/SQL, etc.

To retrieve the version information for Oracle, you execute the following SQL statement:

select * from v$version
where banner like 'Oracle%';

It should return something like this:

Banner
--------------------------------------------------------------------------------------
Oracle11 Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Read More

Add days to a date (skipping Saturdays and Sundays)

Question: I have managed to add days to a given date in a procedure. My question is how do I manipulate the procedure to skip weekends and holidays?

For example:

If the date is 19/9/2003 and I add 2 days, the resulting date should be 23/9/2003.

Answer: To skip weekends when adding days to a date, you will need to create a custom function. Below is a function that we've written called custom_add_days. It accepts two parameters - start_date_in and days_in.

This function takes the start_date_in value and adds the number of days in the days_in variable, skipping Saturdays and Sundays.

This function does not skip holidays as Oracle has no way of recognizing which days are holidays. However, you could try populating a holiday table and then query this table to determine additional days to skip.

CREATE OR REPLACE Function custom_add_days
   (start_date_in date, days_in number)
   return date
IS
   v_counter number;
   v_new_date date;
   v_day_number number;

BEGIN

   /* This routine will add a specified number of days (ie: days_in) to a date (ie: start_date). */
   /* It will skip all weekend days - Saturdays and Sundays */
   v_counter := 1;
   v_new_date := start_date_in;

   /* Loop to determine how many days to add */
   while v_counter <= days_in
   loop
   
      /* Add a day */
      v_new_date := v_new_date + 1;
      v_day_number := to_char(v_new_date, 'd');

      /* Increment counter if day falls between Monday to Friday */
      if v_day_number >= 2 and v_day_number <= 6 then
         v_counter := v_counter + 1;
      end if;

   end loop;

   RETURN v_new_date;
   
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Read More

Execute a function that is defined in a package

Question: How can I execute a function that is defined in a package using Oracle/PLSQL?

Answer: To execute a function that is defined in a package, you'll have to prefix the function name with the package name.

package_name.function_name (parameter1, parameter2, ... parameter_n)

You can execute a function a few different ways.

Solution #1

First, we'll take a look at how to execute a function using a test block. Below we've declared a variable called result that is a number. We've passed in a value of 15000 into the function and the result of the function will be returned to the variable called result.

declare
   result number;
begin
   -- Call the function
   result := package_name.function_name (15000);
end;

Solution #2

We can also execute a function by running an SQL statement. For example:

select package_name.function_name (15000)
from dual;

Read More

Function to return a monthly period

Question: How can I create a function that returns the period that correspond to a month?

A period starts on the first Monday of the month, and ends on a Sunday. If the Sunday does not fall on the 30th or 31st, the procedure should choose the first Sunday of the next month.

For example, the function should return something like this (startdate - enddate):

Oct 6, 2003 - Nov 2, 2003

Answer: Below is a function that accepts as input a date value formatted as 'yyyy/mm/dd'. The function takes this date and returns the period that the date falls within.

create or replace function get_period (pDate varchar2)
   return varchar2
is
   v_period_start date;
   v_period_end date;
   v_check_date date;
   
begin

   /* Determine the 1st of the month */
   v_check_date := trunc(to_date(pDate, 'yyyy/mm/dd'),'MM');
   
   /* Find first monday */
   loop
      exit when to_number(to_char(v_check_date,'d')) = 2;
      v_check_date := v_check_date + 1;
   end loop;
   
   v_period_start := v_check_date;
   
   /* Determine last sunday of current month */
   v_period_end := v_period_start + 27;
   
   /* Take the sunday in next month if the sunday falls */
   /* on the 29th or earlier */
   if to_number(to_char(v_period_end, 'dd')) < 30 then
      v_period_end := v_period_end + 7;
   end if;
   
   return v_period_start || ' - ' || v_period_end;
   
end get_period ;

Read More

Return error message when return datatype is set to NUMBER

Question: I've created a function in PLSQL that returns a number. How can I get this function to return an error message such as 'Course number does not exist!', when the return datatype is set to NUMBER, instead of VARCHAR2?

Answer: How about forcing the function to return an unusual number such as 99999 when the course is not found. Then whenever you evaluate the results from your function, check to see if the value is 99999. If it is, then generate your error message.

For example:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;
   
   cursor c1 is
     select course_number
     from courses_tbl
     where course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;
   
   if c1%notfound then
      cnumber := 9999;
   end if;
   
   close c1;
   
   RETURN cnumber;
   
END;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

Read More

Calculate the average between two dates

Question: I am trying to find the average time between two dates, using PLSQL.

For example:

If I wanted the average time between May 1 and May 3, I should get May 2.

Answer: To find the average time between two dates, you could try the following:

select to_date(date1, 'yyyy/mm/dd')
+ ((to_date(date2, 'yyyy/mm/dd') - to_date(date1, 'yyyy/mm/dd')) /2 )
from dual;

This will calculate the elapsed time between date1 and date2. Then it takes half of the elapsed time and adds it to date1. This should give you the average date.

For example, if you wanted to find the average date between May 1 and May 3, you would do the following:

select to_date('2003/05/01', 'yyyy/mm/dd')
+ ((to_date('2003/05/03', 'yyyy/mm/dd') - to_date('2003/05/01', 'yyyy/mm/dd')) / 2)
from dual;

Read More

Prompt user for a parameter value in SQLPlus

Question: In SQLPlus, I'd like to set up an SQL statement so that the user is prompted for a portion of the query condition.

Answer: You can use the & character to prompt a user for a value. We'll demonstrate how to prompt for both a numeric as well as a text value below:

Prompting for a numeric value

In our first example, we'll prompt the user for a supplier_id value.

In this example, we've entered the following SQL statement:

select * from suppliers
where supplier_id = &supplier_id;

SQLPlus will then prompt as follows:

Enter value for supplier_id:

In this example, we've entered 1. SQLPlus has then returned records for the following SQL statement:

select * from suppliers
where supplier_id = 1;

Prompting for a text value

In our second example, we'll prompt the user for a supplier_name value.

In this example, we've entered the following SQL statement:

select * from suppliers
where supplier_id = '&supplier_name';

Please note that the &supplier_name value is enclosed in single quotes since the supplier_name field is defined as a varchar2 field.

SQLPlus will then prompt as follows:

Enter value for supplier_name:

In this example, we've entered Microsoft. SQLPlus has then returned records for the following SQL statement:

select * from suppliers
where supplier_id = 'Microsoft';

Read More

Execute an SQL script file in SQLPlus

Question: How do I execute an SQL script file in SQLPlus?

Answer: To execute a script file in SQLPlus, type @ and then the file name.

SQL > @{file}

For example, if your file was called script.sql, you'd type the following command at the SQL prompt:

SQL > @script.sql

The above command assumes that the file is in the current directory. (ie: the current directory is usually the 

directory that you were located in before you launched SQLPlus.)

If you need to execute a script file that is not in the current directory, you would type:

SQL > @{path}{file}

For example:

SQL > @/oracle/scripts/script.sql

This command would run a script file called script.sql that was located in the /oracle/scripts directory.

Read More

Retrieve third lowest value from a table

Question: How can I retrieve the third lowest salary amount from a salary table?

Answer: To retrieve the third lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT salary_amount
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount) salary2
      where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT *
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount) salary2
      where rownum <= 3 )
WHERE rnum >= 3;

Read More

Retrieve third highest value from a table

Question: How can I retrieve the third highest salary amount from a salary table?

Answer: To retrieve the third highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT salary_amount
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third highest salary, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT *
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 3 )
WHERE rnum >= 3;

Read More

Retrieve second lowest value from a table

Question: How can I retrieve the second lowest salary amount from a salary table?

Answer: To retrieve the second lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT salary_amount
FROM (select salary2.*, rownum rnum from
            (select * from salary ORDER BY salary_amount) salary2
      where rownum <= 2 )
WHERE rnum >= 2;

If you wanted to retrieve all fields from the salary table for the second lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)

SELECT *
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount) salary2
      where rownum <= 2 )
WHERE rnum >= 2;

Read More

Retrieve second highest value from a table

Question: How can I retrieve the second highest salary amount from a salary table?

Answer: To retrieve the second highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT salary_amount
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 2 )
WHERE rnum >= 2;

If you wanted to retrieve all fields from the salary table for the second highest salary, you could run the following query: (please note that the subquery is sorted in descending order)

SELECT *
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 2 )
WHERE rnum >= 2;

Read More

Retrieve Middle N records from a query

Question: How can I retrieve the Middle N records from a query?

For example, what if I wanted to retrieve records 3 to 5 from my query results. How can I do this?

Answer: To retrieve the Middle N records from a query, you can use the following syntax:

SELECT *
FROM (select alias_name.*, rownum rnum from
               (-- your ordered query --) alias_name
      where rownum <= UPPER_BOUND )
WHERE rnum >= LOWER_BOUND;

For example, if you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM (select suppliers2.*, rownum rnum from
               (select * from suppliers ORDER BY supplier_name) suppliers2
                where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM (select suppliers2.*, rownum rnum from
               (select * from suppliers ORDER BY supplier_name DESC) suppliers2
                where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM (select suppliers2.*, rownum rnum from
               (select * from suppliers ORDER BY supplier_id) suppliers2
                where rownum <= 4 )
WHERE rnum >= 2;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM (select suppliers2.*, rownum rnum from
               (select * from suppliers ORDER BY supplier_id DESC) suppliers2
                where rownum <= 4 )
WHERE rnum >= 2;

Read More

Retrieve Top N records from a query

Question: How can I retrieve the Top N records from a query?

For example, what if I wanted to retrieve the first 3 records from my query results. How can I do this?

Answer: To retrieve the Top N records from a query, you can use the following syntax:

SELECT *
FROM (your ordered query) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum;

For example, if you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum;

Read More

Retrieve Bottom N records from a query

Question: How can I retrieve the Bottom N records from a query?

For example, what if I wanted to retrieve the last 3 records from my query results. How can I do this?

Answer: To retrieve the Bottom N records from a query, you can use the following syntax:

SELECT *
FROM (your query ordered in reverse) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum DESC;

If you want to retrieve the bottom records from a query, you need to order your results in reverse in the "your query ordered in reverse" section of the solution above.

For example, if you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

Notice that although you want the last 3 records sorted by supplier_name in ascending order, you actually sort the supplier_name in descending order in this solution.

If you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;

Read More

PLSQL: Question & Answer

Oracle/PLSQL: Determine the length of a LONG field

LONG Data Types

Question: I have a table in Oracle that contains a field with the data type of LONG. I want to find out how many characters are stored in this LONG field for a particular record in the table.

How can I count the number of characters in a LONG data type field?

Answer: It doesn't appear that you can find the length of a LONG field in SQL. However, you can use PLSQL code to determine the length of a LONG field.

Here is an example of a function that returns the length of the LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the length of the SEARCH_CONDITION field for the selected record.

CREATE or REPLACE function Find_Length
   ( av_owner varchar2, av_cname varchar2)
   RETURN number
   
IS
   long_var LONG;

BEGIN

   SELECT SEARCH_CONDITION INTO long_var
   FROM ALL_CONSTRAINTS
   WHERE owner = av_owner
   AND constraint_name = av_cname;
   
   return length(long_var);
   
END;

You can modify this function to reference your table and field names. Give it a try!

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT owner, constraint_name, Find_Length(owner, constraint_name)
FROM ALL_CONSTRAINTS;

This SQL statement would retrieve the owner and constraint_name fields from the ALL_CONSTRAINTS table, as well as the length of the SEARCH_CONDITION field.

You can modify the Find_Length function to reference your own table and field names. You will also need to modify the function parameters to pass in your own primary key values.

Retrieve the value of a LONG field

Question: I have a table in Oracle that contains a field with the data type of LONG. How can I extract the contents of this LONG field?
If I run the SQL statement below, it just returns <Long>.
SELECT event_details FROM vlts_event_data;
Answer: In Oracle, LONG fields are a bit tricky. However, you can use PLSQL code to determine the value of a LONG field.
Here is an example of a function that returns the value of a LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the value of the SEARCH_CONDITION field for the selected record.

CREATE or REPLACE function Find_Value
   ( av_owner varchar2, av_cname varchar2)
   RETURN varchar2

IS
   long_var LONG;

BEGIN
   SELECT SEARCH_CONDITION INTO long_var
   FROM ALL_CONSTRAINTS
   WHERE owner = av_owner
   AND constraint_name = av_cname;
   
   return long_var;
   
END;

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT owner, constraint_name, Find_Value(owner, constraint_name)
FROM ALL_CONSTRAINTS;

This SQL statement would retrieve the owner and constraint_name fields from the ALL_CONSTRAINTS table, as well as the value stored within the SEARCH_CONDITION field.
You can modify the Find_Value function to reference your own table and field names. You will also need to modify the function parameters to pass in your own primary key values.

Sort a varchar2 field as a numeric field

Question: I have a field defined in Oracle as a varchar2, but it contains numbers. When I use an "order by" clause, the records are sorted ascending by character. But I want to sort it numerically without changing the datatype from varchar2 to numeric. Is there any solution?
Answer: To sort your field numerically, there are two possible solutions:

Solution #1

This first solution only works if the varchar2 field contains all numbers. To do this, you will need to add another field to your "order by" clause that evaluates to a number, but you don't have to include this new field in your "select" portion of the SQL.
For example,
You may have a supplier table defined as:

create table supplier
( supplier_id varchar2(10) not null,
  supplier_name varchar2(60)
);

When you perform the following select statement, the supplier_id field sorts alphabetically, even though it contains numbers.

select * from supplier
order by supplier_id;

However, you could execute the following SQL, which will return a numerically sorted supplier_id:

select * from supplier
order by to_number(supplier_id);

This SQL converts the supplier_id field to a numeric value and then sorts the value in ascending order. This solution returns an error if not all of the values in the supplier_id field are numeric.

Solution #2 (more eloquent solution)

We'd like to thank Kamil for suggesting this solution.
This solution will work even if the varchar2 field contains non-numeric values.
Again, we'll demonstrate this solution on the supplier table, defined as:

create table supplier
( supplier_id varchar2(10) not null,
  supplier_name varchar2(60)
);

Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.
For example,

select * from supplier
order by lpad(supplier_id, 10);

This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.
Please note that if your numbers in the supplier_id field are longer than 10 digits, you may need to increase the second parameter on the LPAD function

Cursor within a cursor

Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?
Answer: Below is an example of how to declare a cursor within a cursor.
In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

create or replace procedure MULTIPLE_CURSORS_PROC is
   v_owner varchar2(40);
   v_table_name varchar2(40);
   v_column_name varchar2(100);
   
   /* First cursor */
   cursor get_tables is
     select distinct tbl.owner, tbl.table_name
     from all_tables tbl
     where tbl.owner = 'SYSTEM';
     
   /* Second cursor */
   cursor get_columns is
     select distinct col.column_name
     from all_tab_columns col
     where col.owner = v_owner
     and col.table_name = v_table_name;
     
   begin
   
   -- Open first cursor
   open get_tables;
   loop
      fetch get_tables into v_owner, v_table_name;
      
      -- Open second cursor
      open get_columns;
      loop
         fetch get_columns into v_column_name;
      end loop;
      
      close get_columns;
      
   end loop;
   
   close get_tables;
   
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

Procedure that outputs a dynamic PLSQL cursor

Question: In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.
I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user enters.

For Example

parameter_1= "Chianti"
parameter_2= "10"
parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?

SELECT * FROM wine
WHERE column1 = parameter_1
AND column2 = parameter_2
AND column3 = parameter_3;

The output of my stored procedure must be a cursor.
Answer: To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.
Let's take a look at how we can do this. We've divided this process into 3 steps.

Step 1 - Table Definition

First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.

create table wine
( col1 varchar2(40),
  col2 varchar2(40),
  col3 varchar2(40)
);

We've made this table definition very simple, for demonstration purposes.

Step 2 - Create package

Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an output parameter in our stored procedure.

create or replace PACKAGE winepkg
IS
   /* Define the REF CURSOR type. */
   TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
END winepkg;

This cursor will accept all fields from the "wine" table.

Step 3 - Create stored procedure

Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.
The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).

create or replace procedure find_wine2
 (col1_in in varchar2,
  col2_in in varchar2,
  col3_in in varchar2,
  c1 out winepkg.wine_type)
as

BEGIN

   /* all columns were entered */
   IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col1 = col1_in
        and wine.col2 = col2_in
        and wine.col3 = col3_in;

   /* col1 and col2 were entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col1 = col1_in
        and wine.col2 = col2_in;
        
   /* col1 and col3 were entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col1 = col1_in
        wine.col3 = col3_in;
        
   /* col2 and col3 where entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col2 = col2_in
        and wine.col3 = col3_in;
        
   /* col1 was entered */
   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col1 = col1_in;
        
   /* col2 was entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col2 = col2_in;
        
   /* col3 was entered */
   ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
        select *
        from wine
        where wine.col3 = col3_in;
        
   END IF;

END find_wine2;

Cursor with variable in an "IN CLAUSE"

Question: I'm trying to use a variable in an IN CLAUSE.

Assumptions & declarations

1. Ref_cursor is of type REF CURSOR declared in Package
2. I will to pass a comma separated Numbers as a string
3. This should be used in the query in the IN Clause
4. Execute the Query and Return the Output as REF Cursor

Something similar to the following:

Create or Replace Function func_name (inNumbers in Varchar2)
   Return PackageName.ref_cursor
As
   out_cursor PackageName.Ref_cursor;
   
Begin
   Open out_cursor
   For Select * from Table_name
   where column_name in (inNumbers);
   
   Return out_cursor;
End;

I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?
Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:

Option #1

Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

Create or Replace Function func_name
   Return PackageName.ref_cursor
As
   out_cursor PackageName.Ref_cursor;
   v1 varchar(2);
   v2 varchar(2);
   v3 varchar(2);
   
Begin

   v1 := '1';
   v2 := '2';
   v3 := '3';
   
   Open out_cursor
   For Select * from Table_name
   where column_name in (v1, v2, v3);
   
   Return out_cursor;
   
End;

Option #2

You could try storing your values in a table. Then use a sub-select to retrieve the values.
For example:

Create or Replace Function func_name
   Return PackageName.ref_cursor
As
   out_cursor PackageName.Ref_cursor;
   
Begin

   Open out_cursor
   For Select * from Table_name
   where column_name in (select values from list_table);
   
   Return out_cursor;

End;

In this example, we've stored our list in a table called list_table.

Avoid "data not found" error in PLSQL code

Question: I'm a new programmer and am trying to include the following statement in my PLSQL code:

select msa_code, mda_desc, zip_code_nk
sales.msa
where zip_code_nk = prod_rec.zip_code_nk;

When there is not a zip_code_nk in the msa table, I'm getting an oracle error saying "Data not found".
How can I code around this? It seem the processor just drops to the exception code and records the record as a failed insert.
Answer: To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned.
For example:

-- Check to make sure that at least one record is returned
select count(1) into v_count
from sales.msa
where zip_code_nk = prod_rec.zip_code_nk;

if v_count > 0 then
   select msa_code, mda_desc, zip_code_nk
   from sales.msa
   where zip_code_nk = prod_rec.zip_code_nk;
end if;

If the count(1) function returns at least one record, then you can perform the "original select statement".

Insert a date/time value into an Oracle table

Question: I have a date field in an Oracle table and I want to insert a new record. I'm trying to insert a date with a time component into this field, but I'm having some problems.
How can I insert this value into the table.
For example, the value is '3-may-03 21:02:44'
Answer: To insert a date/time value into the Oracle table, you'll need to use the to_date function. The to_date function allows you to define the format of the date/time value.
For example, we could insert the '3-may-03 21:02:44' value as follows:

insert into table_name
(date_field)
values
(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

Retrieve the total elapsed time in minutes between two dates

Question: In Oracle, how can I retrieve the total elapsed time in minutes between two dates?
Answer: To retrieve the total elapsed time in minutes, you can execute the following SQL:

select (endingDateTime - startingDateTime) * 1440
from table_name;

Since taking the difference between two dates in Oracle returns the difference in fractional days, you need to multiply the result by 1440 to translate your result into elapsed minutes.
24 * 60 = 1440
24 hours in a day * 60 minutes in an hour

Read More