UTL_FILE Package in Oracle

كما في أي لغة برمجة تستطيع أن تقرأ محتويات ملف أو تكتب إلى ملف , فأوراكل كذلك تقوم بذلك باستخدام الحزمة UTL_FILE تعطي هذه الحزمة مطوري PL/SQL القدرة على قراءة ملفات ASCII من نظام ملفات المخدم (ويندوز أو UNIX) أو كتابتها علية وسوف تجد توصيف هذه الحزمة في الملف :

بفرض أن برنامج أوراكل مثبت على القرص C ولديك النسخة 9i

C:\oracle\ora92\rdbms\admin\utlfile.sql

أولا : لكي تستخدم هذه الحزمة لا بد من إعداد البارامتر UTL_FILE_DIR في الملف init.ora بحيث يأخذ قيمة المجلد الذي يمكن لـ PL/SQL أن تكتب الملفات علية أو تقرأ منه , وقد يكون هناك عدة مداخل لهذا البارامتر يشير كل منها إلى مجلد مختلف يمكن الكتابة عليها من قبل مالك Instance .

كيف أقوم بهذا الإعدادات ؟

أولا : قم بإنشاء مجلد على سطح القرص C وليكن له الاسم tmp وفي هذا المجلد سوف تقوم بوضع الملفات التي تريد تنفيذها أو الملفات التي تكتب فيها .

ثانيا : لتعديل قيمة البارامتر UTL_FILE_DIR هناك العديد من الطريق وأنا أحبذ هذه الطريقة :

1- من قائمة ابدأ أختر Run.

2- اكتب فيها oemapp console ومن ثم اضغط OK .

3- ستبدو لك نافذة اختر منها launch standalone ومن ثم ok

4- ستبدو لك نافذة وستلاحظ وجود بنية شجرية وهناك مجلد باسم Database قم بالنقر على علامة زائد الموجودة بجانبه.

5- ستلاحظ اسم قاعدة البيانات وهو مساوي لاسم Instance قم بالنقر على اسم قاعدة البيانات فمثلا عندي كان اسمها ORAC وهذه التسمية أنت اخترتها عندما قمن بتحميل أوراكل لتكون اسم لقاعدة البيانات .

6- ستبدو لك نافذة تريد منك إدخال اسم المستخدم وكلمة السر , في حقل username اكتب SYS وفي حقل password اكتب كلمة السر لهذا المستخدم فإذا لم تقم بتغيرها عند تثبيت أوراكل فهي (change_on_install) وفي حقل connect as اختر SYSDBA ومن ثم اضغط ok .

7- ستبدو لك البنية الشجرية لقاعدة البيانات , انقر على علامة + الموجودة بجانب instance ومن ثم اختر configuration .

8- ستبدو لك في نفس النافذة صفحة من التبويب عام (general) ستلاحظ وجود زر باسم all initialization parameter قم بالنقر علية ستبدو لك نافذة تحوي جميع البارامترات قم بالنزول إلى اسفل النافذة بشريط التمرير ستجد البارامتر UTL_FILE_DIR وقيمته (value) فارغة كل ما علي هو النقر على حقل القيمة (value) واكتب فيها المسار للمجلد الذي أنشأناه سابقا أي C:\tmp ومن ثم اضغط على الزر Apply ستبدو لك نافذة تطلب منك إعادة تشغيل قاعدة البيانات ما عليك سوى النقر على ok .

9- سيتم إطفاء قاعدة البيانات وتشغيلها كما ستلاحظ في النافذة التي امامك , بعد الانتهاء اضغط على الزر close

10- قم بإغلاق النافذة بالضغط على رمز الإغلاق X أعلى النافذة.

بهذا عزيزي القارئ أنت جاهز الآن لاستخدام هذه الحزمة فدعنا نبحر سويا .

الاجرائيات والتوابع التابعة لهذه الحزمة :

أولا التوابع : FOPEN() ويعيد قيمة من نمط RECORD .

