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
TEST_NAME IN VARCHAR2
, TEST_VERSION IN VARCHAR2
, TEST_START IN DATE
, TEST_END IN DATE
if TEST_NAME = TABLE.COL_1.DEFAULT_VALUE
AND TEST_VERSION = TABLE.COL_2.DEFAULT_VALUE ...
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.