Re: New to Oracle

You have quite a few mistakes in what you wrote is causing you grief which leads me to believe that you haven't really studied Oracle much. For example ... the inner join syntax is different in 8i but both are supported in 9i. CASE in PL/SQL can be used in any manner in 9i and in 8i it is supported in PL/SQL through native dynamic SQL. And there many are other examples I could cite.

But what really troubles me is that you don't seem to have spent any time learning how to do things in Oracle ... it appears that you are just trying to do the same thing in another product which is, if not impossible, a great way to make an unscalable mess lacking in data integrity. Your comment about temp tables, for example. While temp tables are commonly used in TSQL there is almost never a rational reason to use them in Oracle. I would guess that 95% of the time I see them it is developers from a TSQL background who don't understand Oracle's multiversioning, transaction model, use of resources, locking, etc.

I strongly urge you to stop banging code and learn the underlying architecture before you continue down this road any further. In the end you will blame Oracle for what is clearly your own lack of learning the product.

Oracle is NOT a Microsoft product with another company name on the CD. And the fact that you are approaching it that way is a flashing red danger sign.

Daniel Morgan

Jon Waterhouse wrote:

> I'll vote for the Scott Urman book too.
>
> I'm in the process of converting code from TSQL to PL/SQL too. I wrote the
> T_SQL code as a complete newbie to SQL server, knowing that I would
> eventually have to transfer it to run on Oracle. I expected that would be
> the hard part. But the code I am ending up with in Oracle has litle
> resemblance to the T-SQL code.
>
> A short list of the things causing me grief are:
>
> some types of SQL syntax not supported like
> UPDATE tabx
> SET coly = somefunc(tabzcols)
> FROM tax,tabz WHERE etc.
>
> INNER JOIN ...ON (just goes in the WHERE clause)
>
> delete s1 FROM
> spsstest s1 INNER JOIN SPSSTEST s2 ON s1.fil=s2.fil
> WHERE s1.sequence > s2.sequence
>
> becomes DELETE FROM s1 where exists(select 1 from s2 where s1.fil=s2.fil)
>
> CASE() is not supported in PL/SQL. In some cases whre I had used CASE I
> ended up using a cursor and a function to return the value I wanted.
>
> Stored procedures are bound early, not late, so temporary tables have to
> exist before you compile your code. DDL is not allowed in stored procedures
> so they have to be executed as dynamic sql.
>
> Differences in the way automatic and manual conversion of types takes place.
> Oracle uses to_char,to_date etc. rather than CAST
>
> Different date functions.
>
> punctuation (:= (assignment), || (concat) and ; (statement end))
>
> Those are just some of the major things I have been wrestling with in
> converting the code. I'm now starting to wrestle with some performance
> issues that I don't understand at all as yet, like why does:
>
> insert into tabx (col1,col2, etc.) select (col1,col2 etc) from taby where
> tabycolz = something
>
> take _HOURS_ to run, while
>
> create tabx x as select col1,col2 etc from taby where taby.colz = something
>
> takes _SECONDS_?
>
> And then there are DBA issues. With SQLserver I just installed and wrote.
> Only DBA thing I had to worry about was emptying the redo log from time to
> time. With oracle I keep on running out of rollback space (default is not to
> specify an optimal size for rollback segments, so if you have big
> transactions you can easily run out of space even though the rollback
> tablespace is a fair size, because space is not reclaimed), imports through
> ODBC screw up unless table names are all caps, and stored procedures don't
> use roles, so you have to be granted them explicitly.
>
> Good luck!
>
> Jon
Received on Wed Apr 03 2002 - 10:47:26 CST