היום התבקשתי על ידי אחת המפתחות לעזור לה לשמור שליפה של 230 עמודות ולמעלה מ-50 מליון רשומות לקובץ CSV. מבחינתי זו הזדמנות טובה לתעד את זה המסקנות שלי מהבעיה והפתרון היחסית גנרי שיצרתי בשבילו.

שליפות ב-sqlplus מחזירות בדרך כלל את הרשומות באחד משתי דרכים: הדרך הראשונה היא fixed length columns (כלומר העמודה הראשונה מתחילה תמיד במיקום 1, העמודה השנייה תמיד במיקום 7 וכן הלאה). הדרך השנייה היא כ-HTML ועל זה נדבר בהזדמנות אחרת. קובץ CSV הוא בסופו של דבר קובץ טקסט לכל דבר שבו העמודות (ערכים) מופרדים על ידי פסיקים (ומכאן שמו: Comma-separated values).

הכי פשוט היה אם היה אפשר לשלוף את הנתונים ב-sqlplus, לעשות להם spool ושלום על ישראל אבל לצערי זה לא עובד בצורה פשוטה שכזו .לשמחתי ישנן כמה וכמה דרכים להתגבר על זה. כדי למצוא פתרון חקרתי במספר כיוונים – חלק מהמסקנות היו טובות באופן כללי וחלק היו טובות למקרים מסויימים אבל לא למקרה הזה.

מסקנה ראשונה:

ניתן להשתמש במאפיינים שונים של sqlplus כדי ליצור קובץ נקי ומבלי להציג אותו למסך מה שמאיץ את העבודה כי אין צורך להמתין לכתיבה למסך.

set termout off
set feedback off
set timi off
set lines 30000
set trims on
set pages 0
set arrays 500

שורה 1: מנטרלת הצגה למסך אם הסקריפט שנריץ יורץ על ידי שימוש ב-@ או ב-@@.
שורה 2: מנטרלת את ה-feedback שאומר כמה שורות נשלפו והודעות שגיאה.
שורה 3: ביטול תזמון שליפות.
שורה 4: הגדלת גודל השורה עד ל-30 אלף תווים (כמעט המקסימום).
שורה 5: מחיקת רווחים בסוף שורה כאשר עושים spool.
שורה 6: ביטול כותרות העמודות.
שורה 7: הגדלת גודל המערך של החזרת הרשומות ל-500 ערכים. אפשר לשחק עם ערך זה בהתאם לאורך הרשומה והמיקום שממנו מריצים את השליפה. 100 יכול להתאים למרבית המקרים וזה גם ערך ברירת המחדל החל מגרסה 10.

הפרמטרים האלה טובים לכל פתרון לבעיה שאני אציג בהמשך.

מסקנה שנייה:

ניתן להשתמש במאפיין של sqlplus כדי להוסיף את הפסיק בסוף כל עמודה:

set colsep ,

החסרון בפתרון הזה הוא שהוא לא משנה את העובדה שהשליפה עדיין מחזירה תוצאה בתור fixed length ו-sqlplus ימשיך לדפן את הערכים ברווחים. במקרה של הבעיה הנתונה ההבדל היה של מאות ג'יגות בקובץ ה-output. הפתרון הזה מעולה כאשר יש צורך במספר קטן של עמודות ושורות ואז ניתן לייבא את הקובץ לאקסל בקלות.

מסקנה שלישית:

אני מניח שזה הפתרון שבסופו של דבר רוב האנשים מגיעים אליו – שירשור העמודות עם פסיקים כך שבסוף מתקבלת עמודה אחת ארוכה ביותר עם ערכים המופרדים על ידי פסיקים. הבעיה במקרה הזה היא איך לייצר את השליפה בלי יותר מדי מאמץ (אני מזכיר לכם, במקרה שלי היה מדובר בשליפה עם 250 עמודות).
כדי לפתור את הבעיה החלטתי ליצור מהשליפה שביקשה המפתחת באופן זמני view ולהריץ סקריפט קצר אך שימושי שייצר לי את השליפה מה-view. ניתן באותה צורה לייצא ככה כל טבלה בבסיס הנתונים.

declare
    line_cmd     clob;
    p_view_owner varchar2(40) := 'SCOTT';
    p_view_name  varchar2(40) := 'FOR_MIC1';
    p_delimiter  varchar2(5) := '¿';
begin
    line_cmd := 'select';
    for i in (select table_name || '.' || column_name || ' || ''' || p_delimiter || ''' ||' col
                from dba_tab_columns
               where table_name = upper(p_view_name)
                 and owner = upper(p_view_owner)
               order by COLUMN_ID)
    loop
        dbms_output.put_line(line_cmd);
        line_cmd := i.col;
    end loop;
    line_cmd := trim(trailing ' || ''' || p_delimiter || ''' ||' from line_cmd);
    dbms_output.put_line(line_cmd);
    line_cmd := 'from ' || p_view_owner || '.' || p_view_name || ';';
    dbms_output.put_line(line_cmd);
end;
/

לאחר מכן, שמתי את השליפה שיוצרה על ידי הסקריט הזה בתוך סקריפט משלו ביחד עם הפרמטרים ל-sqlplus שתיארתי קודם והרצתי את הסקריפט עם spool כדי ליצור לי את הקובץ הסופי.

בין הפתרונות הנוספים שראיתי בדרך היה יצור של קובץ HTML והסבתו מאוחר יותר ל-CSV (ישנן שיטות רבות לבצע את זה) אבל יצירת קובץ HTML מגדילה את גודל הקובץ ולא מצמצמת אותו אז ויתרתי על הרעיון די בתחילת הדרך.

נקודות נוספות שכדאי לשים אליהן לב

sqlplus -S [username]/[password]@[SID] @make_csv_for_mic.sql

עדכון: שיניתי את הקוד כך שידע לקבל כל delimiter שנרצה כדי להתגבר על הבעיה שבנקודה הראשונה.