FOPEN_NCHAR() ويعيد قيمة من نمط RECORD .

IS_OPEN () ويعيد قيمة من نمط BOOLEAN .

ثانيا الاجرائيات :

FCLOSE() , FCLOSE_ALL() , FFLUSH() , TERMINATE() , GET_LINE() , GET_LINE_NCHAR() , NEW_LINE() , PUTF() , PUTF_NCHAR() .

ملاحظة : لن أتطرق لشرح كل هذه التوابع والاجرائيات تستطيع الحصول على معلومات أكثر من أي مرجع يشرح هذه الحزمة .

طريقة استخدام الحزمة :

إن طريقة استخدام الحزمة بسيطة للغاية ويتم كما التالي :

1- فتح ملفا بالنمط READ,WRITE,APPEND .

2- اقرأ أو أكتب الملف عن طريق الاجرائيات الجاهزة لذلك .

3- أغلق الملف عند الانتهاء .

مثال : للكتابة إلى ملف :

SET SERVEROUTPUT ON //كي تظهر النتائج

DECLARE

L_file utl_file.file_type;

BEGIN

L_file :=utl_file.fopen('c:\tmp','w.txt','w');

Utl_file.put_line(l_file,'One.');

Utl_file.put_line(l_file,'Two.');

Utl_file.put(l_file,'Three.');

Utl_file.fflush(l_file);

Utl_file.fclose(l_file);

Exception

When others then

If utl_file.is_open(l_file) then

Utl_file.fclose(l_file);

End if;

END;

/

الشرح :

قمنا بتعريف متحول l_file من نمط ملف وذلك باستخدام التعليمة التالية :

L_file utl_file.file_type;

وفي جسم البرنامج قمنا بفتح الملف للقراءة وذلك باستخدام التابع fopen والذي يعيد القيمة من نمط record تخزن في المتحول l_file ويملك هذا التابع ثلاثة متحولات : الاول مسار المجلد الذي نريد أن ننشئ الملف بداخلة والذي عرفناه سابقا عندما عدلنا قيمة البارامتر UTL_FILE_DIR والمتحول الثاني اسم الملف مع الامتداد والمتحول الثالث له القيمة W والتي تعني WRITE .

للمتحول الثالث ثلاث قيم

W FOR WRITE

R FOR READ

A FOR APPEND

أما هذه التعليمة Utl_file.put_line(l_file,'One.'); فتقوم بكتابة القيمة One. مع النزول لسطر جديد .

الفرق بين PUT_LINE و PUT الاولى تكتب القيمة مع النزول لسطر جديد أما الثانية فأنها تكتب القيمة فقط ولا ننسى أن نمرر المتحول L_FILE.

أما التعليمة UTL_FILE.FFLUSH(L_FILE) فتقوم بحفظ ما قمنا به من كتابة .

والتعليمة UTL_FILE.FCLOSE(L_FILE) فتقوم بإغلاق الملف .

في حال حصول أي خطأ قبل تعليمة الإغلاق فأن الملف سيبقى مفتوح لذلك قمت بكتابة تعليمة IF في قسم EXCEPTION وتقوم بفحص فيما إذا كان الملف L_FILE لا يزال مفتوحا فإذا كان كذلك أقوم بإغلاقه.

قم بتنفيذ الإجرائية وفي حال النجاح ستجد أن هناك ملف في المسار C:\TMP ويوجد فيه ملف W.TXT قم بفتح الملف ستجد ما قمنا بكتابته باستخدام الإجرائية.

مثال : قراءة محتوى ملف وطباعتة باستخدام الحزمة dbms_output

DECLARE

L_file utl_file.file_type;

L_BUFFER VARCHAR2(4000);

BEGIN

L_FILE :=utl_file.fopen('c:\tmp','w.txt','R');

LOOP

UTL_FILE.GET_LINE(L_FILE,L_BUFFER);

DBMS_OUTPUT.PUT_LINE(L_BUFFER);

END LOOP;

