CODEWITHSUNDEEP
oracle-databaseplsqlexception
triplus
triplus

Reputation: 91

How to continue after in Oracle Dynamic SQL

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?

Upvotes: 1

Views: 92

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132710

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;

Upvotes: 2

Related Questions