Re: New to Oracle

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:12:43 CST