Exception inside if condition in plsql

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have a cursor for inserting employee data from temp table to main table. I have a scenario in which first it will check if temp table department id is exist in main department table; if DeptId does not exist then don't insert employee and update ERR_MSG column in temp table. I am handling exception inside if condition after INSERT statement, but it is giving me an error like

Encountered symbol EXCEPTION when expecting one of the following : begin,end,case,else......

sample code is as below:-

DECLARE

 --var list



    BEGIN

     OPEN ltempCur For



         -- here i am selecting cols from temp table in variables



     LOOP

        FETCH ltempCur 

     INTO 

       --all the declared variables here

     EXIT

    WHEN ltempCur %NOTFOUND;

    BEGIN



     --select statement for checking if employee record exist in table



     IF(lExist == 0)  --if emloyee record not exist

     THEN



       begin

         --check if dept exist in dept table if not exist then i have assigned zero to deptid variable in NO_DATA_FOUND exception

       end;



       IF(vardeptid > 0)  --if condition for which i have mentioned in description



       --Insert statement for employee



       EXCEPTION

       WHEN OTHERS

       THEN

           --handling exp

       COMMIT;

    END;         



       END IF; -- dept id check if end



     --end loop -- close cursor

In above code, if I remove deptid IF ELSE condition then it is working fine, but with that IF condition it is giving error

edited Nov 17 '18 at 9:58

APC

121k16120230

asked Nov 17 '18 at 7:29

Learner1

388

2

You shouldn't put an EXCEPTION section within IF.. END IF .Use RAISE statement if you want to raise an user defined exception when a condition is satisfied. EXCEPTION section should be at the end of a BEGIN..END and can handle or re-raise required exceptions.

– Kaushik Nayak
Nov 17 '18 at 8:01

i want to check if employee insert happens successfully or not, that is why i wrote EXCEPTION after insert statement of employee, can i check rowcount after insert and raise exception if it is zero after END IF?

– Learner1
Nov 17 '18 at 8:03

1

If you want a specific EXCEPTION handler for inserting Employee record then you need to frame that insert statement in a proper block: BEGIN insert ... EXECPTION ... END;

– APC
Nov 17 '18 at 10:01

add a comment |

Encountered symbol EXCEPTION when expecting one of the following : begin,end,case,else......

sample code is as below:-

DECLARE

 --var list



    BEGIN

     OPEN ltempCur For



         -- here i am selecting cols from temp table in variables



     LOOP

        FETCH ltempCur 

     INTO 

       --all the declared variables here

     EXIT

    WHEN ltempCur %NOTFOUND;

    BEGIN



     --select statement for checking if employee record exist in table



     IF(lExist == 0)  --if emloyee record not exist

     THEN



       begin

         --check if dept exist in dept table if not exist then i have assigned zero to deptid variable in NO_DATA_FOUND exception

       end;



       IF(vardeptid > 0)  --if condition for which i have mentioned in description



       --Insert statement for employee



       EXCEPTION

       WHEN OTHERS

       THEN

           --handling exp

       COMMIT;

    END;         



       END IF; -- dept id check if end



     --end loop -- close cursor

In above code, if I remove deptid IF ELSE condition then it is working fine, but with that IF condition it is giving error

edited Nov 17 '18 at 9:58

APC

121k16120230

asked Nov 17 '18 at 7:29

Learner1

388

2

You shouldn't put an EXCEPTION section within IF.. END IF .Use RAISE statement if you want to raise an user defined exception when a condition is satisfied. EXCEPTION section should be at the end of a BEGIN..END and can handle or re-raise required exceptions.

– Kaushik Nayak
Nov 17 '18 at 8:01

i want to check if employee insert happens successfully or not, that is why i wrote EXCEPTION after insert statement of employee, can i check rowcount after insert and raise exception if it is zero after END IF?

– Learner1
Nov 17 '18 at 8:03

1

If you want a specific EXCEPTION handler for inserting Employee record then you need to frame that insert statement in a proper block: BEGIN insert ... EXECPTION ... END;

– APC
Nov 17 '18 at 10:01

add a comment |

Encountered symbol EXCEPTION when expecting one of the following : begin,end,case,else......

sample code is as below:-

DECLARE

 --var list



    BEGIN

     OPEN ltempCur For



         -- here i am selecting cols from temp table in variables



     LOOP

        FETCH ltempCur 

     INTO 

       --all the declared variables here

     EXIT

    WHEN ltempCur %NOTFOUND;

    BEGIN



     --select statement for checking if employee record exist in table



     IF(lExist == 0)  --if emloyee record not exist

     THEN



       begin

         --check if dept exist in dept table if not exist then i have assigned zero to deptid variable in NO_DATA_FOUND exception

       end;



       IF(vardeptid > 0)  --if condition for which i have mentioned in description



       --Insert statement for employee



       EXCEPTION

       WHEN OTHERS

       THEN

           --handling exp

       COMMIT;

    END;         



       END IF; -- dept id check if end



     --end loop -- close cursor

