1 package body GMA_EDITTEXT_PKG AS
2 /* $Header: GMACEDTB.pls 115.3 2003/06/03 17:53:02 kmoizudd noship $ */
3
4 Function Copy_Text(
5 X_Text_Code in NUMBER,
6 X_From_Text_Table in VARCHAR2,
7 X_To_Text_Table in VARCHAR2
8 ) Return Number
9 is
10 pragma AUTONOMOUS_TRANSACTION;
11
12 l_tl_sql_columns varchar2(200);
13 l_hdr_sql_columns varchar2(200);
14 l_check_text_rows varchar2(300);
15 l_Rows_Processed integer;
16 l_New_Text_Code number(15);
17 l_Tl_Sql_statement varchar2(4000);
18 l_Tl_Hdr_Sql_stmt varchar2(4000);
19 l_To_Hdr_Table varchar2(100);
20 l_From_Hdr_Table varchar2(100);
21 l_Cursor integer;
22
23 Begin
24
25 -- List all columns of TEXT table
26 l_tl_sql_columns:=' LANG_CODE,PARAGRAPH_CODE,SUB_PARACODE,LINE_NO,TEXT,LANGUAGE,SOURCE_LANG,
27 LAST_UPDATED_BY,CREATED_BY,LAST_UPDATE_LOGIN ';
28
29 l_hdr_sql_columns:= ' LAST_UPDATED_BY,CREATED_BY,LAST_UPDATE_LOGIN ';
30
31
32 l_Cursor := dbms_sql.open_cursor;
33
34 l_check_text_rows:= ' SELECT distinct TEXT_CODE ' ||
35 ' FROM '||X_From_Text_Table ||
36 ' WHERE Text_Code = :X_Text_Code';
37
38 dbms_sql.parse(l_Cursor,l_check_text_rows,0);
39
40 -- Modified the SQL stmt to use of Bind Variable,this improves the significant performance
41 -- Added by Khaja according to Project plan see bug 2935158
42 dbms_sql.bind_variable(l_Cursor, 'X_Text_Code', X_Text_Code);
43
44 l_Rows_processed:=dbms_sql.execute(l_Cursor);
45
46 -- dbms_output.put_line(l_rows_processed);
47
48 -- Process the new insert if rows exists otherwise no
49 IF dbms_sql.fetch_rows (l_Cursor) > 0 THEN
50
51 -- Generate new Text_code from sequence.
52 SELECT gem5_text_code_s.nextval into l_New_Text_code
53 FROM DUAL;
54
55 -- Main sql stmt which inserts all text lines, replacing text_code with sequence
56 l_TL_Sql_statement:= 'INSERT INTO '||X_To_Text_table||
57 ' ( '||
58 ' TEXT_CODE ' ||','||
59 l_TL_SQL_COLUMNS ||','||
60 ' CREATION_DATE' ||','||
61 ' LAST_UPDATE_DATE' ||
62 ' ) '||
63 ' SELECT :l_New_Text_Code '||','||
64 l_tl_sql_columns ||','||
65 'sysdate'||','||
66 'sysdate'||
67 ' FROM '||X_From_Text_Table ||
68 ' WHERE Text_Code = :X_Text_Code ';
69
70 dbms_sql.parse(l_Cursor,l_TL_Sql_statement,0);
71
72 -- Modified the SQL stmt to use of Bind Variable,this improves the significant performance
73 -- Added by Khaja according to Project plan see bug 2935158
74 dbms_sql.bind_variable(l_Cursor, 'l_New_Text_Code',l_New_Text_Code);
75 dbms_sql.bind_variable(l_Cursor, 'X_Text_Code',X_Text_Code);
76
77 l_Rows_processed:=dbms_sql.execute(l_Cursor);
78
79 -- Prepare the To and From Header table name from the given text table
80
81 l_From_Hdr_Table:=substr(X_From_Text_table,1,instr(upper(X_From_Text_table),'TEXT')+3)||'_HDR';
82 l_To_Hdr_Table:=substr(X_To_Text_table,1,instr(upper(X_To_Text_table),'TEXT')+3)||'_HDR';
83
84 -- GME header table name is different,not having name as _HDR, replacing it with _HEADER
85 -- see bug 2935005
86 IF upper(l_From_Hdr_Table)='GME_TEXT_HDR' then
87 l_From_Hdr_Table:='GME_TEXT_HEADER';
88 END IF;
89
90 IF upper(l_to_Hdr_Table)='GME_TEXT_HDR' then
91 l_To_Hdr_Table:='GME_TEXT_HEADER';
92 END IF;
93
94 -- Get ready with SQL stmt to insert row in header by replacing text_code with sequence
95 l_TL_HDR_Sql_stmt:= 'INSERT INTO '||l_To_Hdr_Table||
96 ' ( '||
97 ' TEXT_CODE' ||','||
98 l_hdr_sql_columns ||','||
99 ' CREATION_DATE' ||','||
100 ' LAST_UPDATE_DATE' ||
101 ' ) '||
102 ' SELECT :l_New_Text_Code '||','||
103 l_hdr_sql_columns ||','||
104 'sysdate'||','||
105 'sysdate'||
106 ' FROM '||l_From_Hdr_Table ||
107 ' WHERE Text_Code = :X_Text_Code ';
108
109 dbms_sql.parse(l_Cursor,l_TL_Hdr_Sql_stmt,0);
110
111 -- Modified the SQL stmt to use of Bind Variable,this improves the significant performance
112 -- Added by Khaja according to Project plan see bug 2935158
113 dbms_sql.bind_variable(l_Cursor, 'l_New_Text_Code',l_New_Text_Code);
114 dbms_sql.bind_variable(l_Cursor, 'X_Text_Code',X_Text_Code);
115
116 l_Rows_processed:=dbms_sql.execute(l_Cursor);
117
118 -- dbms_output.put_line(l_rows_processed);
119
120 dbms_sql.close_cursor(l_Cursor);
121 -- bug #2880608 placing a commit stmt as per Thomas Danial(GMD) for Session parameter request 4/1/03
122 commit;
123 Return l_New_Text_code;
124
125 ELSE
126 dbms_sql.close_cursor(l_Cursor);
127 -- bug #2880608 placing a commit stmt as per Thomas Danial(GMD) for Session parameter request 4/1/03
128 commit;
129 RETURN NULL;
130 END IF;
131
132 EXCEPTION
133 WHEN others THEN
134 IF dbms_sql.is_open (l_Cursor) THEN
135 dbms_sql.close_cursor (l_Cursor);
136 END IF;
137 Raise;
138
139 End Copy_Text;
140
141 Procedure Delete_Text(
142 X_Text_Code in NUMBER,
143 X_From_Text_Table in VARCHAR2
144 )
145 is
146 pragma AUTONOMOUS_TRANSACTION;
147
148 l_tl_sql_statement varchar2(1000);
149 l_from_hdr_Table varchar2(100);
150 l_tl_hdr_Sql_stmt varchar2(4000);
151 l_Rows_Processed integer;
152 l_Cursor integer;
153
154 Begin
155
156 -- Prepare the Delete stmt for text line
157 l_tl_sql_statement:=' DELETE FROM '|| X_From_Text_table ||
158 ' WHERE TEXT_CODE = :X_Text_Code ';
159
160 l_from_Hdr_table:=substr(X_From_Text_table,1,instr(upper(X_From_Text_table),'TEXT')+3)||'_HDR';
161
162 -- GME header table name is different,not having name as _HDR, replacing it with _HEADER
163 -- see bug 2935005
164 IF upper(l_From_Hdr_Table)='GME_TEXT_HDR' then
165 l_From_Hdr_Table:='GME_TEXT_HEADER';
166 END IF;
167
168 -- Prepare the Delete stmt for Header text line
169 l_tl_hdr_sql_stmt:=' DELETE FROM '|| l_From_hdr_table ||
170 ' WHERE TEXT_CODE =:X_Text_Code ';
171
172 l_Cursor := dbms_sql.open_cursor;
173
174 dbms_sql.parse(l_Cursor,l_tl_Sql_statement,0);
175
176 -- Modified the SQL stmt to use of Bind Variable,this improves the significant performance
177 -- Added by Khaja according to Project plan see bug 2935158
178 dbms_sql.bind_variable(l_Cursor, 'X_Text_Code',X_Text_Code);
179
180 l_Rows_processed:=dbms_sql.execute(l_Cursor);
181
182 dbms_sql.parse(l_Cursor,l_tl_hdr_sql_stmt,0);
183
184 -- Modified the SQL stmt to use of Bind Variable,this improves the significant performance
185 -- Added by Khaja according to Project plan see bug 2935158
186 dbms_sql.bind_variable(l_Cursor, 'X_Text_Code',X_Text_Code);
187
188 l_Rows_processed:=dbms_sql.execute(l_Cursor);
189
190 dbms_sql.close_cursor(l_Cursor);
191
192 -- bug #2880608 placing a commit stmt as per Thomas Danial(GMD) for Session parameter request 4/1/03
193 commit;
194
195 EXCEPTION
196 WHEN others THEN
197 IF dbms_sql.is_open (l_Cursor) THEN
198 dbms_sql.close_cursor (l_Cursor);
199 END IF;
200 Raise;
201
202 End Delete_Text;
203
204 End GMA_EDITTEXT_PKG;
[Home] [Help]