הפעם הראשונה שהתחלתי להתעסק עם בסיסי נתונים מסחריים היה בקורס תכנות שעשיתי איפשהו בשנת 1998. השיעור הראשון, אם זכרוני אינו מטעה אותי, התחיל במילים: "היום נלמד על בסיסי נתונים – דבר ראשון בואו נתחבר לבסיס הנתונים באמצעות sqlplus".

הכלי הזה, שכמעט כל מי שעובד עם אורקל מכיר, הוא כלי command line לעבודה עם בסיס הנתונים שנמצא איתנו כבר הרבה מאוד שנים. זה אחד הכלים הכי נוחים להבנה, הכי נוחים לתפעול והוא עובד מעולה עם סקריפטים. יש לו אינטגרציה עם הכלים של קלאסטר (לדוגמה, אם עושים shutdown איתו אז הקלסטר יודע שזה בכוונה ולא מנסה להרים את ה-node מחדש) ואפילו כלים אחרים שאורקל מספקים משתמשים בו וביכולות שלו לעיתים קרובות.

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

לאחרונה נחשפתי לכלי חדש של אורקל שבא לתת ממשק command line תואם sqlplus אבל עם יכולות חדשות ומגניבות: SQLcl והתלהבתי בטירוף.

הכלי החדש, SQLcl (לא ברור אם זה השם הסופי, כנראה שלא, כי השם שלו כבר הוחלף פעם אחת בחודשים האחרונים מ-SDSQL… :)) מפותח במסגרת פרויקט הפיתוח של ה-SQL Developer. זה אחד הכלים היותר מרשימים שנחשפתי אליהם בתחום ולמרות שהוא יצא רק לפני 3 או 4 חודשים, עושה רושם שהוא בדרך להיות המחליף הראוי ל-sqlplus הישן והטוב.

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

איך מתקינים את הכלי

את הכלי הזה (בגרסת early adapter) אפשר להוריד מכאן: דף ההורדה הרשמי של ה-SQL Developer Early Adapter.

כדי לעבוד עם הכלי יש לפתוח את קובץ ה-zip, לוודא שיש לנו JRE Java (אפשר 7, עובד גם עם 8, אבל בכל מקרה לא צריך JDK) וזהו. הכלי מוכן לעבודה. אין צורך ב-Oracle Client או OCI (אלא אם כן אנחנו צריכים זיהוי מבוסס OCI). אם אנחנו בסביבת linux צריך להפוך את קובץ ה-sql ל-executable אבל זה פחות או יותר הכל… 🙂

כדי להתחבר עם הכלי אפשר להשתמש בפורמט התחברות של EZConnect (כלומר hostname:port/service):

[oracle@lnx-oracle66-db11g bin]$ sql zohar@localhost:1521/orcldg
SQLcl: Release 4.1.0 Beta on Wed Mar 11 16:58:24 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.

Password? (**********?) *****
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL>

אבל אם יש לנו Client אז אפשר להתחבר כמו שאנחנו רגילים (הוא יחפש את ה-tnsnames ב-oracle homes שלנו. אם הוא מסתבך אז יש דרך לבדוק את זה, אבל זה עניין לפעם אחרת).

מה הוא יודע לעשות ש-SQLPlus לא?

הכלי הזה אמור להיות בסופו של דבר תואם לחלוטין כך שמרבית הפקודות בו אמורות להיות מוכרות. הנה כמה דוגמאות על קצה המזלג:

Information

אם יש לנו שם של טבלה אבל אנחנו לא יודעים עליה כלום, כדי לקבל קצת אינפורמציה היינו עושים describe. זה היה נותן לנו רשימה של שדות ואז היינו צריכים להתחיל לחקור את הטבלה דרך שליפות: מה המפתח שלה, איזה אינדקסים, איזה constraint-ים וכן הלאה. עבודה מעיקה, לא?

הנה מה שיש לכלי הזה להציע לנו:

