The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
COURSE of the MONTH
9 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Which account to use when connecting to Oracle DB from script?
Posted on 2011-02-27
Hi,
please can somebody help me with the following?
I'm trying to connect to Oracle from PowerShell script. I believe the only problem is in the incorrect connection string and I don't know, which user account should be used in the string.
The hostname is ORA1 and there I have only the default database called "orcl".
If I use lsnrctl status, it displays the info about runninc instance "orcl", so I think this shouldn't be a problem.
I'm able to connect to database from sqlplus. I know almost nothing about Oracle, but it seems that I can connect successfully if I use the username "sys as sysdba".
I set all the passwords to be "abc" just for simplicity.
I doesn't let me in if I use the account sysdba directly - it works only as "sys as sysdba".
Now regarding the script - it's almost the same. If I have "sys" in the connection string, I get an error message saying "you should use 'sys as sysdba' as logon name".
If I change it in the connection string and use the username "sys as sysdba", then I get an error message about incorrect password/logon denied.
Could you please tell me how to fix it? I read hundred of pages about the connection strings, tried multiple combinations, but no avail so far.
Also I'm not sure what value should be in the connection string in "data source". I have "ora1\orcl", which is the hostname\db_name. Should I use something different?
Thank you very much in advance.
Martin
please can somebody help me with the following?
I'm trying to connect to Oracle from PowerShell script. I believe the only problem is in the incorrect connection string and I don't know, which user account should be used in the string.
The hostname is ORA1 and there I have only the default database called "orcl".
If I use lsnrctl status, it displays the info about runninc instance "orcl", so I think this shouldn't be a problem.
I'm able to connect to database from sqlplus. I know almost nothing about Oracle, but it seems that I can connect successfully if I use the username "sys as sysdba".
I set all the passwords to be "abc" just for simplicity.
I doesn't let me in if I use the account sysdba directly - it works only as "sys as sysdba".
Now regarding the script - it's almost the same. If I have "sys" in the connection string, I get an error message saying "you should use 'sys as sysdba' as logon name".
If I change it in the connection string and use the username "sys as sysdba", then I get an error message about incorrect password/logon denied.
Could you please tell me how to fix it? I read hundred of pages about the connection strings, tried multiple combinations, but no avail so far.
Also I'm not sure what value should be in the connection string in "data source". I have "ora1\orcl", which is the hostname\db_name. Should I use something different?
Thank you very much in advance.
Martin
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +1
6 Comments
Active today
if you are logged in as the oracle owner (usually a user called "oracle")
sqlplus / as sysdba
sqlplus / as sysdba
Hi and thank you for the comment.
What you say works nice with sqlplus - I connect successfully.
But the question was more about what should be passed in the connection string. I realize I'm asking about scripting in the Oracle zone, but I believe the connection string is the same for any programming/scripting language.
I'm logged on as local administrator, it's a fresh install of Windows Server 2003 and Oracle, all default, only the password is set to 'abc'.
This problem is driving me crazy as I need to develop quite complex script and I'm stucked just first few lines where I have the connection attempt.
As long as I'm able to work with the database using sqlplus, I believe the database, services and listener are ok. Maybe it's something wrong with my script, but it's just a sample code googled on the Internet, nothing special, see below.
The table 'employees' probably doesn't exist, but didn't get so far to get an error message about this :-) It's just about the connection.
Thanks a lot.
Martin
What you say works nice with sqlplus - I connect successfully.
But the question was more about what should be passed in the connection string. I realize I'm asking about scripting in the Oracle zone, but I believe the connection string is the same for any programming/scripting language.
I'm logged on as local administrator, it's a fresh install of Windows Server 2003 and Oracle, all default, only the password is set to 'abc'.
This problem is driving me crazy as I need to develop quite complex script and I'm stucked just first few lines where I have the connection attempt.
As long as I'm able to work with the database using sqlplus, I believe the database, services and listener are ok. Maybe it's something wrong with my script, but it's just a sample code googled on the Internet, nothing special, see below.
The table 'employees' probably doesn't exist, but didn't get so far to get an error message about this :-) It's just about the connection.
Thanks a lot.
Martin
cls
$connectionString= "Data Source=ora1/orcl;User Id='sys/abc@orcl as sysdba';Password=abc;"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT COUNT(LAST_NAME) FROM EMPLOYEES"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo "Number of employees: "$employeesNames
$connection.Close()
what is the script?
Is it perl script? Or jython script or python script? (I am not much into it, so cant figure it from sample code)
For JDBC, we have following option...
* user String The user name for logging into the
* database.
* password String The password for logging into the
* database.
* database String The connect string for the database.
* internal_logon String A role, such as SYSDBA or SYSOPER,
* that allows you to log on as SYS.
But this is with JDBC, it will be different for string, but the property should remain same for all the DBC.
so try exploring more on internal_logon syntex for your scripting language...
Is it perl script? Or jython script or python script? (I am not much into it, so cant figure it from sample code)
For JDBC, we have following option...
* user String The user name for logging into the
* database.
* password String The password for logging into the
* database.
* database String The connect string for the database.
* internal_logon String A role, such as SYSDBA or SYSOPER,
* that allows you to log on as SYS.
But this is with JDBC, it will be different for string, but the property should remain same for all the DBC.
so try exploring more on internal_logon syntex for your scripting language...
why you want to login as sys any particular reason?
if not why cann't you try as system user... if you know system password.
if you don't know the system password login to database with sys through sqlplus and change the password of system.
alter user system identified by password account unlock;
then you can use system user in your script.
if not why cann't you try as system user... if you know system password.
if you don't know the system password login to database with sys through sqlplus and change the password of system.
alter user system identified by password account unlock;
then you can use system user in your script.
Jaiminpsoni: The script is powershell, but I could do it using any other language I know...ehm, actually VBScript, that's all :-)
Maybe there could be the concept I don't understand - the difference between user and internal_logon.
Praveencpk: I don't need to login as sys - as I mentioned I don't know Oracle and I thought, that this is the simplest way, when I'm working in the virtual environment just for development purposes - that I will not be limited by some restrictions or insufficient privilegues.
By system user you mean a user called "system"? I altered the system user by using your command, went ok, I can login from sqlplus using this account.
Actually as I can see now, the script works! I modified the connection string to be like below and used OleDB from another googled example:
$connString = "password=abc;User ID=SYSTEM;Data Source=orcl;Provider=OraOL
Afaik I haven't tried to login with SYSTEM user account before, maybe this was the core of the problem.
Anyway thank you very much for your help guys, I'm gonna close this question and split the points....and ask some new questions :-)
Martin
Maybe there could be the concept I don't understand - the difference between user and internal_logon.
Praveencpk: I don't need to login as sys - as I mentioned I don't know Oracle and I thought, that this is the simplest way, when I'm working in the virtual environment just for development purposes - that I will not be limited by some restrictions or insufficient privilegues.
By system user you mean a user called "system"? I altered the system user by using your command, went ok, I can login from sqlplus using this account.
Actually as I can see now, the script works! I modified the connection string to be like below and used OleDB from another googled example:
$connString = "password=abc;User ID=SYSTEM;Data Source=orcl;Provider=OraOL
Afaik I haven't tried to login with SYSTEM user account before, maybe this was the core of the problem.
Anyway thank you very much for your help guys, I'm gonna close this question and split the points....and ask some new questions :-)
Martin
Featured Post
This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to Create User-Defined Aggregates in Oracle
Before we begin creating these things, what are user-defined aggregates? They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …
This video shows how to Export data from an Oracle database using the Datapump Export Utility. The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month9 days, 1 hour left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.