Case Of Use

Last week I post a code for show percentages in a way the grafic by user text mode. Today I want to show a new example of this;

                                           
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN grafi format a20

BBDD*ANTO> SELECT s.sid,
  2  s.serial#,
  3  s.machine,
  4  ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
  5  ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
  6  PERCENTAGE_GRAPH_TEXT (ROUND(sl.sofar/sl.totalwork*100, 2)) grafi
  7  FROM v$session s,
  8  v$session_longops sl
  9  WHERE s.sid = sl.sid
 10  AND s.serial# = sl.serial#
 11  /

 SID  SERIAL# MACHINE                        ELAPSED    REMAINING  GRAFI
____ ________ ______________________________ __________ __________ ____________________
####    19162 #######                        0:12       0:5        [******....] 68,63%
####      291 #######                        0:11       0:11       [****......] 49,62%
####      291 #######                        0:11       0:11       [****......] 49,62%
####      291 #######                        0:11       0:11       [****......] 49,62%
####    28799 #######                        1:37       20:55      [..........] 3%

5 rows selected.

 

HTH – Antonio NAVARRO

 

Advertisements

Graphic In Text Mode

I have to admit that I am a fanatic of the text mode, began to play computers in the eighties and practically everything was text mode. Today I will show a very simple function to put a kind of graphic (in text mode) to get our querys a little more interesting.

Here is the code;

-------------------------------------------------------------------------------- 
--Function that generate a graphic for percentages in text mode
--The function have five parametero, first one mandatory and the rest
--are optionals.
--
-- Parameter list;
--
-- Position 1 Is the figure that represent the percentage
-- Position 2 Is how many characters are used for generate the graph text (10)
-- Position 3 Is the simbol used for represent the percent (*)
-- Position 4 Is the simbol used for represent the space until the end ( )
-- Position 5 Is a flag to show the last string (number plus % simbol) (TRUE)
-- 
-- --- example i ----
-- SELECT PERCENTAGE_GRAPH_TEXT (20) FROM DUAL;
--
-- Result: [**........] 20%
--
-- -- example ii -----
-- SELECT PERCENTAGE_GRAPH_TEXT (20, 5, '=', ' ') from dual;
--
-- Result: [=    ] 20%
--
--
--  AUTOR:   Antonio NAVARRO
--  FECHA:   28.10.99
--  MODIFICADO: 28.10.99
--  FICHERO: PERCENTAGE_GRAPH_TEXT.SQL
--  LOCALIZACION: \\SAN/NAS
--
---------------------------------------------------------------------------------

CREATE OR REPLACE
FUNCTION PERCENTAGE_GRAPH_TEXT
(
numero number ,
longitud number default 10 ,
simbolo varchar2 default '*' ,
simbolo_relleno varchar2 default '.' ,
numero_por_centaje boolean default true
) RETURN VARCHAR2 IS

TEXTO VARCHAR2(110);

BEGIN


-- Open textgraph ---
TEXTO := TEXTO || '[';

FOR i IN 1..longitud LOOP
/*** Si estamos en porcentaje escribir simbolo, sino escribir vacio ***/
IF round ((i*100)/longitud) <= numero THEN
TEXTO := TEXTO || simbolo;
ELSE
TEXTO := TEXTO || simbolo_relleno;
END IF;

END LOOP;

-- Close texgraph ---
TEXTO := TEXTO || ']';

IF numero_por_centaje THEN
TEXTO := TEXTO || ' ' || numero || '%';
END IF;

RETURN (TEXTO);


END; /*** PERCENTAGE_GRAPH_TEXT ***/
/

An example of this will be like show below;

WEB*ANTO> SELECT PERCENTAGE_GRAPH_TEXT (20) FROM DUAL;

PERCENTAGE_GRAPH_TEXT(20)
_______________________________________________________________________
[**……..] 20%

WEB*ANTO> SELECT PERCENTAGE_GRAPH_TEXT (20, 5, ‘=’, ‘ ‘) from dual;

PERCENTAGE_GRAPH_TEXT(20,5,’=’,”)
_______________________________________________________________________
[= ] 20%

There are many options and possibilities that are left for the reader.

HTH – Antonio NAVARRO

Ora-01110 And Ora-01187 Errors

Last morning I was duplicating a database, when I finish, dev reported me about the ORA-01110 and ORA-01187 errors where they tryed a query. The problem is the temporary files not was restaured in a correct way (This is a older version of Oracle 10g).

The solution was drop the current temporary tablespace and recreate it.

HTH – Antonio NAVARRO.

“Order By” Clause Using Substr Function

I like to show a example of Order By using a substr function. Usually people order results by columns but in general “Order by” clause (No only Oracle, in this case I refer standard, ANSI SQL/ISO 9075) is a powerfull option for sort in many differents ways.

In this example I order a string (which is a date) by day, month and year;

 
-- Date into the string have the next format;
-- Sun Oct 01 19:26:01 2017

select date_in_string from dual
order by 
  substr (date_in_string, 9, 2),
  substr (date_in_string, 5, 3),
  substr (date_in_string, 21, 4)
/

HTH – Antonio NAVARRO

Language Is Very Important

Today I was reported with a error inserting one row from a dev team. He told me that inserted one row and work fine, but the second row doesn’t work. He saw me the next;

 

 
DBTT*ANTO> insert into tensorflow2  values (to_date ('Jul 17 18:53:59 2017', 'Mon dd hh24:mi:ss yyyy')); 

1 fila creada.

Transcurrido: 00:00:00.03
DBTT*ANTO> insert into tensorflow2  values (to_date ('Aug 06 06:46:05 2017', 'Mon dd hh24:mi:ss yyyy'));
insert into tensorflow2  values (to_date ('Aug 06 06:46:05 2017', 'Mon dd hh24:mi:ss yyyy'))
                                           *
ERROR en línea 1:
ORA-01843: mes no válido

 

Of course, first of all;

i) In this installation, default language is in local language,(Errors messages are in Spanish as you can see).

ii) Jul in English is equal to Jul in Spanish.

ii) Aug in Englies is Ago in Spanish.

Solutions;

i) Write all in english, maybe the best solution in this case.

ii) Change the session parameter (in this case nls_date_language), maybe the worst solution, all is designed to work in Spanish.

                     
DBTT*ANTO> alter session set nls_date_language=english;            

DBTT*ANTO> insert into tensorflow2  values (to_date ('Aug 06 06:46:05 2017', 'Mon dd hh24:mi:ss yyyy'));

1 fila creada.

HTH – Antonio NAVARRO

 

 

How To grep n Lines Before In Solaris

Hi,

I like to show how to get the n lines before a pattern search using grep in Solaris. First of all, Solaris grep command not have a option to get the lines before or after from a match. You must use ggrep compliance to unix standard. Let me see show a pair of examples;

Test 1, this comamnd show the four previous lines from match ‘ORA-00313’;

cat alert_db.log | /usr/sfw/bin/ggrep -B 4 ORA-00313

Test 2, this command show the previous line from match ‘ORA-00313’;

cat alert_db.log | /usr/sfw/bin/ggrep -B 1 ORA-00313

HTH – Antonio NAVARRO