SQL> info mytab
Columns
NAME             DATA TYPE            NULL  DEFAULT    COMMENTS
*OBJECT_ID       NUMBER               No
 OWNER           VARCHAR2(30 BYTE)    Yes
 OBJECT_NAME     VARCHAR2(128 BYTE)   Yes
 SUBOBJECT_NAME  VARCHAR2(30 BYTE)    Yes
 DATA_OBJECT_ID  NUMBER               Yes
 OBJECT_TYPE     VARCHAR2(19 BYTE)    Yes
 CREATED         DATE                 Yes
 LAST_DDL_TIME   DATE                 Yes
 TIMESTAMP       VARCHAR2(19 BYTE)    Yes
 STATUS          VARCHAR2(7 BYTE)     Yes
 TEMPORARY       VARCHAR2(1 BYTE)     Yes
 GENERATED       VARCHAR2(1 BYTE)     Yes
 SECONDARY       VARCHAR2(1 BYTE)     Yes
 NAMESPACE       NUMBER               Yes
 EDITION_NAME    VARCHAR2(30 BYTE)    Yes
 
Indexes
INDEX_NAME        UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS             COLUMN_EXPRESSION
ZOHAR.MYTAB_PK    UNIQUE      VALID                   OBJECT_ID
ZOHAR.MYTAB_IDX1  NONUNIQUE   VALID                   OWNER, OBJECT_NAME

ואם מעניין אותנו לקבל גם קצת מידע על סטטיסטיקות והיסטוגרמות:

SQL> info+ mytab
Columns
NAME             DATA TYPE            NULL  DEFAULT    LOW_VALUE                        HIGH_VALUE                  NUM_DISTINCT   HISTOGRAM
*OBJECT_ID       NUMBER               No                   2                                92856                       87599          NONE
 OWNER           VARCHAR2(30 BYTE)    Yes                  APEX_030200                      ZOHAR2                      26             NONE
 OBJECT_NAME     VARCHAR2(128 BYTE)   Yes                  /1000323d_DelegateInvocationHa   yCbCrSubSamplingType229_T   52264          NONE
 SUBOBJECT_NAME  VARCHAR2(30 BYTE)    Yes                  $VSN_1                           WRH$_WAITST_1398494861_66   132            NONE
 DATA_OBJECT_ID  NUMBER               Yes                  0                                92856                       9698           NONE
 OBJECT_TYPE     VARCHAR2(19 BYTE)    Yes                  CLUSTER                          XML SCHEMA                  44             NONE
 CREATED         DATE                 Yes                  2011.09.17.09.46.13              2015.03.11.17.48.13         1778           NONE
 LAST_DDL_TIME   DATE                 Yes                  2002.10.01.12.41.49              2015.03.11.17.48.13         2558           NONE
 TIMESTAMP       VARCHAR2(19 BYTE)    Yes                  1990-08-26:11:25:00              2015-03-11:17:48:13         2273           NONE
 STATUS          VARCHAR2(7 BYTE)     Yes                  INVALID                          VALID                       2              NONE
 TEMPORARY       VARCHAR2(1 BYTE)     Yes                  N                                Y                           2              NONE
 GENERATED       VARCHAR2(1 BYTE)     Yes                  N                                Y                           2              NONE
 SECONDARY       VARCHAR2(1 BYTE)     Yes                  N                                Y                           2              NONE
 NAMESPACE       NUMBER               Yes                  1                                64                          20             NONE
 EDITION_NAME    VARCHAR2(30 BYTE)    Yes                                                                               0              NONE
 
Indexes
INDEX_NAME        UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS             COLUMN_EXPRESSION
ZOHAR.MYTAB_PK    UNIQUE      VALID                   OBJECT_ID
ZOHAR.MYTAB_IDX1  NONUNIQUE   VALID                   OWNER, OBJECT_NAME

CTAS

מכירים את זה שאתם צריכים לייצר סקריפט של Create table as select? נכון שזה מעיק – למצוא את המבנה של הטבלה, של האינדקסים של ה-constraints?

הנה הפתרון האולטימטיבי. פקודה שמייצרת לנו סקריפט שעושה את זה

