PERL – Insert data into Oracle Database


#!/usr/bin/perl
 
# Use the DBI module. In windows make sure that you have Oracle Client
use DBI; 
 
# Connect to the database 
# Note: connection can be used to execute more than one statement  on any number of tables in the database 

$dbh = DBI->connect('dbi:Oracle:host=1.2.3.4;sid=SID1234;port=1521', 'USERID', 'PASSWORD', { RaiseError => 1, AutoCommit => 1 }) || die "Connect failed: $DBI::errstrn"; 

$sampleData = "insert into SCHEMA.TABLE1 (TABLE1_ID, COL2, COL3) values (SCHEMA.TABLE1_ID_SEQ.nextval ,'DATA1','DATA2')"; 
 
# Create the statement. 
$stmt = $sampleData; 
 
# Prepare and execute the SQL query 
$sth = $dbh->prepare($stmt) || die "prepare: $$stmt: $DBI::errstr"; 
$sth->execute || die "execute: $$stmt: $DBI::errstr"; 
 
# INSERT does not return records 
 
# Clean up the record set 
$sth->finish(); 
 
# We could add another record here as well 
 
# Create the statement.
$stmt = "insert into SCHEMA.TABLE1 (TABLE1_ID, COL2, COL3) values (SCHEMA.TABLE1_ID_SEQ.nextval ,'DATA1','DATA2')"; 
 
# Prepare and execute the SQL query $sth = 
$dbh->prepare($stmt) || die "prepare: $$stmt: $DBI::errstr";  
$sth->execute || die "execute: $$stmt: $DBI::errstr"; 
 
 
# Clean up the record set and the database connection 
$sth->finish(); 
$dbh->disconnect(); 

exit;