Oracle SQL db queries

[JeffAllenNJ]

My team is tired of typing the same queries over and over again in SQL developer with slight variations in select clauses.  I see some posts regarding MS-SQL but I'm not sure if that will work with Oracle DBs. 

I'd like to start simple with a script that connects to a service name using hard-coded credentials and perform a simple query and consolewrite them.   Eventually, I'll put in a true GUI where service names are in a drop down, credentials are collected and a few parameters for the queries and the output is dumped to CSV or something.

Can someone point me in the right direction? 

Edited March 15, 2021 by jaja714

[FrancescoDiMuro]

@jaja714
Just an overview of what you can do with ADO and AutoIt

[JeffAllenNJ]

ooohh ... no Oracle Examples!

[FrancescoDiMuro]

@jaja714
Just look for them

[JeffAllenNJ]

This should hold me over for a while ... Thanks!

[FrancescoDiMuro]

@jaja714
If you have any problem, don't be afraid to post a runnable script, so we can assist you more
Good luck!

EDIT: In fact, there are no examples with Oracle DB in the ADO Wiki section 

Edited March 15, 2021 by FrancescoDiMuro

[JeffAllenNJ]

I'm getting COM errors probably because I'm not creating the connecting string right.  How do I get the connection string correct from, say, right-clicking on connection properties in SQL Developer?

I tried:

    .ConnectionString ="Provider={right-click-properties};Data Source=DVA;User Id={myUserName};Password={myPassWord};"
    .ConnectionString ="Server={right-click-properties};User Id={myUserName};Password={myPassWord};"

 

[FrancescoDiMuro]

@jaja714
I've never used OracleDB, so I can't say what is wrong there but, have you checked this?

[Earthshine]

see this website.

 

Oracle connection strings - ConnectionStrings.com

 

it always worked for me

[JeffAllenNJ]

I don't see any AutoIT code there or examples.  As far as I'm concerned, any of those could work.  I'm just not in the mood to try them all with different flavors of case sensitivity and which quotes to use.  I tried this and it didn't work.

    .ConnectionString ="Data Source=myDB.com;User Id=myUsername;Password=myPassword;Integrated Security=no;"

 

[Earthshine]

First get one that works. Then learn how to construct the string programmatically 

i can’t be arsed to do it for you just because you don’t feel like doing development. You need to learn 

 

and nobody has your Oracle instance installed either

Edited March 15, 2021 by Earthshine

[FrancescoDiMuro]

@Earthshine

Nice profile picture

[Nine]

  On 3/15/2021 at 9:15 PM, FrancescoDiMuro said:

Nice profile picture

Expand  

He looks afraid of something 

[Earthshine]

  On 3/15/2021 at 9:15 PM, FrancescoDiMuro said:

@Earthshine

Nice profile picture

Expand  

Thanks. My son’s first dog. He took that picture years ago with his phone. He was a great little cockapoo. 

Edited March 15, 2021 by Earthshine

[Earthshine]

That’s him begging nicely lol 

He was spoiled and once he even grabbed a sandwich out of my hands and ate the rest of it

He was loved very much

Edited March 15, 2021 by Earthshine

[JeffAllenNJ]

  On 3/15/2021 at 5:43 PM, Earthshine said:

First get one that works.  i can’t be arsed to do it for you just because you don’t feel like doing development. You need to learn 

Expand  

That's what I'm asking for. 

I'm not an Oracle DBA.  I'm just a developer who needs to query some databases.  I don't know how the databases were constructed nor do I have access to the braintrust that created them.  

How do I translate SQL Developer Connection properties into valid connection strings?  I've been down the trial and error road all day yesterday and I'm ready to learn the right way ... from a teacher.

FYI: I tried many combinations, including:

$service = 'mydbname.domain.com'
    $user = 'myusername'
    $pass = 'mypassword'
    .ConnectionString = 'Provider=OraOLEDB.Oracle;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
    .ConnectionString = 'Provider=msdaora;Data Source=' & $service & ';User Id=' & $user & ';Password=' & $pass & ';'
    .ConnectionString = 'Driver={Microsoft ODBC Driver for Oracle};ConnectString=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'
    .ConnectionString = 'Driver={Microsoft ODBC for Oracle};Server=' & $service & ';Uid=' & $user & ';Pwd=' & $pass & ';'

I'm not sure why these are failing.  Is it syntax?
 

Edited March 16, 2021 by jaja714

[Earthshine]

You and your team need to head on over to the people who created the database and get the correct drivers and clients

 

There are no Microsoft SQL developer tools for Oracle that I am aware ofThere are no Microsoft SQL developer tools for Oracle that I am aware of

Edited March 16, 2021 by Earthshine

[JeffAllenNJ]

So, the answer is no?  One cannot derive connection strings from SQL Developer properties?

[JeffAllenNJ]

  On 3/16/2021 at 4:10 PM, Earthshine said:

There are no Microsoft SQL developer tools for Oracle that I am aware ofThere are no Microsoft SQL developer tools for Oracle that I am aware of

Expand  

I am using Oracle SQL Developer.

[JeffAllenNJ]

What about %appdata%\SQL Developer\system\connections.xml?