javakid1993 javakid1993 - 1 month ago 10
SQL Question

Comparing a variable to a column's default value in Oracle procedure

I have a table that has two columns that have a default varchar value. Also I have a stored procedure that takes in 4 values, two varchar values and two date's. I would like to compare the two input varchar values to the default values of the two columns in the table even when no rows have been inserted into the table. Is there a possible way to do this?

This doesn't work, but something along these lines.

create or replace
PROCEDURE TEST_PROC
(
TEST_NAME IN VARCHAR2
, TEST_VERSION IN VARCHAR2
, TEST_START IN DATE
, TEST_END IN DATE
) AS
BEGIN

if TEST_NAME = TABLE.COL_1.DEFAULT_VALUE
AND TEST_VERSION = TABLE.COL_2.DEFAULT_VALUE ...

Answer

Use the data dictionary to get the default value of your columns. Then declare variables in your procedure and compare the input. Like this, for example:

SELECT TABLE_NAME||'.'||COLUMN_NAME, DATA_DEFAULT
INTO tableVariable, defaultVariable
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME=:myTable
AND COLUMN_NAME=:myColumn
AND DATA_DEFAULT IS NOT NULL

You can also use it as a cursor, and loop through it.