· 8 years ago · Dec 06, 2017, 06:12 AM
1CREATE OR REPLACE PROCEDURE CD
2(
3 PARENT_ID IN NUMBER DEFAULT NULL
4, DIR_NAME IN VARCHAR2
5) AS
6 CURRENT_FOLDER_ID NUMBER;
7 NEW_FOLDER_ID NUMBER;
8 NEW_FOLDER_NAME VARCHAR(128);
9BEGIN
10 IF PARENT_ID IS NULL THEN
11 CURRENT_FOLDER_ID:=FILESYSPCKG.CURRENT_FOLDER;
12 ELSE
13 CURRENT_FOLDER_ID:=PARENT_ID;
14 END IF;
15
16 IF CURRENT_FOLDER_ID IS NOT NULL THEN
17 IF DIR_NAME='..' THEN
18 SELECT PARENT_ID INTO NEW_FOLDER_ID FROM FILESYSTEM WHERE ID=CURRENT_FOLDER_ID;
19 SELECT FILENAME INTO NEW_FOLDER_NAME FROM FILESYSTEM WHERE ID=NEW_FOLDER_ID;
20 DBMS_OUTPUT.PUT_LINE('Current dir: ' || NEW_FOLDER_NAME);
21 ELSE
22 SELECT ID INTO NEW_FOLDER_ID FROM FILESYSTEM WHERE PARENT_ID=CURRENT_FOLDER_ID AND FILENAME=DIR_NAME AND TYPE=1;
23 DBMS_OUTPUT.PUT_LINE('Current dir: ' || DIR_NAME);
24 END IF;
25
26 FILESYSPCKG.CURRENT_FOLDER:=NEW_FOLDER_ID;
27
28 END IF;
29
30 exception
31 WHEN no_data_found THEN
32 DBMS_OUTPUT.PUT_LINE('No such directory: ' || DIR_NAME);
33
34 WHEN too_many_rows THEN
35 DBMS_OUTPUT.PUT_LINE('Unexpected error: This should not be happening!');
36
37END CD;
38
39
40
41
42///////////////////////////////////
43--------------------------------------------------------
44-- File created - Wednesday-November-22-2017
45--------------------------------------------------------
46--------------------------------------------------------
47-- DDL for Type MIGR_FILTER
48--------------------------------------------------------
49
50 CREATE OR REPLACE TYPE "MIGR_FILTER" IS OBJECT (
51 FILTER_TYPE INTEGER, -- Filter Types are 0-> ALL, 1->NAMELIST, 2->WHERE CLAUSE, 3->OBJECTID LIST
52 OBJTYPE VARCHAR2(40),
53 OBJECTIDS OBJECTIDLIST,
54 NAMES NAMELIST,
55 WHERECLAUSE VARCHAR2(1000));
56
57/
58
59--------------------------------------------------------
60-- DDL for Type MIGR_FILTER_SET
61--------------------------------------------------------
62
63 CREATE OR REPLACE TYPE "MIGR_FILTER_SET" IS TABLE OF MIGR_FILTER;
64
65/
66
67--------------------------------------------------------
68-- DDL for Type MIGR_REPORT_DETAIL_ROW
69--------------------------------------------------------
70
71 CREATE OR REPLACE TYPE "MIGR_REPORT_DETAIL_ROW" AS OBJECT
72 (CAPTURED_ID NUMBER(38),
73 CAPTURED_NAME VARCHAR2(4000),
74 CONVERTED_NAME VARCHAR2(4000),
75 CAPTURED_TYPE VARCHAR2(4000),
76 CONVERTED_TYPE VARCHAR2(4000),
77 CAPTURE_STATUS VARCHAR2(20),
78 CONVERT_STATUS VARCHAR2(20),
79 GENERATE_STATUS VARCHAR2(20),
80 LOGTEXT VARCHAR2(4000)
81 );
82
83/
84
85--------------------------------------------------------
86-- DDL for Type MIGR_REPORT_DETAIL_TABLE
87--------------------------------------------------------
88
89 CREATE OR REPLACE TYPE "MIGR_REPORT_DETAIL_TABLE" AS TABLE OF MIGR_REPORT_DETAIL_ROW;
90
91/
92
93--------------------------------------------------------
94-- DDL for Type MIGR_REPORT_SUM_ROW
95--------------------------------------------------------
96
97 CREATE OR REPLACE TYPE "MIGR_REPORT_SUM_ROW" AS OBJECT
98 (LABEL VARCHAR2(50),
99 SCHEMA_SUM NUMBER,
100 TABLE_SUM NUMBER,
101 INDEX_SUM NUMBER,
102 CONSTRAINT_SUM NUMBER,
103 VIEW_SUM NUMBER,
104 TRIGGER_SUM NUMBER,
105 SP_SUM NUMBER
106 );
107
108/
109
110--------------------------------------------------------
111-- DDL for Type MIGR_REPORT_SUM_TABLE
112--------------------------------------------------------
113
114 CREATE OR REPLACE TYPE "MIGR_REPORT_SUM_TABLE" AS TABLE OF MIGR_REPORT_SUM_ROW;
115
116/
117
118--------------------------------------------------------
119-- DDL for Type NAMELIST
120--------------------------------------------------------
121
122 CREATE OR REPLACE TYPE "NAMELIST" IS TABLE OF VARCHAR2(40);
123
124/
125
126--------------------------------------------------------
127-- DDL for Type NAME_AND_COUNT_ARRAY
128--------------------------------------------------------
129
130 CREATE OR REPLACE TYPE "NAME_AND_COUNT_ARRAY" IS VARRAY(30) OF name_and_count_t;
131
132/
133
134--------------------------------------------------------
135-- DDL for Type NAME_AND_COUNT_T
136--------------------------------------------------------
137
138 CREATE OR REPLACE TYPE "NAME_AND_COUNT_T" IS OBJECT (
139 OBJECT_NAME varchar2(30),
140 UPDATE_COUNT INTEGER);
141
142/
143
144--------------------------------------------------------
145-- DDL for Type OBJECTIDLIST
146--------------------------------------------------------
147
148 CREATE OR REPLACE TYPE "OBJECTIDLIST" IS TABLE OF INTEGER;
149
150/
151
152--------------------------------------------------------
153-- DDL for Table DUMMY
154--------------------------------------------------------
155
156 CREATE TABLE "DUMMY"
157 ( "ID" NUMBER,
158 "NAME" VARCHAR2(20 BYTE),
159 "INFO" VARCHAR2(20 BYTE)
160 ) ;
161--------------------------------------------------------
162-- DDL for Table FILESYSTEM
163--------------------------------------------------------
164
165 CREATE TABLE "FILESYSTEM"
166 ( "ID" NUMBER,
167 "FILENAME" VARCHAR2(128 BYTE),
168 "TYPE" NUMBER,
169 "PARENT_ID" NUMBER
170 ) ;
171--------------------------------------------------------
172-- DDL for Table FILES_DATA
173--------------------------------------------------------
174
175 CREATE TABLE "FILES_DATA"
176 ( "ID" NUMBER,
177 "FILE_ID" NUMBER,
178 "DATA" BLOB
179 ) ;
180--------------------------------------------------------
181-- DDL for Table FILE_TYPES
182--------------------------------------------------------
183
184 CREATE TABLE "FILE_TYPES"
185 ( "ID" NUMBER,
186 "NAME" VARCHAR2(20 BYTE),
187 "DESCRIPTION" VARCHAR2(128 BYTE)
188 ) ;
189REM INSERTING into DUMMY
190Insert into DUMMY (ID,NAME,INFO) values (1,'TEST','TEEST');
191Insert into DUMMY (ID,NAME,INFO) values (2,'TEST2','TEST2');
192Insert into DUMMY (ID,NAME,INFO) values (3,'N/A','TESTNA');
193Insert into DUMMY (ID,NAME,INFO) values (4,'SOMETEST','TESTTTT');
194REM INSERTING into FILESYSTEM
195Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (1,'root',1,null);
196Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (2,'folder1',1,1);
197Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (3,'folder2',1,1);
198Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (4,'file1.txt',2,1);
199Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (5,'subfolder11',1,2);
200Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (6,'subfile11.txt',2,2);
201Insert into FILESYSTEM (ID,FILENAME,TYPE,PARENT_ID) values (7,'subfolder21',1,3);
202REM INSERTING into FILE_TYPES
203Insert into FILE_TYPES (ID,NAME,DESCRIPTION) values (1,'DIR','Normal directory');
204Insert into FILE_TYPES (ID,NAME,DESCRIPTION) values (2,'FILE','Normal file');
205Insert into FILE_TYPES (ID,NAME,DESCRIPTION) values (3,'RESERVED','Reserved');
206--------------------------------------------------------
207-- DDL for Index FILESYSTEM_PK
208--------------------------------------------------------
209
210 CREATE UNIQUE INDEX "FILESYSTEM_PK" ON "FILESYSTEM" ("ID")
211 ;
212--------------------------------------------------------
213-- DDL for Index FILES_DATA_PK
214--------------------------------------------------------
215
216 CREATE UNIQUE INDEX "FILES_DATA_PK" ON "FILES_DATA" ("ID")
217 ;
218--------------------------------------------------------
219-- DDL for Index FILESYSTEM_UK1
220--------------------------------------------------------
221
222 CREATE UNIQUE INDEX "FILESYSTEM_UK1" ON "FILESYSTEM" ("FILENAME", "PARENT_ID")
223 ;
224--------------------------------------------------------
225-- DDL for Index FILE_TYPES_PK
226--------------------------------------------------------
227
228 CREATE UNIQUE INDEX "FILE_TYPES_PK" ON "FILE_TYPES" ("ID")
229 ;
230--------------------------------------------------------
231-- Constraints for Table FILE_TYPES
232--------------------------------------------------------
233
234 ALTER TABLE "FILE_TYPES" ADD CONSTRAINT "FILE_TYPES_PK" PRIMARY KEY ("ID") ENABLE;
235
236 ALTER TABLE "FILE_TYPES" MODIFY ("ID" NOT NULL ENABLE);
237--------------------------------------------------------
238-- Constraints for Table FILESYSTEM
239--------------------------------------------------------
240
241 ALTER TABLE "FILESYSTEM" ADD CONSTRAINT "FILESYSTEM_PK" PRIMARY KEY ("ID") ENABLE;
242
243 ALTER TABLE "FILESYSTEM" ADD CONSTRAINT "FILESYSTEM_UK1" UNIQUE ("FILENAME", "PARENT_ID") ENABLE;
244
245 ALTER TABLE "FILESYSTEM" MODIFY ("ID" NOT NULL ENABLE);
246--------------------------------------------------------
247-- Constraints for Table FILES_DATA
248--------------------------------------------------------
249
250 ALTER TABLE "FILES_DATA" ADD CONSTRAINT "FILES_DATA_PK" PRIMARY KEY ("ID") ENABLE;
251
252 ALTER TABLE "FILES_DATA" MODIFY ("ID" NOT NULL ENABLE);
253--------------------------------------------------------
254-- Ref Constraints for Table FILESYSTEM
255--------------------------------------------------------
256
257 ALTER TABLE "FILESYSTEM" ADD CONSTRAINT "FILESYSTEM_FILESYSTEM_FK1" FOREIGN KEY ("PARENT_ID")
258 REFERENCES "FILESYSTEM" ("ID") ON DELETE CASCADE ENABLE;
259
260 ALTER TABLE "FILESYSTEM" ADD CONSTRAINT "FILESYSTEM_FILE_TYPES_FK1" FOREIGN KEY ("TYPE")
261 REFERENCES "FILE_TYPES" ("ID") ON DELETE CASCADE ENABLE;
262--------------------------------------------------------
263-- Ref Constraints for Table FILES_DATA
264--------------------------------------------------------
265
266 ALTER TABLE "FILES_DATA" ADD CONSTRAINT "FILES_DATA_FILESYSTEM_FK1" FOREIGN KEY ("FILE_ID")
267 REFERENCES "FILESYSTEM" ("ID") ON DELETE CASCADE ENABLE;
268--------------------------------------------------------
269-- DDL for Package FILESYSPCKG
270--------------------------------------------------------
271
272 CREATE OR REPLACE PACKAGE "FILESYSPCKG" AS
273
274CURRENT_FOLDER NUMBER;
275
276END FILESYSPCKG;
277
278/
279
280--------------------------------------------------------
281-- DDL for Package Body FOLDERE
282--------------------------------------------------------
283
284 CREATE OR REPLACE PACKAGE BODY "FOLDERE"
285AS
286function load return varchar2 is
287begin
288currentFolder := 1;
289lastId := 8;
290end load;
291
292FUNCTION cd(
293 fName VARCHAR2)
294 RETURN VARCHAR2
295IS
296 idFile NUMBER;
297BEGIN
298 SELECT ID_F INTO idFile FROM foldere WHERE NAME = fName;
299 currentFolder := idFile;
300 RETURN ('New folder is ' || fName);
301EXCEPTION
302WHEN NO_DATA_FOUND THEN
303 RETURN ('The folder' || fName || 'does not exist ');
304END cd;
305
306FUNCTION ls
307 RETURN VARCHAR2 IS
308 cursor c1 is select NAME from foldere where ID_P = currentFolder;
309 textValue varchar2(1000);
310 fileN varchar2(20);
311 BEGIN
312 fetch c1 into FILEN;
313 while c1%found loop
314 textValue := textValue || chr(10) || fileN;
315 fetch c1 into FILEN;
316 end loop;
317 RETURN (textValue);
318END ls;
319function lf(fileName VARCHAR2) return varchar2 is
320contentF varchar2(1000);
321begin
322select content_file into contentF from foldere where id_file = fileName and CONTENT_FILE != null;
323exception when NO_DATA_FOUND then return 'The file doesn`t exist';
324end lf;
325
326FUNCTION mkdir(dir VARCHAR2) RETURN VARCHAR2 IS
327idFile number;
328BEGIN
329 select id_file into idFile from foldere where name_file = dir and INFO = null;
330 return 'A folder with the same name already exists';
331 exception when NO_DATA_FOUND then
332 begin
333 INSERT INTO foldere (ID_F, NAME, , ID_P)
334 VALUES (lastId+1,dir,null, currentFolder);
335 commit;
336 return 'The folder was created.';
337 end;
338END mkdir;
339function del(fName varchar2) return VARCHAR2 is
340idF number;
341begin
342select id_file into idF from foldere where fName = NAME and id_parent = ID_P;
343delete from foldere where NAME = fName and ID_P = currentFolder;
344commit;
345return 'The file/folder has been deleted';
346exception WHEN NO_DATA_FOUND then return 'The file doesn`t exist';
347end del;
348END pfoldere;
349
350/
351
352--------------------------------------------------------
353-- DDL for Package Body PFOLDERE
354--------------------------------------------------------
355
356 CREATE OR REPLACE PACKAGE BODY "PFOLDERE"
357AS
358function load return varchar2 is
359begin
360currentFolder := 1;
361lastId := 8;
362end load;
363
364FUNCTION cd(
365 fName VARCHAR2)
366 RETURN VARCHAR2
367IS
368 idFile NUMBER;
369BEGIN
370 SELECT id_file INTO idFile FROM foldere WHERE NAME_FILE = fName;
371 currentFolder := idFile;
372 RETURN ('New folder is ' || fName);
373EXCEPTION
374WHEN NO_DATA_FOUND THEN
375 RETURN ('The folder' || fName || 'does not exist ');
376END cd;
377
378FUNCTION ls
379 RETURN VARCHAR2 IS
380 cursor c1 is select Name_file from foldere where id_parent = currentFolder;
381 textValue varchar2(1000);
382 fileN varchar2(20);
383 BEGIN
384 fetch c1 into FILEN;
385 while c1%found loop
386 textValue := textValue || chr(10) || fileN;
387 fetch c1 into FILEN;
388 end loop;
389 RETURN (textValue);
390END ls;
391function lf(fileName VARCHAR2) return varchar2 is
392contentF varchar2(1000);
393begin
394select content_file into contentF from foldere where id_file = fileName and CONTENT_FILE != null;
395exception when NO_DATA_FOUND then return 'The file doesn`t exist';
396end lf;
397
398FUNCTION mkdir(dir VARCHAR2) RETURN VARCHAR2 IS
399idFile number;
400BEGIN
401 select id_file into idFile from foldere where name_file = dir and CONTENT_FILE = null;
402 return 'A folder with the same name already exists';
403 exception when NO_DATA_FOUND then
404 begin
405 INSERT INTO foldere (ID_FILE, NAME_FILE, CONTENT_FILE, ID_PARENT)
406 VALUES (lastId+1,dir,null, currentFolder);
407 commit;
408 return 'The folder was created.';
409 end;
410END mkdir;
411function del(fName varchar2) return VARCHAR2 is
412idF number;
413begin
414select id_file into idF from foldere where fName = NAME_FILE and id_parent = ID_PARENT;
415delete from foldere where NAME_FILE = fName and ID_PARENT = currentFolder;
416commit;
417return 'The file/folder has been deleted';
418exception WHEN NO_DATA_FOUND then return 'The file doesn`t exist';
419end del;
420END pfoldere;
421
422/
423
424--------------------------------------------------------
425-- DDL for Package Body PFOLDERS
426--------------------------------------------------------
427
428 CREATE OR REPLACE PACKAGE BODY "PFOLDERS" AS
429FUNCTION load RETURN varchar2 AS
430begin
431currentFolder := 1;
432lastId := 8;
433end load;
434
435FUNCTION cd(
436 fName VARCHAR2)
437 RETURN VARCHAR2
438IS
439 idFile NUMBER;
440BEGIN
441 SELECT id INTO idFile FROM folders WHERE NAME = fName;
442 currentFolder := idFile;
443 RETURN ('New folder is ' || fName);
444EXCEPTION
445WHEN NO_DATA_FOUND THEN
446 RETURN ('The folder' || fName || 'does not exist ');
447END cd;
448
449FUNCTION ls
450 RETURN VARCHAR2 IS
451 cursor c1 is select NAME from folders where IDP = currentFolder;
452 textValue varchar2(1000);
453 fileN varchar2(20);
454 BEGIN
455 fetch c1 into fileN;
456 while c1%found loop
457 textValue := textValue || chr(10) || fileN;
458 fetch c1 into FILEN;
459 end loop;
460 RETURN (textValue);
461END ls;
462
463function lf(fileName VARCHAR2) return varchar2 is
464contentF varchar2(1000);
465begin
466select content into contentF from folders where id = fileName and content != null;
467exception when NO_DATA_FOUND then return 'The file doesn`t exist';
468end lf;
469
470FUNCTION mkdir(dir VARCHAR2) RETURN VARCHAR2 IS
471idFile number;
472BEGIN
473 select id into idFile from folders where name = dir;
474 return 'A folder with the same name already exists';
475 exception when NO_DATA_FOUND then
476 begin
477 INSERT INTO folders (ID, NAME,IDP) VALUES (lastId+1,dir,null, currentFolder);
478 commit;
479 return 'The folder was created.';
480 end;
481END mkdir;
482
483function del(fName varchar2) return VARCHAR2 is
484idF number;
485begin
486select id into idF from folders where fName = NAME and id_parent = IDP;
487delete from folders where NAME = fName and IDP = currentFolder;
488commit;
489return 'The file/folder has been deleted';
490exception WHEN NO_DATA_FOUND then return 'The file doesn`t exist';
491end del;
492END PFOLDERS;
493
494/
495
496--------------------------------------------------------
497-- DDL for Package Body Pfolders
498--------------------------------------------------------
499
500 CREATE OR REPLACE PACKAGE BODY "Pfolders" AS
501FUNCTION load RETURN varchar2 AS
502begin
503currentFolder := 1;
504lastId := 8;
505end load;
506
507FUNCTION cd(
508 fName VARCHAR2)
509 RETURN VARCHAR2
510IS
511 idFile NUMBER;
512BEGIN
513 SELECT id INTO idFile FROM folders WHERE NAME = fName;
514 currentFolder := idFile;
515 RETURN ('New folder is ' || fName);
516EXCEPTION
517WHEN NO_DATA_FOUND THEN
518 RETURN ('The folder' || fName || 'does not exist ');
519END cd;
520
521FUNCTION ls
522 RETURN VARCHAR2 IS
523 cursor c1 is select NAME from folders where IDP = currentFolder;
524 textValue varchar2(1000);
525 fileN varchar2(20);
526 BEGIN
527 fetch c1 into fileN;
528 while c1%found loop
529 textValue := textValue || chr(10) || fileN;
530 fetch c1 into FILEN;
531 end loop;
532 RETURN (textValue);
533END ls;
534
535function lf(fileName VARCHAR2) return varchar2 is
536contentF varchar2(1000);
537begin
538select content into contentF from folders where id = fileName and content != null;
539exception when NO_DATA_FOUND then return 'The file doesn`t exist';
540end lf;
541
542FUNCTION mkdir(dir VARCHAR2) RETURN VARCHAR2 IS
543idFile number;
544BEGIN
545 select id into idFile from folders where name = dir;
546 return 'A folder with the same name already exists';
547 exception when NO_DATA_FOUND then
548 begin
549 INSERT INTO folders (ID, NAME,IDP) VALUES (lastId+1,dir,null, currentFolder);
550 commit;
551 return 'The folder was created.';
552 end;
553END mkdir;
554
555function del(fName varchar2) return VARCHAR2 is
556idF number;
557begin
558select id into idF from folders where fName = NAME and id_parent = IDP;
559delete from folders where NAME = fName and IDP = currentFolder;
560commit;
561return 'The file/folder has been deleted';
562exception WHEN NO_DATA_FOUND then return 'The file doesn`t exist';
563end del;
564END Pfolders;
565
566/
567
568--------------------------------------------------------
569-- DDL for Procedure CD
570--------------------------------------------------------
571set define off;
572
573 CREATE OR REPLACE PROCEDURE "CD"
574(
575 PARENT_ID IN NUMBER DEFAULT NULL
576, DIR_NAME IN VARCHAR2
577) AS
578 CURRENT_FOLDER_ID NUMBER;
579 NEW_FOLDER_ID NUMBER;
580 NEW_FOLDER_NAME VARCHAR(128);
581BEGIN
582 IF PARENT_ID IS NULL THEN
583 CURRENT_FOLDER_ID:=FILESYSPCKG.CURRENT_FOLDER;
584 ELSE
585 CURRENT_FOLDER_ID:=PARENT_ID;
586 END IF;
587
588 IF CURRENT_FOLDER_ID IS NOT NULL THEN
589 IF DIR_NAME='..' THEN
590 SELECT PARENT_ID INTO NEW_FOLDER_ID FROM FILESYSTEM WHERE ID=CURRENT_FOLDER_ID;
591 SELECT FILENAME INTO NEW_FOLDER_NAME FROM FILESYSTEM WHERE ID=NEW_FOLDER_ID;
592 DBMS_OUTPUT.PUT_LINE('Current dir: ' || NEW_FOLDER_NAME);
593 ELSE
594 SELECT ID INTO NEW_FOLDER_ID FROM FILESYSTEM WHERE PARENT_ID=CURRENT_FOLDER_ID AND FILENAME=DIR_NAME AND TYPE=1;
595 DBMS_OUTPUT.PUT_LINE('Current dir: ' || DIR_NAME);
596 END IF;
597
598 FILESYSPCKG.CURRENT_FOLDER:=NEW_FOLDER_ID;
599
600 END IF;
601
602 exception
603 WHEN no_data_found THEN
604 DBMS_OUTPUT.PUT_LINE('No such directory: ' || DIR_NAME);
605
606 WHEN too_many_rows THEN
607 DBMS_OUTPUT.PUT_LINE('Unexpected error: This should not be happening!');
608
609END CD;
610
611/
612
613--------------------------------------------------------
614-- DDL for Procedure HELLO_WORLD
615--------------------------------------------------------
616set define off;
617
618 CREATE OR REPLACE PROCEDURE "HELLO_WORLD" AS
619BEGIN
620 DBMS_OUTPUT.PUT_LINE('Hello world from proc!!!');
621END HELLO_WORLD;
622
623/
624
625--------------------------------------------------------
626-- DDL for Procedure PRINT_ALL
627--------------------------------------------------------
628set define off;
629
630 CREATE OR REPLACE PROCEDURE "PRINT_ALL" AS
631BEGIN
632 FOR ent IN (SELECT * FROM DUMMY) LOOP
633 DBMS_OUTPUT.PUT_LINE('Name: ' || ent.NAME);
634 DBMS_OUTPUT.PUT_LINE('Desc: ' || ent.info);
635 DBMS_OUTPUT.PUT_LINE('----------------');
636 END LOOP;
637END PRINT_ALL;
638
639/
640
641////////////////////////////////////////////////
642
643
644--------------------------------------------------------
645-- File created - Wednesday-November-22-2017
646--------------------------------------------------------
647--------------------------------------------------------
648-- DDL for Type MIGR_FILTER
649--------------------------------------------------------
650
651 CREATE OR REPLACE TYPE "MIGR_FILTER" IS OBJECT (
652 FILTER_TYPE INTEGER, -- Filter Types are 0-> ALL, 1->NAMELIST, 2->WHERE CLAUSE, 3->OBJECTID LIST
653 OBJTYPE VARCHAR2(40),
654 OBJECTIDS OBJECTIDLIST,
655 NAMES NAMELIST,
656 WHERECLAUSE VARCHAR2(1000));
657
658/
659
660--------------------------------------------------------
661-- DDL for Type MIGR_FILTER_SET
662--------------------------------------------------------
663
664 CREATE OR REPLACE TYPE "MIGR_FILTER_SET" IS TABLE OF MIGR_FILTER;
665
666/
667
668--------------------------------------------------------
669-- DDL for Type MIGR_REPORT_DETAIL_ROW
670--------------------------------------------------------
671
672 CREATE OR REPLACE TYPE "MIGR_REPORT_DETAIL_ROW" AS OBJECT
673 (CAPTURED_ID NUMBER(38),
674 CAPTURED_NAME VARCHAR2(4000),
675 CONVERTED_NAME VARCHAR2(4000),
676 CAPTURED_TYPE VARCHAR2(4000),
677 CONVERTED_TYPE VARCHAR2(4000),
678 CAPTURE_STATUS VARCHAR2(20),
679 CONVERT_STATUS VARCHAR2(20),
680 GENERATE_STATUS VARCHAR2(20),
681 LOGTEXT VARCHAR2(4000)
682 );
683
684/
685
686--------------------------------------------------------
687-- DDL for Type MIGR_REPORT_DETAIL_TABLE
688--------------------------------------------------------
689
690 CREATE OR REPLACE TYPE "MIGR_REPORT_DETAIL_TABLE" AS TABLE OF MIGR_REPORT_DETAIL_ROW;
691
692/
693
694--------------------------------------------------------
695-- DDL for Type MIGR_REPORT_SUM_ROW
696--------------------------------------------------------
697
698 CREATE OR REPLACE TYPE "MIGR_REPORT_SUM_ROW" AS OBJECT
699 (LABEL VARCHAR2(50),
700 SCHEMA_SUM NUMBER,
701 TABLE_SUM NUMBER,
702 INDEX_SUM NUMBER,
703 CONSTRAINT_SUM NUMBER,
704 VIEW_SUM NUMBER,
705 TRIGGER_SUM NUMBER,
706 SP_SUM NUMBER
707 );
708
709/
710
711--------------------------------------------------------
712-- DDL for Type MIGR_REPORT_SUM_TABLE
713--------------------------------------------------------
714
715 CREATE OR REPLACE TYPE "MIGR_REPORT_SUM_TABLE" AS TABLE OF MIGR_REPORT_SUM_ROW;
716
717/
718
719--------------------------------------------------------
720-- DDL for Type NAMELIST
721--------------------------------------------------------
722
723 CREATE OR REPLACE TYPE "NAMELIST" IS TABLE OF VARCHAR2(40);
724
725/
726
727--------------------------------------------------------
728-- DDL for Type NAME_AND_COUNT_ARRAY
729--------------------------------------------------------
730
731 CREATE OR REPLACE TYPE "NAME_AND_COUNT_ARRAY" IS VARRAY(30) OF name_and_count_t;
732
733/
734
735--------------------------------------------------------
736-- DDL for Type NAME_AND_COUNT_T
737--------------------------------------------------------
738
739 CREATE OR REPLACE TYPE "NAME_AND_COUNT_T" IS OBJECT (
740 OBJECT_NAME varchar2(30),
741 UPDATE_COUNT INTEGER);
742
743/
744
745--------------------------------------------------------
746-- DDL for Type OBJECTIDLIST
747--------------------------------------------------------
748
749 CREATE OR REPLACE TYPE "OBJECTIDLIST" IS TABLE OF INTEGER;
750
751/
752
753--------------------------------------------------------
754-- DDL for Table DUMMY
755--------------------------------------------------------
756
757 CREATE TABLE "DUMMY"
758 ( "ID" NUMBER,
759 "NAME" VARCHAR2(20 BYTE),
760 "INFO" VARCHAR2(20 BYTE)
761 ) ;
762REM INSERTING into DUMMY
763Insert into DUMMY (ID,NAME,INFO) values (1,'TEST','TEEST');
764Insert into DUMMY (ID,NAME,INFO) values (2,'TEST2','TEST2');
765Insert into DUMMY (ID,NAME,INFO) values (3,'N/A','TESTNA');
766Insert into DUMMY (ID,NAME,INFO) values (4,'SOMETEST','TESTTTT');
767--------------------------------------------------------
768-- DDL for Procedure HELLO_WORLD
769--------------------------------------------------------
770set define off;
771
772 CREATE OR REPLACE PROCEDURE "HELLO_WORLD" AS
773BEGIN
774 DBMS_OUTPUT.PUT_LINE('Hello world from proc!!!');
775END HELLO_WORLD;
776
777/
778
779--------------------------------------------------------
780-- DDL for Procedure PRINT_ALL
781--------------------------------------------------------
782set define off;
783
784 CREATE OR REPLACE PROCEDURE "PRINT_ALL" AS
785BEGIN
786 FOR ent IN (SELECT * FROM DUMMY) LOOP
787 DBMS_OUTPUT.PUT_LINE('Name: ' || ent.NAME);
788 DBMS_OUTPUT.PUT_LINE('Desc: ' || ent.info);
789 DBMS_OUTPUT.PUT_LINE('----------------');
790 END LOOP;
791END PRINT_ALL;
792
793/