SQL Server Stored Procedure with # temp table conversion to Oracle

Hi,

I have a SQL Server stored procedure which uses a TEMP table that I need to convert over to Oracle.

I know there is the GLOBAL TEMPORARY table that I can use in Oracle which takes care of the TEMP table. However, what I'm not too sure about is whether or not to use an Oracle FUNCTION or Oracle Stored Procedure. I only have to return back the value which was done with an OUT parameter in SQL Server.

Thoughts this?
LVL 1
davismAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MilleniumaireCommented:
Generally, if you are only returning one value then use a function in Oracle.

If you need to return multiple values then use a stored procedure with an out parameter for each value to be returned (or an in out parameter if the values are also being passed in).

Either the function or the procedure could be included in a stored package but this isn't necessary.
0
MilleniumaireCommented:
There is no benefit to using a function or a procedure, however the call to a function tends to be self documenting.  e.g.

...
v_value := total_sales(v_sales_rep, v_year);
...

Reads better than:

...
total_sales(v_sales_rep, v_year, v_value);
...
It is clear what the function is returning, but it isn't that clear what the procedure is returning.
0
davismAuthor Commented:
I put it all this stuff and then lost my connection! Oh what a pain.

One of the things I have in there was what was the basis for a function over a stored procedure in this context.

Like:

Create or Replace Function
MySchema,My function
(
    pstrFirst Out vchar2;
) return number

AS
or

Create or Replace Procedure
mySchema.myProcedure
(
      pstrLast out vchar2;
)
AS

The only thing I can see is that a function returns a result number whereas the stored procedure does not.

What am I missing here?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MilleniumaireCommented:
When you create a function you specificy the return value and its datatype:

Create or Replace Function
MySchema.Myfunction Returns Varchar2
AS
Begin
   ...
   Return 'This is my return value';
End;

The datatype can be any valid data type, not just a number e.g. boolean, number, varchar2, char etc.

You then use the return statement to return a value from the function.

There is no need to declare out arguments in a function.

Procedures do not use this mechanism to return a value.  It is necessary to define an out argument in the procedure definition and then assign a value to that argument.  eg.

Create or Replace Procedure
MySchema.MyProcedure (o_outarg Out Varchar2)
AS
Begin
   ...
   o_outarg := 'This is my return value'
   ...
End;

Usually, a procedure will either not return any values i.e. has no out arguments, or will return more than one value i.e. have multiple out arguments.  If it returns only a single value then it is better written as a function.  There is nothing forcing you to do this, you could write procedures that return a single value by declaring a single out argument.  It is down to preference and good coding style.
0
Mark GeerlingsDatabase AdministratorCommented:
A couple other things to keep in mind when converting SQL Server code to Oracle code:

1. You may not need "temp" tables at all in Oracle, since Oracle and SQL Server have very different locking and data concurrency models.  If you do want to use a "global temporary table" in Oracle, it should be created just once (usually from a *.SQL script run by a DBA or developer) then a stored procedure can use it.  *DO NOT* create them "on-the-fly" inside a stored procedure in Oracle!

2. Functions vs. procedures:  Either can work.  A function is expected to return exactly one value.  A procedure does not need to return any values, but may be used to return multiple "out" parameters.  I prefer to use functions when no database changes will be done, just "select" statement(s) and procedures for occasions when I want to do an insert, update or delete, but this is just my personal preference, not a rule in Oracle.

3. Oracle PL\SQL procedures *DO NOT* return record sets (at least by default).  This surprises many people with SQL Server experience who are new to Oracle.  Oracle offers "ref cursors" as a way to return a record set (or array of values) if you need that functionality.
0
davismAuthor Commented:
Markgeer,

On #1,

Why should I not create them "on-the-fly" as it is to be a true temporary table created for the instance it is run in.

On #2,
A function is has to be as it's returning a record set. I may need some help with the REF_CURSOR though.

On #3,
Gotchya on the recordset.

At first, I just throught I needed an out parameter but I was wrong. It is looking like a record set.

