hi ,
i want to create tables at oracle9i using vb 6.0 at runtime.I would be grateful to anyone who can help me inthis regard.i'm using windows 2000,oracle 9i,vb 6.0 and ado 2.5.
----------biplab------------
Printable View
hi ,
i want to create tables at oracle9i using vb 6.0 at runtime.I would be grateful to anyone who can help me inthis regard.i'm using windows 2000,oracle 9i,vb 6.0 and ado 2.5.
----------biplab------------
You just need to write "Create Table..." sql statement and execute it via say ADO connection.
Here is a sample logic:
VB Code:
Private Sub Command1_Click() Dim strSQL$ strSQL = "CREATE TABLE USER_LOGIN" & vbNewLine strSQL = strSQL & " (LNAME VARCHAR2(24) NOT NULL," & vbNewLine strSQL = strSQL & " FNAME VARCHAR2(24) NOT NULL," & vbNewLine strSQL = strSQL & " LOGIN_ID VARCHAR2(16) NOT NULL," & vbNewLine strSQL = strSQL & " PWD VARCHAR2(10) NOT NULL)" & vbNewLine strSQL = strSQL & "PCTFREE 10" & vbNewLine strSQL = strSQL & "PCTUSED 40" & vbNewLine strSQL = strSQL & "INITRANS 1" & vbNewLine strSQL = strSQL & "MAXTRANS 255" & vbNewLine strSQL = strSQL & "TABLESPACE TABLE_SPACE1" & vbNewLine strSQL = strSQL & "STORAGE" & vbNewLine strSQL = strSQL & " (INITIAL 1048576" & vbNewLine strSQL = strSQL & " NEXT 1048576" & vbNewLine strSQL = strSQL & " PCTINCREASE 0" & vbNewLine strSQL = strSQL & " MINEXTENTS 1" & vbNewLine strSQL = strSQL & " MAXEXTENTS 505)" & vbNewLine strSQL = strSQL & "/" & vbNewLine strSQL = strSQL & "GRANT DELETE ON USER_LOGIN TO END_USER" & vbNewLine strSQL = strSQL & "/" & vbNewLine strSQL = strSQL & "GRANT INSERT ON USER_LOGIN TO END_USER" & vbNewLine strSQL = strSQL & "/" & vbNewLine strSQL = strSQL & "GRANT SELECT ON USER_LOGIN TO END_USER" & vbNewLine strSQL = strSQL & "/" & vbNewLine strSQL = strSQL & "GRANT UPDATE ON USER_LOGIN TO END_USER" & vbNewLine strSQL = strSQL & "/" adoConnection.Execute strSQL End Sub
how with drop/create table and insert into table on file sql to stored into Oracle 10g with VB 6.0?
You also need to ensure that the user has quota on the tablespace that the table will be created in, and that they have the create objects privaliage, either granted to the role the user has or as an individual.
bludab-- You need to explain what you're asking for hear. I have absolutley no idea what question you are asking.
my idea:
i will input sql file to stored Oracle Server use VB 6, this file contains: drop,crate alter and insert (sql statement)...
that its!
thanks
Are you always going to drop create alter the same table or user? If not you need to execute them from the program and build the SQL as needed. If you store the data partially on the server (in some table that holds a bunch of different SQL commands) you need to be able to set up Oracle replacement values (using the amper sign (&)) this can be a big problem for someone not very familar with Oracle. You may also need to use the execute immediate SQL statement if you modify the SQL using replacement variable and it is used inside a Stored Procedure (or ananonomus block).