Windows Developers still don't get it!



  • Of course Oracle is harder than MS Access and SQL Server. It's a real database not a desktop toy. And yes it is from the Unix world. Windows is crap as a server. Hmm... let's see the last time I had to reboot the database server, last year sounds about right. Let's get the windows jockey's out to reboot once a week so your systems won't crash and burn.

    It's not meant to be visually pleasing it is a database. It is meant to store data relationally and move it from place to place, fast! In addition to the database SQL*Net (the networking protocal for Oracle) blows the doors off of ODBC and OLE or whatever other crap you guys use to connect to SQL Server.

    So the database is faster, more scalable and can return data fast to the client. Sounds good to me. If I understand you correctly, your complaints are based on that you don't know how to do something so therefore it is bad. You obiviously aren't real coders and don't understand databases, so therefore all databases are bad.

    You think Oracle's tools suck, try Teradata's but yet it can do things SQL Server, Informix and Oracle can only dream of.

    One last note, you guys also don't get paid crap. When the box and OS to run the system on only cost a few grand, employers have a hard time justifying your salary. When the technology is expensive, paying an individual well is easy to accept. Everybody can run windows, that was the point. Get a job only the smart people can do.



  • I hate Oracle because self proclaimed Oracle gurus are <font color="#ff0000">elitist haters</font><font color="#000000"> like this fool</font>...



  • Riiiiiight ... but let's not forget who the #1 oracle expert consultant is: Donald Burelson.



  • @mdampier said:

    So the database is faster, more scalable and can return data fast to the client.


    Well I do database-backed websites (mainly) and I'm afraid the experts doing the benchmarks disagree with you on that one.

    And the main reason you get (slightly) larger salaries is that Oracle DBAs and developers have to go on special training courses where they learn:
    • How to use their RDBMS without bursting into tears
    • How to perfect that arrogant sneer they all seem to have
    • How to persuade managers that the massive hardware investment required is justified
    ...and you need to recoup that cost somehow.



  • <font style="BACKGROUND-COLOR: #efefef">Point 1:
    Oracle's legendary stability is a crock of shit. The last company I was with was making an expensive and ill-advised change-over to Oracles application Suite. The further it got, the bigger the catastrophies were.
       My favorite, "How Cool is Oracle" moment came when they were installing another one of the seemingly endless array of patches, fixes and other assorted crap. The production server shit the bed and wasn't right again for days.
       Now keep in mind, this was not the result of some jackass with an Oracle for Dummies Book getting in over his head. This was a systems group that had been using Oracle for years. A technical manager that has been an Oracle dba (an actual official dba not just someone that claims it for the job title) for 7 years. A stellar Unix admin, this stuff was on Soloaris. They were even working with a 3rd party that does maintanance stuff like this as their line of business. The patches worked on the Pilot (test) system but made the production server go down in a blaze.
    Point 2:
      We use computers to make our lives easier. Why would you eshew tools that make you work better and faster. Query Analyzer and Ent Manager are great tools for getting shit done. MS gives them to you as part of their database server. Oracle charges you buckets of cash and does not furnish you with anything that resembles an effiecient tool for accomplishing work. They are cheating you.
    Point 3:
       Companies pay more money for Oracle developers because they are already used to getting bent over and fiscally violated for anything involing the word, "datbase."</font>



  • I had to join just to refute this nonsense.

    First of all, I have been an Oracle programmer for over 8 years. Before that I was a SQLServer DBA/programmer for ~5 years.

    SQLServer is obviously not a desktop toy. It has been eating away at Oracle for years now. It is cheaper, easier and in some cases faster. Just check out the TCP benchmark results http://www.tpc.org/information/results.asp . In general, I agree that Oracle can do many things that SQLServer cannot (ROWNUM, Dynamic SQL permissions, etc.), but conversely there are things that SQLServer does better than Oracle or that Oracle can't do(Case-insensitive database, Temp Tables).

    SQLServer beats the pants off Oracle for ease of use. Oracle has always sucked in this arena. They can't write a user-interface to save their life, their documentation is terrible, and one has to be a masochist to try to administer the damn thing. (Although they are getting better at that). They have used their power for years as an excuse for their complexity. Their army of consultants and overpaid DBAs didn't complain because it kept them very gainfully employed. SQLServer has shown the business world that one doesn't need a fleet of consultants and several DBAs just to run a single server. SQLServer's stated goal is one DBA per 100 servers. Oracle has finally realized that it's old excuse for complexity no longer holds. That is why they have been playing catchup with the past few versions to try to make it easier to use - they just have a long way to go.

    Excusing Oracle's poor tools by pointing to something worse is not even worth responding to.

    And using the fact that you are overpaid to justify your product is idiotic to say the least. Now don't get me wrong - I make a very pretty penny because of Oracle and don't plan to drop my rates out of the goodness of my heart or anything. But to use it as justification is pretty pathetic. Unix as an operating system is terrible. It may be more stable, but again, Windows is catching up damn quickly and I don't see that sad command-line OS family of *nix getting any easier to use. And using the fact that the hardware is over-priced is equally stupid. So let me see if I got this right. The hardware is over-priced, the OS is over-priced and overly-complicated and the database is overly-complicated meaning the programmers and DBAs have to be over-priced. And you are arguing for Oracle? Can I ask a favor - please don't try to help.

    Now, again - don't get me wrong - I'm all for Oracle. It's my livelihood, after all. But I support it because it is more powerful than SQLServer in many respects. PL/SQL blows the pants off T-SQL as a start, and the locking and latching mechanisms in Oracle have always been superior to SQLServer. But I also fully recognize that SQLServer severely beats Oracle in terms of usability and maintainability. What one ends up with is, IMHO:

    SQLServer, which is easier to load, run, write against and administer. It is a very forgiving database where 80% of the work is done for you. This is extremely well tailored for the smaller and simpler databases, as it starts to get more complex and eventually tops out against much larger and much more complex problems - but it's getting better with every release.

    Oracle, which can more easily handle the much larger and much more complex problems assuming you have the staff that is up to the complexities of the product. It is more difficult to load, run, write against and administer, but it's getting better with every release.

    They are like Democrats and Republicans who both started as extremists but are now both very close to the center.

    - Chris



  • <font size="1"><font style="BACKGROUND-COLOR: #ffffff">Whoa, backup there fan boy! The initial post has elements of a grand high priest preaching to the masses while deep in religious fervour.

    </font><font style="BACKGROUND-COLOR: #ffffff">"Of course Oracle is harder than MS Access and SQL Server, It's a real database not a desktop toy."
    "Let's get the windows jockey's out to reboot once a week so your systems won't crash and burn."

    You're comparing Oracle with MS Access which is ridiculous.  That would be like comparing a fine Porsche 911 to inline skates. However, if the main requirement of your transportation is to navigate busy pedestrian areas quickly, your Porsche 911 would be a poor choice. Compare Oracle against Access in its own area and Oracle would lose.  A desktop database is just as real to a business that needs one. Far be it from me to get in the way of making inspiring points to bolster your faith.

    Once again, it’s a fantastic comparison with system administrators. You obviously know what you are doing with a db server if we are to listen to your rhetoric. You have not had to reboot your server for at least a year.  So you compare this with a scenario that is obviously different: a poor system administrator using Windows software. I put it to you that a poor system administrator will run a worse system with UNIX than Windows.  I also put it to you that a good system administrator can run a fantastic server with whatever platform they specialise in. I have had the privilege to work with both UNIX and Windows. In my time I have also worked with great system administrators.  Both platforms run well, albeit differently, in good hands.  Since when was it a bad thing, especially in smaller business sizes, that a system could actually be setup with minimal skills and run reasonably well? Once again, the black art that is information technology must only be available to the elitist.

    </font><font style="BACKGROUND-COLOR: #ffffff">"It's not meant to be visually pleasing it is a database. It is meant to store data relationally and move it from place to place, fast!"
    "So the database is faster, more scalable and can return data fast to the client. Sounds good to me....."

    More rhetoric, and this time it makes less sense than before.  The first quote I believe is based on comments made regarding the Oracle interface.  The idea that, 'It’s got to be hard to use, it’s a serious tool!' is pathetic. Good design is not something to cut down on because you have serious people using it.  Apply the same logic to car design.  The new Ferrari is released and comes under heavy fire for not being supplied with a steering wheel and gear stick. The chassis has also been replaced with the classic Volvo (2 wardrobes shape). Would anyone buy it? The answer is most likely yes.  The same kind of people that would buy it because it’s Ferrari - because they are Ferrari fanatics. Who cares if the interface and styling are not visually appealing or functional? It’s a serious car that gets you from place to place - fast!  The truth is Oracle would be just as good as it is performance wise whether it has a good interface and tools or not. So why not include them? This point has to be expanded further because it ties in with the final part of your spiel.

    </font><font style="BACKGROUND-COLOR: #ffffff">"One last note, you guys also don't get paid crap. When the box and OS to run the system on only cost a few grand, employers have a hard time justifying your salary. When the technology is expensive, paying an individual well is easy to accept. Everybody can run windows, that was the point. Get a job only the smart people can do."

    A salary, in my experience, reflects the worth, responsibility and how useful a person is.  When you perform your job well, paying a high salary is easy to accept. When you are paying for technology to justify your own salary, people do not accept that. To think they do is an insult to people. A DBA typically carries a lot of responsibility. Buying an Oracle server just because you can run it, when a less expensive (and completely capable) solution is available just so you can compete in the DBA virtual dick measuring contest is ridiculous.  They are the kind of people that wish to make (possibly keep) Information Technology/Systems a black art so they can continue to reap unjustified financial benefits. Smart people have a job so they can make smart decisions. They get paid well because they make smart decisions.  Devious people make arbitrary decisions because others around them do not understand what is involved. Get a job only smart people can do, make smart decisions.

    My own point of view is based on using the right system for the job in hand.  Any arbitrary decision over what solution is better without considering the issue at hand is short sighted.  Any person that says "Oracle is better" or "MSSQL is better" without supplying a context is not saying anything at all.  It is pointless to shoe-horn MSSQL servers into a Linux setup that is already established and is ready for Oracle. It is pointless to enforce Oracle into a Windows Small Business Server for the purpose of up to 50 simultaneous users when MSSQL is already there.

    Windows developers/UNIX developers/whatever - it does not matter.  Good developers utilise the platforms that deliver the applications that users/customers require. Developers do this on the most suitable platform for the users/customers. Not taking that into consideration is nothing to do with being a Windows developer, its to do with being a poor developer.

    That concludes my rant for today.
    </font></font>



  • OK, let's see about one of the tools that Oracle imposes on everyone.  The ORACLE INSTALLER is the thing that has caused me more pain over the years.

    What is the prerequisite for running a setup or installation program?  It SHOULD be that you have administrative rights, and that's it.  But Nooooo.  I have had many occasions over the years where this little gem refuses to even run because I don't have the proper version of the JVM installed.  Now, see, you're an install program, right?  Aren't you supposed to have whatever requirements contained in your magic little install bag?  And god forbid you have Oracle installer 8.1.3.4 and are trying to run some Oracle Install that requires you have Oracle installer 8.2.3.6!  Shouldn't any setup/installation be as easy as, oh, RUNNING the install?  Should an install program have versioning (and conflicts) and prerequisites?

    Please try to defend this, I'd like to hear.

    I'm not complaining, as you say, 'because it's hard'.  I'm complaining because it doesn't WORK.



  • @bigjim said:

    ... refuses to even run because I don't have the proper version of the JVM installed. ...


    My favorite question is "why did they go to Java for the installer anyway?!"

    Has anyone ever looked at the task manager in windows when running the 8i installer?  That's the last time I had the pleasure of installing oracle, but I remember it consuming so much memory on one of my test servers that the installation couldn't complete due to lack of memory (this is only during the file copying stage, I wasn't even setting up a database - just having it create the scripts to run later!).

    Rediculous.

    I have to say I hated the old installer (with its 8000 different languages supported and etc etc etc), but I hate the java-based one even more!

    On another note - I love oracle for one feature - PL/SQL.  I've never seen anything remotely as good as it in any other database engine, and for this reason alone I would happily use the OracleDB for any large scale projects for as long as its available.  However, as soon as somebody can add a fully functional language seamlessly to their DB engine (and I'm not talking about something like embedded java like Oracle did -- what a stupid concept), I'll be happy to switch to their product. 

    Of course, there are several other features I like about Oracle, but I can live without all of them except PL/SQL.

    Kelly



  • @kellyleahy said:

    Of course, there are several other features I like about Oracle, but I can live without all of them except PL/SQL.


    One word: Fyracle (Oracle-mode Firebird). Firebird with PL/SQL support. Goodbye Oracle.



  • PostgreSQL and EnterpriseDB

    I agree, Firebird and Fyracle are a decent choice but it seems the best
    free solution are PostgreSQL (http://www.postgresql.org) and
    EnterpriseDB (PL/SQL interface to PostgreSQL :
    http://www.enterprisedb.com).



    Some words about PostgreSQL/EnterpriseDB::

    the richest in feature free database: schemas, statement and row
    triggers, rules, replication, clustering, MVCC,  inheritage,
    unlimited characteristics for database objects ,  ....



    Conclusions:

    PostgreSQL/EnterpriseDB has almost all the avantages of Oracle but doesn't the disavantages.

    There are low in resources, dinamic allocation, automaitenance, free, facts for NO WORKAROUND in Oracle.



    If you like PL/SQL and others Oracle's features, try PostgreSQL/EDB !



    Bye, bye Oracle !




  • @gdans said:

    I agree, Firebird and Fyracle are a decent choice but it seems the best
    free solution are PostgreSQL (http://www.postgresql.org) and
    EnterpriseDB (PL/SQL interface to PostgreSQL :
    http://www.enterprisedb.com).



    Some words about PostgreSQL/EnterpriseDB::

    the richest in feature free database: schemas, statement and row
    triggers, rules, replication, clustering, MVCC,  inheritage,
    unlimited characteristics for database objects ,  ....



    Conclusions:

    PostgreSQL/EnterpriseDB has almost all the avantages of Oracle but doesn't the disavantages.

    There are low in resources, dinamic allocation, automaitenance, free, facts for NO WORKAROUND in Oracle.



    If you like PL/SQL and others Oracle's features, try PostgreSQL/EDB !



    Bye, bye Oracle !




    I' keeping an eye on them, but their PL/SQL implementation is currently far from complete.



  • my main gripe with the Java based installer is that it installs its own JVM (and a very old version at that) despite there being a JVM of a later version installed already.

    This completely messes up any Java applications (like maybe your application server and its web applications which were to access that database) already in place, requiring reversing all the configuration changes Oracle performs unnecessarilly while it's installing.

    As to Firebird, it's indeed great. Fast, small, powerful, and free.



  • I gave up on the Oracle installer years ago. Most of the time, I do the installation manually with the oratool (I have the instructions written down how to do this). Works every single time without fail. In addition, on Mac OS X, Oracle couldn't even get their crappy installer to work correctly with our 1.4SUN JVM, so they just give us a bunch of tarfiles, and tell us to tar zxvf ora10.tar.gz /home/ora (litterary), and then run /home/ora/MakeDatabase, or something like that. I haven't done this in a long time though.



  • haha! i hate the smile of this guy!! ...and i hate his "buy my book" sign! i can't imagine him talkin' to a girl without getting so nervous that he starts to sweat like niagara falls...



  • ups... talkin about #1 consultant!



  • It's ridiculous how many people can't spell "ridiculous"!



  • @ChrisRLong said:

    PL/SQL blows the pants off T-SQL as a start

    Interesting take on the situation.  Although I find PL/SQL to have some great features, such as the modularity provided by packages, it really rubs me the wrong way that it forces one to use cursors (row-based processing) for procedural logic.  One of the great technical advancements of relational databases is relational algebra, which laid the groundwork for set-based operations.  That Oracle long ago chose to promote multi-row access via cursors is like a 70's time warp, and an architectural deficiency that will live with the product for many years to come.  Frankly I do not care if Oracle cursors are blazingly fast.  They require significantly more code, and eventually any performance gains will be eaten away by improvements in query optimization.



  • @Mr Pleasant said:

    @ChrisRLong said:

    PL/SQL blows the pants off T-SQL as a start

    Interesting take on the situation.  Although I find PL/SQL to have some great features, such as the modularity provided by packages, it really rubs me the wrong way that it forces one to use cursors (row-based processing) for procedural logic.  One of the great technical advancements of relational databases is relational algebra, which laid the groundwork for set-based operations.  That Oracle long ago chose to promote multi-row access via cursors is like a 70's time warp, and an architectural deficiency that will live with the product for many years to come.  Frankly I do not care if Oracle cursors are blazingly fast.  They require significantly more code, and eventually any performance gains will be eaten away by improvements in query optimization.



    I can't follow your thoughts, maybe I'm brainwashed. A cursor loop like
    for r in (select * from blablubb) loop
      do_something(r);
    end loop;
    does not require lots of code, and if you don't want a loop at all, "fetch bulk collect" is your friend.


  • @Mr Pleasant said:

    @ChrisRLong said:

    PL/SQL blows the pants off T-SQL as a start

    Interesting take on the situation.  Although I find PL/SQL to have some great features, such as the modularity provided by packages, it really rubs me the wrong way that it forces one to use cursors (row-based processing) for procedural logic.  One of the great technical advancements of relational databases is relational algebra, which laid the groundwork for set-based operations.  That Oracle long ago chose to promote multi-row access via cursors is like a 70's time warp, and an architectural deficiency that will live with the product for many years to come.  Frankly I do not care if Oracle cursors are blazingly fast.  They require significantly more code, and eventually any performance gains will be eaten away by improvements in query optimization.


    I don't get this comment at all - one of the main purposes of stored procedures is row-based processing, and in this respect Oracle is no different that other vendors. However, there is no requirement to use row-based logic in PL/SQL; you can move freely between SQL and cursors. In fact, it is axiomatic in the Oracle world, for performance reasons, to try SQL first and cursors last.


  • @RyuO said:

    ...one of the main purposes of stored procedures is row-based processing, and in this respect Oracle is no different that other vendors. However, there is no requirement to use row-based logic in PL/SQL; you can move freely between SQL and cursors. In fact, it is axiomatic in the Oracle world, for performance reasons, to try SQL first and cursors last.


     

    Although I have been progressively avoiding Oracle systems in general (for a myriad of reasons) over the past few years, I totally agree here to...and of course, the preference of a declarative solution over procedural is not exclusive to Mr. Ellison's empire -- it should apply to any decent DBMS.

     

    Also, I am somewhat confused concerning the claimed advantage of PL/SQL over T-SQL -- although significantly different language implementations, aren't they pretty much equivilent in purpose and capabilities? I ask due to my  large [:D][st]ignorance on T-SQL's syntax and methods.



  • @Element said:

    Also, I am somewhat confused concerning the claimed advantage of PL/SQL over T-SQL -- although significantly different language implementations, aren't they pretty much equivilent in purpose and capabilities? I ask due to my  large [:D][st]ignorance on T-SQL's syntax and methods.



    No, they are not equivalent. Not at all. Worlds apart.



  • @ammoQ said:

    No, they are not equivalent. Not at all. Worlds apart.


     

    My question was obviously qualifed..."equivilent in *purpose and capabilities*". I have already assumed that there is a myriad of other differences.

     

    Again, since I am not versed in T-SQL, a couple high-level example comparisons would be helpful, as opposed to a quick blanket statement.

     



  • @Element said:

    @ammoQ said:

    No, they are not equivalent. Not at all. Worlds apart.

    My question was obviously qualifed..."equivilent in *purpose and capabilities*". I have already assumed that there is a myriad of other differences.

    Again, since I am not versed in T-SQL, a couple high-level example comparisons would be helpful, as opposed to a quick blanket statement.



    PL/SQL has packages. These are modules that can include several functions and procedures, as well as variables, which live till the end of the session. Variables, functions and procedures can be private (only visible for the package) or public.
    (Compared with Java, packages are like final classes with static methods and fields only). AFAIK, TSQL has no comparable concept.

    The controls structures (loops, exception handling etc.) are more comprehensive in PL/SQL. It doesn't matter for small procedures, but for larger programs, it wouldn't want to miss it.

    PL/SQL knows complex data stuctures, like records, arrays and hash tables. AFAIK, TSQL has nothing like that.

    The Oracle database system provides packages which give PL/SQL programs access to file operations, networking, interprocess communication etc.



  • @ammoQ said:

    @Element said:

    @ammoQ said:

    No, they are not equivalent. Not at all. Worlds apart.

    My question was obviously qualifed..."equivilent in *purpose and capabilities*". I have already assumed that there is a myriad of other differences.

    Again, since I am not versed in T-SQL, a couple high-level example comparisons would be helpful, as opposed to a quick blanket statement.



    PL/SQL has packages. These are modules that can include several functions and procedures, as well as variables, which live till the end of the session. Variables, functions and procedures can be private (only visible for the package) or public.
    (Compared with Java, packages are like final classes with static methods and fields only). AFAIK, TSQL has no comparable concept.

    The controls structures (loops, exception handling etc.) are more comprehensive in PL/SQL. It doesn't matter for small procedures, but for larger programs, it wouldn't want to miss it.

    PL/SQL knows complex data stuctures, like records, arrays and hash tables. AFAIK, TSQL has nothing like that.

    The Oracle database system provides packages which give PL/SQL programs access to file operations, networking, interprocess communication etc.


    Well said. One might add that the Ada family, which includes PL/SQL, has the best track record for building large-scale systems. Here is a famous quote from C++ guru PJ Plauger: "Beyond 100,000 lines of code you should probably be coding in Ada".

    Ada lost out to C++ and its successors not because the latter were superior, but because it took forever for Ada to develop UI libraries. Nowadays Ada can use Windows and GTK libraries, so it can cover the same space as Java and C#. I'd have no qualms about building an All-Ada system - PL/SQL on the server and Ada on the front end and middle tier.

    A theme running through arguments comparing different programming languages is that people  don't like to work in multiple languages at the same time, e.g., Java is not a complete solution because it is OK on the middle tier, sucks on the server, and has impedance mismatches with SQL. Well, here is a solution that pares the languages down to two: SQL and Ada(-like).



  • @RyuO said:

    Well said. One might add that the Ada family, which includes PL/SQL, has the best track record for building large-scale systems. Here is a famous quote from C++ guru PJ Plauger: "Beyond 100,000 lines of code you should probably be coding in Ada".

    I totally agree with the whole Ada deal -- so then what other language would one compare to T-SQL?

    I have seen some code examples, but that doesn't always tell the whole story.

     



  • @Element said:

    @RyuO said:

    Well said. One might add that the Ada family, which includes PL/SQL, has the best track record for building large-scale systems. Here is a famous quote from C++ guru PJ Plauger: "Beyond 100,000 lines of code you should probably be coding in Ada".

    I totally agree with the whole Ada deal -- so then what other language would one compare to T-SQL?

    I have seen some code examples, but that doesn't always tell the whole story.

     

    I've only done enough T-SQL to be dangerous, but to me it resembles Visual Basic in both style and substance. Nothing like packages, of course, and it seems to go out of its way to encourage the embedding of UI code. It was designed for Sysbase's original market space, that is, applications as opposed to systems. I'd be afraid to use it in the implementation of a relational database, but it ought to be adequate for a Java-style object store, i.e., where the tables look like screens.



  • @RyuO said:

    I've only done enough T-SQL to be dangerous, but to me it resembles Visual Basic in both style and substance. Nothing like packages, of course, and it seems to go out of its way to encourage the embedding of UI code. It was designed for Sysbase's original market space, that is, applications as opposed to systems. I'd be afraid to use it in the implementation of a relational database, but it ought to be adequate for a Java-style object store, i.e., where the tables look like screens.



    "Basic" was my first thought, too, but not the VB we know today (VB6 or VB.net), but rather the "good old" qbasic included in DOS.



  • Thanks to all for your feedback.  My comments about row vs. set processing are based on PL/SQL's inability to return multi-row records without going through contortions to place the results in a table of records via SELECT INTO or BULK COLLECT, and an equally complicated method for retrieving the results via a simple command line tools like sqlplus.

    Perhaps one of you PL/SQL experts can show how to return more than a single record within a PL/SQL block.  

    >1  begin
      2  select * from v$instance;
      3  end;

    ERROR at line 2:
    ORA-06550: line 2, column 1:
    PLS-00428: an INTO clause is expected in this SELECT statement

     

     



  • @Mr Pleasant said:

    Thanks to all for your feedback.  My comments about row vs. set processing are based on PL/SQL's inability to return multi-row records without going through contortions to place the results in a table of records via SELECT INTO or BULK COLLECT, and an equally complicated method for retrieving the results via a simple command line tools like sqlplus.

    Perhaps one of you PL/SQL experts can show how to return more than a single record within a PL/SQL block.  

    >1  begin
      2  select * from v$instance;
      3  end;

    ERROR at line 2:
    ORA-06550: line 2, column 1:
    PLS-00428: an INTO clause is expected in this SELECT statement


    <font size="2">SQL> -- easy enough in SQL*Plus:
    SQL> SET AUTOPRINT ON
    SQL> VARIABLE c REFCURSOR
    SQL> begin
      2    open :c for select * from v$instance;
      3  end;
      4  /

    PL/SQL procedure successfully completed.


    INSTANCE_NUMBER INSTANCE_NAME
    --------------- ----------------
    HOST_NAME
    -----------------
    VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_
    ---------------- --------- ------------ --- ---------- ------- -----------
    LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO EDITION
    ---------- --- ----------------- ------------------ --------- --- -------
                  1 xe
    INCAPACITOR
    10.2.0.1.0        26-APR-06 OPEN         NO           1 STOPPED
    ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO  XE


    SQL>
    SQL> -- but let's try it with more than one row:
    SQL> SET AUTOPRINT ON
    SQL> VARIABLE c REFCURSOR
    SQL> begin
      2     open :c for
      3        select pid, program from v$process
      4        where pid<10;
      5  end;
      6  /

    PL/SQL procedure successfully completed.


           PID PROGRAM
    ---------- -------------------------
             1 PSEUDO
             2 ORACLE.EXE (PMON)
             3 ORACLE.EXE (PSP0)
             4 ORACLE.EXE (MMAN)
             5 ORACLE.EXE (DBW0)
             6 ORACLE.EXE (LGWR)
             7 ORACLE.EXE (CKPT)
             8 ORACLE.EXE (SMON)
             9 ORACLE.EXE (RECO)

    9 rows selected.

    SQL></font>

    That's SQL*Plus, though, which is on its way out. In normal code, most likely you want a stored procedure that returns an open cursor through a REF CURSOR out parameter, and let some front end tool display it.



  • @Mr Pleasant said:

    Thanks to all for your feedback.  My comments about row vs. set processing are based on PL/SQL's inability to return multi-row records without going through contortions to place the results in a table of records via SELECT INTO or BULK COLLECT, and an equally complicated method for retrieving the results via a simple command line tools like sqlplus.



    The thing to consider is that a cursor is the equivalent of an iterator e.g. in Java. Instead of returning (copies of) large result sets (we are talking about Oracle databases, you don't use that for your moms recipe collection), you get a pointer to the results, which stay in the database. In many cases, this is preferable.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.