I have following pl/sql block:
DECLARE
user_name varchar(255);
custom_exception EXCEPTION;
PRAGMA exception_init( custom_exception, -20001);
BEGIN
SELECT name
INTO user_name
FROM data_table where ID = '1';
IF user_name = 'temp' THEN
RAISE custom_exception;
END IF;
END;
When I run it from Oracle SQL Developer, it works fine. But I am getting ORA-01036: illegal variable name/number while running it from nodejs.
Call from Nodejs code:
var output = await connection.execute(fs.readFileSync('path_to_pl_sql_file', 'utf8'), {
message: {
dir: oracledb.BIND_OUT,
type: oracledb.STRING,
maxSize: 100
}
}, {
autoCommit: true
});
Can someone point out what going wrong here?
The error was not with the PL/SQL block. I was passing in bind variables while executing the PL/SQL from nodejs and that bind variable was not present in the SQL. Hence the error!