· 8 years ago · Dec 13, 2017, 07:30 AM
1--------------------------------------------------------
2-- File created - Wednesday-December-13-2017
3--------------------------------------------------------
4DROP TABLE "FILES" cascade constraints;
5DROP TABLE "FOLDER" cascade constraints;
6DROP SEQUENCE "FILE_SEQ";
7DROP SEQUENCE "NAME_OF_SEQUENCE";
8DROP FUNCTION "CD";
9DROP FUNCTION "DEL";
10DROP FUNCTION "LF";
11DROP FUNCTION "LS";
12DROP FUNCTION "MKDIR";
13DROP FUNCTION "MKFILE";
14DROP TYPE "MIGR_FILTER";
15DROP TYPE "MIGR_FILTER_SET";
16DROP TYPE "MIGR_REPORT_DETAIL_ROW";
17DROP TYPE "MIGR_REPORT_DETAIL_TABLE";
18DROP TYPE "MIGR_REPORT_SUM_ROW";
19DROP TYPE "MIGR_REPORT_SUM_TABLE";
20DROP TYPE "NAMELIST";
21DROP TYPE "NAME_AND_COUNT_ARRAY";
22DROP TYPE "NAME_AND_COUNT_T";
23DROP TYPE "OBJECTIDLIST";
24--------------------------------------------------------
25-- DDL for Type MIGR_FILTER
26--------------------------------------------------------
27
28 CREATE OR REPLACE TYPE "MIGR_FILTER" IS OBJECT (
29 FILTER_TYPE INTEGER, -- Filter Types are 0-> ALL, 1->NAMELIST, 2->WHERE CLAUSE, 3->OBJECTID LIST
30 OBJTYPE VARCHAR2(40),
31 OBJECTIDS OBJECTIDLIST,
32 NAMES NAMELIST,
33 WHERECLAUSE VARCHAR2(1000));
34
35/
36
37/
38--------------------------------------------------------
39-- DDL for Type MIGR_FILTER_SET
40--------------------------------------------------------
41
42 CREATE OR REPLACE TYPE "MIGR_FILTER_SET" IS TABLE OF MIGR_FILTER;
43
44/
45
46/
47--------------------------------------------------------
48-- DDL for Type MIGR_REPORT_DETAIL_ROW
49--------------------------------------------------------
50
51 CREATE OR REPLACE TYPE "MIGR_REPORT_DETAIL_ROW" AS OBJECT
52 (CAPTURED_ID NUMBER(38),
53 CAPTURED_NAME VARCHAR2(4000),
54 CONVERTED_NAME VARCHAR2(4000),
55 CAPTURED_TYPE VARCHAR2(4000),
56 CONVERTED_TYPE VARCHAR2(4000),
57 CAPTURE_STATUS VARCHAR2(20),
58 CONVERT_STATUS VARCHAR2(20),
59 GENERATE_STATUS VARCHAR2(20),
60 LOGTEXT VARCHAR2(4000)
61 );
62
63/
64
65/
66--------------------------------------------------------
67-- DDL for Type MIGR_REPORT_DETAIL_TABLE
68--------------------------------------------------------
69
70 CREATE OR REPLACE TYPE "MIGR_REPORT_DETAIL_TABLE" AS TABLE OF MIGR_REPORT_DETAIL_ROW;
71
72/
73
74/
75--------------------------------------------------------
76-- DDL for Type MIGR_REPORT_SUM_ROW
77--------------------------------------------------------
78
79 CREATE OR REPLACE TYPE "MIGR_REPORT_SUM_ROW" AS OBJECT
80 (LABEL VARCHAR2(50),
81 SCHEMA_SUM NUMBER,
82 TABLE_SUM NUMBER,
83 INDEX_SUM NUMBER,
84 CONSTRAINT_SUM NUMBER,
85 VIEW_SUM NUMBER,
86 TRIGGER_SUM NUMBER,
87 SP_SUM NUMBER
88 );
89
90/
91
92/
93--------------------------------------------------------
94-- DDL for Type MIGR_REPORT_SUM_TABLE
95--------------------------------------------------------
96
97 CREATE OR REPLACE TYPE "MIGR_REPORT_SUM_TABLE" AS TABLE OF MIGR_REPORT_SUM_ROW;
98
99/
100
101/
102--------------------------------------------------------
103-- DDL for Type NAMELIST
104--------------------------------------------------------
105
106 CREATE OR REPLACE TYPE "NAMELIST" IS TABLE OF VARCHAR2(40);
107
108/
109
110/
111--------------------------------------------------------
112-- DDL for Type NAME_AND_COUNT_ARRAY
113--------------------------------------------------------
114
115 CREATE OR REPLACE TYPE "NAME_AND_COUNT_ARRAY" IS VARRAY(30) OF name_and_count_t;
116
117/
118
119/
120--------------------------------------------------------
121-- DDL for Type NAME_AND_COUNT_T
122--------------------------------------------------------
123
124 CREATE OR REPLACE TYPE "NAME_AND_COUNT_T" IS OBJECT (
125 OBJECT_NAME varchar2(30),
126 UPDATE_COUNT INTEGER);
127
128/
129
130/
131--------------------------------------------------------
132-- DDL for Type OBJECTIDLIST
133--------------------------------------------------------
134
135 CREATE OR REPLACE TYPE "OBJECTIDLIST" IS TABLE OF INTEGER;
136
137/
138
139/
140--------------------------------------------------------
141-- DDL for Sequence FILE_SEQ
142--------------------------------------------------------
143
144 CREATE SEQUENCE "FILE_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ;
145/
146--------------------------------------------------------
147-- DDL for Sequence NAME_OF_SEQUENCE
148--------------------------------------------------------
149
150 CREATE SEQUENCE "NAME_OF_SEQUENCE" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 201 CACHE 100 NOORDER NOCYCLE ;
151/
152--------------------------------------------------------
153-- DDL for Table FILES
154--------------------------------------------------------
155
156 CREATE TABLE "FILES"
157 ( "ID_PARENT" NUMBER(7,0),
158 "CONTENT" VARCHAR2(200 BYTE),
159 "NAME" VARCHAR2(20 BYTE)
160 ) ;
161/
162--------------------------------------------------------
163-- DDL for Table FOLDER
164--------------------------------------------------------
165
166 CREATE TABLE "FOLDER"
167 ( "ID_FOLDER" NUMBER(7,0),
168 "NUME_FOLDER" VARCHAR2(20 CHAR),
169 "PARENT_FOLDER" NUMBER(7,0)
170 ) ;
171/
172REM INSERTING into FILES
173SET DEFINE OFF;
174REM INSERTING into FOLDER
175SET DEFINE OFF;
176Insert into FOLDER (ID_FOLDER,NUME_FOLDER,PARENT_FOLDER) values (102,'bin',0);
177Insert into FOLDER (ID_FOLDER,NUME_FOLDER,PARENT_FOLDER) values (0,'root',null);
178--------------------------------------------------------
179-- DDL for Index TABLE1_PK
180--------------------------------------------------------
181
182 CREATE UNIQUE INDEX "TABLE1_PK" ON "FOLDER" ("ID_FOLDER")
183 ;
184/
185--------------------------------------------------------
186-- Constraints for Table FOLDER
187--------------------------------------------------------
188
189 ALTER TABLE "FOLDER" MODIFY ("ID_FOLDER" NOT NULL ENABLE);
190
191 ALTER TABLE "FOLDER" ADD CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID_FOLDER") ENABLE;
192/
193--------------------------------------------------------
194-- Constraints for Table FILES
195--------------------------------------------------------
196
197 ALTER TABLE "FILES" MODIFY ("ID_PARENT" NOT NULL ENABLE);
198/
199--------------------------------------------------------
200-- Ref Constraints for Table FILES
201--------------------------------------------------------
202
203 ALTER TABLE "FILES" ADD CONSTRAINT "FILES_FOLDER_FK1" FOREIGN KEY ("ID_PARENT")
204 REFERENCES "FOLDER" ("ID_FOLDER") ENABLE;
205/
206--------------------------------------------------------
207-- Ref Constraints for Table FOLDER
208--------------------------------------------------------
209
210 ALTER TABLE "FOLDER" ADD CONSTRAINT "FOLDER_FOLDER_FK1" FOREIGN KEY ("PARENT_FOLDER")
211 REFERENCES "FOLDER" ("ID_FOLDER") ENABLE;
212/
213--------------------------------------------------------
214-- DDL for Function CD
215--------------------------------------------------------
216
217 CREATE OR REPLACE FUNCTION "CD"
218(
219 ID_PARINTE IN NUMBER
220, NUME IN VARCHAR2
221) RETURN number
222 is
223 cd_id number;
224BEGIN
225 cd_id:= -1;
226 if nume ='..' then
227 select id_folder into cd_id from folder where id_folder = id_parinte;
228 else
229 select id_folder into cd_id from folder where parent_folder = id_parinte;
230 end if;
231 return cd_id;
232 Exception
233 WHEN NO_DATA_FOUND THEN
234 dbms_output.put_line('no file found.');
235
236END CD;
237
238/
239
240/
241--------------------------------------------------------
242-- DDL for Function DEL
243--------------------------------------------------------
244
245 CREATE OR REPLACE FUNCTION "DEL"
246(
247 ID_PARINTE IN NUMBER
248, NUME IN VARCHAR2
249) RETURN varchar2
250 is
251 deleted varchar2(20);
252BEGIN
253 Declare cd_id number;
254 begin
255 cd_id := -1;
256 select id_folder into cd_id from folder where parent_folder = id_parinte and nume_folder = nume;
257 if cd_id > -1 then
258 DELETE FROM folder
259 WHERE id_folder = cd_id;
260 return ('Deleted'|| deleted);
261 else
262 select id_parent into cd_id from files where id_parent = id_parinte and name = nume;
263
264 if cd_id > -1 then
265 DELETE FROM files
266 WHERE id_parent = id_parinte;
267 return ('Deleted'|| deleted);
268 else
269 return ('Not found'|| deleted);
270 end if;
271 end if;
272
273end;
274END DEL;
275
276/
277
278/
279--------------------------------------------------------
280-- DDL for Function LF
281--------------------------------------------------------
282
283 CREATE OR REPLACE FUNCTION "LF"
284(
285 ID_PARENT IN NUMBER
286, NAME1 IN VARCHAR2
287) RETURN varchar2
288 is
289 content1 varchar2(20);
290BEGIN
291 select files.content INTO content1 from files where files.id_parent=ID_PARENT and files.name = NAME1;
292 return content1;
293
294 Exception
295 WHEN NO_DATA_FOUND THEN
296 dbms_output.put_line('no file found.');
297END LF;
298
299/
300
301/
302--------------------------------------------------------
303-- DDL for Function LS
304--------------------------------------------------------
305
306 CREATE OR REPLACE FUNCTION "LS"
307(
308 ID_PARINTE IN NUMBER
309) RETURN varchar2
310 is
311 filesname varchar2(200);
312 begin
313 DECLARE
314 foldername folder.nume_folder%type;
315 filename files.name%type;
316 CURSOR filenames
317 IS
318 SELECT name
319 FROM files
320 WHERE id_parent = ID_PARINTE;
321 CURSOR foldernames
322 IS
323 SELECT nume_folder
324 FROM folder
325 WHERE parent_folder = ID_PARINTE;
326BEGIN
327 open foldernames;
328 FOR foldername
329 IN foldernames
330 LOOP
331 filesname := filesname || ' ';
332 END LOOP;
333 close foldernames;
334 FOR filename
335 IN filenames
336 LOOP
337 filesname := filesname ||' ' || filename;
338 END LOOP;
339END;
340 return null;
341END LS;
342
343/
344
345/
346--------------------------------------------------------
347-- DDL for Function MKDIR
348--------------------------------------------------------
349
350 CREATE OR REPLACE FUNCTION "MKDIR"
351(
352 ID_PARINTE IN NUMBER
353, NUME_FOLDER IN VARCHAR2
354) return varchar2
355 is
356 created varchar2(20);
357Begin
358
359 insert into folder values(name_of_sequence.nextval,NUME_FOLDER,ID_PARINTE);
360 RETURN ('Dir Added'||created);
361 Exception
362 WHEN dup_val_on_index THEN
363 dbms_output.put_line('Name already exists');
364 WHEN others THEN
365 if sqlcode = -02291 then
366 dbms_output.put_line('Error!');
367 end if;
368END MKDIR;
369
370/
371
372/
373--------------------------------------------------------
374-- DDL for Function MKFILE
375--------------------------------------------------------
376
377 CREATE OR REPLACE FUNCTION "MKFILE"
378(
379 ID_PARENT IN NUMBER
380, NUME IN VARCHAR2
381, COTENT IN VARCHAR2
382) RETURN varchar2
383 is
384 created varchar2(20);
385BEGIN
386 insert into folder values(ID_PARENT,NUME,COTENT);
387 RETURN ('Folder Added'||created);
388
389 Exception
390 WHEN dup_val_on_index THEN
391 dbms_output.put_line('Name already exists');
392 WHEN others THEN
393 if sqlcode = -02291 then
394 dbms_output.put_line('Error!');
395 end if;
396END MKFILE;
397
398/
399
400/