| 1 | -- |
|---|
| 2 | --CALL schema_util.revoke_all_privs('UTIL'); |
|---|
| 3 | -- |
|---|
| 4 | CREATE OR REPLACE PACKAGE util AUTHID CURRENT_USER AS |
|---|
| 5 | -- |
|---|
| 6 | -- Contact: Doug Given, Allan Walter USGS |
|---|
| 7 | -- |
|---|
| 8 | -- Used in production: yes, UTC version |
|---|
| 9 | -- |
|---|
| 10 | -- <LIST DEPENDENT APPLICATIONS HERE> |
|---|
| 11 | -- unknown ? |
|---|
| 12 | -- |
|---|
| 13 | -- <DEPENDS ON> (loaded on server) |
|---|
| 14 | -- NCEDC Parametric tables |
|---|
| 15 | -- package TRUETIME WHERES |
|---|
| 16 | -- Java org.trinet.util.EpochTime |
|---|
| 17 | -- |
|---|
| 18 | -- --------------------------------------------------------------------------- |
|---|
| 19 | -- --------------------------------------------------------------------------- |
|---|
| 20 | -- Return count of waveforms associated with an event. |
|---|
| 21 | -- Example: select UTIL.waveformcount(1111) from dual; |
|---|
| 22 | FUNCTION waveformCount(p_evid IN Event.evid%TYPE) RETURN NUMBER; |
|---|
| 23 | -- |
|---|
| 24 | -- --------------------------------------------------------------------------- |
|---|
| 25 | -- --------------------------------------------------------------------------- |
|---|
| 26 | -- Return count of amplitudes associated with an event. |
|---|
| 27 | -- Example: select UTIL.ampcount(1111) from dual; |
|---|
| 28 | FUNCTION ampCount(p_evid IN Event.evid%TYPE) RETURN NUMBER; |
|---|
| 29 | -- |
|---|
| 30 | -- --------------------------------------------------------------------------- |
|---|
| 31 | -- --------------------------------------------------------------------------- |
|---|
| 32 | -- Return an epoch time as a default string of form "yyyy-MM-dd HH:mm:ss.SSS". |
|---|
| 33 | -- Example: select UTIL.epochtostring(0.) from dual; |
|---|
| 34 | -- Epoch times < 0 are for dates before Jan 1, 1970. |
|---|
| 35 | FUNCTION epochToString(epochsecs NUMBER) RETURN VARCHAR2; |
|---|
| 36 | -- |
|---|
| 37 | -- --------------------------------------------------------------------------- |
|---|
| 38 | -- --------------------------------------------------------------------------- |
|---|
| 39 | -- Return an epoch time as a string of form <pattern> in UTC timezone. |
|---|
| 40 | -- Example: select UTIL.epochtostring(0.,'dd-MM-yyyy HH:mm') from dual; |
|---|
| 41 | FUNCTION epochToString(epochsecs NUMBER, pattern VARCHAR2) RETURN VARCHAR2; |
|---|
| 42 | -- |
|---|
| 43 | -- --------------------------------------------------------------------------- |
|---|
| 44 | -- --------------------------------------------------------------------------- |
|---|
| 45 | -- Return an epoch time as a string of form <pattern> in the given timezone. |
|---|
| 46 | -- Example: select UTIL.epochtostring(0.,'dd-MM-yyyy hh:mm', 'PST') from dual; |
|---|
| 47 | FUNCTION epochToString(epochsecs NUMBER, pattern VARCHAR2, timezone VARCHAR2) RETURN VARCHAR2; |
|---|
| 48 | -- |
|---|
| 49 | -- --------------------------------------------------------------------------- |
|---|
| 50 | -- --------------------------------------------------------------------------- |
|---|
| 51 | -- Return epoch time parsed from the input time string in the input pattern. |
|---|
| 52 | -- NOTE: "ff" format will not work here. |
|---|
| 53 | -- Example: select UTIL.stringtoepoch('01-01-1970 00:00:00.00', 'dd-MM-yyyy hh:mm:ss.ss') from dual; |
|---|
| 54 | FUNCTION stringToEpoch(timeString VARCHAR2, pattern VARCHAR2) RETURN NUMBER; |
|---|
| 55 | -- |
|---|
| 56 | -- --------------------------------------------------------------------------- |
|---|
| 57 | -- --------------------------------------------------------------------------- |
|---|
| 58 | -- Return the local session time zone DATE equivalent to input UTC date string of the specified format. |
|---|
| 59 | FUNCTION utcstring2date(v_datestr VARCHAR2, v_format VARCHAR2) RETURN DATE; |
|---|
| 60 | -- |
|---|
| 61 | ------------------------------------------------------------------------------------------------------ |
|---|
| 62 | ------------------------------------------------------------------------------------------------------ |
|---|
| 63 | -- Return the local DATE equivalent the input epoch LEAP seconds (UTC). |
|---|
| 64 | FUNCTION true2date(v_epochsecs NUMBER) RETURN DATE; |
|---|
| 65 | -- |
|---|
| 66 | -- --------------------------------------------------------------------------- |
|---|
| 67 | -- --------------------------------------------------------------------------- |
|---|
| 68 | -- Return epoch LEAP seconds equivalent to the input local session DATE. |
|---|
| 69 | FUNCTION date2true(v_date DATE) RETURN NUMBER; |
|---|
| 70 | -- |
|---|
| 71 | -- --------------------------------------------------------------------------- |
|---|
| 72 | -- --------------------------------------------------------------------------- |
|---|
| 73 | -- Return the local DATE equivalent the input epoch NOMINAL seconds (UTC). |
|---|
| 74 | FUNCTION nominal2date(v_epochsecs NUMBER) RETURN DATE; |
|---|
| 75 | -- |
|---|
| 76 | -- --------------------------------------------------------------------------- |
|---|
| 77 | -- --------------------------------------------------------------------------- |
|---|
| 78 | -- Return epoch NOMINAL seconds equivalent to the input local session DATE. |
|---|
| 79 | FUNCTION date2nominal(v_date DATE) RETURN NUMBER; |
|---|
| 80 | -- |
|---|
| 81 | ------------------------------------------------------------------------------------------------------ |
|---|
| 82 | ------------------------------------------------------------------------------------------------------ |
|---|
| 83 | -- Return UTC date string equivalent to the input DATE in the DEFAULT (TRUETIME) format. |
|---|
| 84 | FUNCTION date2utcstring(v_date DATE) RETURN VARCHAR2; |
|---|
| 85 | -- |
|---|
| 86 | ------------------------------------------------------------------------------------------------------ |
|---|
| 87 | ------------------------------------------------------------------------------------------------------ |
|---|
| 88 | -- Return UTC date string equivalent to the input DATE in the format specified. |
|---|
| 89 | FUNCTION date2utcstring(v_date DATE, v_format VARCHAR2) RETURN VARCHAR2; |
|---|
| 90 | -- |
|---|
| 91 | -- --------------------------------------------------------------------------- |
|---|
| 92 | -- --------------------------------------------------------------------------- |
|---|
| 93 | -- Return String for station name plus closest town. |
|---|
| 94 | -- Example: select UTIL.longname('CI','PAS') from dual; |
|---|
| 95 | FUNCTION longName(p_net VARCHAR2, p_sta VARCHAR2) RETURN VARCHAR2; |
|---|
| 96 | -- |
|---|
| 97 | -- --------------------------------------------------------------------------- |
|---|
| 98 | -- --------------------------------------------------------------------------- |
|---|
| 99 | -- Print input strings showing index where they first differ |
|---|
| 100 | -- ( e.g. length of shortest string) else prints 'strings identical'. |
|---|
| 101 | PROCEDURE print_str_diff(p_str_1 VARCHAR2, p_str_2 VARCHAR2); |
|---|
| 102 | -- |
|---|
| 103 | -- --------------------------------------------------------------------------- |
|---|
| 104 | -- --------------------------------------------------------------------------- |
|---|
| 105 | -- Return index where input strings first differ (or length of shortest string) |
|---|
| 106 | -- return value is -1 if input strings are identical. |
|---|
| 107 | FUNCTION str_diff(p_str_1 VARCHAR2, p_str_2 VARCHAR2) RETURN PLS_INTEGER; |
|---|
| 108 | -- |
|---|
| 109 | /* Procedure to print long character strings using DBMS_OUTPUT |
|---|
| 110 | Author: Oleg Savkin Nov 2005 |
|---|
| 111 | Parameters: |
|---|
| 112 | IN_TEXT - text to print |
|---|
| 113 | IN_TEXT_LENGTH - output string length. Default is 255 (maximum allowed for DBMS_OUTPUT) |
|---|
| 114 | IN_DIVIDER - divider between words. |
|---|
| 115 | Used to do not split the whole word when start new print line |
|---|
| 116 | Default is SPACE |
|---|
| 117 | IN_NEW_LINE - new line divider. If there is this divider withing string to print out, then |
|---|
| 118 | string will be first printed till this divider, and then start from new line. |
|---|
| 119 | Default NULL |
|---|
| 120 | Examples: |
|---|
| 121 | |
|---|
| 122 | print_out(<text>); |
|---|
| 123 | print_out(<text>, 80); |
|---|
| 124 | print_out(<text>, 20); |
|---|
| 125 | print_out(<text>, 255, ' '); |
|---|
| 126 | print_out(<text>, 250, ' ', chr(10)); |
|---|
| 127 | |
|---|
| 128 | Last example: print text breaking it by spaces. |
|---|
| 129 | If there is new line character within test, it will be printed on the different line. |
|---|
| 130 | */ |
|---|
| 131 | PROCEDURE print_out( IN_TEXT VARCHAR2, |
|---|
| 132 | IN_TEXT_LENGTH NUMBER DEFAULT 255, |
|---|
| 133 | IN_DIVIDER VARCHAR2 DEFAULT CHR(32), |
|---|
| 134 | IN_NEW_LINE VARCHAR2 DEFAULT NULL); |
|---|
| 135 | -- --------------------------------------------------------------------------- |
|---|
| 136 | -- --------------------------------------------------------------------------- |
|---|
| 137 | -- |
|---|
| 138 | -- Pragma below means package in not persistent and variable values are not |
|---|
| 139 | -- retained between calls. See Oracle8 Bible, pg. 481 |
|---|
| 140 | -- Otherwise, it would persist for duration of a session. |
|---|
| 141 | -- PRAGMA SERIALLY_REUSABLE; |
|---|
| 142 | END util; |
|---|
| 143 | |
|---|
| 144 | / |
|---|
| 145 | -- |
|---|
| 146 | SHOW ERRORS; |
|---|
| 147 | -- |
|---|
| 148 | CREATE OR REPLACE PACKAGE BODY util AS |
|---|
| 149 | P_TRUETIME_FORMAT CONSTANT VARCHAR2(21) := 'YYYY/MM/DD HH24:MI:SS'; |
|---|
| 150 | -- --------------------------------------------------------------------------- |
|---|
| 151 | -- --------------------------------------------------------------------------- |
|---|
| 152 | FUNCTION waveformCount(p_evid IN Event.evid%TYPE) RETURN NUMBER AS |
|---|
| 153 | -- |
|---|
| 154 | v_cnt Number := 0; |
|---|
| 155 | -- |
|---|
| 156 | BEGIN |
|---|
| 157 | SELECT count(wfid) INTO v_cnt FROM AssocWaE WHERE evid = p_evid; |
|---|
| 158 | -- |
|---|
| 159 | RETURN v_cnt; |
|---|
| 160 | -- |
|---|
| 161 | END; |
|---|
| 162 | -- --------------------------------------------------------------------------- |
|---|
| 163 | -- --------------------------------------------------------------------------- |
|---|
| 164 | -- NOTE: Amps are associated with Origins and not Events(AssocAmO) |
|---|
| 165 | -- So we must first lookup the preferred origin of the event then count its amps. |
|---|
| 166 | FUNCTION ampCount(p_evid IN Event.evid%TYPE) RETURN NUMBER AS |
|---|
| 167 | -- |
|---|
| 168 | v_cnt Number := 0; |
|---|
| 169 | -- |
|---|
| 170 | BEGIN |
|---|
| 171 | -- Instead of outer join subquery ? How about: |
|---|
| 172 | -- SELECT count(ao.ampid) INTO v_cnt FROM AssocAmO ao, Event e |
|---|
| 173 | -- WHERE ao.orid = e.prefor and e.evid = p_evid; -- aww |
|---|
| 174 | -- --------------------------------------------------------------------------- |
|---|
| 175 | SELECT count(ampid) INTO v_cnt FROM AssocAmO WHERE orid = ( |
|---|
| 176 | SELECT event.prefor FROM event, origin where |
|---|
| 177 | (event.prefor = origin.orid(+)) AND (event.evid = p_evid) |
|---|
| 178 | ) ; |
|---|
| 179 | -- |
|---|
| 180 | RETURN v_cnt; |
|---|
| 181 | -- |
|---|
| 182 | END; |
|---|
| 183 | -- --------------------------------------------------------------------------- |
|---|
| 184 | -- --------------------------------------------------------------------------- |
|---|
| 185 | FUNCTION epochToString(epochsecs NUMBER) RETURN VARCHAR2 |
|---|
| 186 | AS LANGUAGE JAVA |
|---|
| 187 | NAME 'org.trinet.util.EpochTime.epochToString(double) return String'; |
|---|
| 188 | -- ---------------------------------------------------------------------------- |
|---|
| 189 | -- --------------------------------------------------------------------------- |
|---|
| 190 | FUNCTION epochToString(epochsecs NUMBER, pattern VARCHAR2) RETURN VARCHAR2 |
|---|
| 191 | AS LANGUAGE JAVA |
|---|
| 192 | NAME 'org.trinet.util.EpochTime.epochToString(double,java.lang.String) return String'; |
|---|
| 193 | -- ---------------------------------------------------------------------------- |
|---|
| 194 | -- --------------------------------------------------------------------------- |
|---|
| 195 | FUNCTION epochToString(epochsecs NUMBER, pattern VARCHAR2, timezone VARCHAR2) RETURN VARCHAR2 |
|---|
| 196 | AS LANGUAGE JAVA |
|---|
| 197 | NAME 'org.trinet.util.EpochTime.epochToString |
|---|
| 198 | (double,java.lang.String,java.lang.String) return String'; |
|---|
| 199 | -- ---------------------------------------------------------------------------- |
|---|
| 200 | -- --------------------------------------------------------------------------- |
|---|
| 201 | FUNCTION stringToEpoch(timeString VARCHAR2, pattern VARCHAR2) RETURN NUMBER |
|---|
| 202 | AS LANGUAGE JAVA |
|---|
| 203 | NAME 'org.trinet.util.EpochTime.stringToEpoch |
|---|
| 204 | (java.lang.String,java.lang.String) return double'; |
|---|
| 205 | -- ---------------------------------------------------------------------------- |
|---|
| 206 | -- ---------------------------------------------------------------------------- |
|---|
| 207 | -- |
|---|
| 208 | -- RETURN String for long station name: staname + closest town |
|---|
| 209 | -- |
|---|
| 210 | FUNCTION longName(p_net VARCHAR2, p_sta VARCHAR2) RETURN VARCHAR2 AS |
|---|
| 211 | -- |
|---|
| 212 | p_lat NUMBER := 0; |
|---|
| 213 | p_lon NUMBER := 0; |
|---|
| 214 | v_staname VARCHAR2(50); |
|---|
| 215 | v_town VARCHAR2(80); |
|---|
| 216 | -- Use cursor here because can't guarantee the select will return just 1 row |
|---|
| 217 | CURSOR c_stanameCursor IS |
|---|
| 218 | SELECT staname, lat, lon FROM Station_Data WHERE net LIKE p_net AND sta LIKE p_sta; |
|---|
| 219 | -- |
|---|
| 220 | BEGIN |
|---|
| 221 | -- Use cursor here because can't guarantee the select will return just 1 row |
|---|
| 222 | OPEN c_stanameCursor; |
|---|
| 223 | FETCH c_stanameCursor INTO v_staname, p_lat, p_lon; |
|---|
| 224 | -- |
|---|
| 225 | v_town := WHERES.getNearestTown(p_lat, p_lon); |
|---|
| 226 | -- |
|---|
| 227 | IF (v_staname IS NULL) THEN -- if StaName is missing don't print "," |
|---|
| 228 | RETURN RTRIM(v_town); |
|---|
| 229 | ELSE |
|---|
| 230 | RETURN INITCAP(RTRIM(v_staname)) || ', ' || RTRIM(v_town); |
|---|
| 231 | END IF; |
|---|
| 232 | -- |
|---|
| 233 | END longName; |
|---|
| 234 | -- ---------------------------------------------------------------------------- |
|---|
| 235 | -- --------------------------------------------------------------------------- |
|---|
| 236 | -- Print input strings showing index where they first differ |
|---|
| 237 | -- ( e.g. length of shortest string) else prints 'strings identical'. |
|---|
| 238 | PROCEDURE print_str_diff(p_str_1 VARCHAR2, p_str_2 VARCHAR2) AS |
|---|
| 239 | min_diff_len PLS_INTEGER; |
|---|
| 240 | len_str1 PLS_INTEGER; |
|---|
| 241 | len_str2 PLS_INTEGER; |
|---|
| 242 | BEGIN |
|---|
| 243 | len_str1 := nvl(length(p_str_1),0); |
|---|
| 244 | len_str2 := nvl(length(p_str_2),0); |
|---|
| 245 | min_diff_len := least(len_str1,len_str2); |
|---|
| 246 | FOR idx in 1..min_diff_len LOOP |
|---|
| 247 | IF (substr(p_str_1, idx, 1) <> substr(p_str_2, idx, 1)) THEN |
|---|
| 248 | dbms_output.put_line(substr(p_str_1,1,idx)); |
|---|
| 249 | dbms_output.put_line(lpad('*',idx,'-')); |
|---|
| 250 | dbms_output.put_line(substr(p_str_2,1,idx)); |
|---|
| 251 | RETURN; |
|---|
| 252 | END IF; |
|---|
| 253 | END LOOP; |
|---|
| 254 | IF (len_str1 <> len_str2) THEN |
|---|
| 255 | dbms_output.put_line(substr(p_str_1,1,min_diff_len+1)); |
|---|
| 256 | dbms_output.put_line(lpad('*',min_diff_len+1,'-')); |
|---|
| 257 | dbms_output.put_line(substr(p_str_2,1,min_diff_len+1)); |
|---|
| 258 | ELSE |
|---|
| 259 | dbms_output.put_line('strings identical'); |
|---|
| 260 | END IF; |
|---|
| 261 | END print_str_diff; |
|---|
| 262 | -- ---------------------------------------------------------------------------- |
|---|
| 263 | -- --------------------------------------------------------------------------- |
|---|
| 264 | -- Return index where input strings first differ ( or length of shortest string) |
|---|
| 265 | -- return value is -1 if input strings are identical. |
|---|
| 266 | FUNCTION str_diff(p_str_1 VARCHAR2, p_str_2 VARCHAR2) RETURN PLS_INTEGER AS |
|---|
| 267 | min_diff_len PLS_INTEGER; |
|---|
| 268 | len_str1 PLS_INTEGER; |
|---|
| 269 | len_str2 PLS_INTEGER; |
|---|
| 270 | BEGIN |
|---|
| 271 | len_str1 := nvl(length(p_str_1),0); |
|---|
| 272 | len_str2 := nvl(length(p_str_2),0); |
|---|
| 273 | min_diff_len := least(len_str1,len_str2); |
|---|
| 274 | FOR idx IN 1..min_diff_len LOOP |
|---|
| 275 | IF (substr(p_str_1, idx, 1) <> substr(p_str_2, idx, 1)) THEN |
|---|
| 276 | RETURN idx; |
|---|
| 277 | END IF; |
|---|
| 278 | END LOOP; |
|---|
| 279 | IF (len_str1 <> len_str2) THEN |
|---|
| 280 | RETURN min_diff_len; |
|---|
| 281 | ELSE |
|---|
| 282 | RETURN -1; |
|---|
| 283 | END IF; |
|---|
| 284 | END str_diff; |
|---|
| 285 | ------------------------------------------------------------------------------------------------------ |
|---|
| 286 | ------------------------------------------------------------------------------------------------------ |
|---|
| 287 | -- Return the local time zone DATE equivalent to a UTC date string in specified input date format. |
|---|
| 288 | FUNCTION utcstring2date(v_datestr VARCHAR2, v_format VARCHAR2) RETURN DATE IS |
|---|
| 289 | BEGIN |
|---|
| 290 | RETURN |
|---|
| 291 | to_date( |
|---|
| 292 | to_char(from_tz(to_timestamp(v_datestr, v_format), 'GMT') AT TIME ZONE SESSIONTIMEZONE, P_TRUETIME_FORMAT) |
|---|
| 293 | ); |
|---|
| 294 | END utcstring2date; |
|---|
| 295 | ------------------------------------------------------------------------------------------------------ |
|---|
| 296 | ------------------------------------------------------------------------------------------------------ |
|---|
| 297 | -- Return session local time zone DATE equivalent the input epoch LEAP seconds (UTC). |
|---|
| 298 | FUNCTION true2date(v_epochsecs NUMBER) RETURN DATE IS |
|---|
| 299 | BEGIN |
|---|
| 300 | -- Must remove the leap seconds from the string for ORACLE time objects don't accept 60 in seconds field -aww |
|---|
| 301 | RETURN utcstring2date(truetime.utc2posix(truetime.true2string(v_epochsecs)), P_TRUETIME_FORMAT); -- :SS :60 to :59 here |
|---|
| 302 | END true2date; |
|---|
| 303 | ------------------------------------------------------------------------------------------------------ |
|---|
| 304 | ------------------------------------------------------------------------------------------------------ |
|---|
| 305 | -- Return session local time zone DATE equivalent the input epoch NOMINAL seconds (UTC). |
|---|
| 306 | FUNCTION nominal2date(v_epochsecs NUMBER) RETURN DATE IS |
|---|
| 307 | BEGIN |
|---|
| 308 | RETURN utcstring2date(truetime.nominal2string(v_epochsecs), P_TRUETIME_FORMAT); |
|---|
| 309 | END nominal2date; |
|---|
| 310 | ------------------------------------------------------------------------------------------------------ |
|---|
| 311 | ------------------------------------------------------------------------------------------------------ |
|---|
| 312 | -- Return UTC date string equivalent to the input DATE in the specified format. |
|---|
| 313 | FUNCTION date2utcstring(v_date DATE) RETURN VARCHAR2 IS |
|---|
| 314 | BEGIN |
|---|
| 315 | RETURN date2utcstring(v_date, P_TRUETIME_FORMAT); |
|---|
| 316 | END date2utcstring; |
|---|
| 317 | ------------------------------------------------------------------------------------------------------ |
|---|
| 318 | ------------------------------------------------------------------------------------------------------ |
|---|
| 319 | -- Return UTC date string equivalent to the input DATE in the specified format. |
|---|
| 320 | FUNCTION date2utcstring(v_date DATE, v_format VARCHAR2) RETURN VARCHAR2 IS |
|---|
| 321 | BEGIN |
|---|
| 322 | RETURN |
|---|
| 323 | TO_CHAR(CAST(v_date AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'GMT', v_format); |
|---|
| 324 | END date2utcstring; |
|---|
| 325 | ------------------------------------------------------------------------------------------------------ |
|---|
| 326 | ------------------------------------------------------------------------------------------------------ |
|---|
| 327 | -- Return epoch LEAP seconds equivalent to the input local session DATE. |
|---|
| 328 | FUNCTION date2true(v_date DATE) RETURN NUMBER IS |
|---|
| 329 | BEGIN |
|---|
| 330 | RETURN truetime.string2true(date2utcstring(v_date, P_TRUETIME_FORMAT)); |
|---|
| 331 | END date2true; |
|---|
| 332 | ------------------------------------------------------------------------------------------------------ |
|---|
| 333 | ------------------------------------------------------------------------------------------------------ |
|---|
| 334 | -- Return epoch NOMINAL seconds equivalent to the input local session DATE. |
|---|
| 335 | FUNCTION date2nominal(v_date DATE) RETURN NUMBER IS |
|---|
| 336 | BEGIN |
|---|
| 337 | RETURN truetime.string2nominal(date2utcstring(v_date, P_TRUETIME_FORMAT)); |
|---|
| 338 | END date2nominal; |
|---|
| 339 | ------------------------------------------------------------------------------------------------------ |
|---|
| 340 | ------------------------------------------------------------------------------------------------------ |
|---|
| 341 | -- |
|---|
| 342 | -- Author: Oleg Savkin Nov 2005 |
|---|
| 343 | -- |
|---|
| 344 | PROCEDURE print_out( IN_TEXT VARCHAR2, |
|---|
| 345 | IN_TEXT_LENGTH NUMBER DEFAULT 255, |
|---|
| 346 | IN_DIVIDER VARCHAR2 DEFAULT CHR(32), |
|---|
| 347 | IN_NEW_LINE VARCHAR2 DEFAULT NULL) IS |
|---|
| 348 | lv_print_text VARCHAR2(32767); |
|---|
| 349 | ln_position PLS_INTEGER; |
|---|
| 350 | ln_divider_position PLS_INTEGER; |
|---|
| 351 | ln_total_printed PLS_INTEGER; |
|---|
| 352 | ln_string_length PLS_INTEGER; |
|---|
| 353 | BEGIN |
|---|
| 354 | |
|---|
| 355 | IF IN_TEXT_LENGTH >255 |
|---|
| 356 | THEN |
|---|
| 357 | ln_string_length := 255; |
|---|
| 358 | ELSE |
|---|
| 359 | ln_string_length := IN_TEXT_LENGTH; |
|---|
| 360 | END IF; |
|---|
| 361 | |
|---|
| 362 | IF LENGTHB(IN_TEXT) <=IN_TEXT_LENGTH |
|---|
| 363 | THEN |
|---|
| 364 | dbms_output.put_line( IN_TEXT ); |
|---|
| 365 | ELSE |
|---|
| 366 | |
|---|
| 367 | ln_position := 1; |
|---|
| 368 | ln_total_printed := 0; |
|---|
| 369 | |
|---|
| 370 | LOOP |
|---|
| 371 | lv_print_text := SUBSTR( IN_TEXT,ln_position, ln_string_length ); |
|---|
| 372 | |
|---|
| 373 | IF IN_NEW_LINE IS NULL |
|---|
| 374 | THEN |
|---|
| 375 | ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider |
|---|
| 376 | ELSE |
|---|
| 377 | ln_divider_position := INSTR(lv_print_text, IN_NEW_LINE, -1); |
|---|
| 378 | IF ln_divider_position = 0 |
|---|
| 379 | THEN |
|---|
| 380 | ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider |
|---|
| 381 | END IF; |
|---|
| 382 | END IF; |
|---|
| 383 | |
|---|
| 384 | IF ln_divider_position = 0 |
|---|
| 385 | THEN |
|---|
| 386 | ln_divider_position := ln_string_length; |
|---|
| 387 | END IF; |
|---|
| 388 | |
|---|
| 389 | IF ln_divider_position <=ln_string_length |
|---|
| 390 | THEN |
|---|
| 391 | lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position); |
|---|
| 392 | |
|---|
| 393 | IF length( lv_print_text ) <> lengthb(lv_print_text) |
|---|
| 394 | THEN |
|---|
| 395 | ln_divider_position := ln_divider_position-(lengthb(lv_print_text)-length( lv_print_text )); |
|---|
| 396 | lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position); |
|---|
| 397 | |
|---|
| 398 | IF IN_NEW_LINE IS NULL |
|---|
| 399 | THEN |
|---|
| 400 | ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider |
|---|
| 401 | ELSE |
|---|
| 402 | ln_divider_position := INSTR(lv_print_text, IN_NEW_LINE, -1); |
|---|
| 403 | IF ln_divider_position = 0 |
|---|
| 404 | THEN |
|---|
| 405 | ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider |
|---|
| 406 | END IF; |
|---|
| 407 | END IF; |
|---|
| 408 | |
|---|
| 409 | IF ln_divider_position = 0 |
|---|
| 410 | THEN |
|---|
| 411 | ln_divider_position := ln_string_length-(lengthb(lv_print_text)-length( lv_print_text )); |
|---|
| 412 | END IF; |
|---|
| 413 | |
|---|
| 414 | lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position); |
|---|
| 415 | END IF; |
|---|
| 416 | |
|---|
| 417 | IF ln_divider_position = 0 |
|---|
| 418 | THEN |
|---|
| 419 | ln_divider_position := ln_string_length; |
|---|
| 420 | END IF; |
|---|
| 421 | |
|---|
| 422 | ln_position := ln_position+ln_divider_position; |
|---|
| 423 | END IF; |
|---|
| 424 | |
|---|
| 425 | ln_total_printed := ln_total_printed+LENGTHB(lv_print_text); |
|---|
| 426 | |
|---|
| 427 | lv_print_text := TRIM( lv_print_text ); |
|---|
| 428 | --dbms_output.put_line('***'); |
|---|
| 429 | dbms_output.put_line( lv_print_text ); |
|---|
| 430 | |
|---|
| 431 | EXIT WHEN ln_position >= LENGTH(TRIM(IN_TEXT)); |
|---|
| 432 | |
|---|
| 433 | END LOOP; |
|---|
| 434 | |
|---|
| 435 | IF ln_position <ln_total_printed -- printed not everything |
|---|
| 436 | THEN |
|---|
| 437 | dbms_output.put_line( substr( IN_TEXT, ln_position, ln_total_printed ) ); |
|---|
| 438 | END IF; |
|---|
| 439 | |
|---|
| 440 | END IF; |
|---|
| 441 | EXCEPTION |
|---|
| 442 | WHEN others |
|---|
| 443 | THEN |
|---|
| 444 | dbms_output.put_line( 'ERROR :'||SQLERRM ); |
|---|
| 445 | dbms_output.put_line( 'ln_position: '||ln_position ); |
|---|
| 446 | dbms_output.put_line( 'ln_divider_position: '||ln_divider_position ); |
|---|
| 447 | |
|---|
| 448 | END print_out; |
|---|
| 449 | -- |
|---|
| 450 | END util; |
|---|
| 451 | |
|---|
| 452 | / |
|---|
| 453 | -- |
|---|
| 454 | SHOW ERRORS; |
|---|
| 455 | -- |
|---|
| 456 | CREATE OR REPLACE PUBLIC SYNONYM util FOR util; |
|---|
| 457 | -- |
|---|
| 458 | --GRANT EXECUTE ON util TO TRINETDB_EXECUTE; |
|---|
| 459 | --GRANT EXECUTE ON util TO public; |
|---|
| 460 | --@@grant_prod_tab_privs util |
|---|
| 461 | --CALL schema_util.grant_privs('UTIL'); |
|---|
