For whatever reason, I have escaped using any Oracle database in my 30+ year IT Consulting career, and now at my new client, I’m finally using it, at least to run simple queries. As a BizTalk developer, so much depends on the client. I have worked with clients that call MSSQL Stored Procs from BizTalk, and I have maintained those huge stored procs. In other clients that are more SOA based, BizTalk only calls web services, and the web services do all the communication with the SQL back end databases.

Today I was trying to find a “PARTS” table, which was like finding a needle in a haystack of about 1000 different tables. The client uses an ERP system called “IFS” and all I was told it was a special table created by the client outside of ERP system.

SELECT *   FROM dba_tables WHERE table_name LIKE '%PART%' and OWNER not like 'IFS%' and OWNER not like 'SYS%'
ORDER BY TABLE_NAME

I cannot include any client tables in this blog, but here’s an example of searching the Oracle SYS tables:

SELECT *   FROM dba_tables WHERE table_name LIKE '%COL%'  and OWNER not like 'IFS%'
ORDER BY TABLE_NAME

You can take it one level deeper and join tables and columns, then search for either table name of column name:

select * from cols C
inner join dba_tables T on C.Table_Name = T.Table_Name 
where T.table_name like '%PART%'
order by T.Table_Name, C.Column_ID 

 

When switching from Microsoft’s SQL Studio Manager to “PL/SQL Developer”, there are a few things to get used to.
In Microsoft, you use F5 to execute the SQL, but in PL/SQL you use F8.  If you hit “F5” like you maybe in the habit of doing, you will get the “Explain SQL” screen (which you can then close).

The other biggest difference, is that by default, “PL/SQL Developer” returns about 40 rows, which I believe is based on my screen size and resolution.

There are two icons that sort of look like double-arrows or the fast-forward button on a VCR/DVD/DVR player.

Click the one on the left to get another 40 rows.  Click the one on the right to retrieve all the data.

There is not “top x” syntax in Oracle.  In Microsoft, you can say “Select Top 25 * from TableName”, but in PL/SQL you either use this feature built into the query tool, or you can manually enter “Select* from TableName where RowNum <= 25”.

 

PL/SQL can be licensed for about $216 per developer (2014 prices) and is sold by “AllAroundAutomations” http://www.allroundautomations.com/plsqldev.html (but I also found a download here – not sure if it just a trial or limited functionality: http://download.cnet.com/PL-SQL-Developer/3000-10254_4-10012692.html  A more expensive tools is “TOAD” (created by Quest and pricing available ($975 and up) from Dell here: https://shop.software.dell.com/682/purl-toad-for-oracle.

 

References:

1. http://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle/205746#205746  (for how to enumerate or list Oracle table names)

2, http://www.w3schools.com/sql/sql_top.asp  (for Top 25 vs rownum <= 25)