So, for instance I am filling the record set with

1, Nancy
2, John
3, Mark
4, Traci
5, Lisa

I want to return that entire record set. Do you happen to have a sample of code that might lead to this output as a recordset? Something that I'll pick up on the other end with like VB.NET?
0
Mark GeerlingsDatabase AdministratorCommented:
On #1,
Why should I not create them "on-the-fly" [in Oracle]?  Because, that's *NOT* the way Oracle stored procedures and global temporary tables work best!  If you do create objects "on-the-fly" in Oracle stored procedures, you cannot refer to them directly, you have to use the more complex, and slower "execute immediate..." syntax.

On #2,
A function is has to be as it's returning a record set.  No!  Oracle functions *DO NOT* return record sets!  They return a single value (or variable).

On #3
"Do you happen to have a sample of code ..as a recordset."  Sorry, no.  All of my Oracle experience has been with Oracle client tools (Oracle Forms and Oracle Reports) which build their own record sets internally, without depending on PL\SQL procedures to do that.  I have read about "ref cursors" (which are a way in Oracle to return a record set from a procedure) but I have never needed to use or learn that syntax.
0
davismAuthor Commented:
Sorry, my bad on #2. I meant that SP's return the recordset. So a stored procedure it is.  I was completely flip-flopped.

0
MilleniumaireCommented:
Note on #2 above:  Oracle functions CAN return cursors.  Cursors are not limited to procedures.
0
Mark GeerlingsDatabase AdministratorCommented:
Basically, I'm trying to help you realize that the way things are done in SQL Server is *NOT* necessarily the best way to do things in other SQL-based databases.  Yes, Oracle and SQL Server both support the basic SQL verbs (select, insert, update, delete) but beyond that, there are probably more differences than similarities between the two systems.  For example, Oracle stored procedure syntax does not even directly support any other SQL verbs (like: create, alter, drop, etc.).  These commands are only possible in Oracle stored procedures inside the "execute immediate..." command, but that has performance and security implications.
0
davismAuthor Commented:
markgeer,
I understand and understood that, what I'm trying to see about is the best way to achieve comparable utilization. The SP that I have that I'm trying to convert has no alter or anything. I'm not sure what you are referring to with CREATE because you do have a "CREATE OR REPLACE" which functions much the same way.

Milleniumaire: this has me confused. markgeer, just mentioned that functions can only return 1 value. Or are you referring to that 1 value being the recordset?

0
Mark GeerlingsDatabase AdministratorCommented:
Sure, you have to use the word "create" in Oracle to create a PL\SQL function or procedure.  But, this word does not become part of the procedure or function, and you do not normally execute the "create procedure..." or "create function..." syntax inside PL\SQL.  These SQL commands are usually executed in SQL*Plus or TOAD.  Oracle's PL\SQL syntax only directly supports the four basic SQL verbs: select, insert, update and delete.

As far as I know, Oracle functions cannot return a "ref cursor", a record set or an array of any kind as the "return" value.  But, Oracle functions can optionally return "out" parameters (like Oracle procedures can) and these could include a "ref cursor".  Some applications however (Oracle Reports for example) cannot handle an "out" parameter being returned by a function in addition to the "return" value, so generally if an "out" parameter is desired, a procedure is used for this.
0
MilleniumaireCommented:
From Oracle PL/SQL Programming (3rd edition) by Steven Feuerstein:

"A PL/SQL function can return virtually any kind of data known to PL/SQL, from scalars (single, primitive values like dates and strings) to complex structures like collections, object types, cursor variables, and LOBs (large objects)....."

Cursor variables = ref cursors = recordset

I've not done it myself, but I would probably tend to believe Steven Feuerstein (PL/SQL guru) over markgeer ;-)
0
davismAuthor Commented:
markgeer,

It's a little interesting what you are saying with the create and all because in RapidSql is DOES show the create and all. Granted anything before the create is gone but that's the same in SQL Server and Sybase as well. Maybe RapidSql puts that on there but it's a little strange because how would it know. I would think it's part of the artifact itself like in Sybase and SQL Server.

