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 : [I][This question was asked by gangadharam.p][/I]
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.
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
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
Re: Finding Error in PL/SQL Code
[QUOTE=raghav_sy;7526]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[/QUOTE]
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?
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 [COLOR="Red"]isORA-01438: value larger than specified precision allowed for this column[/COLOR]
PL/SQL procedure successfully completed.
regards,
RSY
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