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
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
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
- Ref_cursor is of type REF CURSOR declared in Package
- I will to pass a comma separated Numbers as a string
- This should be used in the query in the IN Clause
- Execute the Query and Return the Output as REF Cursor
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