It would definitely be interesting to know on the ref_cursor with a function. I may see about exploring that one a bit more or if you all have any information that would be great.
0
Mark GeerlingsDatabase AdministratorCommented:
Milleniumaire is correct, Steve Feuerstein knows PL\SQL *MUCH* better than I do!  I'm not familiar with the tool: "RapidSQL", but I do know that Oracle stored procedure (PL\SQL) syntax does not directly support the DDL verbs like: create, alter, drop, etc.  Also, Oracle best practices *DO NOT* include creating objects dynamically in stored procedures.

Oracle stored procedure syntax is optimized for performance and scalability by using a concept called "early binding" (at compile time, not run-time).  This means that when Oracle stored procedures are compiled, the table and column references in that procedure (or function) are "bound" to the database opbects they refer to.  This means those object must already exist at compile time.  SQL Server on the other hand uses "late binding" (at run time) of the table and column references to the actual database tables and columns, so SQL Server stored procedures can support dynamically-created objects.  The penalty though is slower run-time performance of SQL Server stored procedures compared to Oracle stored procedures.
0
davismAuthor Commented:
SQL Server and Sybase are the exact same with early binding with the exception of temporary tables. The temporary tables *MUST* be defined prior to compilation hence what I was saying about the stuff before the create is LOST when compiled and the artifact is set in the DBMS. For it to successfully compile in those DBMS's the reference it uses MUST exist.

If you're doing a dynamic SQL then that is another thing but is fraught with issues in and of itself with the optimizer knowing what is availavle and the statistics updated accordingly.

So, I'm not sure I'm following what you are getting at with Oracle - am I misunderstanding something? (Very possible in relation to this).
0
Mark GeerlingsDatabase AdministratorCommented:
Oracle stored procedures treat "global temporary tables" the same way as permament tables.  That is, they must exist before a procedure that references them can be compiled.  That is very different from the Sybase and SQL Server practise of creating "temp" tables dynamically inside stored procedures.  This difference is because of the very different way that Oracle handles record locks and data concurrency than those systems do.  Because of that, Oracle stored procedures rarely need to use temp tables.  But in some cases, they can cut processing times.  In Oracle,if needed, they are created just once, outside of PL\SQL, and the temp table definition is persistent (as with normal tables).  What is temporary with "global temp tables" in Oracle is just the contents.
0
davismAuthor Commented:
So, with a "global temporary table" it's also recognized as the structure is persisent but the data within the structure is not? If that's the case, it is a little weird because you have really no uniqueness.

You're right, that is very different.

In light of that, what is the best manner when I do not want do have the structure persisent to maintain the uniqueness attributes of execution. So basically, 10 people can run the function or stored procedure (whichever it ends up being) and each has a different set of results unique (data uniqueness) to their request. (uniqueness of execution basically a separate thread of execution).

0
Mark GeerlingsDatabase AdministratorCommented:
Oracle handles that for you with global temp tables!  Each user sees only his/her own contents of the global temp table, even if other users run the same procedure at the same time.  And, if a single user logs in twice, each individual session for that users sees only its own data, not the data that belongs to the other session of the same user.

Oracle automatically clears the contents of global temp tables either:
1. when a commit occurs in that session (this is the default)
or
2.  when the user logs out (the other option).
0
Mark GeerlingsDatabase AdministratorCommented:
When you say "I do not want do have the structure [of a global temp table]  persisent" that displays your SQL Server bias.  In Oracle, you definitely want the structure to persist because it saves so much processing overhead at runtime.  As I said earlier, SQL Server and Oracle are two very different systems that happen to both be "SQL compliant" at the basic level.  But programming approaches and best practises vary widely between the two.
0
davismAuthor Commented:
Interesting...

So, that is grounds for the use of one and it can be taken as a "PRO"; are there any "CON"'s to using the global temp table?

