1 package body ecx_xslt_utils as
2 -- $Header: ECXXSLTB.pls 120.2.12010000.2 2008/08/22 20:04:39 cpeixoto ship $
3
4 LAST_UPDATED_BY CONSTANT pls_integer := 0;
5 CREATED_BY CONSTANT pls_integer := 0;
6 LAST_UPDATE_LOGIN CONSTANT pls_integer := 0;
7 FILE_TYPE CONSTANT varchar2(50) := 'XSLT';
8
9 procedure ins
10 (
11 i_filename in varchar2,
12 i_version in varchar2,
13 i_application_code in varchar2,
14 i_payload in clob,
15 i_retcode OUT NOCOPY number,
16 i_retmsg OUT NOCOPY varchar2
17 )
18 is
19 i_id pls_integer;
20 i_creation_date date;
21 i_new_version number;
22 begin
23 -- if version is null find out the max for the given details
24 if (i_version is null)
25 then
26 begin
27 select id, creation_date, version
28 into i_id, i_creation_date, i_new_version
29 from ecx_files
30 where application_code = i_application_code
31 and version = (select max(version)
32 from ecx_files
33 where application_code = i_application_code
34 and name = i_filename
35 and type = FILE_TYPE)
36 and name = i_filename
37 and type = FILE_TYPE;
38 exception
39 when no_data_found then
40 -- since there is no max version present enter this in DB
41 -- with default version = 0.0
42 i_new_version := 0.0;
43 end;
44 else
45 -- if the data already exists in the table get the creation date and the id
46 -- and use this in the insert
47 begin
48 select id, creation_date
49 into i_id, i_creation_date
50 from ecx_files
51 where application_code = i_application_code
52 and version = i_version
53 and name = i_filename
54 and type = FILE_TYPE;
55
56 exception
57 when no_data_found then
58 -- this is not present in the DB so insert it
59 i_new_version := to_number(i_version);
60 null;
61 end;
62 end if;
63
64 if (i_id is not null AND i_creation_date is not null)
65 then
66 -- update the entry with the latest data
67 update ecx_files
68 set last_update_date = sysdate,
69 payload = i_payload
70 where id = i_id;
71 else
72 -- insert into ecx_files
73 insert into ecx_files
74 (
75 id,
76 type,
77 name,
78 version,
79 application_code,
80 last_update_date,
81 last_updated_by,
82 creation_date,
83 created_by,
84 last_update_login,
85 payload
86 )
87 values
88 (
89 ecx_files_s.nextval,
90 FILE_TYPE,
91 i_filename,
92 i_new_version,
93 i_application_code,
94 sysdate,
95 LAST_UPDATED_BY,
96 sysdate,
97 CREATED_BY,
98 LAST_UPDATE_LOGIN,
99 i_payload
100 );
101 end if;
102 i_retcode := 0;
103 i_retmsg := ' XSLT name = ' || i_filename || ' application_code = ' || i_application_code ||
104 ' version = ' || i_new_version || ' Successfully loaded';
105 exception
106 when others then
107 i_retcode := 2;
108 i_retmsg := SQLERRM || 'XSLT file cannot be loaded';
109 end ins;
110
111
112 procedure del
113 (
114 i_filename in varchar2,
115 i_version in varchar2,
116 i_application_code in varchar2,
117 i_retcode OUT NOCOPY number,
118 i_retmsg OUT NOCOPY varchar2
119 )
120 is
121
122 begin
123 -- if version is null, delete the max version entry that matches the details
124 if (i_version is null)
125 then
126 delete from ecx_files
127 where application_code = i_application_code
128 and version = (select max(version)
129 from ecx_files
130 where application_code = i_application_code
131 and name = i_filename
132 and type = FILE_TYPE)
133 and name = i_filename
134 and type = FILE_TYPE;
135 else
136 delete from ecx_files
137 where application_code = i_application_code
138 and (i_version is null or version = i_version)
139 and name = i_filename
140 and type = FILE_TYPE;
141 end if;
142
143 if(sql%rowcount = 0)
144 then
145 i_retcode := 2;
146 i_retmsg := ' XSLT does not Exist';
147 else
148 i_retcode := 0;
149 i_retmsg := ' XSLT Successfully Deleted';
150 end if;
151 exception
152 when others then
153 i_retcode := 2;
154 i_retmsg := SQLERRM || ' XSLT cannot be deleted';
155 end del;
156
157 end ecx_xslt_utils;
[Home] [Help]