Exception

When NO_DATA_FOUND then

If utl_file.is_open(l_file) then

Utl_file.fclose(l_file);

End if;

END;

/

في هذا المثال نقوم بقراءة محتوى ملف سطر سطر باستخدام تعليمة LOOP وفي كل مره نخزنه في المتحول L_BUFFER كما عرفناه من نمط VARCHAR2 وكما تلاحظون وضعنا له الحجم 4000 حتى لا تحدث أية مشكلة عند قراءة السطر

لأنه إذا كان طول السطر أكبر من 4000 ستلاحظ وجود رسالة خطأ تخبرك أن طول المتحول صغير.

DBMS_OUTPUT.PUT_LINE(L_BUFFER); : تقوم هذه التعليمة بطباعة محتوي المتحول على الشاشة .

ملاحظة : في هذا المثال لم نقم بإغلاق الملف المفتوح وهذا يخفض من الاداء ويزيد من عدد المؤشرات المفتوحة لذا أنصح بإضافة هذه التعليمة بعد الانتهاء من الحلقة أي بعد END LOOP

Utl_file.fclose(l_file);

مثال أخير :

في هذا المثال سوف أقوم بقراءة محتويات ملف ومن قم سأقوم بتنفيذ التعليمات الموجود بداخلة , كما سأقوم بجعل PL/SQL تقوم بعمل رائع وهو محاكاة التعليمة finally والموجودة في لغة البرمجة جافا .

1- قم بإنشاء ملف emp20.txt وذلك في المجلد c:\tmp واكتب فيه هذا الكود :

Create table emp20 as

Select *

From employees

Where department_id=20

قم بحفظ الملف .

ملاحظة : كما تلاحظ أن هذه التعليمة يجب ألا تنتهي بفاصلة منقوطة وذلك لأننا سوف نستخدم الحزمة DBMS_SQL لتنفيذ هذا الكود وقد قمت بشرح الحزمة في موضوع سابق .

ملاحظة : هذه الإجرائية يتم تنفيذها على المستخدم hr

Set serveroutput on

CREATE OR REPLACE procedure exec_sql_from_file

is

l_file utl_file.file_type;

l_lines DBMS_SQL.varchar2s;

l_cur pls_integer;

l_exec pls_integer;

procedure cleanup

is

begin

if sqlcode <> 0 then

dbms_output.put_line('There is an error in procedure');

end if;

if utl_file.is_open(l_file) then

utl_file.fclose(l_file);

end if;

if dbms_sql.is_open(l_cur) then

dbms_sql.close_cursor(l_cur);

end if;

end cleanup;

begin

begin

l_file:=utl_file.fopen('c:\tmp','emp20.txt','R');

loop

utl_file.get_line(l_file,l_lines(l_lines.count +1));

end loop;

exception

when NO_DATA_FOUND then

NULL;

End;

l_cur:=dbms_sql.open_cursor;

dbms_sql.parse(l_cur,l_lines,l_lines.FIRST,l_lines.LAST,TRUE,DBMS_SQL.native);

l_exec :=DBMS_SQL.EXECUTE(l_cur);

DBMS_SQl.close_cursor(l_cur);

cleanup;

EXCEPTION

WHEN OTHERS THEN

CLEANUP;

raise;

end exec_sql_from_file;

/

قم بترجمة الاجرائية وتنفيذها كالتالي :

اولا للترجمة : قم بالدخول الى المستخدم hr باستخدام sqlplus.

انسخ الكود السابق والصقة في sqlplus ويفترض ان تحصل على النتيجة Procedure created.

قم بتنفذ الاجرائية كالتالي : exec exec_sql_from_file;

في حال النجاح ستحصل على الرسالة التالية :

PL/SQL procedure successfully completed

للتأكد من أن التنفيذ تم بشكل صحيح

نفذ الاستعلام التالي :

Select * from emp20;

