0

I've got a particularly pressing issue; I have a suite of stored procs in MSSQL that don't directly translate to Oracle. I've made a good attempt, but I think that my paradigm isn't working the same in Oracle that it is in MSSQL - and it's a fundamental thing.

In MSSQL, I:

  • Create a temporary table (I don't know the structure at design time, only run-time - I create the temp table from another table) Insert records into the temporary table

  • THEN, Run a dynamically determined set of stored procedures (from data in the database) that refine the data (i.e. delete rows that don't meet the criteria) from the temporary table

  • THEN, Return the records from the temporary table.

That works alright in MSSQL (at the moment, until I can refine the entire layer). However, in oracle, we attempted the same thing. Since Oracle doesn't have the same temp table paradigm, I ended up creating a table and then dropping it at the end of the transaction. What I'd really like to do is figure out how to make this perform like it does in MSSQL.

I thought maybe I could use a cursor in the same way as the MSSQL temp table, but I can't see how to delete records from a cursor without deleting records from the database - at least, that's what I find every time I look up delete cursors.

EXAMPLES:

(MSSQL)

SELECT 
  objectid, typeid, field1, field2, field3, field4
INTO
  #temptable
FROM
  objecttable

EXEC FilterRecordsSuite #temptable

SELECT * FROM #temptable

(ORACLE)

BEGIN
  EXECUTE IMMEDIATE 
      'CREATE TABLE mytemptable_<uniquevalue> AS 
       SELECT 
         objectid, typeid, field1, field2, field3, field4
       FROM
         objecttable
      ';

  COMMIT;

  FilterRecordsSuite mytemptable_<uniquevalue>

  OPEN :some_ref_cursor FOR
      'SELECT * FROM mytemptable_<uniquevalue>';

  EXECUTE IMMEDIATE 'DROP TABLE mytemptable_<uniquevalue>'

This isn't my code, this is old and horrible. I'm just trying to make it work until I can refactor the whole thing into something intelligible. Unfortunately I have very little experience with Oracle and the immediate demands of keeping the application functional and even mildly performant outweigh my ability to refactor.

Any ideas?

Noel
  • 10,152
  • 30
  • 45
  • 67
Toadicus Rex
  • 35
  • 1
  • 6
  • Add (one of) the SQLServer stored procedures and the current version of the Oracle one, thanks – Serpiton May 14 '14 at 17:13
  • I would but I am restricted by internal policy as this relates directly to the security of our application - but I can create a mock - I'll try to do that. – Toadicus Rex May 14 '14 at 17:20
  • I've posted some example code - note that the FilterRecordSuite removes records from the temp tables... that's really all it does, but it's a dynamic set of procedures that remove records. – Toadicus Rex May 14 '14 at 17:34
  • I'm afraid you will have to re-design it completely. Simply MSSQL patterns do not work in Oracle. In Oracle the rule of thumb is "Never use DDLs for business logic". Also dynamic SQL should be used occasionally. Imagine that all the SQL PL/SQL is compiled into some kind of bytecode, which is processed by some kind of virtual machine. In fact Oracle's PL/SQL is NOT a scripting language. Oracle compiles it in the background and it also maintains dependencies between tables, views, PL/SQL code, compiled bytecode, generated SQL execution plans. – ibre5041 May 15 '14 at 08:27
  • This is not helpful. As I stated before, this isn't my design. I recognize that this isn't optimal, but I'm between a rock and a hard place. I'm trying to get a functional and semi-performant solution in place to remove the FAR BIGGER KLUGE that I'm trying to get rid of. This is an interim step. What I NEED is to figure out a way to handle this NOW, and I can't rewrite from the ground up as you suggest until we have time to plan it out. If you have a suggestion for that, do tell - otherwise I already know this isn't optimal. – Toadicus Rex May 16 '14 at 19:35
  • Also useful, though not exactly what you asked for may be: [ways to avoid global temp tables in oracle](http://stackoverflow.com/q/2918466/458741) and [Does Oracle have an equivalent of SQL Server's table variables?](http://stackoverflow.com/q/670461/458741) – Ben May 18 '14 at 12:02

0 Answers0