I haven’t had to do much with Oracle for a while and totally forgot how to create the next “autonumber” or “sequence” for a table’s ID key. So here’s a reminder.
into PERSON (CREATEDDATE, ID, ...) values (SYSDATE, PERSON_seq.NEXTVAL, ...)
Category Archives: Oracle
Describing my tables, views, stored procs etc.
Having become a little too reliant (at times) on great GUI tools for interacting with my databases, I had to remind myself it’s pretty easy to use code to do this, sure good old Aqua Studio does it with CTRL+D on a data object in the editor, or both Oracle SQL Developer and the MS SQL Server Management Studio allow us to easily drill down the item in the data object tree, but still. Here it is in code…
Tables and views
For Oracle
desc TableName
For SQL Server
exec sp_columns TableName
Stored Procs
For Oracle
desc StoredProceName
For SQL Server
exec sp_help StoredProceName
In fact sp_help can be used for Stored Procs and Tables/Views.
What version of Oracle am I connecting to ?
We recently moved from using Oracle 10g to Oracle 11g
Note: I have access to the DB in this scenario but only via Oracle SQL Developer
I wanted to verify that the version I was connecting to was indeed 11g, so found this
SELECT * FROM PRODUCT_COMPONENT_VERSION;
Which returns the following
[table “” not found /]Oracle, where’s my data object
Had a strange issue with Oracle SQL Developer whereby it wasn’t showing a data object which I knew existed and realised I didn’t know the SQL to find it – sure SQL Developer does it all in the GUI but it’s nice to know how to do it in code, so here goes
select * from ALL_OBJECTS where OBJECT_NAME = 'SomeTableViewProcOrWhatever'