Pl sql cursor with parameter in condition

Question

Let’s say I have a procedure with a user input parameter.

Create procedure Match_Record(v_match_ID)

Cursor match is
Select *
From table A
Where not exists(select ‘bbb’ from table B
                              Where a. v_match_ID = b. v_match_ID);

It won’t compile because v_match_ID is not a valid identifier.

So is execute immediate the only option here?

Are you trying to pass in a column name or a literal here? If `V_MATCH_ID` is a column name then yes, you'll need to use execute immediate (or DBMS_SQL). — Ben, Dec 27 '17 at 16:15
I don't know what question to close this as a duplicate of because there are so many things you might be doing but here are some relevant ones: https://stackoverflow.com/q/12956627/458741, https://stackoverflow.com/q/15786953/458741, https://stackoverflow.com/q/25182858/458741 — Ben, Dec 27 '17 at 16:20

score 2 · Answer 1 · answered Dec 27 '17 at 16:29

"I’m trying to pass a column name"

In which case, yes, you need to use dynamic SQL. Rather than execute immediate probably you need to open a cursor variable.

Create procedure Match_Record(v_match_ID)
    ... 
    rc sys_refcursor;
begin
    open rc for 
        'Select *
         From table A
         Where not exists(select blah from table B
                           Where a.'||v_match_ID||' = b.'|| v_match_ID||')';

Pl sql cursor with parameter in condition

1 Answers1