Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as vabb
SQL>
SQL> CREATE OR REPLACE Procedure VABB.Insert_Individual_departure(
2 p_date IN VARCHAR, --1
3 p_time IN VARCHAR, --2--------------------------
4 p_reg_no IN VARCHAR, --3
5 p_location_cd IN VARCHAR, --4
6 p_operator_cd IN VARCHAR, --5
7
8 p_flight_no IN VARCHAR, --6
9 p_nature IN VARCHAR, --7
10 p_pilot_in_command IN VARCHAR, --8
11 p_pob IN VARCHAR, --9
12 p_scheduled IN VARCHAR, --10
13
14 p_route_cd IN VARCHAR, --11
15 p_gcd IN VARCHAR, --12
16 p_position_type IN VARCHAR, --13
17 p_position_cd IN VARCHAR, --14
18 p_runway_no IN VARCHAR, --15
19
20 p_nop IN VARCHAR, --16
21 p_diverted_flight IN VARCHAR, --19
22 p_haj_flight IN VARCHAR, --20
23
24 p_chargeable_flight IN VARCHAR, --21
25 p_ficadc IN VARCHAR, --22
26 p_remarks IN VARCHAR, --23
27 p_transit_passengers IN VARCHAR, --24
28 p_touchgo_status IN VARCHAR, --25
29
30 p_uncontrolled_loc IN VARCHAR, --26
31 p_use_aaixray IN VARCHAR, --27
32 p_fullstop_landdate IN VARCHAR, --28
33 p_nooftouchdowns IN VARCHAR, --29
34 p_commoncounter IN VARCHAR, --30
35 p_watch_extension IN VARCHAR, --31
36 p_type IN VARCHAR, --32
37 p_start_time IN VARCHAR,
38 p_dtd_time IN VARCHAR,--------------------------
39
40
41
42 p_entered_by IN VARCHAR,-- This variable refers to the user id.
43 p_current_loc IN VARCHAR,-- This variable refers to the current airport code.
44 p_select_field OUT VARCHAR,-- To make the corresponding text box select automatically if any error occurs.
45 p_user_errors OUT VARCHAR -- To Propogate validation errors to the user.
46 )
47
48 AS
49
50 v_utc_datetime VARCHAR(16);
51 v_time_difference NUMBER;
52
53 v_fmuid VARCHAR(20);
54 v_flight_no VARCHAR(20);
55 v_position_type VARCHAR(2);
56 v_last_parked_datetime VARCHAR(16);
57
58 v_credit_facility CHAR;
59 v_free_facility CHAR;
60 v_billable_flag CHAR Default 'Y';
61 v_ca12_new CHAR Default 'T';
62 v_ca12_no VARCHAR(12);
63 v_bay_allocated VARCHAR(10);
64 v_prioritized CHAR;
65 v_srno Tc_Parking_Register.Srno%TYPE;
66 v_airport_type CHAR;
67
68 v_bay_parking NUMBER;
69 v_hangar_parking NUMBER;
70 v_open_parking NUMBER;
71
72
73 v_temp VARCHAR(1000);
74 v_condition VARCHAR(200);
75 v_ncb_status CHAR;
76 v_runway_no VARCHAR(10);
77 v_arr_watch_extension Number;
78
79 p_mail_loaded Number:=0;
80 p_cargo_loaded Number:=0;
81 p_entered_date Date:= Sysdate;
82 v_Delay Number(5);
83 v_DelayPb Number(5);
84
85 cursor c_arrival_details is select flight_no,arr_date,ata,dep_location,nature,arr_pob,mail_unloaded,cargo_unloaded,arr_gcd,operator_cd,scheduled,chargeable,WatchExt_Arr from tc_arrival_log where status='A' and Upper(Trim(reg_no))=Upper(Trim(p_reg_no));
86
87 c_arr_details c_arrival_details%rowtype;
88
89 user_ex Exception; --Single user defined exception to jump from the code and to inform the error to user.
90 Begin
91
92 --Setting the user errors to 0, means initially there are no errors.
93 p_user_errors := '0'; --0 means No Errors;
94
95 -- Step 1: To calculate the utc date and time and to find the differnce between the entered date and system date.
96 p_user_errors := check_datetime(p_date,p_time);
97 If(p_user_errors!='0') Then
98 p_select_field := 'document.forms[0].actualTime.select();';
99 raise user_ex;
100 End If;
101 --End of Step 1;
102
103 --Step 2: To check for Registration No.
104 p_user_errors := check_reg_no(p_reg_no);
105 If(p_user_errors!='0') Then
106 p_select_field := 'document.forms[0].Reg_No.select();';
107 raise user_ex;
108 End If;
109 --End of Step 2.
110
111
112 --Step 3: To check for Location.
113 p_user_errors := check_location(p_location_cd);
114 If(p_user_errors!='0') Then
115 p_select_field := 'document.forms[0].Dep_Location.select();';
116 raise user_ex;
117 End If;
118 --End of Step 3.
119
120 --Step 4: To check for Operator.
121 p_user_errors := check_operator(p_operator_cd);
122 If(p_user_errors!='0') Then
123 p_select_field := 'document.forms[0].OPERATOR_CD.select();';
124 raise user_ex;
125 End If;
126 --End of Step 4.
127
128 --The below condition is to check whether the arrival exists for a given registration no.
129 p_user_errors := check_arrival(p_reg_no);
130 If(p_user_errors!='0') Then
131 p_select_field := 'document.forms[0].Reg_No.select();';
132 raise user_ex;
133 End If;
134 --End of checking the arrival existence.
135
136 --Step 7: To check for last parking date and time.
137 v_last_parked_datetime := last_parking_datetime(p_reg_no);
138 v_time_difference := datetime_difference(p_date||' '||p_time,v_last_parked_datetime);
139 If(v_time_difference <0) Then
140 p_user_errors := 'Departing Date and Time cannot be less than or equal or equal to previous parking date and time '||v_last_parked_datetime;
141 p_select_field:= 'document.forms[0].date.select();';
142 raise user_ex;
143 End If;
144 --End of Step 7.
145
146 --Step 5: To check slot validation.
147 p_user_errors:= Slot_Checking(p_reg_no,p_date,p_time);
148 --p_user_errors := fn_check_slot(p_reg_no,p_date,p_time,'D',p_current_loc);
149 If(p_user_errors!='0') Then
150 --p_user_errors := 'Please change the Departure Date / Time. The Departure Date / Time entered is conflicting with an existing movement, or The Flight is currently not on Bay.';
151 p_select_field := 'document.forms[0].date.select();';
152 raise user_ex;
153 Else
154 p_user_errors := '0';
155 End If;
156 --End of Step 5.
157
158 --Step 6: To check for Last parked position.
159 v_position_type := last_parking_position(p_reg_no);
160 If(p_nature!='T' AND v_position_type='H') Then
161 p_user_errors:= 'This Flight Is Not in Bay. Please Change The Parking Position To Bay Before Departing.<Br>Use Parking Register.';
162 p_select_field:= 'document.forms[0].date.focus();';
163 raise user_ex;
164 End If;
165 -- End of Step 6.
166
167 --Step 8: To check for credit facility and free facility and billable flag.
168 v_condition := 'Upper(Trim(Operator_Cd))=Upper(Trim('''||p_operator_cd||''')) and Upper(Trim(Location_Cd))=Upper(Trim('''||p_current_loc||'''))';
169 v_credit_facility := get_description('Nvl(Decode(CreditFacility,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);
170
171 v_free_facility := get_description('Nvl(Decode(FreeFacility_Flag,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);
172
173 If(v_free_facility = 'Y') Then
174 v_billable_flag := 'N';
175 End If;
176 --End of Step 8.
177
178 --Step 9: To check whether ca12 no exists, if not generate a new ca12 no.
179 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
180
181 v_ca12_no := get_description('al.CA12_No','TC_Arrival_Log al,tc_ca12info ca',v_condition);
182
183 If(v_ca12_no != '0') Then
184 v_ca12_new := 'F';
185 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
186 v_ncb_status := get_description('ca.ncb_status','TC_Arrival_Log al,tc_ca12info ca',v_condition);
187
188 If(v_ncb_status='P') Then
189 p_user_errors := 'The Operator of this Aircraft does not have Credit Facility And Complete Bill should be raised before its departure.<br><font color=blue> Partial Bill has been Raised.<br> Either Non Credit bill or Landing and Parking charges bill has been raised.</font>';
190 p_select_field:= 'document.forms[0].date.select();';
191 raise user_ex;
192 End If;
193 Else
194 If(v_free_facility='N' AND v_credit_facility='N') Then
195 p_user_errors := 'Bill has not raised for this Aircraft. The Operator of this Aircraft does not have credit facility and bill should be raised before its departure.';
196 p_select_field:= 'document.forms[0].date.select();';
197 raise user_ex;
198 End If;
199 v_ca12_no := get_nextcode_gby('Tc_ca12info','ca12_no',10,date_format('YYMMDD'));
200 End If;
201 --End of Step 9.
202
203 /*
204 v_condition := 'Upper(Trim(Flight_No))=Upper(Trim('''||p_flight_no||''')) and (Status in (''OS'',''DL'') OR Status is Null) And Flight_Dt='''||p_date||''' and Dep_Location='''||p_current_loc||'''';
205 v_bay_allocated := get_description('Bay_Allocated','Tc_EstimatedFlights',v_condition);
206 v_prioritized := get_description('Prioritized','Tc_EstimatedFlights',v_condition);
207 */
208
209
210 /*
211 --Update estimated flights.
212 Update Tc_EstimatedFlights set Tow_Status='D', Tow_Time=p_time, Reg_No=Upper(Trim(p_reg_no)), Route_Cd=p_route_cd, Gcd=p_gcd Where Flight_no=p_flight_no and Flight_Dt=p_date and Dest_Location=Upper(Trim(p_location_cd)) and Dep_Location=p_current_loc;
213 */
214
215 v_condition := 'Upper(Trim(Reg_No))=Upper(Trim('''||p_reg_no||''')) and Status=''A''';
216 v_fmuid := get_description('Fmuid','Tc_Arrival_Log',v_condition);
217
218 --Step 10: To Insert/Update the corresponding tables.
219
220
221 --Insert into parking register.
222 v_srno := get_nextcode_gby('Tc_Parking_Register','Srno',11,date_format('YYMMDD'));
223
224 Insert into Tc_Parking_Register(Fmuid,Srno,Reg_No,Parking_Date,Position_Type,Position_Cd,Actual_time,Status,Entered_Dt,Entered_by) values (v_fmuid,v_srno,Upper(Trim(p_reg_no)),p_date,'D',v_bay_allocated,p_time,'A',p_entered_date, p_entered_by);
225
226 --Calculate parking hours.
227 v_airport_type := get_description('Airport_Type','Tc_Location_Mt','Location_Cd='''||p_current_loc||'''');
228
229 v_bay_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'B');
230 v_bay_parking := roundoff_minutes('P',v_airport_type,p_nature,'R', p_date,v_bay_parking);
231
232 v_condition:= 'od.operator_cd=mt.operator_cd and od.location_cd='''||p_current_loc||''' and Upper(Trim(mt.reg_no))=Upper(Trim('''||p_reg_no||'''))';
233
234 If(get_description('od.Use_AAI_Housing','TC_OPERATOR_DT od,TC_AIRCRAFT_MT mt',v_condition)='Y') Then
235 v_hangar_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'H');
236 v_hangar_parking := roundoff_minutes('H',v_airport_type,p_nature,'R', p_date,v_hangar_parking);
237 Else
238 v_hangar_parking := 0;
239 End If;
240
241 v_open_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'O');
242 v_open_parking := roundoff_minutes('E',v_airport_type,p_nature,'R', p_date,v_open_parking);
243
244
245 --End of Calculating parking hours.
246
247 --Update parking register.
248 Update Tc_parking_Register set Status='D' where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';
249
250 v_runway_no := p_runway_no;
251 if(Trim(p_runway_no)='' or p_runway_no is null) Then
252 v_runway_no := get_description('Rwy_No','Tc_Runway_Mt','Rownum <= 1');
253 End If;
254
255 --Insert into departure log.
256 select TIMEDIFFINMINS(p_time,p_dtd_time) d into v_Delay from dual;
257 select TIMEDIFFINMINS(p_time,p_start_time) dl into v_DelayPb from dual;
258 --DelayPbValue
259 /* Formatted on 2011/06/20 12:28 (Formatter Plus v4.8.8) */
260 INSERT INTO tc_departure_log
261 (fmuid, flight_no, dep_date, dep_reg_no,
262 dest_location, startuptime, atd, dtd,
263 Delay, delaypb, rwy_no, pilot_in_cmd, hyt_no,
264 pob, transit_pax, mail_loaded,
265 cargo_loaded, bay_allocated, prioritized, scheduled,
266 nature, touchgo_status, ca12_no, uncontrolled_location,
267 route_cd, dep_gcd, remarks, entered_by, entered_dt,
268 dep_nop, use_aai_xray, hajflight, operator_cd,
269 service_units, fullstop_landingdate,
270 chargeable, noof_touchdowns, common_counter, TYPE
271 )
272 VALUES (v_fmuid, p_flight_no, p_date, UPPER (TRIM (p_reg_no)),
273 UPPER (TRIM (p_location_cd)),p_start_time, p_time, p_dtd_time, v_Delay,
274 v_DelayPb, v_runway_no, p_pilot_in_command, p_ficadc, p_pob,
275 p_transit_passengers, p_mail_loaded, p_cargo_loaded,
276 v_bay_allocated, v_prioritized, p_scheduled, p_nature,
277 p_touchgo_status, v_ca12_no, p_uncontrolled_loc, p_route_cd,
278 p_gcd, p_remarks, p_entered_by, p_entered_date, p_nop,
279 NVL (p_use_aaixray, 'N'), p_haj_flight, p_operator_cd, '0',
280 p_fullstop_landdate, p_chargeable_flight,
281 NVL (p_nooftouchdowns, 0), NVL (p_commoncounter, 'N'), p_type
282 );
283 --The below two statements are to fetch corresponding arrival details.
284 open c_arrival_details;
285 fetch c_arrival_details into c_arr_details;
286
287 --Insert/Update ca12info.
288 If(v_ca12_new ='T') Then
289 Insert Into Tc_Ca12Info(Fmuid,Ca12_No,Reg_No,Arr_Flight_No,Arr_Date,Arr_Gmt,Dep_Location,Arr_Pob, Mail_Unloaded,Cargo_Unloaded,Arr_FlightNature,Dep_Flight_No,Dep_Date,Dep_GMT,Dest_Location,Dep_POB, Mail_Loaded,Cargo_Loaded,Dep_FlightNature,DepScheduled,Billable_Flag,Operator_Cd,Arr_Gcd,Dep_Gcd, Uncontrolled_Location,Manual_Flag,Use_AAI_Xray,Arr_Operator_Cd,Dep_Operator_Cd,ArrScheduled, Arr_Chargeable,Dep_Chargeable,Parking_Hrs,Housing_Hrs,Open_Parking_Hours,Noof_TouchDowns,Common_Counter,Arr_Watch_Hrs,Dep_Watch_Hrs,Type,Entered_By,Entered_dt) Values (v_fmuid,v_ca12_no,Upper(Trim(p_reg_no)),c_arr_details.flight_no,c_arr_details.arr_date,c_arr_details.ata, c_arr_details.dep_location,c_arr_details.Arr_Pob,c_arr_details.Mail_Unloaded, c_arr_details.Cargo_Unloaded,c_arr_details.Nature,p_flight_No,p_date,p_time,p_location_cd,p_pob, p_mail_loaded,p_cargo_loaded,p_nature,p_scheduled,v_billable_flag,p_operator_cd, c_arr_details.arr_gcd,p_gcd,p_uncontrolled_loc,'N',Nvl(p_use_aaixray,'N'),c_arr_details.operator_cd, p_operator_cd,c_arr_details.scheduled,c_arr_details.chargeable,p_chargeable_flight,v_bay_parking, v_hangar_parking,v_open_parking,Nvl(p_nooftouchdowns,0),Nvl(p_commoncounter,'N'),c_arr_details.WatchExt_Arr,p_watch_extension,p_type,p_entered_by,p_entered_date);
290
291 Else
292 If(v_credit_facility='Y') Then
293 Update TC_Ca12Info Set Dep_Flight_No=p_flight_no,Dep_Date=p_date,Dep_GMT=p_time,Dest_Location=Upper(Trim(p_location_cd)), Dep_POB=p_pob,Mail_Loaded=p_mail_loaded,Cargo_Loaded=p_cargo_loaded, Arr_Date=c_arr_details.arr_date,Arr_Gmt=c_arr_details.ata, Dep_FlightNature=p_nature,DepScheduled=p_scheduled,Parking_Hrs=v_bay_parking, Housing_Hrs=v_hangar_parking,Billable_Flag=v_billable_flag,Operator_Cd=p_operator_cd, DEP_GCD=p_gcd,Uncontrolled_Location=p_uncontrolled_loc,Manual_Flag='N',Entered_By=p_entered_by, Entered_Dt=p_entered_date,Open_Parking_Hours=v_open_parking,Use_AAI_Xray=Nvl(p_use_aaixray,'N'), Dep_Operator_Cd=p_operator_cd,Dep_Chargeable=p_chargeable_flight,Noof_TouchDowns=Nvl(p_nooftouchdowns,0),Common_Counter=Nvl(p_commoncounter,'N'),Arr_Watch_Hrs=c_arr_details.WatchExt_Arr,Dep_Watch_Hrs=p_watch_extension,Type=p_type where CA12_No=v_ca12_no;
294 --v_temp := 'updating ca12 '||v_ca12_new||' '||v_credit_facility;
295 End If;
296 End If;
297 --End of Step 10.
298 close c_arrival_details;
299
300 --Update Arrival Log.
301 Update Tc_Arrival_Log set Status='D',Ca12_No=v_ca12_no where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';
302
303 /***************************************************************************************************
304 --This block is for testing purpose, where ever you want test the value, just assign that value to v_temp variable.
305 If( v_temp!='0') Then
306 p_user_errors := v_temp;
307 p_select_field:= 'document.forms[0].date.focus();';
308 raise user_ex;
309 End If;
310 *****************************************************************************************************/
311 Commit;
312 Exception
313 When user_ex Then
314 rollback;
315 End;
316 /
Procedure created
SQL> Select To_Char(Parking_Date,'DD/Mon/YYYY')||' '||Actual_Time INTO v_last_parked_datetime from Tc_Parking_Register where (Srno,Reg_No) in (Select Max(Srno),Reg_No from Tc_Parking_Register where Status='A' and Upper(Trim(Reg_No))=Upper(Trim('5YKQQ')) group by Reg_No);
Select To_Char(Parking_Date,'DD/Mon/YYYY')||' '||Actual_Time INTO v_last_parked_datetime from Tc_Parking_Register where (Srno,Reg_No) in (Select Max(Srno),Reg_No from Tc_Parking_Register where Status='A' and Upper(Trim(Reg_No))=Upper(Trim('5YKQQ')) group by Reg_No)
ORA-00905: missing keyword
SQL> Select To_Char(Parking_Date,'DD/Mon/YYYY')||' '||Actual_Time from Tc_Parking_Register where (Srno,Reg_No) in (Select Max(Srno),Reg_No from Tc_Parking_Register where Status='A' and Upper(Trim(Reg_No))=Upper(Trim('5YKQQ')) group by Reg_No);
TO_CHAR(PARKING_DATE,'DD/MON/Y
------------------------------
SQL> SELECT * FROM Tc_Parking_Register WHERE Reg_No='5YKQQ';
FMUID SRNO REG_NO PARKING_DATE POSITION_TYPE POSITION_CD ACTUAL_TIME STATUS ENTERED_BY ENTERED_DT EFID
---------- ----------- -------- ------------ ------------- ------------ ----------- ------ ---------- ----------- ---------
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-----------
6/30/2011 1
SQL> SELECT to_date(SYSDATE,'dd/Mon/yyyy hh24mi') SYSDATE FROM DUAL;
SELECT to_date(SYSDATE,'dd/Mon/yyyy hh24mi') SYSDATE FROM DUAL
ORA-00923: FROM keyword not found where expected
SQL> SELECT to_date(SYSDATE,'dd/Mon/yyyy hh24mi') FROM DUAL;
TO_DATE(SYSDATE,'DD/MON/YYYYHH
------------------------------
6/30/0011
SQL> SELECT to_date(SYSDATE,'dd/Mon/yyyy hh24mi:SS') FROM DUAL;
TO_DATE(SYSDATE,'DD/MON/YYYYHH
------------------------------
6/30/0011
SQL> select to_char(sysdate,'HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'HH24:MI:SS')
-----------------------------
12:30:32
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD/MON/YYYYHH
------------------------------
30/Jun/2011 12:30:52
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24:MI') sysdate from dual;
TO_CHAR(SYSDATE,'DD/MON/YYYYHH
------------------------------
30/Jun/2011 12:30
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24:MI') sysdate from dual;
select to_char(sysdate,'dd/Mon/yyyy HH24:MI') sysdate from dual
ORA-00923: FROM keyword not found where expected
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24:MI') from dual;
TO_CHAR(SYSDATE,'DD/MON/YYYYHH
------------------------------
30/Jun/2011 12:31
SQL> SELECT to_date(SYSDATE,'dd/Mon/yyyy hh24:mi') FROM DUAL;
TO_DATE(SYSDATE,'DD/MON/YYYYHH
------------------------------
6/30/0011
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24:MI'),10) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
11 12:39
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24:MI'),9) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
011 12:45
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24:MI'),16) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
45
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24:MI'),18) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24:MI'),17) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
5
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24MI'),17) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
SQL> select SUBSTR(to_char(sysdate,'dd/Mon/yyyy HH24:MI'),17) from dual;
SUBSTR(TO_CHAR(SYSDATE,'DD/MON
------------------------------
5
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24:MI') sysdate from dual;
select to_char(sysdate,'dd/Mon/yyyy HH24:MI') sysdate from dual
ORA-00923: FROM keyword not found where expected
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24:MI') from dual;
TO_CHAR(SYSDATE,'DD/MON/YYYYHH
------------------------------
30/Jun/2011 12:55
SQL> select to_char(sysdate,'dd/Mon/yyyy HH24MI') from dual;
TO_CHAR(SYSDATE,'DD/MON/YYYYHH
------------------------------
30/Jun/2011 1255
SQL> Select To_Number(to_char(to_date(p_arr_datetime,'dd/Mon/yyyy HH24MI')-to_date(p_dep_datetime,'dd/Mon/yyyy HH24MI'))) t1 into v_time_difference From Dual;
Select To_Number(to_char(to_date(p_arr_datetime,'dd/Mon/yyyy HH24MI')-to_date(p_dep_datetime,'dd/Mon/yyyy HH24MI'))) t1 into v_time_difference From Dual
ORA-00904: "P_DEP_DATETIME": invalid identifier
SQL> edit
SQL> /
TO_NUMBER(TO_CHAR(TO_DATE('01/
------------------------------
-58.9618055555556
SQL> Select To_Number(to_char(to_date('01/Jan/2011 1250','dd/Mon/yyyy HH24MI')-to_date('01/Mar/2011 1155','dd/Mon/yyyy HH24MI'))) From Dual;
TO_NUMBER(TO_CHAR(TO_DATE('01/
------------------------------
-58.9618055555556
SQL> Select To_Number(to_date('01/Jan/2011 1250','dd/Mon/yyyy HH24MI')-to_date('01/Mar/2011 1155','dd/Mon/yyyy HH24MI')) From Dual;
TO_NUMBER(TO_DATE('01/JAN/2011
------------------------------
-58.9618055555556
SQL> Select To_Char(Sysdate,p_format) INTO v_format from Dual;
Select To_Char(Sysdate,p_format) INTO v_format from Dual
ORA-00904: "P_FORMAT": invalid identifier
SQL> Select To_Char(Sysdate,'DD/MON/YYYY HH24MI') from Dual;
TO_CHAR(SYSDATE,'DD/MON/YYYYHH
------------------------------
30/JUN/2011 1601
SQL> DESC DUAL;
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y
SQL> SELECT date_format('01/06/2011','dd/Mon/yyyy') from dual;
SELECT date_format('01/06/2011','dd/Mon/yyyy') from dual
ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_FORMAT'
SQL> SELECT date_format('01/06/2011','dd/Mon/yyyy') s from dual;
SELECT date_format('01/06/2011','dd/Mon/yyyy') s from dual
ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_FORMAT'
SQL> SELECT date_format('01/Jun/2011','dd/Mon/yyyy') s from dual;
SELECT date_format('01/Jun/2011','dd/Mon/yyyy') s from dual
ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_FORMAT'
SQL> SELECT date_format('01/Jun/2011','dd/Mon/yyyy') from dual;
SELECT date_format('01/Jun/2011','dd/Mon/yyyy') from dual
ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_FORMAT'
SQL> SELECT date_format(sysdate,'dd/Mon/yyyy') from dual;
SELECT date_format(sysdate,'dd/Mon/yyyy') from dual
ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_FORMAT'
SQL> SELECT date_format(sysdate,'dd/Mon/yyyy') from dual;
SELECT date_format(sysdate,'dd/Mon/yyyy') from dual
ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATE_FORMAT'
SQL> alter session nls_date_format='DD/MM/YYYY HH:MM:SS AM';
alter session nls_date_format='DD/MM/YYYY HH:MM:SS AM'
ORA-00922: missing or invalid option
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> commit;
Commit complete
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> commit;
Commit complete
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> COMMIT;
Commit complete
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> COMMIT;
Commit complete
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> COMMIT;
Commit complete
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> COMMIT;
Commit complete
SQL> alter session set nls_date_format='DD/MM/YYYY';
Session altered
SQL> COMMIT;
Commit complete
SQL> select datediff( 'mi','01/06/2011 1150' ,'02/06/2011 1250') minutes from dual;
select datediff( 'mi','01/06/2011 1150' ,'02/06/2011 1250') minutes from dual
ORA-01830: date format picture ends before converting entire input string
SQL> select datediff( 'mi','01/06/2011 1150' ,'02/06/2011 1250') minutes from dual;
select datediff( 'mi','01/06/2011 1150' ,'02/06/2011 1250') minutes from dual
ORA-01830: date format picture ends before converting entire input string
SQL> select datediff11( 'mi','01-06-2011 1150' ,'02/06/2011 1250') minutes from dual;
select datediff11( 'mi','01-06-2011 1150' ,'02/06/2011 1250') minutes from dual
ORA-01830: date format picture ends before converting entire input string
SQL>
SQL> declare
2 a date;
3 b date;
4 begin
5 a := sysdate;
6 dbms_lock.sleep(10); -- sleep about 10 seconds give or take
7 b := sysdate;
8
9 dbms_output.put_line( b-a || ' of a day has elapsed' );
10 dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
11 dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
12 dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
13 end;
14
15
16
17 /
declare
a date;
b date;
begin
a := sysdate;
dbms_lock.sleep(10); -- sleep about 10 seconds give or take
b := sysdate;
dbms_output.put_line( b-a || ' of a day has elapsed' );
dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
end;
ORA-06550: line 7, column 13:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 7, column 13:
PL/SQL: Statement ignored
SQL> set serveroutput on;
SQL>
SQL> declare
2 a date;
3 b date;
4 begin
5 a := sysdate;
6 dbms_lock.sleep(10); -- sleep about 10 seconds give or take
7 b := sysdate;
8
9 dbms_output.put_line( b-a || ' of a day has elapsed' );
10 dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
11 dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
12 dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
13 end;
14
15 /
declare
a date;
b date;
begin
a := sysdate;
dbms_lock.sleep(10); -- sleep about 10 seconds give or take
b := sysdate;
dbms_output.put_line( b-a || ' of a day has elapsed' );
dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
end;
ORA-06550: line 7, column 13:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 7, column 13:
PL/SQL: Statement ignored
SQL> select numtodsinterval(sysdate-sysdate,'day') time_difference from dates;
select numtodsinterval(sysdate-sysdate,'day') time_difference from dates
ORA-00942: table or view does not exist
SQL> alter session set nls_date_format='DD/MON/YYYY';
Session altered
SQL> COMMIT;
Commit complete
SQL>
No comments:
Post a Comment