Blog Name
  • Dezember 2018

Did you already know: Testtabellen erzeugen mit SQLcl

Tabellenkopien zu erzeugen um beispielsweise einige Tests auszuführen ist eines der Themen das nie aus der Mode kommen wird. Es stehen dafür verschiedene Möglichkeiten und Werkzeuge zur Verfügung, die unterschiedlich aufwändig in der Anwendung sind. Beispiele sind Datapump Export/Import, SQL*LOADER oder auch SQL*Plus Skripte. Weniger bekannt ist dabei die Nutzung des Werkzeuges Oracle SQL Developer Command Line (kurz SQLcl). SQLcl, der "kleine Bruder" von SQL Developer, vereinigt dabei die Vorteile eines Linemode Werkzeugs mit den Features von SQL Developer. SQLcl sollte genau wie SQL Developer separat von OTN geladen werden. Die Installation ist dann ähnlich wie bei SQL Developer: Einfach die Zip Datei von OTN laden, in einen leeren Folder auspacken und schon kann man starten. Skripte zum Erstellen von Testtabellen sind dann mit den vorgefertigten Kommandos ganz einfach und schnell zu erzeugen.
Hinweis: Möchte man statt einet Kopie der Daten "echte Testdaten", die maskiert sind, erzeugen, könnte man die Oracle Technologie Data Masking verwenden. weitere Informationen dazu finden sich

Um beispielsweise das DDL Kommando zur Erzeugung einer Testtabelle zu generieren, reicht es aus das SQLcl Kommando DDL zu verwenden.

SQL> ddl emp

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") 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" ;

Möchte man das DDL Kommando ohne Segmentinformationen generieren, kann man vorab ein entsprechendes SET DDL absetzen. Das Ergebnis sieht dann folgendermaßen aus.

SQL> set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES off
SQL> set sqlformat insert
SQL> ddl scott.emp
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
        "T" NUMBER,
        "D" NUMBER,
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   )
  INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW
  DISTRIBUTE AUTO NO DUPLICATE ;

Um die entsprechenden INSERT Kommandos zu erhalten, nutzt man das SQLcl Kommando SET SQLFORMAT mit dem Wert INSERT. Danach reicht ein einfaches SELECT Kommando zur Generierung aus.

SQL> SET SQLFORMAT INSERT
SQL> select * from emp fetch first 5 rows only;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,T,D) values ('7369','SMITH','CLERK','7
902',to_timestamp('17.12.80','DD.MM.RR HH24:MI:SSXFF'),'800',null,'20',null,null);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,T,D) values ('7499','ALLEN','SALESMAN'
,'7698',to_timestamp('20.02.81','DD.MM.RR HH24:MI:SSXFF'),'1600','300','30',null,null);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,T,D) values ('7521','WARD','SALESMAN',
'7698',to_timestamp('22.02.81','DD.MM.RR HH24:MI:SSXFF'),'1250','500','30',null,null);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,T,D) values ('7566','JONES','MANAGER',
'7839',to_timestamp('02.04.81','DD.MM.RR HH24:MI:SSXFF'),'2975',null,'20',null,null);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,T,D) values ('7654','MARTIN','SALESMAN
','7698',to_timestamp('28.09.81','DD.MM.RR HH24:MI:SSXFF'),'1250','1400','30',null,null);

Zusammen mit einem SPOOL Kommando sieht der Ablauf dann folgendermaßen aus.

Die generierte Datei emptab.sql sieht dann folgendermaßen aus.

  CREATE TABLE "SCOTT"."EMP" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE, 
	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) ;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7369','SMITH','CLERK','7902',to_timestamp('17.12.80','DD.MM.RR HH24:MI:SSXFF'),'800',null,'20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7499','ALLEN','SALESMAN','7698',to_timestamp('20.02.81','DD.MM.RR HH24:MI:SSXFF'),'1600','300','30');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7521','WARD','SALESMAN','7698',to_timestamp('22.02.81','DD.MM.RR HH24:MI:SSXFF'),'1250','500','30');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7566','JONES','MANAGER','7839',to_timestamp('02.04.81','DD.MM.RR HH24:MI:SSXFF'),'2975',null,'20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7654','MARTIN','SALESMAN','7698',to_timestamp('28.09.81','DD.MM.RR HH24:MI:SSXFF'),'1250','1400','30');

Hinweis: Benötigt man Unterstützung bei der Anwendung der SQLcl Kommandos, kann man sich mit HELP die vollständige Syntax und teilweise auch hilfreiche Erklärungen anzeigen lassen. Folgendes Beispiel zeigt die Verwendung.

SQL> help set sqlformat
SET SQLFORMAT
  SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

Weitere Informationen

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services