How to continue after in Oracle Dynamic SQL

Question

is it possible to continue after an exception is raised. I generated a little example of code which is similar to what I would like to perform. I can only get null values from the first query, the other querys always get me something back.

Case Username = InUser Then
Execute Immediate 
'select Username from Users where Fullname = ' || '''' || InUser || '''' into Varuser;
<< go_on >>
Execute Immediate    
'select Orderno from orders where requester = ' || '''' || VarUser || '''' into VarOrderno

-- other stuff and cases will happen here.

End Case;

EXCEPTION WHEN NO_DATA_FOUND THEN
VarUser := InUser;
Goto go_on;

I know, that if the first dynamic sql doesn't deliver anything, then I use the InUser. Is there something possible?

Tony Andrews · Accepted Answer · 2016-06-13 13:23:54Z

2

Wrap the dynamic select with begin/end and handle the exception there:

  Case Username = InUser Then

    begin
      Execute Immediate 
        'select Username from Users where Fullname = ' || '''' || InUser || '''' into Varuser;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      VarUser := InUser;
    end;

    Execute Immediate    
      'select Orderno from orders where requester = ' || '''' || VarUser || '''' into VarOrderno

  End Case;

answered Jun 13, 2016 at 13:23

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

Collectives™ on Stack Overflow

How to continue after in Oracle Dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related