3.2.3.2 Create and Delete Database Tables
Use the ore.create function to create a persistent table in an Oracle Database schema.
Note:
When creating a table in Oracle Machine Learning for R, if you use lowercase or mixed case for the name of the table, then you must use the same lowercase or mixed case name in double quotation marks when using the table in a SQL query or function. If, instead, you use an all uppercase name when creating the table, then the table name is case-insensitive: you can use uppercase, lowercase, or mixed case when using the table without using double quotation marks. The same is true for naming columns in a table.Creating the table automatically creates an ore.frame
proxy object for the table in the R environment that represents your database
schema. The proxy ore.frame object has the same name as the table.
You can delete the persistent table in an Oracle Database schema with the
ore.drop function.
Caution:
Only use the ore.drop function to delete a database
table and its associated ore.frame proxy object. Never use it
to remove an ore.frame object that is not associated with a
permanent database table. To remove an ore.frame object for a
temporary database table, use the ore.rm function.
Example 3-13 Using ore.create and ore.drop to Create and Drop Tables
This example creates tables in the database and drops some of them.
# Create the AIRQUALITY table from the data.frame for the airquality data set.
ore.create(airquality, table = "AIRQUALITY")
# Create data.frame objects.
df1 <- data.frame(x1 = 1:5, y1 = letters[1:5])
df2 <- data.frame(x2 = 5:1, y2 = letters[11:15])
# Create the DF1 and DF2 tables from the data.frame objects.
ore.create(df1, "DF1")
ore.create(df2, "DF2")
# Create the CARS93 table from the data.frame for the Cars93 data set.
ore.create(Cars93, table = "CARS93")
# List the OML4R proxy objects.
ore.ls()
# Drop the CARS93 object.
ore.drop(table = "CARS93")
# List the OML4R proxy objects again.
ore.ls()Listing for This Example
R> # Create the AIRQUALITY table from the data.frame for the airquality data set.
R> ore.create(airquality, table = "AIRQUALITY")
R> # Create data.frame objects.
R> df1 <- data.frame(x1 = 1:5, y1 = letters[1:5])
R> df2 <- data.frame(x2 = 5:1, y2 = letters[11:15])
R> # Create the DF1_TABLE and DF2_TABLE tables from the data.frame objects.
R> ore.create(df1, "DF1")
R> ore.create(df2, "DF2")
R> # Create the CARS93 table from the data.frame for the Cars93 data set.
R> ore.create(Cars93, table = "CARS93")
R> # List the OML4R proxy objects.
R> ore.ls()
[1] "AIRQUALITY" "CARS93" "DF1" "DF2_"
R> # Drop the CARS93 object.
R> ore.drop(table = "CARS93")
R> # List the OML4R proxy objects again.
R> ore.ls()
[1] "AIRQUALITY" "DF1_" "DF2"Note:
A text query having more than 4000 characters or storing a value of over 4000 characters in a CLOB column will result in an error stating “ORA-01704: string literal too long”. Use a bind variable if the data is large as shown below. For more information on bind variables see ROracle.
library(ROracle)
options(error = expression(NULL))
Sys.setlocale(‘LC_ALL’, ‘C’)
cat(‘\n Welcome to ROracle(OCI) World\n’);
cat(‘\n DBI Version : ’);
print(packageVersion(‘DBI’));
cat(‘\n’);
#Creating table whose fields are of different type
createStr <- ‘create table TMRQORABND1_TAB(row_num number, id1 clob)’;
insStr <- ‘insert into TMRQORABND1_TAB values(:1, :2)’;
selStr <- ‘select * from TMRQORABND1_TAB order by row_num’;
y <- ‘1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcdef’;
z <- y
z <- paste(y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, ‘1234567890abcdef1234567890abcdef’, sep = ‘’);
c32767 <- paste(z, z, z, z, z, z, z, z, y, y, y, y, y, y, y, y, y, y, y,
‘1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcde’,
sep = ‘’)
print(nchar(c32767))
c32766 <- paste(z, z, z, z, z, z, z, z, y, y, y, y, y, y, y, y, y, y, y,
‘1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcd’,
sep = ‘’)
print(nchar(c32766))
c32768 <- paste(z, z, z, z, z, z, z, z, y, y, y, y, y, y, y, y, y, y, y,
‘1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcdef’,
sep = ‘’)
print(nchar(c32768))
y <- paste(y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, ‘1234567890abcdef1234567890abcdef’, sep = ‘’);
y1 <- y
y <- paste(y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, ‘1234567890abcdef1234567890abcdef’, sep = ‘’);
y2 <- y
y <- paste(y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, ‘1234567890abcdef1234567890abcdef’, sep = ‘’);
y3 <- y
y4 <- paste(y3, y3, y3, y3, y3)
r1c2 <- paste(y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y, y,
y, y, ‘1234567890abcdef1234567890abcdef’, sep = ‘’);
print(nchar(y));
drv <- dbDriver(‘Oracle’);
cat(‘\n ROracle driver allocated.\n’);
con <- dbConnect(drv,‘scott’,‘tiger’);
cat(‘\n One database connection object created.\n’);
#tryCatch(
#{
if (dbExistsTable(con, ‘TMRQORABND1_TAB’))
dbGetQuery(con,‘drop table TMRQORABND1_TAB’);
dbGetQuery(con, createStr);
cat(‘\nTable created with columns data type as raw(n) \n’);
x <- 1;
dbGetQuery(con, insStr, data.frame(x,r1c2));
dbCommit(con);
x <- c(2, 3, 4, 5, 6, 7, 8, 9, 10);
yy <- c(y1, y2, y3, z, y4, c32767, c32766, c32768, ‘’);
dbGetQuery(con, insStr, data.frame(x, yy));
dbCommit(con)
print(dbGetQuery(con, ‘select row_num, length(id1) from TMRQORABND1_TAB’));
x <- 100;
y <- paste(y, c32767, sep = ‘’);
dbGetQuery(con, insStr, data.frame(x,y));
dbCommit(con)
s <- dbSendQuery(con, selStr)
cinfo <- dbColumnInfo(s)
print(dbGetQuery(con, ‘select row_num, length(id1) from TMRQORABND1_TAB’));
res <- dbGetQuery(con, selStr)
if (res[,2][1] != r1c2) {
print(paste(‘Row’, res[,1][1], cinfo[,1][2], res[,2][1],
‘not equal to’, r1c2))
} else {
print(paste(‘Row’, res[,1][1], cinfo[,1][2], ‘length is’,
nchar(res[,2][1]),
‘length of data is’, nchar(r1c2)), sep = ‘’)
}
for (i in 2:9)
{
if (res[,2][i] != yy[i-1]) {
print(paste(‘Row’, res[,1][i], cinfo[,1][2], res[,2][i],
‘not equal to’, yy[i-1]))
} else {
print(paste(‘Row’, res[,1][i], cinfo[,1][2], ‘length is’,
nchar(res[,2][i]),
‘length of data is’, nchar(yy[i-1])), sep = ‘’)
}
}
if (!is.na(res[,2][10])) {
print(paste(‘Row’, res[,1][10], cinfo[,1][2], res[,2][10],
‘not equal to’, yy[9]))
} else {
print(paste(‘Row’, res[,1][10], cinfo[,1][2], ‘length is’,
nchar(res[,2][10]),
‘length of data is’, nchar(yy[9])), sep = ‘’)
}
if (res[,2][11] != y) {
print(paste(‘Row’, res[,1][11], cinfo[,1][2], res[,2][11],
‘not equal to’, y))
} else {
print(paste(‘Row’, res[,1][11], cinfo[,1][2], ‘length is’,
nchar(res[,2][11]),
‘length of data is’, nchar(y)), sep = ‘’)
}
#}, finally = {
dbGetQuery(con,‘drop table TMRQORABND1_TAB’);
cat(‘\n ROracle driver deallocated successfully.\n’);
cat(‘Releasing resources...‘);
dbDisconnect(con);
cat(‘\n Connection with database removed successfully.\n’);
dbUnloadDriver(drv);
cat(‘done\n’);
#}) # tryCatch()Parent topic: Create and Manage R Objects in Oracle Database