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.
“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?
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
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.
I knew most people know of this feature in Oracle but yet i just wanted to refresh your mind on this little yet powerful statement.
I just got reminded of this today because frankly this is the first time i am using it in work. We had a table something resembling the following structure.
CHILDID is the unique id of a person with name NAME. His manager or his parent is denoted by the value in the PARENTID. NULL values in the PARENTID indicate that he is the “BIGGEST PARENT”.
We had this requirement. Pick up a childid somewhere in the middle of the hierarchy, say 103 and find his “root” parent. Meaning the parent of the parent of the parent (or the parent where the hierarchy ends).
Of course we can put a series of subqueries if we know the depth of the hierarchy. But Oracle provides this wonderful pseudocolumn called LEVEL and a few notable clauses.
Here is the syntax of the statement (Never mind. Syntaxes are always headaches).
Syntax:
SELECT…
[START WITH initial_condition]
CONNECT BY PRIOR recurse_condition
Key:
START WITH : The row(s) to be used as the root of the hierarchy
CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy
The PRIOR keyword can be on either side of the = operator.
Done with Syntax.
Here we go,
CONNECT BY PRIOR childid=parentid will return a TOP-DOWN hierarchical results wherein the resultset starts with your child being the top most person and displays all the children of your childid.
i.e.
SELECT LEVEL, NAME, CHILDID FROM RELATIONSHIP START WITH CHILDID=103 CONNECT BY PRIOR CHILDID=PARENTID;
will return
CONNECT BY PRIOR parentid=childid will give BOTTOM-UP results. So, this the query we wanted …
SELECT LEVEL, NAME, CHILDID FROM RELATIONSHIP START WITH CHILDID=103 CONNECT BY PRIOR PARENTID=CHILDID;
The LEVEL pseducolumn returns a number indicating the level in the heirarchy: 1 for a root row, 2 for a child of a root, and so on. As we already know what we put after the START WITH statement becomes the root of the hierarchy.
Now, that we want the root parent, i just put a small hack around (I really dont know whether the following query is the most optimal one but yet gave what i really needed)
SELECT * FROM
(SELECT LEVEL, NAME, CHILDID FROM RELATIONSHIP START WITH CHILDID=103 CONNECT BY PRIOR PARENTID=CHILDID
ORDER BY LEVEL DESC)
WHERE ROWNUM<2>
(I am just using our original derived query as a view and extracting the top most record — forgot to mention the ORDER BY clause). Here comes what was needed.
Here is the complete SQL i used
–Table creation
CREATE TABLE RELATIONSHIP (CHILDID NUMBER, NAME VARCHAR2(50), PARENTID NUMBER)
–Population
INSERT INTO RELATIONSHIP VALUES (100, ‘ARUN’, NULL);
INSERT INTO RELATIONSHIP VALUES (101, ‘CHILD OF ARUN’, 100);
INSERT INTO RELATIONSHIP VALUES (102, ‘GRAND CHILD OF ARUN’, 101);
INSERT INTO RELATIONSHIP VALUES (103, ‘GREAT GRAND CHILD OF ARUN’, 102);
INSERT INTO RELATIONSHIP VALUES (104, ‘GREAT GREAT GRAND CHILD OF ARUN’, 103);
INSERT INTO RELATIONSHIP VALUES (100123, ‘NEVER KNEW WHO THIS IS’, 143143);
– Verification
SELECT * FROM RELATIONSHIP
–Top down results
SELECT LEVEL, NAME, CHILDID FROM RELATIONSHIP START WITH CHILDID=103 CONNECT BY PRIOR CHILDID=PARENTID
–Bottom up results
SELECT LEVEL, NAME, CHILDID FROM RELATIONSHIP START WITH CHILDID=103 CONNECT BY PRIOR PARENTID=CHILDID
–Derived query
SELECT * FROM (SELECT LEVEL, NAME, CHILDID FROM RELATIONSHIP START WITH CHILDID=103 CONNECT BY PRIOR PARENTID=CHILDID ORDER BY LEVEL DESC)
WHERE ROWNUM<2
If you are Oracle user at home, you should have really felt the pain of the database server eating most of your system resources. Added to it, in all possibilities, we are using an illegal copy of the Personal Edition or the Enterprise Edition.
You would never imagine running Java applications against Oracle just because of the simple reason we can never run our application servers and database server and, of course, our IDE in a single machine. So, we just move in for MySQL, Postgre or other open-source alternatives.
You would have already known that DB2, just a few months back, came up with a free edition of its Database. And Microsoft is also coming up with its free edition of SQL Server 2005 as ‘Express Edition’
Oracle now joins the bandwagon. And, as always, Oracle is our favourite database.
(Please go through the document (at least the faq) and the pdf attachment for more details). The twp_general_10gdb_product
_family.pdf has a wonderful “feature comparison” table at the end of it.
Please dont get alarmed when they say Java development is not possible in Oracle 10g XE. They just mean that we cannot write Oracle PL/SQL stored procedures in Java. And i’ve still not seen anybody writing
“CREATE PROCEDURE DEPT_PROCEDURE AS LANGUAGE JAVA”
That because Oracle XE doesnt come with an inbuilt JVM. (You know Oracle 9i came up with JRE 1.4 bundled inside). Regular SQL, (ALL) PL/SQL, JDBC calls will just work fine.
Best part, Oracle 10g XE comes just as a 250MB iso image.
Linux users, who were denied of the privilege of using Oracle, just because we dont get a pirated version of the full database, can now download their RPM installers direct from the oracle site. Debian users (Ubuntu, Kubuntu, MEPIS, Mandriva, CentOS) can download their .deb installers. All installers are around the same size.
Good luck.
