Home

# Node Oracle

# Resource

node-oracledb: Node.js module for Oracle Database

Node.js Sequelize with Oracle Database — it’s official | by Christopher Jones | Oracle Developers | Medium

# Oracle Set Up

Create table in Oracle 11g.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table posts (
  id number,
  title varchar2(100),
  content varchar2(4000)
);

create sequence posts_seq;

create trigger posts_id_trg
  before insert on posts
  for each row
begin
  select posts_seq.nextval
    into :new.id
    from dual;
end;

# Express CRUD

npm i express oracledb -S

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
const express = require('express');
const oracledb = require('oracledb');
const app = express();
const PORT = process.env.PORT || 3000;

// Database connection settings
const dbConfig = {
    user: 'apps',
    password: 'apps',
    connectString: 'erpdev.sercomm.com:1523/DEV',
};

// Thick Mode
if (process.platform === 'win32' || (process.platform === 'darwin' && process.arch === 'x64')) {
    oracledb.initOracleClient({ libDir: 'C:/Program Files/instantclient_11_2' });  // enable node-oracledb Thick mode
}

// Connect to Oracle Database
// async function getConnection () {
//     const connection = await oracledb.getConnection(dbConfig);
//     return connection
// }

// Middleware
app.use(express.json());

// TODO: Every request is executed. the best way is use the global connection
app.use(async (req, res, next) => {
    req.db = await oracledb.getConnection(dbConfig);
    next();
});

// Route to get all posts
app.get('/api/posts', async (req, res) => {
    try {
        const result = await req.db.execute(
            `SELECT * FROM posts`,
            [],
            { outFormat: oracledb.OUT_FORMAT_OBJECT }
        );
        res.json(result.rows);
    } catch (err) {
        console.error(err.message);
        res.status(500).send('Server Error');
    }
});


// Route to find Post by ID
app.get('/api/posts/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const result = await req.db.execute(
            `SELECT * FROM posts WHERE id = :id`,
            [id],
            { outFormat: oracledb.OUT_FORMAT_OBJECT }
        );

        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Post not found' });
        }

        res.json(result.rows[0]);
    } catch (err) {
        console.error(err.message);
        res.status(500).send('Server Error');
    }
});

// Route to create a post
app.post('/api/posts', async (req, res) => {
    const { title, content } = req.body;
    try {
        await req.db.execute(
            `INSERT INTO posts (title, content) VALUES (:title, :content)`,
            [title, content],
            { autoCommit: true }
        );
        res.send('Post created successfully');
    } catch (err) {
        console.error(err.message);
        res.status(500).send('Server Error');
    }
});

// Route to update a post
app.put('/api/posts/:id', async (req, res) => {
    const { title, content } = req.body;
    const { id } = req.params;
    try {
        await req.db.execute(
            `UPDATE posts SET title = :title, content = :content WHERE id = :id`,
            [title, content, id],
            { autoCommit: true }
        );
        res.send('Post updated successfully');
    } catch (err) {
        console.error(err.message);
        res.status(500).send('Server Error');
    }
});

// Route to delete a post
app.delete('/api/posts/:id', async (req, res) => {
    const { id } = req.params;
    try {
        await req.db.execute(
            `DELETE FROM posts WHERE id = :id`,
            [id],
            { autoCommit: true }
        );
        res.send('Post deleted successfully');
    } catch (err) {
        console.error(err.message);
        res.status(500).send('Server Error');
    }
});

// Start server
app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});

# Curl Test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
###
curl http://localhost:3000/api/posts


### 
curl http://localhost:3000/api/posts/1


### 
curl -X POST \
     -H "Content-Type: application/json" 
     -d '{"title": "Learn Bun", "content": "Bun!!!"}'\
    http://localhost:3000/api/posts


### 
curl -X PUT \
     -H "Content-Type: application/json" 
     -d '{"title": "Learn Bun", "content": "Bun Bun Bun!!!"}'\
    http://localhost:3000/api/posts/3


### 
curl -X DELETE http://localhost:3000/api/posts/2

# .Net Core + Oracle

# System.Data.OracleClient

dotnet add package System.Data.OracleClient

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
using System;
using System.Data.OracleClient;

class Program
{
    static void Main(string[] args)
    {
        // Not configuration in NETWORK\ADMIN\tnsnames.ora
        // const string connectionString = @"Data Source=(DESCRIPTION=
        //                                                         (ADDRESS=(PROTOCOL=tcp)(HOST={host})(PORT=1523))
        //                                                     (CONNECT_DATA=
        //                                                         (SERVICE_NAME = DEV)
        //                                                     )
        //                                                 );User ID={username};Password={password};";
        // Configured in NETWORK\ADMIN\tnsnames.ora
        const string connectionString = @"Data Source=DEV;User ID={username};Password={password};";


        // Do not add a semicolon at end of the table name or you will get ORA-00911: invalid character
        const string queryString = "select empno, ename from scott.emp";

        using OracleConnection connection = new(connectionString);
        OracleCommand command = connection.CreateCommand();
        command.CommandText = queryString;

        try
        {
            connection.Open();

            OracleDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}", reader[0], reader[1]);
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            throw;
        }
    }
}

# Oracle.ManagedDataAccess.Client

dotnet add package Oracle.ManagedDataAccess.Client

.Net Core connect oracle 11g using Oracle.ManagedDataAccess.Core 23.4.0 needs config sqlnet.allowed_logon_version_server=11 and sqlnet.allowed_logon_version_client=11 in sqlnet.ora, or you will get ORA-28040: The database does not accept your client’s authentication protocol; login denied.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace Program
{
    class GettingStarted
    {
        //Prerequisite: This app assumes the user has already been created with the
        // necessary privileges
        //Set the demo user id, such as DEMODOTNET and password
        public static string user = "apps";
        public static string pwd = "apps";

        //Set the net service name, Easy Connect, or connect descriptor of the pluggable DB, 
        // such as "localhost/XEPDB1" for 18c XE or higher
        public static string db = "{hostname}:1523/DEV";

        // # .Net Core connect oracle 11g using Oracle.ManagedDataAccess.Core 23.4.0 needs config sqlnet.allowed_logon_version_server and sqlnet.allowed_logon_version_client=11 in sqlnet.ora, 
        // # or you will get ORA-28040: The database does not accept your client's authentication protocol; login denied.	
        // sqlnet.allowed_logon_version_server=11
        // sqlnet.allowed_logon_version_client=11

        static void Main()
        {
            string conStringUser = "User Id=" + user + ";Password=" + pwd + ";Data Source=" + db + ";";

            using OracleConnection con = new(conStringUser);
            using OracleCommand cmd = con.CreateCommand();
            try
            {
                con.Open();
                Console.WriteLine("Successfully connected to Oracle Database as " + user);
                Console.WriteLine();

                //Retrieve sample data
                // Do not add a semicolon at end of the table name or you will get ORA-00911: invalid character
                cmd.CommandText = "select empno, ename from scott.emp";
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("{0}\t{1}", reader.GetString(0), reader.GetString(1));
                }


                reader.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

# .Net Core + MSSQL LocalDB

dotnet add package Microsoft.Data.SqlClient

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
using Microsoft.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        // CreateTable();
        // Insert();
        // Read();
        // Delete();
        Oracle.Run();
    }

    public static void CreateTable()
    {
        try
        {
            using SqlConnection con = new("Server=(localdb)\\SQLCourse;Database=Student;");

            SqlCommand cm = new(@"create table student(
                                    id int not null,
                                    name varchar(100), 
                                    email varchar(50), 
                                    join_date date)", con);

            con.Open();

            cm.ExecuteNonQuery();

            Console.WriteLine("Table created Successfully");
        }
        catch (Exception e)
        {
            Console.WriteLine("OOPs, something went wrong." + e);
        }
    }

    public static void Insert()
    {
        try
        {
            using SqlConnection con = new("Server=(localdb)\\SQLCourse;Database=Student;");

            SqlCommand cm = new(@"insert into student (id, name, email, join_date) 
                                            values ('101', 'Ronald Trump', 'ronald@example.com', '1/12/2017')", con);

            con.Open();

            cm.ExecuteNonQuery();

            Console.WriteLine("Record Inserted Successfully");
        }
        catch (Exception e)
        {
            Console.WriteLine("OOPs, something went wrong." + e);
        }
    }


    public static void Read()
    {
        try
        {
            using SqlConnection con = new("Server=(localdb)\\SQLCourse;Database=Student;");

            SqlCommand cm = new(@"select * from student", con);

            con.Open();

            SqlDataReader dataReader = cm.ExecuteReader();

            while (dataReader.Read())
            {
                Console.WriteLine(dataReader["id"] + " " + dataReader["name"] + " " + dataReader["email"]);
            }

            Console.WriteLine("Record Inserted Successfully");
        }
        catch (Exception e)
        {
            Console.WriteLine("OOPs, something went wrong." + e);
        }
    }


    public static void Delete()
    {
        try
        {
            using SqlConnection con = new("Server=(localdb)\\SQLCourse;Database=Student;");

            SqlCommand cm = new("delete from student where id = '101'", con);

            con.Open();

            cm.ExecuteNonQuery();

            Console.WriteLine("Record Deleted Successfully");
        }
        catch (Exception e)
        {
            Console.WriteLine("OOPs, something went wrong." + e);
        }
    }
}
Edited on