Home » SQL & PL/SQL » SQL & PL/SQL » What is the best (most efficient) way to process complex Select Queries? (Oracle 11g, Vista)
What is the best (most efficient) way to process complex Select Queries? [message #436452] Wed, 23 December 2009 12:12 Go to next message
Donovan2008
Messages: 4
Registered: December 2009
Location: Saint Louis
Junior Member
GOAL: Find the best way to handle SELECT Queries using VB.NET thru an oracle database.

We are currently going thru a conversion from COLDFUSION to ASP.NET (using VB). I'm trying to figure the best way to retrieve data from ORACLE. The ideas tossed around are Procedures, functions, packages, views, and ref cursors.

Also, I'm confused the differences between procedures and functions. And I'm unsure how to call them to VB.net with the parameters.

I was able to get a SELECT procedure to work but I don't know how to pass the parameters to the code. I'm unsure if I'm even doing it correctly.

Please help.
Example code:

create or replace
PROCEDURE proc_q_intf_test
IS
BEGIN
for x in (SELECT intf_tm_frqcy_qty, intf_tm_frqcy_ntvl,
(CASE
WHEN intf_tm_frqcy_qty > 0
AND intf_tm_frqcy_ntvl IS NOT NULL
THEN RTRIM(LTRIM(intf_tm_frqcy_qty))
|| '/'
|| RTRIM(LTRIM(intf_tm_frqcy_ntvl))
WHEN TO_CHAR(intf_tm_frqcy_qty) = 0
AND intf_tm_frqcy_ntvl IS NOT NULL
THEN intf_tm_frqcy_ntvl
WHEN TO_CHAR(intf_tm_frqcy_qty) IS NULL
AND intf_tm_frqcy_ntvl IS NOT NULL
THEN intf_tm_frqcy_ntvl
ELSE 'The owner is another value'
END
) CONCAT
FROM Intf)
loop
dbms_output.put_line(x.intf_tm_frqcy_qty || ' '||x.intf_tm_frqcy_ntvl || ' '|| x.CONCAT);
end loop;
Re: What is the best (most efficient) way to process complex Select Queries? [message #436454 is a reply to message #436452] Wed, 23 December 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

You should start by only posting code that contains correct syntax.


(SELECT intf_tm_frqcy_qty,
        intf_tm_frqcy_ntvl,
        (CASE
           WHEN intf_tm_frqcy_qty > 0
                AND intf_tm_frqcy_ntvl IS NOT NULL
           THEN Rtrim(Ltrim(intf_tm_frqcy_qty))
                ||'/'
                ||Rtrim(Ltrim(intf_tm_frqcy_ntvl))
           WHEN To_char(intf_tm_frqcy_qty) = 0
                AND intf_tm_frqcy_ntvl IS NOT NULL
           THEN intf_tm_frqcy_ntvl
           WHEN To_char(intf_tm_frqcy_qty) IS NULL
                AND intf_tm_frqcy_ntvl IS NOT NULL
           THEN intf_tm_frqcy_ntvl
           ELSE 'The owner is another value'
         END) concat
 FROM   intf) 

[Updated on: Wed, 23 December 2009 12:27]

Report message to a moderator

Re: What is the best (most efficient) way to process complex Select Queries? [message #436461 is a reply to message #436454] Wed, 23 December 2009 12:54 Go to previous messageGo to next message
Donovan2008
Messages: 4
Registered: December 2009
Location: Saint Louis
Junior Member
Sorry, I'm new to this forum.

CREATE OR REPLACE PROCEDURE proc_q_intf_test
IS
BEGIN
for x in (SELECT intf_tm_frqcy_qty,
intf_tm_frqcy_ntvl,
(CASE
WHEN intf_tm_frqcy_qty > 0
AND intf_tm_frqcy_ntvl IS NOT NULL
THEN Rtrim(Ltrim(intf_tm_frqcy_qty))
||'/'
||Rtrim(Ltrim(intf_tm_frqcy_ntvl))
WHEN To_char(intf_tm_frqcy_qty) = 0
AND intf_tm_frqcy_ntvl IS NOT NULL
THEN intf_tm_frqcy_ntvl
WHEN To_char(intf_tm_frqcy_qty) IS NULL
AND intf_tm_frqcy_ntvl IS NOT NULL
THEN intf_tm_frqcy_ntvl
ELSE 'The owner is another value'
END) concat
FROM intf)
LOOP
dbms_output.put_line(x.intf_tm_frqcy_qty || ' '||x.intf_tm_frqcy_ntvl || ' '|| x.CONCAT);
END LOOP;
Re: What is the best (most efficient) way to process complex Select Queries? [message #436462 is a reply to message #436461] Wed, 23 December 2009 12:56 Go to previous messageGo to next message
Donovan2008
Messages: 4
Registered: December 2009
Location: Saint Louis
Junior Member
I still can't get the format correctly.

All I want is some advice on the below problem.

GOAL: Find the best way to handle SELECT Queries using VB.NET thru an oracle database.

We are currently going thru a conversion from COLDFUSION to ASP.NET (using VB). I'm trying to figure the best way to retrieve data from ORACLE. The ideas tossed around are Procedures, functions, packages, views, and ref cursors.

Also, I'm confused the differences between procedures and functions. And I'm unsure how to call them to VB.net with the parameters.

I was able to get a SELECT procedure to work but I don't know how to pass the parameters to the code. I'm unsure if I'm even doing it correctly.

Please help.
Re: What is the best (most efficient) way to process complex Select Queries? [message #436463 is a reply to message #436452] Wed, 23 December 2009 12:57 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Donovan2008 wrote on Wed, 23 December 2009 19:12
GOAL: Find the best way to handle SELECT Queries using VB.NET thru an oracle database.

We are currently going thru a conversion from COLDFUSION to ASP.NET (using VB). I'm trying to figure the best way to retrieve data from ORACLE. The ideas tossed around are Procedures, functions, packages, views, and ref cursors.

Yes, these are objects used for handling SQL on Oracle side.
Donovan2008 wrote on Wed, 23 December 2009 19:12
Also, I'm confused the differences between procedures and functions.

The difference is the same as in other languages - functions return a value, procedures does not. It is possible to return a value in an OUT parameter in both of them.
Donovan2008 wrote on Wed, 23 December 2009 19:12
And I'm unsure how to call them to VB.net with the parameters.
I was able to get a SELECT procedure to work but I don't know how to pass the parameters to the code. I'm unsure if I'm even doing it correctly.

The code posted tries to write the content of SELECT using DBMS_OUTPUT package. This will not return anything. One of the correct ways of coding this on Oracle side is described here: http://www.orafaq.com/wiki/REF_CURSOR Alternatively you may create a procedure having the SYS_REFCURSOR variable as OUT parameter - it depends on your liking and/or coding standards.
Calling it from VB.net and binding parameters - this is probably question for VB side; I have never worked with it.
Re: What is the best (most efficient) way to process complex Select Queries? [message #436464 is a reply to message #436462] Wed, 23 December 2009 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE PROCEDURE Proc_q_intf_test
IS
BEGIN
  FOR x IN (SELECT intf_tm_frqcy_qty,
                   intf_tm_frqcy_ntvl,
                   (CASE
                      WHEN intf_tm_frqcy_qty > 0
                           AND intf_tm_frqcy_ntvl IS NOT NULL
                      THEN Rtrim(Ltrim(intf_tm_frqcy_qty))
                           ||'/'
                           ||Rtrim(Ltrim(intf_tm_frqcy_ntvl))
                      WHEN To_char(intf_tm_frqcy_qty) = 0
                           AND intf_tm_frqcy_ntvl IS NOT NULL
                      THEN intf_tm_frqcy_ntvl
                      WHEN To_char(intf_tm_frqcy_qty) IS NULL
                           AND intf_tm_frqcy_ntvl IS NOT NULL
                      THEN intf_tm_frqcy_ntvl
                      ELSE 'The owner is another value'
                    END) concat
            FROM   intf)
  LOOP
    dbms_output.Put_line(x.intf_tm_frqcy_qty
                         ||' '
                         ||x.intf_tm_frqcy_ntvl
                         ||' '
                         ||x.concat);
  END LOOP;
END; 

http://www.orafaq.com/forum/t/88153/0/ - shows how to Format
Re: What is the best (most efficient) way to process complex Select Queries? [message #436466 is a reply to message #436463] Wed, 23 December 2009 13:18 Go to previous messageGo to next message
Donovan2008
Messages: 4
Registered: December 2009
Location: Saint Louis
Junior Member
Do you have more examples of the ref cursors? I'm still a little confused on the parameters.

I would like something a little more complex.
Re: What is the best (most efficient) way to process complex Select Queries? [message #436467 is a reply to message #436466] Wed, 23 December 2009 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Donovan2008 wrote on Wed, 23 December 2009 11:18
Do you have more examples of the ref cursors? I'm still a little confused on the parameters.

I would like something a little more complex.


You are supposed to SEARCH before posting FAQ!

http://www.orafaq.com/node/1303
Re: What is the best (most efficient) way to process complex Select Queries? [message #436484 is a reply to message #436466] Wed, 23 December 2009 15:41 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Donovan2008 wrote on Wed, 23 December 2009 14:18

I would like something a little more complex.


Do you mean less complex?
Previous Topic: Getting one line from table per person
Next Topic: Ignore ORA-01427: single-row subquery returns more than one row
Goto Forum:
  


Current Time: Wed Dec 07 23:56:40 CST 2016

Total time taken to generate the page: 0.14137 seconds