How to create procedure in Oracle

10 pts.
Tags:
Microsoft Excel import/export
Oracle development
Oracle import/export
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) )
ASKED: April 11, 2009  12:28 PM
UPDATED: July 30, 2009  3:55 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I will presume you already know how to write insert as select so that you can insert data from one table to another.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following