Jul
28
2009
Error Debug กับ FORMAT_ERROR_BACKTRACE
Written by Administrator   

     เพิ่งจะได้ มีโอกาสได้รู้จัก Package   DBMS_UTILITY.FORMAT_ERROR_BACKTRACE    ไม่รู้ว่า เชยไปหรือเปล่า เพราะว่า มีให้ใช้ตั้งแต่  Oracle 10g  
   จากเหตุที่อยากหาฟังก์ชันที่ทำหน้าที่  จับ Error  และ บอกบรรทัดให้หน่อย ว่า Error ที่บรรทัดไหน   และที่สำคัญ  ต้องบอกได้ว่า มัน Error ที่ Procedure ไหน หรือ Function ไหนได้ด้วย
  ปกติเวลาเขียน โปรแกรม  Procedure ที่เขียนขึ้นมาเรียกกันหลายระดับ  กว่าจะรู้ว่ามัน Error ที่จุดไหน ใช้เวลาไปพักใหญ่ๆ ทีเดียว ยิ่งไปกันใหญ่ถ้าเป็น Error ประเภทลักปิดลักเปิด  เด๊่ยวดีเดี๋ยวร้าย (ไม่รู้ใครเขียนโปรแกรมแล้วเจอแบบนี้บ้าง  หุหุ)
 
   เหมือนฟ้ามาโปรด   เมื่อไปเปิดเจอ DBMS_UTILITY.FORMAT_ERROR_BACKTRACE  สามารถช่วยได้จริงๆ
  โดยทั่วไป  ใน PL/SQL การเขียนเพื่อดักจับ Error  จะใช้ 2 Function คือ  SQLCODE   และ SQLERRM  ซึ่งทำหน้าที่บอก Error Code  และ Error Message  เท่านั้น


  ลองดูตามตัวอย่างนะครับ
    1. สร้าง  Procedure ที่มี Error ก่อน  ชื่อว่า p_error
SQL> CREATE OR REPLACE PROCEDURE p_error
  2  IS
  3     x   NUMBER;
  4     y   VARCHAR2 (10);
  5  BEGIN
  6     x := 10;
  7     y := 'A';
  8     x := y;    (Error  ตรงนี้  )
  9     y := 'B';
 10  EXCEPTION
 11     WHEN OTHERS
 12     THEN
 13        DBMS_OUTPUT.PUT_LINE (SQLERRM);   
 14  END;
 15  /
 Procedure created.
 2.    สร้าง Procedure อีกอัน เพื่อเรียกใช้ p_error  คือ    p_call_proc
SQL> CREATE OR REPLACE PROCEDURE p_call_proc
  2  IS
  3  BEGIN
  4     p_error;
  5  END;
  6  /

Procedure created.

3.   ลองสั่ง Run  
SQL> exec p_call_proc
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

PL/SQL procedure successfully completed.

แจ้ง  Message  อย่างเดียว  หาไปเหอะ  ไล่ code ไปเลยนะ  มัน Error  แน่นอน


ลองเพิ่ม DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

SQL> CREATE OR REPLACE PROCEDURE p_error
  2  IS
  3     x   NUMBER;
  4     y   VARCHAR2 (10);
  5  BEGIN
  6     x := 10;
  7     y := 'A';
  8     x := y;
  9     y := 'B';
 10  EXCEPTION
 11     WHEN OTHERS
 12     THEN
 13        DBMS_OUTPUT.PUT_LINE (SQLERRM);
 14        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
 15  END;
 16  /

Procedure created.

ลอง  Run ดูอีกที
 SQL> exec p_call_proc;
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SCOTT.P_ERROR", line 8

เย้ๆๆ  รู้แล้วว่า มี Error เกิดขึ้นที่ Procedure  ไหน  บรรทัดที่เท่าไหร่    ก็ตามไปแก้ในจุดที่ Error แจ้งมา
  รู้สึกว่า โลกนี้น่าอยู่ขึ้นอีกเยอะ 5555 (ว่าไปนั่น)    แค่นี้ก็ช่วยให้โปรแกรมเมอร์หา Bug ได้ง่ายขึ้นแล้วล่ะครับ

Comments
Add New Search
lookpranoi     |203.147.15.xxx |2014-05-17 00:05:16
เยี่ยมไปเลย ครับ
Gabriel Mink  - Error Debug аёЃаё±аёљ FORMAT_ERROR_BACKTRACE     |193.187.94.xxx |2019-11-25 23:30:47
If you don't know how to find the best university in the world for you, I
recommend you to read this article[/url] and also read about
opportunities for Boston university students who are interested in
entrepreneurship on the website in order to understand that Boston university is one of the best
educational institutions in the world. Also if you want to be involved in
military actions you can read about the fact that military-connected
freshmen go to the University of Chicago here and you also can go to the University of Chicago. And in the end, if you
want to be connected with the geographic job you have to read about
two researchers from the University of Washington will study the
climate change in the arctic on this site and order a University of Chicago as one of the best educational
institutions that also has faculties connected with geographics.
Wilmer Keppner  - Error Debug аёЃаё±аёљ FORMAT_ERROR_BACKTRACE     |109.94.220.xxx |2019-11-28 21:54:10
Hi! If you are going to enroll a good university, then I reccomend you to
go to the University of Washington because you can speedily prepare and
 enroll this university with one hundred percent - 100 guarantee. See
