Solved

creating tables

Posted on 2003-11-21
10
241 Views
Last Modified: 2010-05-03
hello,
   i want to know how to  create a procedure to create 7 tables  in oracle database to use in vb code(by calling the procedure)
0
Comment
Question by:kalyani_g
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Expert Comment

by:tsravank
ID: 9802407
Is the table structure for all the 7 same or different
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 9802461
You can create a .sql file, where in this file includes all the SQLstatement statement you want, example:

DROP TABLE TBLLBSUSER_TRIAL;

DROP SEQUENCE SQLBSUSER_TRIAL;

CREATE TABLE TBLLBSUSER_TRIAL (ID NUMBER NULL, UNIQUEID VARCHAR2(20) NULL, EMAIL VARCHAR2(255) NULL,
HP VARCHAR2(20) NULL, AGREESMSSERVICE VARCHAR2(3) NULL, AGREETNC VARCHAR2(3) NULL,
ISBOOKMARK VARCHAR2(3) NULL, TOOMANYSMS VARCHAR2(3) NULL, PROMONONEED VARCHAR2(3) NULL,
PROMONOGOOD VARCHAR2(3) NULL, INCONVENIENTTIME VARCHAR2(3) NULL, OTHER VARCHAR2(3) NULL,
OTHERREASON VARCHAR2(255) NULL, SPECIFYHOWFREQUENT VARCHAR2(3) NULL, SPECIFYINFOTYPE VARCHAR2(3) NULL,
DISCOUNT30 VARCHAR2(3) NULL, TELLUTIMERCVSMS VARCHAR2(3) NULL, SIMPLYIRRITATING VARCHAR2(3) NULL,
UCOMMENT VARCHAR2(1000) NULL, IP VARCHAR2(14) NULL,
SUBSCRIBE VARCHAR2(20) NULL, UPDATED DATE NULL, ADDDATE DATE NULL);

CREATE SEQUENCE SQLBSUSER_TRIAL INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER TRLBSUSER_TRIAL_ID
BEFORE INSERT OR UPDATE OF "ID" ON TBLLBSUSER_TRIAL
FOR EACH ROW
BEGIN      
  SELECT SQLBSUSER_TRIAL.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER TRLBSUSER_TRIAL_DATE  
BEFORE INSERT OR UPDATE OF "ADDDATE" ON TBLLBSUSER_TRIAL  
FOR EACH ROW
BEGIN    
  SELECT SYSDATE INTO :NEW.ADDDATE FROM DUAL;
END;
/

COMMIT;

then try to run the .sql file by using code below:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long

Private Function StartDoc(ByVal FileName As String, Optional CommandLine As String = "") As Long
    Dim Scr_hDC As Long
    Scr_hDC = GetDesktopWindow()
    'change "Open" to "Explore" to bring up file explorer
    StartDoc = ShellExecute(Scr_hDC, "Open", FileName, CommandLine, vbNullString, 1)
End Function

run your .sql file like:

StartDoc "E:\Oracle\Ora81\BIN\SQLPLUSW.EXE", "uat/uat @c:\test.sql"

Hope this helps
0
 

Author Comment

by:kalyani_g
ID: 10206185
7 tabels will have different structure
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kalyani_g
ID: 10206186
7 tabels will have different structure
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 10207000
>>7 tabels will have different structure
It's definitely True! You need to design your Entity (tables) Relationship Diagram (ERD), so that mean the tables created should be relational.

Try the above example?
0
 

Author Comment

by:kalyani_g
ID: 10215052
hello ryancys
       I explain what I need in detail.using sql file creating tables is ok.but it is open to the client also.my idea is that if I do it in a oracle procedure so that I just use the procedure name and I can give vb exe file to client .Only I know the password of the database so it is safe and no one can touch my procedure.this is what my plan.if it is possible plz tell me how to write a procedure or is there any other solution for this problem.    

regards
kalyani
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 10218422
The table structures should always remains the same, unless it's part of user requirement that need create different tables periodically.

And i'm not very familiar with Procedure in Oracle so i cant help you that much on that.

If above comments dont help, my suggestion will be PAQ as 0 pts with refund.

regards
0
 

Accepted Solution

by:
amp072397 earned 0 total points
ID: 10618636
PAQed, with points refunded (20)

Thanks very much!
amp, ee admin, amp*at*experts-exchange.com
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question