Wednesday, November 25, 2009

Oracle VS SQL debug

Can you spot the difference?

MS SQL

    SELECT PATNT_REFNO, PATAL_REFNO
    FROM TMP_PATSEARCH_FPATID

Oracle

    DBMS_OUTPUT.PUT_LINE('PATNT_REFNO'
            || CHR(9) || 'PATAL_REFNO');
    FOR c1 IN ( SELECT PATNT_REFNO, PATAL_REFNO
                FROM TMP_PATSEARCH_FPATID )
    LOOP
        DBMS_OUTPUT.PUT_LINE( TO_CHAR(c1.PATNT_REFNO)
                 || CHR(9) || TO_CHAR(c1.PATAL_REFNO) );
    END LOOP;

5 comments:

Simeon Pilgrim said...

Are you saying that Oracle is bad because to display query results you have to print them where MS SQL prints all results by default? Because the later sounds very under performant.

Now if your saying something different like when I using the correct MS SQL development tools I can see the result of my queries, where-as when I do stuff wrong in Oracle I have to jump lots of hoops, then I puzzled why you don't just use the correct tools.

Mr Dee said...

I am saying that
1) an Oracle SPROC returns a list of records inside a SYS_REFCURSOR.
using the free tools that come from oracle there is no easy way to interrogate this object to see what is returned.
2) an MS/SQL SPROC returns a list of records using the free tools that come from MS I see the object immediately (rows/ columns nicely aligned). If I want to see the intermediate results all I have to do is add a simple SELECT or PRINT statement. This is developer friendly while still managing to be performant.

If you have a suggestion for a better Oracle tool **please** let me know (and don't say TOAD, I am sick of the monthly NAG/DISABLE/DOWNLOAD cycle)

Simeon Pilgrim said...

SQL Developer:
http://www.oracle.com/technology/products/database/sql_developer/index.html
Is what I mainly use.

Mr Dee said...

Ditto - if you have a simple way to interrogate SYS_REFCURSORs returned from a SPROC I am all ears!

Mr Dee said...

That is what I thought

Post a Comment