uw essay prompts in order to learn how to write an essay totally, you can also find a data
there where you can buy your ideal article in order to enter
that university, not even with 100 probability, but with two hundred
percent - 200 probability. ____________________________________And here
are some more practicable sources:В  https://lifeisanepisode.com/how-to-find-the-bes... - there is knowledge about the best educational institutions in the
world, https://www.internetvibes.net/2019/11/08/opport... - here about one of the best universities - Boston, https://bootcampmilitaryfitnessinstitute.com/20... - and here a little about the University of Chicago, which ...
Guillermo Duggin  - Error Debug аёЃаё±аёљ FORMAT_ERROR_BACKTRACE     |45.145.130.xxx |2019-12-06 21:19:37
If you study at a not very good university, then it will be interesting for
you to read the article - Do I Need A educational institution Diploma If
I Graduated From A University That Is Not As Prestigious As New York
university ? where there is information where to buy NYU Supplement Essay . Also, if you are studying at a bad university, then you anticipated do
not have access to any programs, so you may be interested to study
How Does Stanford Mobile Help Stanford educational institution
Students Achieve Better Academic Results? This article has information
what stanford cisco vpn is. And also, if you still don’t study at a bad educational institution
, but just choose a  educational institution , you should search UChicago Essay Prompts and read an article aboutThe Best Admission Essay Writing Services For
College Or university !
Theresia Conlon  - Error Debug аёЃаё±аёљ FORMAT_ERROR_BACKTRACE     |193.202.84.xxx |2019-12-06 21:24:19
If you study at a bad university, then it will be interesting for you to
read the article - Do I Need A university Diploma If I Graduated From
A University That Is Not As Prestigious As New York university ? where
there is information where to buy NYU Supplement Essay . Also, if you are studying at a bad university, then you feasible do not
have access to any programs, so you may be interested to read How Does
Stanford Mobile Help Stanford university Students Achieve Better Academic
Results? This article has information what stanford cisco vpn is. And also, if you still don’t study at a bad educational institution
, but just choose a  educational institution , you should search UChicago Essay Prompts and read an article aboutThe Best Admission Essay Writing Services For
College Or university !
Lane Vanderzee  - Error Debug аёЃаё±аёљ FORMAT_ERROR_BACKTRACE     |45.140.205.xxx |2019-12-07 01:14:46
If you would like to study at good educational institutions, then you need
to know about their benefits. For example, all top
educational institutions have their own applications. You can read an
essay about stanford mobile that is called "Technological advantages of studying at top
universities: is Stanford mobile an example of the best app for
Stanford educational institution students?". You also need to be
aware that after you learn about the benefits of studying at top
educational institutions and want to enroll in one of them, you need
to write an introductory essay. That is why you can use essayassist to buy Princeton supplement essay or some other introductory essay.
Augustine Whiteley  - Error Debug аёЃаё±аёљ FORMAT_ERROR_BACKTRACE     |45.67.213.xxx |2019-12-07 01:19:57
If you would like to learn at good universities, then you need to know
about their benefits. For example, all top educational institutions
have their own applications. You can read an paper about stanford mobile that is called "Technological advantages of learning at top
universities: is Stanford mobile an example of the best app for Stanford
university students?". You also need to be aware that after you
learn about the aids of studying at top educational institutions and want
to enroll in one of them, you need to write an introductory
essay. That is why you can use essayassist to buy Princeton supplement essay or some other introductory essay.
Cecila  - Error Debug กับ FORMAT_ERROR_BACKTRACE     |81.171.69.xxx |2019-12-10 16:37:36
Hey there my name is name! First of all congratulations this is a really well
written article, I actually learned a thing or two. I am a mother of two boys,
James 6 years old and Lucas 2 years old. I Just wanted to get your opinion on
something – I was just reading this review of an online child reading system
over at http://bit.ly/38vxhf4 and that woman reviewing this system shows a 18
month child reading like 5 years old. I Would love to know what you think about
it and what you think about this system she review..are you familiar with it?
Thanks in advance! Can’t wait to hear your reply as I’m thinking about
giving a try the system she recommends. My kids are my life and I want to
provide them a better future.
Write comment
Name:
Email:
 
Website:
Title:
UBBCode:
[b] [i] [u] [url] [quote] [code] [img] 
 
 
:angry::0:confused::cheer:B):evil::silly::dry::lol::kiss::D:pinch:
:(:shock::X:side::):P:unsure::woohoo::huh::whistle:;):s
:!::?::idea::arrow:
 
Please input the anti-spam code that you can read in the image.

3.26 Copyright (C) 2008 Compojoom.com / Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."