Solved

creating tables

Posted on 2003-11-21
10
235 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 49

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
 

Author Comment

by:kalyani_g
ID: 10206186
7 tabels will have different structure
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 49

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 49

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now