nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Writing data table result set to existing Oracle table

gerardmcl

Hi,

I'm very new to JMP scripting. I have built a script to read in from an Oracle table. It applies a Fit_Model to the data (couldn't replicate this in Oracle hence the need for JMP) and want to write it back out to Oracle.

At the moment I am using dt3<<save database("DRIVER={Oracle in instantclient11_1};DBQ=#####;UID=####;SPWD=####;","TBL_STAGING_EXAMPLE");

This creates the table, but I really would need to be able to INSERT INTO an existing table instead of creating a new table.

Any help would be greatly appreciated, as I say I'm brand new to JMP script and I can't find answer in JMP Script manual.

Thanks,

Accepted answers

pmroz

Perhaps your JMP table columns are numeric? That's what is indicated by the error message. Also you are missing some single quotes for some of the columns. Try wrapping each column with the CHAR function. If that still doesn't work print out your sql_statement variable.

Current Data Table(dt);

dbc = Create Database Connection(

"DRIVER={Oracle in instantclient11_1};DBQ=DBASE;UID=UNAME;SPWD=PWORD;"

);

For( i = 1, i <= N Rows( dt ), i++,

sql_statement = "INSERT INTO SCHEMA.TBL_TABLE " ||

"(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8) " ||

"VALUES(" || "'" ||

char(Column( 1 )[i]) || "', '" || char(Column( 2 )[i]) || "', '" ||

char(Column( 3 )[i]) || "', '" || char(Column( 4 )[i]) || "', '" ||

char(Column( 5 )[i]) || "', '" || char(Column( 6 )[i]) || "', '" ||

char(Column( 7 )[i]) || "', '" || char(Column( 8 )[i]) || "') " ;

print(sql_statement);

Execute SQL( dbc, sql_statement );

);

Close Database Connection( dbc );

All comments

pmroz

If you're in JMP 10, here's how to insert a record. Note that DSN_STRING is the connection string to my database. Note also that the SQL string has to be on one continuous line, no comments.

sql_statement =

"INSERT INTO myschema.mytable " ||

"(one_col, second_col, third_col) " ||

"VALUES(" ||

"'" || first_value || "', " ||

"'" || second_value || "', " ||

"'" || third_value || "') ";

dbc = Create Database Connection(dsn_string);

// JMP will add a commit to the insert statement.

Execute SQL(dbc, sql_statement);

Close Database Connection(dbc);

// In JMP 8 or 9 just use this:

// Open Database(dsn_string, sql_statement);

gerardmcl

Thank you very much for replying.

With the values I enter how can I specify they are from the data table?

I have tried simply using column(1) etc but this doesn't work. I am from a PL/SQL background so my brain says dt.column(1) but this doesn't work.

Can you please clarify?

dt=rftmdl[1][Table Box(3)] << make combined datatable("Tbl_Combinied");

sql_statement =

"INSERT INTO SCHEMA.TBL_TABLE " ||

"(col1, col2, col3, col4, col5, col6, col7, col8) " ||

"VALUES(" ||

"'" || column(1) || "', " ||

"'" || column(2)|| "', " ||

"'" || column(3)|| "', " ||

column(4)|| ", " ||

column(5)|| ", " ||

column(6)|| ", " ||

column(7)|| ", " ||

"'" || column(8) || "') ";

dbc = Create Database Connection("DRIVER={Oracle in instantclient11_1};DBQ=DBNAME;UID=USER;SPWD=PASS;");

// JMP will add a commit to the insert statement.

Execute SQL(dbc, sql_statement);

Close Database Connection(dbc);

Also if you know any useful links or JMP script guides, all help is greatly accepted.

Thanks again.

pmroz

Gerard,

You're almost there. To add multiple records you have to loop over the rows in your JMP table and issue separate INSERT statements.

dt = rftmdl[1][Table Box(3)] << make combined datatable("Tbl_Combinied");

current data table(dt);

dbc = Create Database Connection("DRIVER={Oracle in instantclient11_1};DBQ=DBNAME;UID=USER;SPWD=PASS;");

for (i = 1, i <= nrows(dt), i++,

sql_statement =

"INSERT INTO SCHEMA.TBL_TABLE " ||

"(col1, col2, col3, col4, col5, col6, col7, col8) " ||

"VALUES(" ||

"'" || column(1)[i] || "', " ||

"'" || column(2)[i] || "', " ||

"'" || column(3)[i] || "', " ||

column(4)[i]|| ", " ||

column(5)[i] || ", " ||

column(6)[i] || ", " ||

column(7)[i] || ", " ||

"'" || column(8)[i] || "') ";

// JMP will add a commit to the insert statement.

Execute SQL(dbc, sql_statement);

);

