Archive

Archive for the ‘Database’ Category

Grant Table User to Another User [Oracle]

August 22, 2016 Leave a comment

Below is snippet command for granting user table to another user

declare
  cursor t_name is select table_name from user_tables ;
  command varchar2(500);
begin
for c in t_name loop
command := 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||c.table_name|| ' TO <other_user>';
   dbms_output.put_line(command);
   execute immediate command;
end loop;
end;


Jakarta, 24 August 2016

 

A. Ahmad Kusumah

Advertisements

Install ORACLE on AIX 6.1

July 24, 2016 Leave a comment

Oracle 11g Installation procedure on AIX system v.6.1 environment, on IBM P-series machine is summarized from best practice at one of our  client. This procedure, will be followed by Websphere Application Server Installation procedure and configuration on the same environment and machine.

The steps of Oracle Installation 11g on AIX System is briefly described as follow :

1. Please check software pre-requisite on AIX system :

  • bos.adt.base
  • bos.adt.lib
  • bos.adt.libm
  • bos.perf.libprefstat
  • bos.perf.perfstat
  • bos.perf.proctools
  • xlC.aix50.rte 8.0.0.8 or later
  • xlC.rte 8.0.0 or later

2. Run command below to verify the pre-requisite :

  • lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.perfstat bos.perf.libperfstat bos.perf.proctoolschecking

3. Change value of max number of processes allowed  :

  • Run “smit chgsys”
  • Set “Maximum number of PROCESSES allowed per user” > 2048
  • Verify value of “ARG/ENV list size in 4K byte blocks” >= 128smith

4. Create user and groups for oracle installation, using the following command:

  • mkgroup oinstall
  • mkgroup dba
  • mkgroup oper
  • useradd -g oinstall -G dba, oper -m oracle
  • passwd oracle #Set password for oracle useruseroracle

5.  Create oracle home directory and set ownership and privileges

  • mkdir -p /database/oracle/app
  • chown -R oracle:oinstall /database/oracle/app
  • chmod -R 755 /database/oracle/apphomeoracle

6. Change display setting on AIX

  • vi /home/oracle/.profile
  • add line : “DISPLAY=:1.0; export DISPLAY

7. Set ORACLE_HOME

  • vi /home/oracle/.profile
  • add the following lines :
  • ORACLE_BASE  = /opt.app/oracle
  • ORACLE_SID = orcl
  • export ORACLE_BASE
  • export ORACLE_SID
  • ORACLE_HOME = $ORACLE_BASE/product/11.1.0/db1
  • PATH = $ORACLE_HOME/bin:$PATH
  • export ORACLE_HOME
  • export PATHpathoracle

8. Switch user to oracle and run installer as usual with options -ignorePrereq

runinstaller

 

install

 

and follow installation procedure till finish …

 

Bogor, Sunday 24 July 2016

 

A. Ahmad Kusumah

 

 

 

 

 

Rails + Mysql OSX

April 9, 2015 Leave a comment

Today when running rails application using agains MySQL Database, got the following error :

sc1

The strange thing is mysql already installed on my machine :

1. mysql 2015-04-09 16-11-47

Seems rails cannot locate libmysqlclient.18.dylib which is located in /usr/local/mysql/lib/libmysqlclient.18.dylib

because by default rails locate that file in /usr/lib folder.

Simple solution for this problem is creating symlink from /usr/local/mysql/lib to /usr/lib.

2. bash 2015-04-09 16-15-21

Update for ElCapiten:

if throw Operation Not permitted, change target folder from /usr/lib to /usr/local/lib

and now rails can start perfectly ..

 

Jakarta , 9 April 2015

 

 

A. Ahmad Kusumah

Display Tag ajax pagination and Hibernate

April 4, 2013 4 comments

Display Tag ajax

Display tag is one of powerful tools for displaying collection on table template, light weight, customizable, and fully supported for java web application. Read more here for more information and how to implement this tools on web projects.

By default, display tag will automatically create pagination and navigation if data length greater than page size property and act as server side process and run synchronously. This mechanism at some point will reduce performance, especially if the displayed data processed without pagination query and has huge massive data.

In this post, will be explained how to combine display tag, Jquery as powerfull ajax framework, and hibernate for making such light mechanism to populate data, display and boost pagination  performance. this sample using Struts2 framework.

The steps are :

1. Create 2 jsp files, as main page and display tag page.

mainPage.jsp

<%@ include file=”/include/definitions.jsp”%>

<!DOCTYPE html>

<html lang=”id”>

<head>

<title>Persetujuan Permintaan Pengadaan (PR)</title>

 

<script type=”text/javascript”>

if (!display)

var display = {};

