PL/SQL: Script translate view to create table

Basic script, boleh modify lagi untuk PK, nullable dll.

...

 SELECT ' "' || COLUMN_NAME || '" ' || DATA_TYPE || ' (' || data_length || ' ' || decode(char_used,'B','BYTE') || ') , '  
 FROM user_tab_columns   
 WHERE table_name='TABLE_NAME'  
 ;  

Oracle DB: lsnrctl on Windows

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\aidy>lsnrctl

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 14-FEB-2013 10:29:38

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is C:\oracle\product\11.2.0\dbhome_3\network\admin\listener.ora
Log messages written to c:\oracle\diag\tnslsnr\ikcm-110304\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ikcm-110304)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                14-FEB-2013 10:29:54
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\11.2.0\dbhome_3\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\ikcm-110304\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ikcm-110304)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL>

PL/SQL: dba_data_files

Selalu lupa mana letaknya EM? Macam mana nak akses EM untuk modify/view tablespace?
atau prefer guna sqldeveloper/sqlplus

Boleh query gunakan table dba_data_files

SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;

desc dba_data_files;

select * from dba_data_files;

alter database datafile
   'imsdata.dbf'
autoextend on;

Ibatis 2: SQL Map untuk Oracle DB Procedure.

 <procedure id="StaffAppraisal.updateKra" parameterClass="map">  
      <![CDATA[  
       {  
           #p_return_val,jdbcType=DECIMAL,mode=OUT# = call appraisal.update_kra(   
                #p_staff_id,jdbcType=VARCHAR,mode=IN#,   
                #p_appraisal_seq,jdbcType=VARCHAR,mode=IN#,  
                #p_session_code,jdbcType=VARCHAR,mode=IN#,   
                #p_year,jdbcType=VARCHAR,mode=IN#,  
                #p_kra,jdbcType=VARCHAR,mode=IN#,   
                #p_errmsg,jdbcType=VARCHAR,mode=OUT#  
           )  
       }  
      ]]>  
 </procedure>  

Dev info : 
Codename= APAR
DB = Oracle 10G

Contoh DAO (tiada kena mengena dengan map di atas):
...

      public String sendEmail (String formOwnerId, String emailType, String refCode)  
      {  
           String errorMesej = null;  
           Map<String,String> param = new HashMap<String,String>();  
           param.put("p_staff_id"     , formOwnerId);  
           param.put("p_email_type", emailType);  
           param.put("p_ref"          , refCode);  
           param.put("p_errmsg"     , refCode);  
           this.getSqlMapClientTemplate().update("EMAIL.sendEmail", param);  
           errorMesej = param.get("p_errmsg");  
           return errorMesej;  
      }  

...


How to call an Oracle 10G procedure in ibatis 2?

Ibatis : Retrieve value from Oracle DB's function

SQL Mapping

 <parameterMap class="map" id="getJobDescParam">  
      <parameter property="p_job_code" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>  
 </parameterMap>  
 <select id="getJobDesc" parameterMap="getJobDescParam" resultClass="java.lang.String">  
      select hr_util.get_job_desc(?) from dual  
 </select>  

Contoh kod DAO

      public String getJobDesc(String jobCode)   
      {  
           String returnString;  
           Map<String, Object> map = new HashMap<String, Object>();  
           map.put("p_job_code", jobCode);  
           // Reference com.icms.biz.dao.ibatis.maps.StaffMain.xml  
           returnString = (String)getSqlMapClientTemplate().queryForObject("getJobDesc",map);  
           if(StringUtils.isEmpty(returnString)){  
                returnString = "-";  
           }  
           return returnString;  
      }