apex.server.process('', { pageItems: '#P1_NEW' }, { dataType: 'text' });
Oracle PL/SQL Function To Get Week Number Within The Month
function get_week_num(p_date date) return number is
l_cnt pls_integer := 0;
begin
for i in 1 .. to_number(to_char(p_date, 'dd')) - 1 loop
if to_char(trunc(p_date, 'mm') + i, 'd') = '1' then
l_cnt := l_cnt + 1;
end if;
end loop;
return case when l_cnt < 1 then 1 else l_cnt + 1 end;
end get_week_num;
Using Oracle DBMS_SQLTUNE
A sample usage of DBMS_SQLTUNE
set timing on;
select count(*) from slow_table where col1 = ‘XYZ’ and col2 = ‘XYZ’;
— Elapsed: 00:00:06.631
declare
l_sql clob;
l_task_id clob;
begin
l_sql := ‘select count(*) from slow_table where col1 = :B1 and col2 = :B2’;
l_task_id := dbms_sqltune.create_tuning_task(
sql_text => l_sql,
bind_list => sql_binds(
anydata.ConvertVarchar2(‘XYZ’),
anydata.ConvertVarchar2(‘XYZ’)
),
user_name => ‘OWNER_NAME’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 300,
task_name => ‘sql_tuning_task_test_01’
);
dbms_output.put_line(l_task_id);
end;
/
select task_id, task_name, status from user_advisor_log where task_name = ‘sql_tuning_task_test_01’;
exec dbms_sqltune.execute_tuning_task(task_name => ‘sql_tuning_task_test_01’);
select task_id, task_name, status from user_advisor_log where task_name = ‘sql_tuning_task_test_01’;
select dbms_sqltune.report_tuning_task(‘sql_tuning_task_test_01’) as recommendations from dual;
exec dbms_sqltune.drop_tuning_task(task_name => ‘sql_tuning_task_test_01’);
Oracle Apex: Two Ways to Export an Application from Itself
declare
l_files apex_t_export_files;
l_blob blob := empty_blob();
begin
l_files := apex_export.get_application(p_application_id => :app_id);
l_blob := wwv_flow_utilities.clob_to_blob(p_clob => l_files(1).contents);
sys.htp.init;
wwv_flow_utilities.print_download_header(
p_mime_type => ‘appllication/x-sql’,
p_file_name => ‘apex_exp_demo_m1.sql’,
p_is_attachment => true
);
sys.wpg_docload.download_file(l_blob);
end;
declare
l_files apex_t_export_files;
l_export apex_data_export.t_export;
begin
l_files := apex_export.get_application(p_application_id => :app_id);
l_export.content_clob := l_files(1).contents;
l_export.mime_type := ‘application/x-sql’;
l_export.file_name := ‘apex_exp_demo_m2’;
l_export.format := ‘sql’;
l_export.as_clob := true;
apex_data_export.download(l_export);
end;
Oracle SQL To Get What Sessions Are Using an Object
The following query shows what Oracle sessions are using a given object.
select a.object, a.type, a.sid, b.serial#, b.username, b.osuser, b.program
from v$access a, v$session b
where a.sid = b.sid
and a.object = 'XYZ'
order by a.object
Equalize Numbers Algorithm
Let’s build an algorithm that equalizes all the numbers in a set by making steps in which we increment 1 to all numbers but one element until all numbers are equal. Let’s also count the number of necessary steps to complete the task.
For example, in the sequence 3, 8, 5 all we would have to do is:
3, 8, 5
4, 8, 6 (1 step – not changing 2nd element)
5, 8, 7 (2 steps – not changing 2nd element)
6, 8, 8 (3 steps – not changing 2nd element)
7, 9, 8 (4 steps – not changing 3rd element)
8, 9, 9 (5 steps – not changing 2nd element)
9, 10, 9 (6 steps – not changing 3rd element)
10, 10, 10 (7 steps – not changing 2nd element)
So, for the initial set of 3, 8, 5 the final set is 10, 10, 10 and we used 7 steps.
Ok, now let’s got for the PL/SQL code:
create or replace procedure prc_equalize(p_numbers varchar2) authid current_user is
l_numbers apex_application_global.n_arr;
l_count number;
l_higher number;
l_higher_pos number;
l_all_equal boolean;
begin
for a in (
select trim(column_value) el
from apex_string.split(p_numbers, ',')
) loop
l_numbers(l_numbers.count + 1) := a.el;
end loop;
l_count := 0;
loop
-- get higher
l_higher := l_numbers(1);
l_higher_pos := 1;
for i in 2 .. l_numbers.count loop
if l_numbers(i) >= l_higher then
l_higher := l_numbers(i);
l_higher_pos := i;
end if;
end loop;
-- increment all but higher
for i in 1 .. l_numbers.count loop
if i <> l_higher_pos then
l_numbers(i) := l_numbers(i) + 1;
end if;
end loop;
l_count := l_count + 1;
-- check if equalized
l_all_equal := true;
for i in 1 .. l_numbers.count loop
for j in i .. l_numbers.count loop
if l_numbers(i) <> l_numbers(j) then
l_all_equal := false;
end if;
end loop;
end loop;
exit when l_all_equal;
end loop;
dbms_output.put(p_numbers || ' => ');
for i in 1 .. l_numbers.count loop
dbms_output.put(l_numbers(i) || case when i < l_numbers.count then ', ' end);
end loop;
dbms_output.new_line;
dbms_output.put_line(l_count || ' steps.');
end prc_equalize;
We can call the above code as in exec prc_equalize('3,8,5');
and get a result as
3,8,5 => 10, 10, 10
7 steps.
Reverse Vowels Algorithm
Let’s create a function that will reverse the vowels in a given string. For example, for the word “apple” the result would be “eppla”. For the string “frozen” the result would be “frezon”.
Ok, now to the PL/SQL code:
create or replace function reverse_vowels(p_string clob) return clob authid current_user is
c_vowels constant varchar2(5) := 'aeiou';
type t_string is table of varchar2(1) index by binary_integer;
l_string t_string;
l_left pls_integer;
l_right pls_integer;
l_left_vowel varchar2(1);
l_right_vowel varchar2(1);
l_exchanged boolean;
l_return clob;
begin
if trim(p_string) is null or length(trim(p_string)) = 1 then
return p_string;
end if;
for i in 1 .. length(p_string) loop
l_string(i) := substr(p_string, i, 1);
end loop;
l_left := 1;
l_right := length(p_string);
while l_left < l_right loop
l_left_vowel := l_string(l_left);
if instr(c_vowels, lower(l_left_vowel)) > 0 then
l_exchanged := false;
while l_right > l_left and not l_exchanged loop
l_right_vowel := l_string(l_right);
if instr(c_vowels, lower(l_right_vowel)) > 0 then
l_string(l_left) := l_right_vowel;
l_string(l_right) := l_left_vowel;
l_exchanged := true;
end if;
l_right := l_right - 1;
end loop;
end if;
l_left := l_left + 1;
end loop;
for i in l_string.first .. l_string.last loop
l_return := l_return || l_string(i);
end loop;
return l_return;
end reverse_vowels;
Using DBMS_PROFILER to check PL/SQL performance on Oracle Database
After installing DBMS_PROFILER you can use the following to gather the statistics about you code.
declare
l_id number;
begin
dbms_profiler.start_profiler('my code run on ' || to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'), run_number => l_id);
call_your_package.procedure;
dbms_profiler.stop_profiler;
dbms_output.put_line(l_id);
end;
/
At this point the statistics were saved to the following tables: plsql_profiler_data, plsql_profiler_runs, plsql_profiler_units.
No you can use the below query to get detailed information:
SELECT ds.line, p.total_occur, p.total_time Msec, ds.text
FROM dba_source ds, (
SELECT ppu.unit_owner, ppu.unit_name, ppu.unit_type, ppd.line#, ppd.total_occur,
ppd.total_time/1000000 total_time
FROM plsql_profiler_data ppd, plsql_profiler_units ppu
WHERE ppu.runid = 7
AND ppu.runid = ppd.runid
AND ppu.unit_number = ppd.unit_number) p
WHERE ds.owner = p.unit_owner(+)
AND ds.name = p.unit_name(+)
AND ds.type = p.unit_type(+)
AND ds.line = p.line#(+)
AND ds.name = 'PACKAGE_NAME'
AND ds.owner = 'PACKAGE_OWNER'
AND p.total_time > 0
ORDER BY p.total_time desc;
How to obtain the certificates from HTTPS websites
In order to get websites’ certificates and then add those certificates to an Oracle Wallet you can use a free tool called OpenSSL. Usually it comes installed with Linux and Mac OS X. For windows it is possible to google it and download the installer.
Here are some samples on how to execute OpenSSL and obtains certificates.
- Gmail’s SMTP – openssl s_client -starttls smtp -connect smtp.gmail.com:587 -showcerts
- For other sites – openssl s_client -connect google.com:443 -showcerts
Get all certificates starting from the second to put into Oracle’s wallet. The first certificate should not be in there.
Now, lets create the Oracle’s wallet.
orapki wallet create -wallet /path -pwd pwd
Then, lets add a certificate to this wallet.
orapki wallet add -wallet /path -trusted_cert -cert /path/cert1.pem
You can see what’s in your certificate.
orapki wallet display -wallet /path
Thanks to this post: https://fuzziebrain.com/content/id/1720/
Keeping Javascript in modules inside Oracle Apex
Step 1: create a file using the naming convention myModule.page#.js (i.e. myModule.page110.js)
var myModule = myModule || {};
myModule.page110 = {
pageLoad: function () {
jQuery(‘#P110_ITEM’).focus().select();
},
myOtherFunction: function () {
console.log(‘…’);
}
}
Step 2: after uploading the file to “shared components -> static files”, refer to it in “Apex page -> File URLs” section
#APP_IMAGES#myModule.page110.js
Step 3: any calls to javascript code should now be something like “myModule.page110.pageLoad();”
*** Note ***
doesn’t matter if your code is one line only; keep it in a separate file.
https://vmorneau.me/avoid-javascript-mess/
Oracle PL/SQL code to generate dummy data
with test_data as (
select rownum as id,
initcap(dbms_random.string('l', dbms_random.value(10, 25))) || ' ' ||
initcap(dbms_random.string('l', dbms_random.value(10, 30))) as name,
initcap(dbms_random.string('l', dbms_random.value(10, 50))) as city,
trunc(to_date('01-01-2015', 'DD-MM-YYYY') + dbms_random.value(-36500, 0)) dob
from dual
connect by level <= 100000
)
select id, name, replace(lower(name), ' ') || '@company.com' email, city, dob
from test_data;
Oracle PL/SQL Script to test CPU Performance
The following script can be used to get some reference on the server’s CPU time. I used it to check the difference of execution time between two servers.
SET SERVEROUTPUT ON SET TIMING ON DECLARE n NUMBER := 0; BEGIN FOR f IN 1..10000000 LOOP n := MOD (n,999999) + SQRT (f); END LOOP; DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99')); END; /
This script should work on any Oracle version.
Thanks to http://www.juliandyke.com/CPUPerformance/CPUPerformance.php
How to dynamically change an Oracle Apex modal dialog’s title
You can change a modal dialog’s title calling the following javascript code:
apex.util.getTopApex().jQuery(“.ui-dialog-content”).dialog(“option”, “title”, ‘New title ‘ + $v(‘PNN_ITEM’));
How to manually change Oracle Apex internal Admin’s password
Hi. Here it follows the necessary code to be used as SYSDBA in order to change the internal Admin password of your Oracle Apex installation.
— 1st step
alter session set current_schema = APEX_050100;
— 2nd step
select rtrim(min(user_id)) user_id,
nvl(rtrim(min(email_address)), ‘ADMIN’) email_address
from wwv_flow_fnd_user
where security_group_id = 10
and user_name = upper(‘ADMIN’);
— 3rd step
declare
l_g_security_group_id number := wwv_flow_security.g_security_group_id;
l_g_user varchar2(255) := wwv_flow_security.g_user;
begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_security.g_user := ‘ADMIN’;
wwv_flow_fnd_user_int.create_or_update_user( p_user_id => 1940108883932253,
p_username => ‘ADMIN’,
p_email => ’email@mail.com’,
p_password => ‘password’ );
commit;
wwv_flow_security.g_security_group_id := l_g_security_group_id;
wwv_flow_security.g_user := l_g_user;
end;
/
Tested on Oracle Apex 5.1. Good luck!
How to search into all columns from your Oracle Database
Hey there!
This is a good way to search into all columns in your database for a determined word.
declare v_search_key varchar2(30) := 'Brasil'; v_count integer; begin for t in (select owner, table_name from dba_tables /*where owner = 'HR'*/) loop for c in (select column_name, data_type from dba_tab_cols where owner = t.owner and table_name = t.table_name) loop v_count := 0; begin if c.data_type in ('BLOB', 'CLOB') then execute immediate 'select count(*) from "' || t.owner || '"."' || t.table_name || '" where dbms_lob.instr("' || c.column_name || '", utl_raw.cast_to_raw(''' || v_search_key || ''')) > 0' into v_count; elsif c.data_type in ('VARCHAR2', 'NUMBER', 'DATE', 'CHAR', 'NVARCHAR2') then execute immediate 'select count(*) from "' || t.owner || '"."' || t.table_name || '" where to_char("' || c.column_name || '") like ''%' || v_search_key || '%''' into v_count; end if; exception when others then dbms_output.put_line(t.owner || '.' || t.table_name || '.' || c.column_name || ' data type: ' || c.data_type || ' error: ' || sqlerrm); end; if v_count > 0 then dbms_output.put_line(t.owner || '.' || t.table_name || '.' || c.column_name || ' found: ' || v_count); end if; end loop; end loop; end; /
Good luck!
English Learner Diary #1
Hi, people.
I am starting this diary to write about what I am studying at the VGC Language School, in Vancouver, Canada. This is the first post about English learning and I am pretending do this every day after the classes.
This post is about the yesterday’s class, october 6, 2014. We started studying Present Perfect and Past Simple.
The present perfect is formed with the verb have + past participle.
Samples
- I have had breakfast (today).
- I have bought a new jacket.
We will continue in this subject for more classes. I have some good news about my progress and I will tell more in the next posts. Don’t miss it! :)
See you.
Como não receber SPAM das operadoras de celular
Olá!
Dica de utilidade pública: como não receber aqueles SMS inconvenientes com besteirol das operadoras? Segundo a Anatel, podemos mandar um SMS grátis, com a palavra SAIR, para os números abaixo de acordo com sua operadora e o cancelamento dos envios será automático. Segue a relação dos números:
Claro: 888
Oi: 55555
TIM: 4112
Vivo: 457
Abraços.
Alguns Cursos Online Gratuitos
Pessoal,
Seguem alguns links com diversos cursos gratuitos que podem ser feitos online:
Escola Completa: http://escolacompleta.com
Escola Virtual (Fundação Bradesco): http://www.ev.org.br
Fundação Getúlio Vargas: http://www5.fgv.br/fgvonline/cursosgratuitos.aspx
Sebrae: http://www.ead.sebrae.com.br
Senado Federal: http://www12.senado.gov.br/senado/ilb/ead/cursos/cursos-a-distancia
Senar (Serviço Nacional de Aprendizagem Rural): http://www.canaldoprodutor.com.br/eadsenar
Abraços.
SAP ABAP: criando tabelas
Olá, pessoal!
Segue abaixo um passo a passo de como criar uma tabela no SAP. Seguem também imagens ilustrativas dos passos.
Observação: para cada objeto criado, não esquecer de salvar e ativar o mesmo.
1. Acessar a transação SE11:
2. Criar domínios:
3. Criar elementos de dados (usando os domínios criados anteriormente):
3. Criar a tabela (usando os elementos de dados criados anteriormente):
4. Testar a tabela criada. Para isso vamos usar a transação SM30:
Ok, nossa tabela está criada e aceitando dados!
Otimização básica em ORACLE SQL: funções COALESCE e NVL
Normalmente usamos a função NVL quando queremos tratar um possível valor nulo. A função usada na forma NVL(:V1, “VALOR DEFAULT”) retornará “VALOR DEFAULT” caso a variável :V1 seja nula. Uma outra forma de usar a função NVL seria passando como valor default uma chamada a uma função que retorne um valor, exemplo, NVL(:V1, VALOR_QUALQUER()). No exemplo anterior, podemos ter um atraso na execução pois o ORACLE sempre executará a função VALOR_QUALQUER() mesmo que a variável :V1 não seja nula, ou seja, se a função VALOR_QUALQUER() demorar 5 minutos para retornar, a execução do nosso exemplo demorará os mesmos 5 minutos independentemente da variável :V1.
Para deixar nossos SELECT’s sempre otimizados devemos usar a função COALESCE(:V1, VALOR_QUALQUER()) pois o ORACLE somente recorrerá ao segundo parâmetro se o primeiro parâmetro for nulo.
Uma ressalva em relação à função COALESCE é que ela espera tipos de dados consistentes entre os parâmentros usados. A função NVL é mais flexível, fazendo conversões implícitas enquanto que COALESCE retorna erro para tipos inconsistentes.
Update Em Tabela Muito, Muito Grande
1. criar uma tabela temporária já com o UPDATE desejado:
create table tb_teste_tmp as select campo, [campo atulizado] from tb_teste;
2. gravar os relacionamentos e “dropar” a tabela antiga:
drop table tb_teste cascade constraints;
3. renomear a tabela nova e refazer as constraints;
rename tb_teste_tmp to tb_teste;
Teste feito no Oracle 9i.
Oracle Flashback Query
Instalando o APEX 3.2 no Oracle XE
Passo 1: download da versão 3.2.
Agregando Linhas em Colunas
Tabela Original
Grupo Valor
10 2
10 3
11 5
11 8
11 13
12 6
Select Agrupado/Agregado
Grupo Valor1 Valor2 Valor3
10 2 3
11 5 8 13
12 6
Não há um modo direto de fazer isso em ORACLE (ou, infelizmente, não conheço ainda). Uma sugestão seria o seguinte SELECT:
select grupo,
rtrim(xmlagg(xmlelement(e, valor || ‘,’)).extract(‘//text()’), ‘,’) coluna
from tabela
group by grupo
O que resultaria no seguinte:
Grupo Coluna
10 2,3
11 5,8,13
12 6
Essa saída ainda não é perfeita mas, combinada com uma boa formatação poderá resolver boa parte dos problemas.
Testado em ORACLE 9i e ORACLE 10g.
Evitando VALUE_ERROR em FOR .. LOOP
Exceptions – Seção de Declaração de Variáveis
Memória – Quanto Estou Consumindo?
Bem vindo!
Olá! Nesse blog escrevo algumas coisas sobre programação com ênfase nas tecnologias: Oracle Database, Oracle SQL, Oracle PL/SQL, Oracle Application Express (Oracle Apex).
Fique à vontade para entrar em contato comigo no email: jonatasgomes@gmail.com.
Obrigado pela visita!