SQL> ctas t t2
 
  CREATE TABLE "ZOHAR"."T2"
   (    "DUMMY"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 
as
select * from T
SQL>

DDL

ואם אנחנו לא רוצים לעשות CTAS אלא סתם ליצור את הטבלה (על כלל חלקיה) במקום אחר?

SQL> ddl mytab
 
  CREATE TABLE "ZOHAR"."MYTAB"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30),
         CONSTRAINT "MYTAB_PK" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 
  CREATE INDEX "ZOHAR"."MYTAB_IDX1" ON "ZOHAR"."MYTAB" ("OWNER", "OBJECT_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

Command Alias

נניח שיש לנו סקריפיטים של שליפות שאנחנו מריצים הרבה (לדוגמה, session-ים מחוברים או נעילות) אז אנחנו יכולים לתת שם לשליפה (כמו view) אבל גם לפורמט שלה כמו שהיינו מקבלים ב-sqlplus- לדוגמה format של עמודות ולהפעיל את השליפה על ידי קריאה לפקודה.

SQL> alias dual=select * from dual;
SQL> dual
Command=dual
 
DUMMY
-----
X

מה שמגניב פה זה שאפשר להכין סקריפטים שלמים (כולל פרמטרים שמתקבלים מהמשתמש) כדי להפעיל את השליפה.

SQL> alias dual2=select :parameter from dual;
SQL> dual2 2
Command=dual2
 
:PARAMETER
--------------------------------
2

Bridge

נניח שיש לנו שליפה שאמורה לשלוף מכמה בסיסי נתונים – נניח מה המצב של ה-data guard.

ברור שאנחנו לא רוצים ליצור database link בין בסיסי הנתונים האלה, אבל אנחנו כן היינו רוצים לשלוף מכולם בו זמנית. אז מה עושים? היום אנחנו מייצרים סקריפט שיתחבר פעם אחת לפה, יחזיר תוצאה ואחרי זה פעם אחת משם ויחזיר תוצאה ואז אנחנו כבר נדוג את מה שאנחנו צריכים מתוך הפלט.

שימוש אחר – העברת מידע מבסיס נתונים אחד לאחר (insert table as select) כשאנחנו לא יכולים או לא רוצים לחבר עם dblink את שני בסיסי הנתונים.

ה-bridge מאפשר לנו ליצור "גשר" בין בסיסי הנתונים דרך ה-client שלנו. הכלי יתחבר (במקביל) לכמה בסיסי נתונים ויאפשר לנו לשלוף את התוצאה בלי להתחבר פעמיים (אפילו באותה שליפה אם נרצה). הכלי משתמש ב-jdbc כדי ליצור את ההתחברות ועובד כרגע רק בין אורקל לאורקל.

SQL> create table zohar2.t as select 'zohar2' from dual;
  
Table ZOHAR2.T created.
  
SQL> bridge zohar_test as "jdbc:oracle:thin:zohar2/zohar2@localhost:1521:orcldg"(select * from zohar2.t);
Table zohar_test : insert succeeded
SQL> select * From zohar_test;
  
A
------
zohar2
  
SQL> select * from dual where dummy = (select a from zohar_test);
  
no rows selected

סיכום

עקרונית, אם עדיין לא התלהבתם מהכלי אז כנראה שאתם בעסק הלא נכון. הרשימה שנתתי עד עכשיו הייתה כמה דוגמאות על קצה המזלג. עוד לא הזכרתי בכלל את העובדה שהכלי יודע לייצר פלטים מסוגים שונים (כמו CSV או JSON) בפקודה בודדת, שהוא נותן לנו command history (כולל לחיצה על חץ למעלה כדי לחזור פקודות אחורה), שהוא יודע לתת פלט בצבע, שהוא יודע להשלים פקודות באמצעות tab, שהודעות השגיאה מגיעות כבר עם התיאור המלא מהדוקומנטציה, או שהוא יודע לבצע שליפות של XQuery.

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

שורה תחתונה: אני צופה לכלי הזה עתיד מזהיר ומכניס אותו מיד לארגז הכלים המומלצים שלי…