Skip to Main Content

Programming Languages & Frameworks

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue during Node.js deployment on Application CLoud ORA-01045: user lacks CREATE SESSION privilege

SmiersNov 17 2016 — edited Nov 22 2016

Hello,

I started working with Node.js and oracledb in order to access the database.

I'm testing this on Application Container Cloud and the Database Cloud.

I followed the tutorial : Oracle Application Container Cloud Service: Building a RESTful Node.js Web Service with Oracle Database Cloud Service

http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/apaas/node-cloud-REST-DB/nodecloud-RESTDB.html#section4

It looks there are some errors in the tutorial

Amongs others the connection properties. Below the described connection setup from the tutorial, deployment in this way is not possible,

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

var connectionProperties = {
  user: process.env.NODE_ORACLEDB_USER || "oracle",
  password: process.env.NODE_ORACLEDB_PASSWORD || "oracle",
  connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "localhost/xe"
};

I changed the connection to the following (I copied the connection string from the DB Cloud instance)

var connectionProperties = {

  user: process.env.NODE_ORACLEDB_USER || "MyUser",

  password: process.env.NODE_ORACLEDB_PASSWORD || "MyPassword",

  connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "[IP ADDRESS DB SERVER]:1521/[PDB].[XXXX]"

};

Now the application deployment fails one step further

     ORA-01045: user [DEMO-USER] lacks CREATE SESSION privilege; logon denied

I created this DB user with the following privilges/roles

     CONNECT, CREATE SESSION, RESOURCE

I am able to connect from SQL Developer towards the DB Cloud instance.

What could be the reason of this issue?

I even tried adding DBA privileges to the DB user, same result.

Thanks

Léon

This post has been answered by Smiers on Nov 20 2016
Jump to Answer

Comments

Frank Kulash

Hi, @albert-chao
Please assume all the tables are created.
No; whenever you have a question, actually post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from that data. For DML questions (such as INSERT) the results will be what the changed table looks like after the DML is completed.

Solomon Yakobson
Answer

You don't assign SELECTed data to a variable via assignment statement. You use INTO clause:

Declare
tot_records number(10);
records_loaded number(10);
process_start_time timestamp;
begin
select count(1) into tot_records from employees;
insert into department select * from employees;
records_loaded := sql%rowcount;
process_start_time := current_timestamp;
insert into summary(total,tot_loads,start_time) values(tot_records,records_loaded,process_start_time);
end;

Also why are you inserting employee data into department table???
SY.

Marked as Answer by Albert Chao · Sep 21 2021
Frank Kulash

Hi, [Albert Chao](/ords/forums/user/Albert Chao)

tot_records := select count(1) into lv_count from employees;

won't work in PL/SQL; you can't have SELECT immediately after :=.
Do something like this instead:

	select  count (*)
	into 	tot_records
	from 	employees;
mathguy

I have tried but not able insert the values into the summary table.
In this case, the mistake was so obvious that we didn't need more information from you. But in general, if something "doesn't work", you won't get a lot of help if you just say "it doesn't work". You need to tell us exactly what is not working. Does compilation fail? (It would, in this case - with an error message pointing exactly to the place in the code where the error is.) Does the compilation succeed, but then you get an error thrown when you execute the block? Does execution go through, but you get the wrong result? Etc.
Please keep this in mind next time you ask a question (here or on any other forum). Also, make sure to always state your database version - in many cases (even though not in your problem here), that is the key piece of information.

1 - 4

Post Details

Added on Nov 17 2016
6 comments
2,545 views