Close Database Connection(dbc);

To refer to an individual table cell you can use syntax like this:

column(1)[3]; // Refers to the cell in the current data table, in the first column, row 3

column(dt, 5)[7]; // Cell in column 5, row 7 of the table referred to by the variable dt

column(dt, "World")[2]; // Cell in column named World, row 2, in the table referred to by dt. This is the syntax I use most frequently

:World[2]; // Cell in column World, row 2, in the current data table.

Good luck!

Peter

gerardmcl

Hi,

I have tried working with this but getting an error that seemingly is due to the concatentation.

All columns in the table are VARCHAR2, the concatenation looks fine from an Oracle developer point of view.

Could you please help put me over the finishing line?

argument should be character in access or evaluation of 'Concat' , Bad Argument( Column( 5 ) )

In the following script, error marked by /*###*/

Current Data Table( dt );

dbc = Create Database Connection(

"DRIVER={Oracle in instantclient11_1};DBQ=DBASE;UID=UNAME;SPWD=PWORD;"

);

For( i = 1, i <= N Rows( dt ), i++,

sql_statement = "INSERT INTO SCHEMA.TBL_TABLE " || /*###*/

"(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8) " || /*###*/

"VALUES(" || /*###*/"'" || /*###*/Column( 1 ) || /*###*/"', " || /*###*/

"'" || /*###*/Column( 2 ) || /*###*/"', " || /*###*/"'" || /*###*/

Column( 3 ) || /*###*/"', " || /*###*/Column( 4 ) || /*###*/", " || /*###*/

Column( 5 ) || /*###*/", " || /*###*/Column( 6 ) || /*###*/", " || /*###*/

Column( 7 ) || /*###*/", " || /*###*/"'" || /*###*/Column( 8 ) || /*###*/

"') " /*###*/;

Execute SQL( dbc, sql_statement );

);

Close Database Connection( dbc );

pmroz

Just wanted to update this thread with some new information. I was trying to do multiple inserts (~8000) into an Oracle table and ran into performance problems when inserting one record at a time. The "trick" is to use the INSERT ALL INTO command. The code below inserts 200 records at a time.

dt = data table("RandomData");

dsn_string = "Driver={Oracle in OraClient11g64};Dbq=<your-database>;UID=<schema>;PWD=<password>;";

dbc = create database connection(dsn_string);

sql_statement =

"\[CREATE TABLE <schema>.FYI_RANDOM

(ID NUMBER,

RAND_INTEGER NUMBER,

RAND_NUMBER NUMBER,

RAND_DATETIME DATE,

RAND_STRING VARCHAR2(80))]\";

execute sql(dbc, sql_statement);

// Remove rows from the table (for repeated testing)

execute sql(dbc, "truncate table <schema>.fyi_random");

t2 = Tick Seconds();

max_inserts = 200; // Do 200 inserts at a time

sql_statement = "INSERT ALL ";

for (i = 1, i <= nrows(dt), i++,

idc = dt:id[i];

rand_integerc = dt:rand_integer[i];

rand_numberc = dt:rand_number[i];

rand_datetimec = format(dt:rand_datetime[i], "m/d/y h:m:s");

rand_stringC = dt:rand_string[i];

sql_statement = sql_statement || evalinsert(

" INTO <schema>.FYI_RANDOM(ID, RAND_INTEGER, RAND_NUMBER, RAND_DATETIME, RAND_STRING)

VALUES(^idc^, ^rand_integerc^, ^rand_numberc^,

to_date('^rand_datetimec^', 'MM/DD/YYYY HH:MI:SS AM'),

'^rand_stringc^')

");

if (mod(i, max_inserts) == 0,

// Weird - INSERT ALL INTO requires a SELECT statement at the end - use a dummy one

sql_statement = sql_statement || " SELECT 1 FROM DUAL";

execute sql(dbc, sql_statement);

sql_statement = "INSERT ALL ";

);

if (mod(i, 200) == 0,

caption("Inserted " || char(i) || " records");

wait(0);

);

// See if there's anything left to do

if (sql_statement != "INSERT ALL ",

execute sql(dbc, sql_statement);

);

close database connection(dbc);

t1 = tick seconds();

print(t1 - t2);

caption(remove);

wait(0);

The INSERT ALL INTO command is actually not that new - the examples I found online show it working in Oracle 9. Here are some links with more information:

https://www.techonthenet.com/oracle/questions/insert_rows.php

sql - Best way to do multi-row insert in Oracle? - Stack Overflow