Oracle / PLSQL: Avoid data not found error in PLSQL code

 

Oracle / PLSQL: 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
FROM 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".

Oracle / PLSQL: How to fix ORA-04098 Error

Oracle / PLSQL: How to fix ORA-04098 Error

Question: What is the solution for the oracle generated error ORA-04098 ?

Answer: For an ORA-04098 error, the cause might be:

Cause

A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.

Resolution

The options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.

You can also try running the following command to check for errors on the trigger:

SHOW ERRORS TRIGGER trigger_name;

Replace trigger_name with the name of your trigger.

Oracle / PLSQL: Cursor with variable in an IN CLAUSE

 Oracle / PLSQL: Cursor with variable in an IN CLAUSE

Question: I'm trying to use a variable in an IN CLAUSE of a cursor. Here are my assumptions and 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
IS
   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
IS
   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
IS
   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.

 

Oracle / PLSQL: 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.

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

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;

Oracle / PLSQL: Cursor within a cursor

 Oracle / PLSQL: 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.

 

Oracle / PLSQL: 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

 

Oracle / PLSQL: 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'));

Learn more about the TO_DATE function.

 

Oracle / PLSQL: Extract the filename suffix from a full file path

Question: I have a string that contains the full path to a file. How can I extract the suffix for a filename from the string in Oracle?

For example, if I have a full file path as follows:

'c:\windows\temp\example.xls'

I want to retrieve the following:

'xls'

Answer: You can create a custom function in Oracle that will retrieve the filename from the string value.

The following function called get_filename will extract the directory path. It will work with both Windows and UNIX system file paths.

CREATE OR REPLACE FUNCTION get_file_suffix
   (p_path IN VARCHAR2)
   RETURN varchar2

IS
   v_file_suffix VARCHAR2(10);

BEGIN

   v_file_suffix := SUBSTR(p_path,(INSTR(p_path,'.',-1,1)+1),length(p_path));
   RETURN v_file_suffix;

EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;

END;

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

SELECT get_file_suffix('c:\windows\temp\example.xls')
FROM dual;

This SQL statement would return 'xls'.

 

Oracle / PLSQL: Extract the filename (including suffix) from a full file path

Question: I have a string that contains the full path to a file. How can I extract the filename (including the suffix) from the string in Oracle?

For example, if I have a full file path as follows:

'c:\windows\temp\example.xls'

I want to retrieve the following:

'example.xls'

Answer: You can create a custom function in Oracle that will retrieve the filename from the string value.

The following function called get_filename will extract the directory path. It will work with both Windows and UNIX system file paths.

CREATE or REPLACE function get_filename
   (p_path IN VARCHAR2)
   RETURN varchar2

IS
   v_file VARCHAR2(100);

BEGIN

   -- Parse string for UNIX system
   IF INSTR(p_path,'/') > 0 THEN
      v_file := SUBSTR(p_path,(INSTR(p_path,'/',-1,1)+1),length(p_path));

   -- Parse string for Windows system
   ELSIF INSTR(p_path,'\') > 0 THEN
      v_file := SUBSTR(p_path,(INSTR(p_path,'\',-1,1)+1),length(p_path));

   -- If no slashes were found, return the original string
   ELSE
      v_file := p_path;

   END IF;

   RETURN v_file;

END;

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

SELECT get_filename('c:\temp\example.xls')
FROM dual;

This SQL statement would return 'example.xls'.

 

Oracle / PLSQL: Extract the directory path from a full file path

Question: I have a string that contains the full path to a file. How can I extract the directory path from the string in Oracle?

For example, if I have a full file path as follows:

'c:\windows\temp\example.xls'

I want to retrieve the following:

'c:\windows\temp'

Answer: You can create a custom function in Oracle that will retrieve the directory path from the string value.

The following function called get_dir_path will extract the directory path. It will work with both Windows and UNIX system file paths.

CREATE OR REPLACE FUNCTION get_dir_path
   (p_path IN VARCHAR2)
   RETURN varchar2

IS
   v_dir VARCHAR2(1500);

BEGIN

   -- Parse string for UNIX system
   IF INSTR(p_path,'/') > 0 THEN
      v_dir := SUBSTR(p_path,1,(INSTR(p_path,'/',-1,1)-1));

   -- Parse string for Windows system
   ELSIF INSTR(p_path,'\') > 0 THEN
      v_dir := SUBSTR(p_path,1,(INSTR(p_path,'\',-1,1)-1));

   -- If no slashes were found, return the original string
   ELSE
      v_dir := p_path;
   END IF;

   RETURN v_dir;

END;

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

SELECT get_dir_path('c:\windows\temp\example.xls')
FROM dual;

This SQL statement would return 'c:\windows\temp'.

 

Oracle / PLSQL: Difference between an empty string and a null value

Question: What is the difference between an "empty" value and a "null" value? When I select those fields that are "empty" versus "null", I get two different result sets.

Answer: An empty string is treated as a null value in Oracle. Let's demonstrate.

We've created a table called suppliers with the following table definition:

create table suppliers
( supplier_id number,
  supplier_name varchar2(100)
);

Next, we'll insert two records into this table.

insert into suppliers (supplier_id, supplier_name )
values ( 10565, null );

insert into suppliers (supplier_id, supplier_name )
values ( 10567, '' );

The first statement inserts a record with a supplier_name that is null, while the second statement inserts a record with an empty string as a supplier_name.

Now, let's retrieve all rows with a supplier_name that is an empty string value as follows:

select * from suppliers
where supplier_name = '';

When you run this statement, you'd expect to retrieve the row that you inserted above. But instead, this statement will not retrieve any records at all.

Now, try retrieving all records where the supplier_name contains a null value:

select * from suppliers
where supplier_name is null;

When you run this statement, you will retrieve both rows. This is because Oracle has now changed its rules so that empty strings behave as null values.

It is also important to note that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions.