Solved

creating tables

Posted on 2003-11-21
10
239 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
10 Comments
 

Expert Comment

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

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

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

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 50

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

856 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