My workspace

Most of all oracle security features could be done by oracle proxy authentication. Oracle proxy authentication provides fine grained access control for temporary users of the account, without compromising the original password even enabling database auditing and logging. In this current post i will first setup database for proxy authentication and later will connect to it by connection pool. 1) Database setup: sqlplus /nolog conn sys/manager@orcl as sysdba create a proxy user create user proxy_user identified by pw_proxy default tablespace users temporary tablespace temp; create a target user create user target_user identified by pw_target default tablespace users temporary tablespace temp quota unlimited on users; now we will alter target user to connect through proxy user alter user target_user grant connect through proxy_user; Also grant create session and the create table system privilege grant create session, create table to targ...

Last week, in one of our project, which we decided to upgrade sql queries on ibatis needed dynamic sql query execution. As we decided to use annotations as much as possible on sql mapper - it's seems not a straight to build dynamic query. At first tried to do something as follows: DONOT TRY THIS - IT'S WRONG WAY TO BUILD DYNAMIC QUERY @Update("update ${schemaName}.fdc_uf uf" + " set uf.reestr_date = #{reestDate}," + " uf.reestr_number = #{reestNum}" + " where uf.reestr_number IS NULL AND uf.reestr_date IS NULL and uf.id in " + " <foreach item="item" index="index" collection="list" open="(" separator="," close=")">"+ " #{item}"+ " </foreach>") After some tries, read the user guide attentively, found there are another a few way to build dynamic query, one of them to use Select/Insert/UpdateProvider. These alternative SQL annotations allo...

Last few weeks i have been working with a project ,where we decided to use Ibatis for generating complex report. Ibatis is a very small smart ORM tools to execute complex query but, in version 3, developers made a vast change and it's difficult to migrate from version 2 to version 3. In this post i am going to describe how to call pl/sql package function from within Ibatis3. First of all we will create two small tables and a pl/sql package for demonstration: -- Create table create table ADDRESSES ( ADR_ID INTEGER not null, ADR_CITY VARCHAR2(15), ADR_COUNTRY VARCHAR2(15) not null ); alter table ADDRESSES add primary key (ADR_ID); create table PERSONS ( PRS_ID INTEGER not null, PRS_FATHER_ID INTEGER, PRS_MOTHER_ID INTEGER, PRS_ADR_ID INTEGER, PRS_FIRST_NAME VARCHAR2(15), PRS_SURNAME VARCHAR2(15) ); / alter table PERSONS add constraint PRS_ADR_FK foreign key (PRS_ADR_ID) references ADDRESSES (ADR_ID); alter table PERSONS add constraint PRS_PRS_FATHER_FK fo...