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
– Ben, Commented Dec 27, 2017 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: stackoverflow.com/q/12956627/458741, stackoverflow.com/q/15786953/458741, stackoverflow.com/q/25182858/458741 — Ben
– Ben, Commented Dec 27, 2017 at 16:20

APC · Accepted Answer · 2017-12-27 16:29:09Z

2

"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||')';

answered Dec 27, 2017 at 16:29

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pl sql cursor with parameter in condition

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related