?
Solved

creating tables

Posted on 2003-11-21
10
Medium Priority
?
245 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

649 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