In above code, if I remove deptid IF ELSE condition then it is working fine, but with that IF condition it is giving error

edited Nov 17 '18 at 9:58

APC

121k16120230

asked Nov 17 '18 at 7:29

Learner1

388

Encountered symbol EXCEPTION when expecting one of the following : begin,end,case,else......

sample code is as below:-

DECLARE

 --var list



    BEGIN

     OPEN ltempCur For



         -- here i am selecting cols from temp table in variables



     LOOP

        FETCH ltempCur 

     INTO 

       --all the declared variables here

     EXIT

    WHEN ltempCur %NOTFOUND;

    BEGIN



     --select statement for checking if employee record exist in table



     IF(lExist == 0)  --if emloyee record not exist

     THEN



       begin

         --check if dept exist in dept table if not exist then i have assigned zero to deptid variable in NO_DATA_FOUND exception

       end;



       IF(vardeptid > 0)  --if condition for which i have mentioned in description



       --Insert statement for employee



       EXCEPTION

       WHEN OTHERS

       THEN

           --handling exp

       COMMIT;

    END;         



       END IF; -- dept id check if end



     --end loop -- close cursor

In above code, if I remove deptid IF ELSE condition then it is working fine, but with that IF condition it is giving error

oracle plsql

edited Nov 17 '18 at 9:58

APC

121k16120230

asked Nov 17 '18 at 7:29

Learner1

388

edited Nov 17 '18 at 9:58

APC

121k16120230

asked Nov 17 '18 at 7:29

Learner1

388

edited Nov 17 '18 at 9:58

APC

121k16120230

edited Nov 17 '18 at 9:58

APC

121k16120230

edited Nov 17 '18 at 9:58

APC

121k16120230

asked Nov 17 '18 at 7:29

Learner1

388

asked Nov 17 '18 at 7:29

Learner1

388

asked Nov 17 '18 at 7:29

Learner1

388

2

You shouldn't put an EXCEPTION section within IF.. END IF .Use RAISE statement if you want to raise an user defined exception when a condition is satisfied. EXCEPTION section should be at the end of a BEGIN..END and can handle or re-raise required exceptions.

– Kaushik Nayak
Nov 17 '18 at 8:01

i want to check if employee insert happens successfully or not, that is why i wrote EXCEPTION after insert statement of employee, can i check rowcount after insert and raise exception if it is zero after END IF?

– Learner1
Nov 17 '18 at 8:03

1

If you want a specific EXCEPTION handler for inserting Employee record then you need to frame that insert statement in a proper block: BEGIN insert ... EXECPTION ... END;

– APC
Nov 17 '18 at 10:01

add a comment |

2

You shouldn't put an EXCEPTION section within IF.. END IF .Use RAISE statement if you want to raise an user defined exception when a condition is satisfied. EXCEPTION section should be at the end of a BEGIN..END and can handle or re-raise required exceptions.

– Kaushik Nayak
Nov 17 '18 at 8:01

i want to check if employee insert happens successfully or not, that is why i wrote EXCEPTION after insert statement of employee, can i check rowcount after insert and raise exception if it is zero after END IF?

– Learner1
Nov 17 '18 at 8:03

1

If you want a specific EXCEPTION handler for inserting Employee record then you need to frame that insert statement in a proper block: BEGIN insert ... EXECPTION ... END;

– APC
Nov 17 '18 at 10:01

You shouldn't put an EXCEPTION section within IF.. END IF .Use RAISE statement if you want to raise an user defined exception when a condition is satisfied. EXCEPTION section should be at the end of a BEGIN..END and can handle or re-raise required exceptions.

– Kaushik Nayak
Nov 17 '18 at 8:01

i want to check if employee insert happens successfully or not, that is why i wrote EXCEPTION after insert statement of employee, can i check rowcount after insert and raise exception if it is zero after END IF?

– Learner1
Nov 17 '18 at 8:03

If you want a specific EXCEPTION handler for inserting Employee record then you need to frame that insert statement in a proper block: BEGIN insert ... EXECPTION ... END;

– APC
Nov 17 '18 at 10:01

add a comment |

1 Answer
1

active

oldest

votes

There is a single end if against two IF clauses. Probably, you have not ended the IF clause for variable lExists.

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

I have ended it, jst did not mention in above sample code.

– Learner1
Nov 17 '18 at 8:05

Okay. All the IF clauses must end within the same begin end block in which they were started

– Namandeep_Kaur
Nov 17 '18 at 8:07