display.jwebs = {

onTableLoad : function() {

$(“table#s th.sortable”).each(function() {

$(this).click(function() {

var link = $(this).find(“a”).attr(“href”);

jQuery.facebox(‘<div style=”padding: 17px; font-size: 36px;”>Loading….</div>’);

$(“div#hasil”).load(link, {}, display.jwebs.onTableLoad);

jQuery.facebox.close();

return false;

});

});

 

$(“div#hasil .pagelinks a”).each(function() {

$(this).click(function() {

var link = $(this).attr(“href”);

jQuery.facebox(‘<div style=”padding: 17px; font-size: 36px;”>Loading….</div>’);

$(“div#hasil”).load(link, {}, display.jwebs.onTableLoad);

jQuery.facebox.close();

return false;

});

});

}

};

 

$(document).ready(

function() {

$(“div#hasil”).load(

“${contextPath}/ajax/report/permintaan.jwebs”, {},display.jwebs.onTableLoad);

});

</script>

</head>

<body>

<!– BreadCrumbs –>

<div class=”breadCrumb module”>

<ul>

<li><s:a action=”dashboard”>Home</s:a></li>

</ul>

</div>

<!– End of BreadCrumbs –>

 

<div id=”right”>

<div class=”section”>

<!– Alert –>

<s:if test=”hasActionErrors()”>

<div class=”message red”>

<s:actionerror />

</div>

</s:if>

<s:if test=”hasActionMessages()”>

<div class=”message green”>

<s:actionmessage />

</div>

</s:if>

 

<div class=”box”>

<div class=”title”>

Daftar Permintaan Pengadaan <span class=”hide”></span>

</div>

<div class=”content”>

<div id=”hasil”></div>

</div>

</div>

</div>

</div>

</body>

</html>

 

display.jsp

 

<%@ include file=”/include/definitions.jsp”%>

<s:hidden name=”prcMainHeader.ppmId” id=”ppmId”></s:hidden>

<display:table name=”${listPrcMainHeader }” id=”s” pagesize=”10″

excludedParams=”*” style=”width: 100%;” class=”style1″

requestURIcontext=”true”

requestURI=”${contextPath }/ajax/report/permintaan.jwebs”

size=”resultSize” partialList=”true”>

<display:column title=”No.” style=”width: 20px; text-align: center;”>${s_rowNum}</display:column>

<display:column title=”Permintaan” total=”true” style=”width: 200px;”>

<c:choose>

<c:when test=”${update ==1 }”>

<a href=”${contextPath }/procurement/approval.jwebs?id=${s.ppmId }”>${s.ppmNomorPr

}</a>

</c:when>

<c:otherwise>

${s.ppmNomorPr }

</c:otherwise>

</c:choose>

 

</display:column>

<display:column title=”Nama Permintaan” style=”width: 150px; ”

class=”center”>${s.ppmSubject }</display:column>

<display:column title=”User Skr” style=”width: 150px;” class=”center”>${s.admUserByPpmCurrentUser.completeName }</display:column>

<display:column title=”Satker” style=”width: 100px;” class=”center”>${s.admDept.deptName }</display:column>

<display:column title=”Tanggal” class=”center” style=”width: 200px;”>

<fmt:formatDate value=”${s.createdDate }”

pattern=”HH:mm:ss, dd MMM yyyy” />

</display:column>

<display:column title=”Aksi” class=”center” style=”width: 200px;”>

<input type=”submit” name=”proses” value=”Proses”

onclick=”setValue(‘${s.ppmId }’);FreezeScreen(‘Data Sedang Di proses’)”

class=”uibutton” />

</display:column>

</display:table>

 

2. create action to populate data.

/**

* main page action

* @return

* @throws Exception

*/

@Action(

results={

@Result(name=SUCCESS,location=”report/mainPage.jsp”),

@Result(name=”ok”,location=”report/pemintaan”, type=”redirectAction”),

},value=”report/permintaan”

)

public String reportPr() throws Exception{

try {

 

} catch (Exception e) {

addActionError(e.getMessage());

return “ok”;

}

return SUCCESS;

}

 

/**

* ajax call action

* @return

* @throws Exception

*/

@Action(

results={

@Result(name=SUCCESS,location=”report/ajax/display.jsp”)

},value=”ajax/report/permintaan”

)

public String ajaxReportPr() throws Exception{

try {

model.setResultSize(prcService.countPrcMainHeaderPR(null));

model.setListPrcMainHeader(prcService.listPrcMainHeaderPR(null,model.getPage(request),size));

} catch (Exception e) {

addActionError(e.getMessage());

}

return SUCCESS;

}

3. Add method getPage(HttpRequest) to filter page data from display tag link navigation

/**

* get page from display tag

*

* @param request

* @return

* @author kusumah

*/

public int getPage(HttpServletRequest request) {

int page = 0;

@SuppressWarnings(“rawtypes”)

Enumeration paramNames = request.getParameterNames();

while (paramNames.hasMoreElements()) {

String name = (String) paramNames.nextElement();

if (name != null && name.startsWith(“d-“) && name.endsWith(“-p”)) {

String pageValue = request.getParameter(name);

if (pageValue != null) {

page = Integer.parseInt(pageValue) – 1;

}

}

}

return page;

}

3. create hibernate query to populate data.

