Q:
How to create procedure in Oracle
I WANT TO CREATE A PROCEDURE FOR DOING FOLLOWING 4 ACTIONS
1)
That will first delete all data from xa and then load data from xls file (TOWN.XLS on d drive)with columns USER_ID, CLUSTER_CODE,TOWN, TOWN_CATEGORY, TOWN_TYPE, TYPE, STATUS
INto the table XA
TABLE XA
(
USER_ID VARCHAR2(40) NOT NULL,
CLUSTER_CODE VARCHAR2(15) NOT NULL,
TOWN VARCHAR2(50),
TOWN_CATEGORY VARCHAR2(50),
TOWN_TYPE VARCHAR2(50),
TYPE VARCHAR2(25),
STATUS VARCHAR2(10)
)
2) perform the insert as
insert into XC select a.user_id,a.user_id,xa.cluster_code,a.full_name,a.CIRCLE, xa.TOWN, xa.TOWN_CATEGORY, xa.TOWN_TYPE, xa.TYPE, xa.STATUS, default
from A,xa where a.user_id=xa.user_id and user_id in (select user_id from xa)
TABLE XC
(
CRTCODE VARCHAR2(25) NOT NULL,
USER_ID VARCHAR2(40) NOT NULL,
CLUSTER_CODE VARCHAR2(15) NOT NULL,
OUTLET_NAME VARCHAR2(100) NOT NULL,
CIRCLE VARCHAR2(50) NOT NULL,
TOWN VARCHAR2(50),
TOWN_CATEGORY VARCHAR2(50),
TOWN_TYPE VARCHAR2(50),
TYPE VARCHAR2(25),
STATUS VARCHAR2(10),
CREATED_DATE DATE DEFAULT SYSDATE
)
3) perform the insert as
insert into xb select user_id,full_name,default,circle,otc_code,default,default from a
TABLE XB
(
USERID VARCHAR2(40) NOT NULL,
USERNAME VARCHAR2(40) NOT NULL,
USERTYPE NUMBER DEFAULT 0 NOT NULL,
CIRCLE VARCHAR2(25) NOT NULL,
OTC_CODE VARCHAR2(10),
PASSWORD VARCHAR2(20) DEFAULT NULL,
PWDCHANGEFLAG NUMBER DEFAULT NULL
)
4) if role id 130 not mapped for userid (xls sheet )then
insert user_id with role_id 130 in table xd
TABLE XD
(
USER_ID VARCHAR2(40),
ROLE_ID NUMBER(4)
)
1)
That will first delete all data from xa and then load data from xls file (TOWN.XLS on d drive)with columns USER_ID, CLUSTER_CODE,TOWN, TOWN_CATEGORY, TOWN_TYPE, TYPE, STATUS
INto the table XA
TABLE XA
(
USER_ID VARCHAR2(40) NOT NULL,
CLUSTER_CODE VARCHAR2(15) NOT NULL,
TOWN VARCHAR2(50),
TOWN_CATEGORY VARCHAR2(50),
TOWN_TYPE VARCHAR2(50),
TYPE VARCHAR2(25),
STATUS VARCHAR2(10)
)
2) perform the insert as
insert into XC select a.user_id,a.user_id,xa.cluster_code,a.full_name,a.CIRCLE, xa.TOWN, xa.TOWN_CATEGORY, xa.TOWN_TYPE, xa.TYPE, xa.STATUS, default
from A,xa where a.user_id=xa.user_id and user_id in (select user_id from xa)
TABLE XC
(
CRTCODE VARCHAR2(25) NOT NULL,
USER_ID VARCHAR2(40) NOT NULL,
CLUSTER_CODE VARCHAR2(15) NOT NULL,
OUTLET_NAME VARCHAR2(100) NOT NULL,
CIRCLE VARCHAR2(50) NOT NULL,
TOWN VARCHAR2(50),
TOWN_CATEGORY VARCHAR2(50),
TOWN_TYPE VARCHAR2(50),
TYPE VARCHAR2(25),
STATUS VARCHAR2(10),
CREATED_DATE DATE DEFAULT SYSDATE
)
3) perform the insert as
insert into xb select user_id,full_name,default,circle,otc_code,default,default from a
TABLE XB
(
USERID VARCHAR2(40) NOT NULL,
USERNAME VARCHAR2(40) NOT NULL,
USERTYPE NUMBER DEFAULT 0 NOT NULL,
CIRCLE VARCHAR2(25) NOT NULL,
OTC_CODE VARCHAR2(10),
PASSWORD VARCHAR2(20) DEFAULT NULL,
PWDCHANGEFLAG NUMBER DEFAULT NULL
)
4) if role id 130 not mapped for userid (xls sheet )then
insert user_id with role_id 130 in table xd
TABLE XD
(
USER_ID VARCHAR2(40),
ROLE_ID NUMBER(4)
)
ASKED:
Apr 11 2009 12:28 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
That said, the only difficult part of your request is getting data form the excel spreadsheet. The reality is there is no simple way to do this. You must do one of:
1) export you excel sheet to a CSV file, then define an external table to the file
2) buy a third party excel mapper product (if you can find it, and it won't be cheap), that provides an api from oracle PLSQL that allows you to manipuate excel files directly. Good luck with that one.
3) check oracle for a utility or api that does same as #2. I seem to recall that had such a product at one point but do not remember its name. Maybe a tar put to metalink can get you that answer.
#1 above is the most common way people do it. This fact should tell you something.
Given #1 above, once you have defined the external table, you can query it for data directly so you do not actualy have to load it anywhere unless you want a record of it for historical purposes, or you need to update the data. The point here of course is that the excel data is not directly available via the XLS file.
Good luck, Kevin