You can more than one temp table, basically, if a there is another process and it uses a temp table that has a different structure?

It would also seem very busy on the potential number of tables. Is there another way rather than dealing with temp tables?
0
davismAuthor Commented:
It's not biased. It's looking at scalability. and maintainability with controling the volume of artifacts. How is that biased? II think it's the complete opposite. I'm looking at what options are available to produce the same result in Oracle.
0
Mark GeerlingsDatabase AdministratorCommented:
Remember, the main reason for needing temp tables in SQL Server is because it has a very different (I would say, less efficient from my biased Oracle perespective) way of handling record locks and data concurrency than Oracle does.  You rarely need a temp table at all in Oracle, since a simple cursor will usually get you the data you need even if other users are adding, changing or deleting records in the same table, or even some of the same records your cursor is fetching!  Oracle's "consistent read" mechanism will continue to present your cursor witht he data as it was the instant you started your query, even if your query runs for seconds, minutes or hours.
0
davismAuthor Commented:
2 things:

1) Is not a cursor a performance impediment on a DBMS? Because it treats each row as a single entity? Much like actioning on one record at a time in like C++, VB. NET, C#, etc? Wouldn't the optimizer see that as a performance issue? If Oracle is treating that different; in what way would that be? When one fetches the cursor is it returning one row or a bulk set of data from the resultset?

2) When dealing with the changing the same record in your cursor that somebody else is working. It comes into line with the commit action on a FIFO basis. So, would not one of those situations be considered a "dirty read"?

Case #2 is not without the realm of the conversion that I'm doing anyway because that wouldn't happen anyway; it was more a question.

Case #1 is relevant though.
0
Mark GeerlingsDatabase AdministratorCommented:
1) A cursor is a reality that cannot be avoided in a DBMS.  I would say it is an integral feature.  Yes, cursors fetch indivdual rows, but in Oracle those rows are fetched as they were at the time the cursor started to fetch the first row.  They can either be returned as a "ref cursor" or they can be processed one at a time by the procedure.

2) There are no "dirty reads" in Oracle because of the way Oracle handles data concurrency.
0
davismAuthor Commented:
A "dirty read" is a concept. What is Oracle doing to handle the concept differently?

A cursor is a reality...yes, but it obviously really depends on the DBMS in which case some can definitely be avoided. If you associate a ref_cursor with a recordset then yes. You are absolutely right. The processing would be different. Mass (multiiple rows) vs. singleton (single row)