public Object getListByDetachedCiteriaPaging(DetachedCriteria crit,int page, int size){

Session ses = HibernateUtil.getSession();

List<Object> lst = new ArrayList<Object>();

Criteria cr = crit.getExecutableCriteria(ses);

cr.setFirstResult(page*size).setMaxResults(size);

lst =cr.list();

ses.clear();

ses.close();

return lst;

}

 

@Override

public int countPrcMainHeaderPR(Object object) throws Exception {

DetachedCriteria dc = DetachedCriteria.forClass(PrcMainHeader.class);

dc.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

dc.setProjection(Projections.rowCount());

Object obj = getEntityByDetachedCiteria(dc);

return Integer.valueOf(String.valueOf(obj)).intValue();

}

@SuppressWarnings(“unchecked”)

@Override

public List<PrcMainHeader> listPrcMainHeaderPR(Object object, int page,

int i) throws Exception {

DetachedCriteria dc = DetachedCriteria.forClass(PrcMainHeader.class);

dc.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

dc.setFetchMode(“admUserByPpmCurrentUser”, FetchMode.JOIN);

dc.setFetchMode(“admDept”, FetchMode.JOIN);

dc.add(Restrictions.isNull(“ppmNomorRfq”));

return (List<PrcMainHeader>) getListByDetachedCiteriaPaging(dc, page, i);

}

 

and voila …

now display tag paging navigation will use ajax mechanism with internal query paging.

 

Thursday, 4 April 2013

Jwebs Tower

 

A. Ahmad Kusumah

Oracle Datafile Recovery (ORA-01172)

February 19, 2013 Leave a comment

Today, i Face a problem on oracle db server. the symptoms are :

  1. Try to login, but exception thrown,  said ‘ORA-01033: ORACLE initialization or shutdown in progress’
  2. Try to check listener status, result : Listener is running but nothing weird
  3. Try to open database manualy using command : ‘alter dabase open’, exception raised, ‘ORA-01172 : recovery of thread 1 stuck at block 747 of file 3’

To resolve this problem , the steps are :

  1. Connect as sysdba
  2. invoke following command : ‘select name, status, enabled from v$datafile where file#=3‘. The id come from file id raised in ORA-01172. The Result :  Id
  3. The result shows datafile need to be recovered
  4. To recover the datafile, invoke the following command ‘recover datafile 3’ , The id come from file id above. -
  5. Then invoke command to open database.

Voilaa .. now i can login to my oracle box without problem.

#NB : Don’t forget to backup database regularly, using hot backup or cold backup.

 

Jakarta WEBS Tower

Tuesday, 19 Februari 2012

 

A. Ahmad Kusumah

 

 

 

Categories: Database Tags: ,

PostgreSQL and Hibernate ‘Upper case’ problem

September 5, 2012 Leave a comment

Today,  i find out strange buggy problem on postgresql and hibernate integration implementation …

as an illustration, i have a table, named user with the following column

USER {

ID,

USERNAME,

PASSWORD

}

Then i create model according to current table as follow :

@Entity

@Table(name = “user”)

public class User implements Serializable {

private Integer id;

private String username;

private String password;

public User(){

}

public User(int id){

this.id = id;

}

@Id

@GeneratedValue(strategy = IDENTITY)

@Column(name = “ID”, unique = true, nullable = false)

public Integer getId() {

return id;

}

..

..

..

But, the strange problem appear when the query invoked, the following problem arises .

error

error

“column this_.ID doesn’t exist “

after several time debugging and try new different configuration, the solution is just convert all column name in user table and table name into lowercase.

and my table now is like this :

user{

id,

username,

password

}

and also the model change to lower case in annotation mapping declaration ..

@Id

@GeneratedValue(strategy = IDENTITY)

@Column(name = “id”, unique = true, nullable = false)

 

Jakarta, 5 September 2012

JWeBs Tower

 

A. Ahmad Kusumah

 

Categories: Database, Java Tags: , ,

Password life time on oracle

July 17, 2012 1 comment

Alter Profile

By default, user created in oracle has password expiration on 180 days. It means, when the time reached, the user will be forced to change their current password. This mechanism from security view is has advantages, it makes user aware to always maintain their current profile and data.

But, in other hand, sometimes an application need user with no expiration date of password such as ERP, Procurement etc. In order to facilitate this needs, there’s 2 ways to do so :

  1. Create stand alone profile with unlimited expiration date and assign to user
  2. Update user current profile, set limit of password expiration to unlimited

This time we’ll show the second method, update user current profile.

The steps are :

1. check current user profile using command :

select profile from dba_users where username='<username>’;

2. Change limit of password expiration policy o unlimited

alter profile <profile_name> limit password_life_time UNLIMITED;

3. Check profile after to make sure policy has been updated

select resource_name,limit from dba_profiles where profile='<profile_name>’;

Now,all users with this profile has no expiration date policy for their password …

Lampung, 17 July 2012

Aah Ahmad Kusumah

(IT Professional Consultant and Developer)