Posts

Showing posts from February, 2013

Oracle - duplicate fields in RECORD,TABLE or argument list are not permitted

Problem:
Error: PLS-00323: subprogram or cursor 'UPD_COSTELEMENTX' is declared in a package specification and must be defined in the package body
Error: PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted
Error: PL/SQL: Item ignored  

procedure UPD_COSTELEMENT (p_commit in number, p_out_msg out varchar2) is
(...)
    l_logfile_name            varchar2(250);
p_out_msg              varchar2(4000);   
    lf                                 utl_file.file_type;

    begin
        dbms_output.enable(null);

(...) 

Cause:

Double initialization of the variable p_out_msg.

Oracle - ORA-29282: invalid file ID, ORA-06512: at "SYS.UTL_FILE"

Problem:
Connecting to the database APPS_MANPOWER_PLUS_APPS.
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 878
ORA-06512: at "APPS_MANPOWER_PLUS.PKG_UPD_BULK", line 421
ORA-06512: at line 6
--------------------------------------------------------------------------------
Process exited.


, generated by:

(...) 
  begin
    dbms_output.enable(null);

    dbms_output.put_line(RPAD('-',80,'-'));
utl_file.put_line(lf, RPAD('-',80,'-'));

    l_logfile_name := 'UPD_XXX' || '_' || to_char(sysdate, 'YYYYMMDDHH24MISS') || '.log';

lf := utl_file.fopen('OPERATION_LOGS', l_logfile_name, 'W');


    dbms_output.put_line('BEGIN: PKG_UPD_BULK.UPD_
XXX');
    utl_file.put_line(lf, 'BEGIN: PKG_UPD_BULK.UPD_
XXX');
(...) 

Cause:
Write in the file before first open it. The correct code should be:

(...) 
  begin
    dbms_output.enable(null);

    l_logfile_name := 'UPD_XXX' || '_&#…

Oracle - Send mail with UTL_SMTP

Procedure PL/SQL to send a email using UTL_SMTP package:

Declare
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection('', 25);
  UTL_SMTP.helo(l_mail_conn, '
');
  UTL_SMTP.mail(l_mail_conn, '');
  UTL_SMTP.rcpt(l_mail_conn, '  UTL_SMTP.data(l_mail_conn, '' || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;

Spring - BeanCreationException or CGLIB2 when using @Transactional(readOnly = true)

If the instruction @Transactional(readOnly = true) is causing the bellow error(s):
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'xxx' defined in class path resource [xxx/xxx.xml]: Initialization of bean failed; nested exception is org.springframework.aop.framework.AopConfigException: Cannot proxy target class because CGLIB2 is not available. Add CGLIB to the class path or specify proxy interfaces.

Caused by: org.springframework.aop.framework.AopConfigException: Cannot proxy target class because CGLIB2 is not available. Add CGLIB to the class path or specify proxy interfaces.
fix it by adding the cglib jar, or if you use maven:
<dependency>
   <groupid>cglib</groupid>
   <artifactid>cglib</artifactid>
   <version>2.2</version>
</dependency>

APEX - Interactive report submission, set up UDP_MAIL ACLs

This post describes how to set up APEX to send emails in order to allow users to subscribe to interactive report:

Configure APEX with the SMTP server, port and user credentials:
Verify the existing ACLs:


select * from dba_network_acl_privileges;

Add the APEX user, APEX_040000 in my case, to the ACL list of allowed users to send email, or in a more general form, use UDP_MAIL:

begin
    dbms_network_acl_admin.add_privilege (
    acl       => 'netacl.xml',
    principal => 'APEX_
0XXXXX',
    is_grant  => TRUE,
    privilege => 'connect'
    );
    commit;
end;
/


begin
    dbms_network_acl_admin.add_privilege (
    acl       => 'netacl.xml',
    principal => 'APEX_
0XXXXX',
    is_grant  => TRUE,
    privilege => 'resolve'
    );
    commit;
end;
/

Oracle - Function/Procedure call with output to console

Take the following Oracle function as example:

sql>create or replace
function "func_get_project_id" (p_project_code varchar2) return varchar2 is
  param_value varchar2(255);
  begin
    select project.id into param_value from project, costelement where project.costelementid = costelement.id and code = p_project_code;
    return param_value;
  exception
    when others then                                     
      return sqlerrm;
end func_get_project_id;


1- The first option is to call the function using a select:

sql>select func_get_project_id('e/0900-04') from dual;

2- The second option is to set the activate the server output:

sql>set serveroutput on
sql>exec dbms_output.put_line( func_get_project_id('e/0900-04'));


3- The third and last option is to set the activate the server output via PL/SQL:

sql>(...)
  param_value varchar2(255);
  begin
dbms_output.enable (1000000);
    select project.id into param_value from project, costelement where project.costelem…

Oracle - String empezando por (Starts With)

El script/procedimiento abajo testa si una dada Organización es padre de una segunda pasada como argumento:

declare
dummy varchar2(500);
f_unit varchar2(50);
f_parent_unit varchar2(50);
begin
f_unit := 'AA-ABC';
f_parent_unit := 'AA-AB';
begin
select * into dummy
from dual
where f_unit like f_parent_unit || '%'
and f_unit != f_parent_unit;
dbms_output.put_line('DATA FOUND');
exception
when others then
dbms_output.put_line('NO DATA FOUND');
end;
end;
/


En una forma aun mas general solo necesitéis:

begin
select * into dummy
from dual
where f_unit like f_parent_unit || '%'
and f_unit != f_parent_unit;
dbms_output.put_line('DATA FOUND');
exception
when others then
dbms_output.put_line('NO DATA FOUND');

Oracle - Field Starts With

The following script/procedure tests if a given Organization is the father of a second:

declare
dummy varchar2(500);
f_unit varchar2(50);
f_parent_unit varchar2(50);
begin
f_unit := 'AA-ABC';
f_parent_unit := 'AA-AB';
begin
select * into dummy
from dual
where f_unit like f_parent_unit || '%'
and f_unit != f_parent_unit;
dbms_output.put_line('DATA FOUND');
exception
when others then
dbms_output.put_line('NO DATA FOUND');
end;
end;
/


In a more general form you will only need:

begin
select * into dummy
from dual
where f_unit like f_parent_unit || '%'
and f_unit != f_parent_unit;
dbms_output.put_line('DATA FOUND');
exception
when others then
dbms_output.put_line('NO DATA FOUND');

Oracle - Generate a Random number in Oracle between a interval

You can easily generate a random number by:

select dbms_random.normal from dual;

Unfortunately, Oracle does not provide a solution if you require a random number between a interval of values. To do it you might wand to do:

create or replace
function RANDOM_NUMBER(lower IN int,higher IN int)
return number
is
begin
  return trunc(abs(( GREATEST(lower, higher) - LEAST(lower, higher) + 1 ) * abs(dbms_random.value)) + LEAST(lower, higher));
end;


My own solution involved a last steep:

select * 
from ( select RANDOM_NUMBER( (select min(id) from organisation), (select max(id) from organisation))
from dual);