To me what you are describing is basically the same as a recordset and a program (again like C++, C#, VB, etc) just interacting with that record in the the recordset. Then moving to the next record in the recordset and on and on.

I'm gotta be missing something here; I'm not sure what it is though.
0
Mark GeerlingsDatabase AdministratorCommented:
Your original question was about a temp table in a SQL Server procedure and your need to convert the procedure to Oracle.  I've been trying to make the point that the use of, and even the need for,  temp tables in stored procedures is *VERY* different in Oracle than SQL Server.  In Oracle they are rarely needed, and are *NOT* created in stored procedures.

Both Oracle and SQL Server procedures can return "out" parameters, but how the values for the "out" parameters are determined inside the procedure is often very different because of the inherent differences between SQL Server and Oracle regarding record locks and data concurrency.

"What is Oracle doing to handle the [dirty read] concept differently?"  Oracle uses "rollback segments" to construct a consistent (or "clean") read for queries that take some time, even if other users or processes are changing the rows involved in the query.  It is possible that a long-running Oracle query can get the "snapshot too old" error, if other processes have modified the records for the query.  But rather than presenting a "dirty read" to the user, Oracle aborts the query at that point.  This error is rare in well-designed, and well-tuned Oracle systems.
0
davismAuthor Commented:
Ok, I sense that you are not proponent of SQL Server or Sybase. I sense that you are a very strog proponent of Oracle and is the cure-all end-all. That's perfectly fine and everybody is entitled to their opinions.  I'm not a proponent of any DBMS in particular. I've just relayed to things I've worked with and know.

I just asked a question related to TEMP tables and an Oracle function or stored procedure. Right now, unless I'm missing something, there is not enough information either way for me to make an informed decision.

0
davismAuthor Commented:
By the way, thanks for the response on the "dirty read" - that was well stated and very informative.
0
Mark GeerlingsDatabase AdministratorCommented:
No, I don't consider Oracle the cure-all, even though I do know Oracle much better than Sybase or SQL Server.  I recognize that SQL Server integrates very well with Microsoft tools, and in general SQL Server databases require less DBA effort than Oracle databases.  On the flip side, Oracle is available for most of the common O/Ses in the world and Oracle is very tunable for different server hardware, numbers of CPUs, amount of RAM, disk systems, type of application, etc. so performance of Oracle can be very good.

I was concerned that you were approaching Oracle with the mindset that the way SQL Server gets things done (or forces you to do things) is the best way for all SQL-based systems, and that certainly is not true.

I suggest that you likely won't need temp tables at all in Oracle stored procedures that are converted from SQL Server procedures.  At most, I would estimate that maybe 2-5% of Oracle stored procedures that are converted from SQL Server procedures will benefit from global temp tables in Oracle.  In most cases, use of a global temp table in Oracle will only slow down the procedure, and not change the output at all.
0
Mark GeerlingsDatabase AdministratorCommented:
MS SQL Server also has more user-friendly DBA or management tools than Oracle does, and SQL Server stored procedures can do O/S tasks directly, while Oracle stored procedures cannot.  Oracle stored procedures though can call Java stored procedures, and Java can do O/S tasks, so O/S tasks (like reading or writing an ASCII file, or moving, copying or deleting a file on disk) can be done from Oracle PL\SQL, just not as easily.

I do have more than 10 years of experience running Oracle databases on Windows, and found that combination easy to manage, and quite efficient.  I would not describe the Windows O/S as stable though.  In our last year of running our corporate Oracle database on Windows, using 32-bit WindowsServer2003 Enterprise Edition, we averaged a crash per week of the database or O/S.  We switched to Linux three years ago for our Oracle server O/S, and have had only two crashes in three years.  Less than a crash per year is certainly more stable than a crash per week!  I don't find Linux to be user-friendly, and I don't find it to be faster than Windows, but the stability is nice.
0
davismAuthor Commented:
Thanks, I have about 8 years experience in Sybase and SQL Server. The Sybase we had on AIX and now Linux. SQL Server...well Windows. I have had a version of Sybase ASE running on one of my Windows machines for a while and pretty stable.

Some of the DBMS's being on Windows can be stable from my experience but it really depends upon what other tasks or endeavors are being done at the same time. That can lead to unstability. :-)

You mentioned that Oracle does not need to use the TEMP' tables expect in very rare situations. What I need to try and figure out is how to do that in with the conversion of this SP and if that conversion should be an Oracle function or SP. That's what I'm not too sure about.

Any books that you know of that may help in how that is done and decided upon?
0
Mark GeerlingsDatabase AdministratorCommented:
No, I haven't come across any good books, articles or web links recently that describe SQL Server to Oracle migrations.  I have seen some in the past (especially magazine articles, like in the "Oracle Professional" magazine that used to be published by Pinnacle Publishing) but I don't have any recent examples.

I would start by assuming that a function in SQL Server should become a function in Oracle, and a procedure in SQL Server should become a procedure in Oracle, mainly to reduce the amount of changes in your calling application code.  But I suspect that to replace the SQL Server use of temp tables in Oracle procedures (or functions) with optimized Oracle PL\SQL code will require some manual coding effort on your part.  It may be as simple as just writing Oracle queries to select from the base tables or views directly rather than the "two-step" approach of SQL Server to first create and populate a temp table, then select from that.
0
davismAuthor Commented:
Sorry, should have clarifed...do you know of any good books that address Oracle functions and SP's and may have some example styles as well? Something I can use as a reference?

The writing of a query against an Oracle DB is something I've done before.

Basically, what I have is a process that takes the values and puts them into a temporary table. Then once all the processing is done it then queries that temporary table and produces a recordset which can equate to a ref_cursor from what I understand.

So, like:

"1, Nancy "
      Insert into table
      {other processing}
"2, John"
      Insert into table
      {other processing}
"3, Mark"
     Insert into table
     {other processing}
"4, Traci"
    Insert into table
    {other processing}
"5, Lisa"
    Insert into table
    {other processing}

be able to pull all values from that table from a "ref_cursor" (i.e. recordset).

I don't know with Oracle, if it's not going into a table then I'm not sure where it would live.

Do you know of any books that would help with something like that?
0
Mark GeerlingsDatabase AdministratorCommented:
Do these records get inserted into a permanent table as well as into a temp table in the SQL Server procedure?  Where do these records come from, a query based on some conditions, etc.?

A "ref cursor" in Oracle can select from: a permanent table, a view, a global temporary table, and/or any combination of these, and/or multiples of any of these.  Whether a global temp table is helpful or not in Oracle depends on the kind of processing that the procedure must do (and whether the results need to be saved to a permanent table or not) and on where the values come from that need to be processed.

Probably about 5% of the procedures and function in our custom Oracle system use global temporary tables, and I suspect that is typical of most Oracle systems.  The vast majority of our stored procedures don't need (and would not benefit from) a temp table.
0
davismAuthor Commented:
markgeer:

Sorry for the delay...a few things I had to deal with.

No, the records do not get inserted into a permanent table. The records to come from other queries.

It's just a temporary location (which as you know is what the TEMP table does) that allows other SQL to be executed against it. In this case the information is put into a temporary storage location (i.e. temp table). There is a possibility that let's say a process id in Oracle is subsequently tacked on to the end of the first name. So, the result of the first record in the example would by like "1, Nancy 02343".  A final select pulls out all the information in the temporary location.

So, the TEMP table aspect/concept in this context is just temporary storage of information which is provided like a ref_cursor or recordset at the end of all the processing.

What does Oracle have to temporary storage of information to be available during the execution of a function or stored procedure? A memory array? A non-persisent ref_cursor?

The temporary storage of information being able to be provided in the resulting deliverable ref_cursor?

This making sense what I'm trying to get at? (I'm thinking it does but I may not be conveying it very well.)

