Oracle 10g: Flush Data To External Table - Using Insert Statement

I'm using external table as a passage to load data from a flat file ''scname.CSV' to respective table - "GBL_SPECIES_MST".

Script:

/*
*
* Pump data to table:
*
*
*
* @Migration SPECIESDIR
* @author Nur Fiza Suhati
* @version 1.0
*
*/
/*
1. Directory to database
*/
CREATE OR REPLACE DIRECTORY SPECIESDIR AS 'C:\oracle\product\10.1.0\oradata\myep';
DROP TABLE SPECIES_EXT;
create table SPECIES_EXT (
ID varchar2(100),SCIENTIFICNAME VARCHAR2(100))
Organization external
(type oracle_loader
default directory SPECIESDIR
access parameters (records delimited by newline
fields terminated by '')
location ('scname.csv'))
reject limit 100000;

TRUNCATE TABLE GBL_SPECIES_MST;
INSERT INTO GBL_SPECIES_MST (SPECIESID,SCIENTIFICNAME)
(
SELECT SPECIES_EXT.ID,SPECIES_EXT.SCIENTIFICNAME
FROM SPECIES_EXT SPECIES_EXT
)
;

I'm so not rational at the moment....

...Frustated..iyup..i'm all that..

just came out from the boss' room talking 'bout future plan...bla bla...and usually i just snap out from all the topics...but everytime he says 'bout his plan..the only plan that i ever put my eyeballs in will be this database section plan - ORACLE...

meetings over meetings..he'll say..'yes..i need 1 or 2 people in this'...usually there'll be 2 names crafted in my head...and definitely so far i think one out of those 2 names will be 'ME'...

i just can't stand waiting and hoping that it is me whom clutching that OCA or OCP cert...therefore in a relax manner I asked him..who does he want to send for the oracle certification..wohooo!!..I AM NOT HIS FAVOR'..I told him - I've spent my 2 years focusing in 'ONLY' oracle programming..and i really want to move into this path..he just nodded and said, 'oo i need to discuss this with the others'..PLUS..'MR M writes good stored proc'...my prompt answer: 'oo..en K tak tengok saya punye lagi(oo you haven't seen mine yet)'...

..come to think about it...i just feel like my artwork has not being appreciated..i thank you Rohaizal for giving me the opportunity for those oracle training...I don't blame Mr M for being recognized by the boss..maybe he really is good..but If..just If there's someone here to sell my name for that seat...I might be seen...

well..my dreams are not shreadded yet..Backup plan - of course spending my very own kaching...but..I must earn more than what i got today...How? Leaving is one of the options......

Oracle 10g: Show All Tables Command

...at times when we need to list out all the tables in our database..this command saves us a lot of time...

select owner, table_name, tablespace_name
from dba_tables
group by owner,table_name, tablespace_name

Oracle 10g: Change from datatype number to boolean

I was supposed to change a field's datatype from integer to boolean. It took me quite a while to disentangle the problem...so I figure out that table alteration (scripting) in oracle 10g is quite confusing compared to SQL 2000. I kept on receiving this type of error:

*** ORA-01451: column to be modified to NULL cannot be modified to NULL ***

There are multiple solutions being suggested through out the net....but I chose to keep it stupidly simple to solve it....

  

1. Column to be modified must be empty to change datatype...What I did was:

UPDATE [table_name]

SET [column_name] = NULL

UPDATE GBL_DOCUMENT_MST
SET ISPAYMENT = NULL

  

2. Now....I can alter the table successfully...

Alter table GBL_DOCUMENT_MST
modify
(
ISPAYMENT CHAR(1) CHECK (ISPAYMENT IN ( 'T', 'F' ))
);

  

3. Time to Execute!...

Oracle 10g: IN Operator

I always forgot on how to use the IN Operator....very useful yet so easy to be forgotten...


I had a task where I had to update the child's table(
gbl_mouprogress_dtl) STATUS-field automatically ONCE the master table (
gbl_mou_mst) STATUS-field being updated using the OWNERTYPE-field, OWNERID-field.

UPDATE GBL_MOUPROGRESS_DTL
SET STATUS = STATUS_,
LASTMODIFIEDBY = LASTMODIFIEDBY_,
LASTMODIFIEDDATE = SYSDATE
WHERE MOUPROGRESSID IN (
SELECT gbl_mou_mst.mouid
FROM gbl_mou_mst gbl_mou_mst
WHERE ( (gbl_mou_mst.status = WHERESTATUS_)
AND (gbl_mou_mst.branchid = BRANCHID_)
AND (gbl_mou_mst.ownertype = OWNERTYPE_)
AND (gbl_mou_mst.ownerid = OWNERID_)
)
);

MS Project 2007: Templates From Microsoft Project

No idea on how to start a project? Templates should be able to help you stop 'wandering'...:)

Make sure you have the Templates menu on the right sidebar...choose On Computer...

A dialog box will pop out....choose any template you reckon..e.g: Software Development...

.....the Output....

MS Project 2007: Create New Project

Double Click Image to Enlarge...

find Project > Project Information from the menu...

set your Start Date...then click 'OK'..


A new project has been created...

What if... we want to create a new project from the existing project ?

Click on New >

• Blank Project
• Totally new project
• Create new project from your current project file
• Copy file from any project and save as as new one

MS Project 2007: Today I learn MS Project...

Project Management? powwow...never been listed along my career path. I rather be a specialist of 'something' than becoming a project manager. But! We never knew the future...

Few weeks ago, we attended a 2-day-course learning Microsoft Project 2007. I shall be providing the notes that I took for everyone's reference. Sharing is caring... :)

Oracle 10g: Create Database

Thinking on how to create a new database? Follow these steps:

..choose

..then..

..then..

..then..

..finally..

Oracle 10g: Installation

I found my own reference for Oracle 10g installation that I made previously. Hope this will help everyone to install at your own. But first you have to download the software from http://www.oracle.com/ . Its Free! After finished downloading it, make sure you find a folder called install and find a file called SETUP.EXE inside the install folder. Double click the setup file. Choose Install Product and this will come out. [Double Click Image to Enlarge].

We can choose any type that we want. For starter, we select the Standard Edition.

Uncheck the ‘Create Starter Database’. It is better to create new database separately.

...then...

...then...

...then...

...and finally...Click Exit...