Results 1 to 7 of 7

Thread: Finding Error in PL/SQL Code

  1. #1
    Expert Member
    Join Date
    Oct 2006
    Answers
    209

    Finding Error in PL/SQL Code

    suppose someone wrote a pl/sql code of some 2000 lines without any exception. It has no. of DML statements and some more works.When it is executed it is not updating table. Then how to find out the error? plz give me the ans.advanced thanks

    NOTE : [This question was asked by gangadharam.p]


  2. #2
    Junior Member
    Join Date
    Feb 2007
    Answers
    2

    Re: Finding Error in PL/SQL Code

    We can find where the exception is by keeping number of dbms statements after each DML statement.It will not print the message where there is an error.


  3. #3
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Finding Error in PL/SQL Code

    use the "show error" after executing ur pl/sql code.
    This will return the error message with line number


  4. #4
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: Finding Error in PL/SQL Code

    hi,
    making use of, dbms_ouput.put_line, this is the way to debug any pl/sql code.
    show error is useful to find the errors at compilation not for runtime errors.

    i will give a brief idea, abt some of the things to be kept in mind:

    use dbms stmnt...
    1) while entering pl/sql block: this will make sure that your code started the execution.
    2)use dbms to print all the values u are recieving from the calling env. --to make sure that u are getting the correct values.
    3)before select stmt, use DBMS to print whcih select stmt u are performing.
    3)after every select stmnt. in your code use dbms to print values fetched by that select stmt.
    4)after every select stmt. use DBMS to print the posible error using SQLERRM. like this
    dbms_output.put_line('the error for selecting employee detail is:' ||SQLERRM);
    5)then if u are making any calls to any of the pl/sql code, use DBMS to print the vlues u will be sending to called sub-program.
    6)print when u are exiting the program.

    there are lot of ways where u can use DBMS, i think the above points will give an idea to experiment more.

    regards,

    RSY


  5. #5
    Junior Member
    Join Date
    Feb 2007
    Answers
    2

    Re: Finding Error in PL/SQL Code

    Quote Originally Posted by raghav_sy View Post
    hi,
    making use of, dbms_ouput.put_line, this is the way to debug any pl/sql code.
    show error is useful to find the errors at compilation not for runtime errors.

    i will give a brief idea, abt some of the things to be kept in mind:

    use dbms stmnt...
    1) while entering pl/sql block: this will make sure that your code started the execution.
    2)use dbms to print all the values u are recieving from the calling env. --to make sure that u are getting the correct values.
    3)before select stmt, use DBMS to print whcih select stmt u are performing.
    3)after every select stmnt. in your code use dbms to print values fetched by that select stmt.
    4)after every select stmt. use DBMS to print the posible error using SQLERRM. like this
    dbms_output.put_line('the error for selecting employee detail is:' ||SQLERRM);
    5)then if u are making any calls to any of the pl/sql code, use DBMS to print the vlues u will be sending to called sub-program.
    6)print when u are exiting the program.

    there are lot of ways where u can use DBMS, i think the above points will give an idea to experiment more.

    regards,

    RSY

    Hi,
    I want to know one thing.SQLERRM is used to print error number and message in pro*c.can we use it in pl/sql?


  6. #6
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: Finding Error in PL/SQL Code

    hi friend,

    honestly i do not have any idea abt PRO* C, but yes SQLERRM will work in pl/sql.

    well u can hae a look here:

    sql> create table d (sl number(3), name varchar2(2));

    SQL> set serverout on;

    SQL> begin
    2 insert into d values ('1232423','RSY');
    3 exception
    4 when others
    5 then
    6 dbms_output.put_line('error is' ||SQLERRM);
    7* end;
    SQL> /
    error isORA-01438: value larger than specified precision allowed for this column
    PL/SQL procedure successfully completed.


    regards,
    RSY

    Last edited by raghav_sy; 02-09-2007 at 01:29 AM. Reason: for giving example.........

  7. #7
    Junior Member
    Join Date
    Jan 2008
    Answers
    24

    Re: Finding Error in PL/SQL Code

    use the below one after executing pl/sql block

    sql> show errors

    it displays errors along with line numbers


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact