Hello,
I'm creating a procedure that will validate some user entered data. Users can add values into a text box on my web page and I need to pass those to a oracle procedure and check the table if these values exist. I need to return a list of values that do not exist. This is what I have so far
CREATE OR REPLACE PROCEDURE VALIDATE
(
P_VLIST IN VARCHAR2 ,
P_RECORDS OUT SYS_REFCURSOR
)
AS
V_SQL VARCHAR2(4000) := '' ;
BEGIN
V_SQL := 'SELECT VAL '||CHR(10) ;
V_SQL := V_SQL || 'FROM DVALUES '||CHR(10) ;
V_SQL := V_SQL || 'WHERE VAL NOT IN (' || P_VLIST || ')'||CHR(10) ;
V_SQL := V_SQL || ' ORDER BY VAL' ||CHR(10) ;
OPEN P_RECORDS FOR V_SQL;
END VALIDATE ;
/
my issue is this is going to return all the values from DVALUES. Can I put my list in a temporary table and do a left join to DVALUES and where the values from both tables match I can create a bool column that has a 1 or 0 that shows if the value is good or not and then select out of that table and return the list of bad values?
Thank you