Yesterday i came across this particular system table USER_SOURCE. Gives the source SQL for a variety of Oracle objects including Procedures and Types. I dont have oracle installed in my home. So no screenshots.
You would have already known that USER_VIEWS table will give you the source select statement of a VIEW.
Tags: oracle, source sql
“Oracle has just announced from EclipseCon that it is donating the TopLink O/R persistence framework to the OSS community. This is a fairly substantial contribution of code.”
Here are some info on the framework.
When you are using Oracle as your backend, you really might want to prefer Toplink over Hibernate. From the house of Oracle (at least after all those buyouts), it obviously has lots of support for Oracle database. On the other side too, it looks like it has a stable caching and lazy loading mechanism than Hibernate. People have used Toplink against other databases in Production environment and they just say it performs slightly better than Hibernate.
“If you are using the Oracle DB then support for hints, hierarchical queries, XDB XML Type with query constructs, Object-Relational database types with queries, many SQL extended function and types (TIMESTAMP*, NCLOB, …), excellent support for complex stored procedures and functions, as well as support of Virtual Private Database will definitely be key factors in your comparison.” (source : oracle.com)
Toplink has been in the market for over 10 years now. But what stopped it from becoming popular? Struts had better documentation whereas Webwork had a better framework. Same is the case with Toplink. Lack of good documenation initially and the cost involved in buying Toplink (Toplink was, of course, commercial initially) were the top things that were stopping Toplink from becoming popular. Now that Toplink has good documentation and become opensource, what’s stopping us?
Tags: o/r mapping, opensource, oracle
From Oracle 10g, Dropped tables can be restored. Meaning, you can now rollback one of your DDL statement. This is how you do it.
drop table t;
Table dropped.
desc t;
ERROR: ORA-04043: object t does not exist
select count(*) from t;
select count(*) from t;
ERROR at line 1: ORA-00942: table or view does not exist
flashback table t to before drop;
Flashback complete.
You have now restored your table.
Behind the scenes, what happens is that your dropped table is renamed and just gets restored from the recyle-bin when you “flashback”.
drop table t;
Table dropped.
select table_name from user_tables;
no rows selected
select object_name from recyclebin;
OBJECT_NAME
——————————
BIN$7oTtcup30ZfgMAGK/3hjKw==
flashback table t to before drop;
Flashback complete.
select object_name from recyclebin;
no rows selected
select table_name from user_tables;
TABLE_NAME
——————————
T
So, your table will not really be dropped. But just like any operating system moved to the recycle-bin from which you can restore later.
If you dont want your tables to be restored and be gone forever, then
drop table t purge;
Table dropped.
select table_name from user_tables;
no rows selected
select object_name from recyclebin;
no rows selected
source: Ask Tom
If you want to fetch the results between Xth row and Yth row in an Oracle resultset, here is the most optimal solution.
This works in Oracle 8.1 and above.
select *
from ( select a.*, rownum rnum
from
( YOUR_QUERY_GOES_HERE
-- including the order by ) a
where rownum < = MAX_ROWS )
where rnum >= MIN_ROWS
source : Ask Tom
Tags: oracle
I had this problem at work a few weeks back and thought i could share with you. Most of you must have known this. But yet, I thought this post could be useful so that i could come back here and refresh myself.
I have a staging table, a few colums of which are populated from direct flat file loads (Oracle SQL Loader utility). I need to populate a few other columns of this table looking up from a master table. Finally, after all columns has been populated, i move the staging table records to a “fair copy table”.
Let me give you an example. Our staging table will be staging_table. Four columns of it (key_to_all_locks, flat_file_load_colm_1, flat_file_load_colm_2 and flat_file_load_colm_3) are flat file loads. (please look the screenshot for full description)
I have a master table master_table which has many columns. Out of which i need to lookup three columns (lookup_colm1, lookup_colm2 and lookup_colm3) using the key_to_all_locks column.
And i will do a lookup only when the lookup_or_not_flag is ‘Y’. And i should not unnecessarily lookup for the ‘already populated records’ from the master_table.
One other information, a single flat file load will populate a minimum of 10K records into the staging_table.
Simple problem. But if i had to do this the Java way, i need to execute a select query for the records whose values are not yet populated from the master_table and then loop through the ResultSet. Meaning, i should avoid lookups for those records for which the flat_file_load_colm_1, flat_file_load_colm_2 and flat_file_load_colm_3) are not null and loop through the rest of the records who needs lookup (lookup_or_not_flag is ‘Y’).
While looping through the ResultSet, i should fetch the key_to_all_locks column and then execute a select on the master_table to fetch all the columns which needs to be populated in the staging_table. And finally, execute an update query on the staging_table with the newly fetched values.
Looks like a lot of job for the Garbage collector.
Doing it the PL/SQL way would also mean the same thing. Only advantage over the Java way is that we could escape with lesser number of Java-DB bridge hits).
I was planned to use a MERGE statement for this scenario. For the benefit of those, who just like me, forgot what MERGE statement really does, here is an explanation from the Oracle Handbook.
Merge statements
1) provides the ability to conditionally update or insert data into a table
2) performs an UPDATE if the row exists and an insert if its a new row.
Syntax
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join_condition)
WHEN MATCHED THEN
UPDATE SET
col1=col_val1,
col2=col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
and this is what i did. Since i just want an UPDATE and not an INSERT, this is the query i derived.
MERGE INTO staging_table AS st
USING master_table as mt
ON st.key_to_all_locks=mt.key_to_all_locks
WHEN MATCHED THEN
UPDATE SET
st.lookup_colm_from_master_table1=lookup_colm1,
st.lookup_colm_from_master_table2=lookup_colm2,
st.lookup_colm_from_master_table3=lookup_colm3
WHEN NOT MATCHED THEN
INSERT VALUES (NULL,NULL,NULL)
(WHEN MATCHED AND WHEN NOT MATCHED are optional clauses only from Oracle 10g. Unfortunately i was working on 9i)
Only later did i find that i was in deep trouble when i came to know that in a MERGE statement, the source and the target tables should have the same structure. But i had some 11 columns in the master_table and 8 columns in staging_table. And the MERGE INTO clause cannot follow a VIEW. A Table name is needed. Only a USING clause can follow a VIEW.
So, the oly alternative i had is to use the UPDATE clause. I can use one subquery per column like
UPDATE staging_table SET
st.lookup_colm_from_master_table1=(SELECT lookup_colm1 FROM master_table where key_to_all_locks=?),
st.lookup_colm_from_master_table2=(SELECT lookup_colm2 FROM master_table where key_to_all_locks=?),
st.lookup_colm_from_master_table3=(SELECT lookup_colm3 FROM master_table where key_to_all_locks=?)
Meaning there is one table level SEARCH for master_table per column. master_table actually is a huggeeeeeeeee table.
Then i came across this particular UPDATE clause which i guess you might find helpful. I dont really know whether this is ANSI standard and could be used for other RDBMS. But this is really cool. And best of it, it solved by problem.
UPDATE staging_table SET
(lookup_colm_from_master_table1,
lookup_colm_from_master_table2,
lookup_colm_from_master_table3)=
(SELECT lookup_colm1,
lookup_colm2,
lookup_colm3
from master_table where key_to_all_locks=?)
WHERE lookup_colm_from_master_table1 IS NOT NULL
AND lookup_or_not_flag=’Y’
AND key_to_all_locks=?
I know i am setting the same value of key_to_all_locks twice. Of course they are value inputs for two different tables. Just let me know if its possible to set key_to_all_locks only once.
Tags: merge clause, oracle
