DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Why Database Migrations Take Months and How to Speed Them Up
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems

Trending

  • From Zero to Production: Best Practices for Scaling LLMs in the Enterprise
  • Why Database Migrations Take Months and How to Speed Them Up
  • A Simple, Convenience Package for the Azure Cosmos DB Go SDK
  • Why High-Performance AI/ML Is Essential in Modern Cybersecurity
  1. DZone
  2. Data Engineering
  3. Databases
  4. Basic CRUD Operations Using Perl and DBD::Oracle

Basic CRUD Operations Using Perl and DBD::Oracle

Use a driver to help you directly implement CRUD operations, giving you more control over your data and helping you make it work for you.

By 
Blaine Carter user avatar
Blaine Carter
·
Sep. 17, 16 · Tutorial
Likes (5)
Comment
Save
Tweet
Share
4.6K Views

Join the DZone community and get the full member experience.

Join For Free

In this series, we're going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the DBD::Oracle driver.

A Good ORM Will Handle Most of Your Needs

An ORM tool can handle many of the repetitive processes when interfacing with a database.  Your project might call for an ORM tool such as DBIx::Class. No doubt about it, a good ORM can come in very handy. An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why Learn to Use the Driver Directly?

An ORM brings its own, different complexity to a project.  It may be overkill for some projects. There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you're like me, it's hard to be satisfied with a black box approach. You want to know more about how your tools work and you want to have options, just in case.

Martin Fowler said, "Mapping to a relational database involves lots of repetitive, boilerplate code. A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%."

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we'll use. Please make sure you're connected to a schema in which you can safely execute commands like these.

CREATE TABLE LCS_PEOPLE (
    id NUMBER GENERATED BY DEFAULT AS identity,
    name VARCHAR2(20),
    age NUMBER,
    notes VARCHAR2(100)
)
/

ALTER TABLE LCS_PEOPLE
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
/

CREATE TABLE LCS_PETS (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(20),
    owner NUMBER,
    type VARCHAR2(100)
)
/

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
/

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

INSERT INTO LCS_PEOPLE (name, age, notes)
 VALUES ('Bob', 35, 'I like dogs')
/

INSERT INTO LCS_PEOPLE (name, age, notes)
 VALUES ('Kim', 27, 'I like birds')
/

INSERT INTO LCS_PETS (name, owner, type)
 VALUES ('Duke', 1, 'dog')
/

INSERT INTO LCS_PETS (name, owner, type)
 VALUES ('Pepe', 2, 'bird')
/

COMMIT
/

 Making the Connection

  1. Use strict.
  2. Use the DBD::Oracle driver.
  3. Get the connection string from the environment variable.
  4. Create the connection object.
  5. Set the connection to raise errors.
use strict;
use DBI;
my $connectString=$ENV{'db_connect'};
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') || die "Database connection not made: $DBI::errstr";
$con->{RaiseError} = 1; # set the connection to raise errors

We will include this code section with all examples and use the connection object "con" throughout the series.

Cleanup

To clean up the database when you are finished with the series, you just need to drop the two tables.  Please make sure you're connected to the correct schema where you created the tables.

drop table LCS_PETS
/

drop table LCS_PEOPLE
/


Guide to Perl DBD::Oracle CRUD Series

Initial Setup
Create records (Coming soon)
Retrieve records
Update records (Coming soon)
Delete records (Coming soon)

Database Perl (programming language)

Published at DZone with permission of Blaine Carter, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Why Database Migrations Take Months and How to Speed Them Up
  • Unmasking Entity-Based Data Masking: Best Practices 2025
  • How Trustworthy Is Big Data?
  • Fixing Common Oracle Database Problems

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!