Archive for January, 2014

spelunking oracle (generate sql queries)

January 7, 2014

a way to get an overview of a bunch of tables – I wanted to know what was useful in a database, so I listed up about 500 tables and looked at a brief snapshot of each.

This code:

select owner,object_name ,
'select '' '' as ' || object_name || ' , Q.* from ' || owner || '.' || object_name || ' Q where rownum < 100; ' as copyandexecute

from all_objects
where object_type='TABLE'
and owner in ('AAISP' )

generates these queries:

select ' ' as ADHOCREQUESTSTTNGREGIONS , Q.* from AAISP.ADHOCREQUESTSTTNGREGIONS Q where rownum < 100;
select ' ' as ADHOCREQUESTSTTNGMTGPTTRNS , Q.* from AAISP.ADHOCREQUESTSTTNGMTGPTTRNS Q where rownum < 100;
select ' ' as ADHOCREQUESTSTTNGMPTNGRPS , Q.* from AAISP.ADHOCREQUESTSTTNGMPTNGRPS Q where rownum < 100;
select ' ' as ADHOCREQUESTSTTNGFEATURES , Q.* from AAISP.ADHOCREQUESTSTTNGFEATURES Q where rownum < 100;
select ' ' as ADHOCREQUESTSTTNGFACLAYOUTS , Q.* from AAISP.ADHOCREQUESTSTTNGFACLAYOUTS Q where rownum < 100;

I had to learn how to get the string literals in there correctly … I don’t do Oracle day-to-day.

Advertisements

oracle connection from visual studio 2012 problem

January 7, 2014

I’ve been connecting to three or four oracle databases off and on for about 5 years.

In a new project, I added a new entry to TNSNames and tried to connect to a new database, and I spent close to 12 hours trying to get it to work.

I may have had a problem with my tnsnames entry … but what seemed to do it finally was this:

Oracle looks for the tnsnames.ora file in the directory defined in the TNS_ADMIN environment variable – If you are running as different users, then maybe the TNS_ADMIN environment variable is not set, and therefore it cannot find the file?

could we live without stack overflow?

also, in the process I made sure to install the ODAC stuff downloaded from oracle.com.

But the fact that visual studio server and also my programs could connect to other oracle databases also defined in the same tnsnames.ora file is something I may not ever understand.