?
Solved

creating tables

Posted on 2003-11-21
10
Medium Priority
?
243 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 53

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

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 53

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

743 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