الشرح : سنقوم بهذه الإجرائية بقراءة محتوى ملف والذي يحوي تعليمة إنشاء جدول باسم emp20 من الجدول employees والموجود في ال schema HR .

سنقوم بفتح الملف للقراءة ومن ثم ننفذ حلقة للحصول على كل سطر من الملف ونخزنه في المصفوفة l_lines وبعد الانتهاء من الحلقة سنقوم بتنفيذ محتوى المصفوفة باستخدام DBMS_SQL كما مر معنا سابقا ولكن هنا يجب تمرير بداية المصفوفة ونهايتها للاجرائية parse كما تلاحظ في الكود.

بعد الانتهاء من التنفيذ سنقوم بإغلاق ال cursor ومن ثم استدعاء الإجرائية cleanup وعملها مثل عمل finally

ماذا تحوي الإجرائية cleanup ؟

تقوم فحص قيمة sqlcode إذا كانت هذه القيمة لا تساوي الصفر فهذا يعني أن هناك خطأ في الإجرائية وسوف تطبع لك رسالة وبعدها نقوم باغلاق الملف والمؤشر في حال كانا مفتوحين كما تلاحظ في الكود أعلاه.

لقد استدعيت هذه الاجرائية في مكانين :

الاول : في حال أنتهت جميع التعليمات السابقة لها بنجاح وتم الوصول لها فسوف يتم تنفيذها ولكن لن ترى رسالة الخطأ لان sqlcode=0 وسوف تقوم باغلاق الملف كذلك .

الثاني : في القسم exception للاجرائية الرئيسية لماذا استدعيتها هناك لنضرب مثلا :

حاول أن تعدل الملف emp20.txt وقم بإضافة فاصلة منقوطة آخر الملف ومن ثم قم بحفظ الملف ومن ثم قم بحذف الجدول emp20 في حال وجودة وذلك كالتالي

drop table emp20 ;

قم بترجمة بتنفيذ الإجرائية الآن سيتم تنفيذ الإجرائية ولكن سيحدث خطأ عند تنفيذ التعليمة التالية :

l_exec :=DBMS_SQL.EXECUTE(l_cur);

وذلك لان الكود يحتوي على فاصلة منقوطة وسوف تحصل على رسالة الخطأ التالية :

ORA-00911: invalid character

وهذا الخطأ سيقودك إلى القسم exception والذي نقوم فيه بتنفيذ الإجرائية cleanup حيث سوف يتم طبع العبارة

There is an error in procedure

ومن ثم سيتم اغلاق المؤشر والملف المفتوح وذلك لكي نضمن حفظ الموارد وعدد المؤشرات المفتوحة .

بعد الانتهاء من تنفيذ الإجرائية سيتم تنفيذ الإجرائية raise والتي تصدر لك رسالة الخطأ ومن دونها لن تعرف رسالة الخطأ حيث ستحصل على الخرج التالي :

There is an error in procedure

BEGIN exec_sql_from_file; END;

*

ERROR at line 1:

ORA-00911: invalid character

ORA-06512: at "HR.EXEC_SQL_FROM_FILE", line 44

ORA-06512: at line 1

في حال قمت بحذف هذه الإجرائية raise ومن ثم إعادة ترجمة الإجرائية وتنفيذها ستحصل على التالي :

There is an error in procedure

PL/SQL procedure successfully completed.

وهذا السطر الأخير يخبرك انه تم تنفيذ الإجرائية بنجاح مع أن هذا غير صحيح قم بتنفيذ التعليمة التالية :

Select * from emp20;

ستجد أن الجدول غير موجود.

إذن لقد قمت بمحاكاة finally في جافا حيث أن هذه التعليمة يتم وضع فيها كود يتم تنفيذه في حال نجاح أو فشل الكود السابق لها , وهذا ما قمت به ففي حال نجاح الكود يتم تنفيذ cleanup وكذلك في حال حدوث أي خطأ سيتم تنفيذها وبهذا أضمن حفظ الموارد وعدد المؤشرات المتوفرة لكل جلسة .