Reference to a PL/SQL Variable

[Dagon]

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit

I'm trying to write a procedure in which there are a large number of input parameters passed as s type:

type t_a is object
(col1 varchar2(10),
col2 number,
col3 varchar2(50)
...);

procedure find_obj(p_a in t_a)
...

I have a database table:

column_name column_value
col1 'xx'
col2 45

What I need to be able to do is match col1 and col2 on the input with the values in the table and, if there is a match on these, return true. The values of col3, col4 etc are simply ignored if they're not in the table. There can be any combination of the columns in the database table. So, for example, we could have col7/col30 or col3/col9/col26.

It's impossible to do this just with "if" statements (there are too many possibilities). I essentially need to be able to do some sort of dynamic reference to the column names of the input record. This sort of thing is possible in Forms PL/SQL with NAME_IN and COPY but I was wondering if there was an equivalent in the database PL/SQL.

 

[BJCooperIT]

Sounds like you will need to dynamically build your SQL statement using a series of IF statements that check the input parameters to see which are valued and formats according to datatype.

DECLARE
V_SQL varchar2(2000) :=
      'select count(*) from MY_TABLE where 1=1';
BEGIN
if   col1 is not null then
     V_SQL := V_SQL ||'and col1='''||col1||'''';
end if;
if   col2 is not null then
     V_SQL := V_SQL ||'and col2='||col2;
end if;
................

--
-- dynamically execute the SQL statement
--
END;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[Dagon]

I wasn't actually checking against a table. I wanted to lookup the parameter values against values stored in the database, so the code would look something like:

cursor cu_cursor is
select column_name, column_value from table;
matched := 1;
for rec_cursor in cu_cursor loop
    if p_a.<rec_cursor.column_name> <> rec_cursor.column_value then
       matched := 0;
    end if;
end loop;

where p_a.<rec_cursor.column_name> is somehow assigned dynamically.

I suppose I could cast my input parameter into a table and run dynamic SQL against that but I'm not sure how efficient that would be.