yes, i am doing that, and if i comment out IF and END IF of dept exist then code works fine, i think i have to raise exception in dept exist if and then handle it at the end, have to google some stuff :-D

– Learner1
Nov 17 '18 at 8:09

Not really, I believe. You could put an exception handler before end in the begin section where you are checking if dept exist in dept table.

– Namandeep_Kaur
Nov 17 '18 at 15:52

I have ended it, just did not mention in above sample code - then what code are we trying to fix here?

– William Robertson
Nov 18 '18 at 12:12

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53349171%2fexception-inside-if-condition-in-plsql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

There is a single end if against two IF clauses. Probably, you have not ended the IF clause for variable lExists.

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

I have ended it, jst did not mention in above sample code.

– Learner1
Nov 17 '18 at 8:05

Okay. All the IF clauses must end within the same begin end block in which they were started

– Namandeep_Kaur
Nov 17 '18 at 8:07

yes, i am doing that, and if i comment out IF and END IF of dept exist then code works fine, i think i have to raise exception in dept exist if and then handle it at the end, have to google some stuff :-D

– Learner1
Nov 17 '18 at 8:09

Not really, I believe. You could put an exception handler before end in the begin section where you are checking if dept exist in dept table.

– Namandeep_Kaur
Nov 17 '18 at 15:52

I have ended it, just did not mention in above sample code - then what code are we trying to fix here?

– William Robertson
Nov 18 '18 at 12:12

add a comment |

There is a single end if against two IF clauses. Probably, you have not ended the IF clause for variable lExists.

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

I have ended it, jst did not mention in above sample code.

– Learner1
Nov 17 '18 at 8:05

Okay. All the IF clauses must end within the same begin end block in which they were started

– Namandeep_Kaur
Nov 17 '18 at 8:07

yes, i am doing that, and if i comment out IF and END IF of dept exist then code works fine, i think i have to raise exception in dept exist if and then handle it at the end, have to google some stuff :-D

– Learner1
Nov 17 '18 at 8:09

Not really, I believe. You could put an exception handler before end in the begin section where you are checking if dept exist in dept table.

– Namandeep_Kaur
Nov 17 '18 at 15:52

I have ended it, just did not mention in above sample code - then what code are we trying to fix here?

– William Robertson
Nov 18 '18 at 12:12

add a comment |

There is a single end if against two IF clauses. Probably, you have not ended the IF clause for variable lExists.

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

There is a single end if against two IF clauses. Probably, you have not ended the IF clause for variable lExists.

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

answered Nov 17 '18 at 8:03

Namandeep_Kaur

12517

I have ended it, jst did not mention in above sample code.

– Learner1
Nov 17 '18 at 8:05

Okay. All the IF clauses must end within the same begin end block in which they were started

– Namandeep_Kaur
Nov 17 '18 at 8:07

yes, i am doing that, and if i comment out IF and END IF of dept exist then code works fine, i think i have to raise exception in dept exist if and then handle it at the end, have to google some stuff :-D

– Learner1
Nov 17 '18 at 8:09

Not really, I believe. You could put an exception handler before end in the begin section where you are checking if dept exist in dept table.

– Namandeep_Kaur
Nov 17 '18 at 15:52

I have ended it, just did not mention in above sample code - then what code are we trying to fix here?

– William Robertson
Nov 18 '18 at 12:12

add a comment |

I have ended it, jst did not mention in above sample code.

– Learner1
Nov 17 '18 at 8:05

Okay. All the IF clauses must end within the same begin end block in which they were started

– Namandeep_Kaur
Nov 17 '18 at 8:07

yes, i am doing that, and if i comment out IF and END IF of dept exist then code works fine, i think i have to raise exception in dept exist if and then handle it at the end, have to google some stuff :-D

– Learner1
Nov 17 '18 at 8:09

Not really, I believe. You could put an exception handler before end in the begin section where you are checking if dept exist in dept table.

– Namandeep_Kaur
Nov 17 '18 at 15:52

I have ended it, just did not mention in above sample code - then what code are we trying to fix here?

– William Robertson
Nov 18 '18 at 12:12

I have ended it, jst did not mention in above sample code.

– Learner1
Nov 17 '18 at 8:05

Okay. All the IF clauses must end within the same begin end block in which they were started

– Namandeep_Kaur
Nov 17 '18 at 8:07

yes, i am doing that, and if i comment out IF and END IF of dept exist then code works fine, i think i have to raise exception in dept exist if and then handle it at the end, have to google some stuff :-D

– Learner1
Nov 17 '18 at 8:09

Not really, I believe. You could put an exception handler before end in the begin section where you are checking if dept exist in dept table.

– Namandeep_Kaur
Nov 17 '18 at 15:52

I have ended it, just did not mention in above sample code - then what code are we trying to fix here?

– William Robertson
Nov 18 '18 at 12:12

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky