Error: DPI-1059: bind variables are not supported in DDL statements when call procedure using node-oracledb

Question

I created the stored procedure below in sql-developer :

CREATE OR REPLACE PROCEDURE CREATE_USER(
 t_username IN VARCHAR2, 
 t_password IN VARCHAR2,
 t_default_table IN VARCHAR2,
 t_quota IN VARCHAR2
)
IS
    m_statement VARCHAR2(1300);
    
    m_username VARCHAR2(30) := t_username;
    m_password VARCHAR2(30) := t_password;
    m_default_table VARCHAR2(30) := t_default_table;
    m_quota VARCHAR2(30) := t_quota;
    
BEGIN
    m_statement := 'create user ' || t_username || ' identified by ' || t_password;

    IF m_default_table != 'NULL' THEN
         m_statement := m_statement || ' DEFAULT TABLESPACE ' || m_default_table;
    END IF;

    IF m_quota != '0' AND m_default_table != 'NULL' THEN
        m_statement := m_statement || ' QUOTA ' || m_quota || 'M ON ' || m_default_table;
    END IF;
    EXECUTE IMMEDIATE (m_statement);
END;

And this compiled with no errors. I also connected to oracle. Then i got a user data (req.body) like this:

{ username: 'a', password: 'a', tablespace: 'NULL', quota: '0' }

But when i execute the 'call' procedure query:

oracle.getConnection(
        {
            uuser : "AN",
            password: "AN123",
            connectString: "localhost:1521/orcl"
        },
        (t_err, t_connection) => {
            if(t_err){
                console.error(t_err);
                return;
            }
            t_connection.execute(
                `BEGIN 
                    createUser(:username, :password, :tablespace, :quota);
                END;`,
                {
                    username: req.body.username,
                    password: req.body.password,
                    tablespace: req.body.tablespace,
                    quota: req.body.quota,
                    
                },
                (t_er, t_reslt) => {
                    if(t_er){
                        console.error(t_er);
                        return;
                    }

I received:

[Error: DPI-1059: bind variables are not supported in DDL statements] {
  errorNum: 0,
  offset: 0
}

I tried many ways to fix the procedure or the nodejs code but it didn't work. I'm very new to this topic. Can anyone help me, please?

Answer 1

You code runs without error for me - once I fix the conflict between the name in the SQL statement CREATE_USER and the name in the JS file createUser. Really make sure you are not calling some prior version of the PL/SQL package that does attempt to use bind variables in the CREATE USER statement (this was noted in comments). As the error states: this usage is not supported by Oracle DB. For example, you cannot do:

connection.execute(`create user :un identified by :pw`, ['cj2', 'cj2password']);

• This is a DDL statement
• It tries to use bind variables

And this isn't supported. And it fails with DPI-1059. Bind variables are used for data, not the text of statements.

Two other tips:

• Filter or sanitize your input values to avoid SQL Injection attacks
• Use the async/await style of programming (instead of callbacks) to save yourself some headaches. See all the node-oracledb examples.