0
Mark GeerlingsDatabase AdministratorCommented:
If the records do come from other queries, and they get a process Id attached, but they don't get saved that way to a permanent table, and you want other SQL statements to be executed against this set of records then, yes, it sounds like a global temporary table may be the best option for this in Oracle.  But, the contents of an Oracle global temporary table can never be shared across database sessions.  They are  *ONLY* visible to the Oracle database session that populated them.  So the "other SQL statements" that access these records must be executed in the same session that created the records.  They may be in other PL\SQL procedures or functions, as long as those are all executed in the same session.

Also, global temporary tables in Oracle are created just once, usually by a DBA, and outside of PL\SQL.  Then they can be used by PL\SQL procedures or functions just like permanent tables with the exception that the data in them will be cleared automatically either whenever a commit happens in that session, or when the session logs off, depending on how the global temporary table was created, either with the default value: "on commit delete rows" or the optional value: "on commit preserve rows".

Oracle also supports memory arrays, but I find the coding to much simpler for global temporary tables.  Yes, global temporary tables can be queried by a procedure that returns the results in a "ref cursor" as long as that "ref cursor" will be used by the same database session that populated the temp table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davismAuthor Commented:
Yes, it would ONLY be visible for the one session using it. I understand the structure persistency.  

Let me *play* around with this a little and I'm going to update the points because I think this conversation warrants point increase.

More info to come. :-)

0
davismAuthor Commented:
markgeer,

Thanks for the information. I *think* I have everything taken care of now. Now, I just need to test it out a little further and then see about getting it in the VB.NET application. Thanks for the time and information though. Very much appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.