Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Column Name
Dynamic Column Name [message #280457] Tue, 13 November 2007 13:11 Go to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
8.1.7.0.0 - test
9i - production

What is the preferred method to dynamically declare which column to select from within a pl/sql block? For example:

CREATE OR REPLACE PROCEDURE test(
				v_repaircur OUT myVars.CommonCurTyp
                      			)
IS
BEGIN
     OPEN v_repaircur FOR
	SELECT (*variable_here*).ordernum FROM repair_detail;    
END;
/



The two methods I am currently looking at are dynamic sql or simply breaking out the select statement with an if/then clause. Speed isn't as large of a concern as reliability and functionality when used on newer versions of oracle. Thanks in advance-

[Updated on: Wed, 14 November 2007 12:10]

Report message to a moderator

Re: Dynamic Row Name [message #280459 is a reply to message #280457] Tue, 13 November 2007 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a bad design.
Quote:

Speed isn't as large of a concern as ...

You say that because you are alone on your test database with few data.
This will not be the case in a real environment when you'll start to kill the performances.

Regards
Michel
Re: Dynamic Row Name [message #280468 is a reply to message #280459] Tue, 13 November 2007 14:57 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
Yes, I agree Michel, I hadn't thought that far ahead I suppose. Performance/speed should always be a top concern. Given this new requirement, can anyone recommend the best method to achieve my original post?

I've been through several pages and haven't really seen anything conclusive as to which route to take. Thanks again-

[Updated on: Tue, 13 November 2007 15:14]

Report message to a moderator

Re: Dynamic Row Name [message #280482 is a reply to message #280457] Tue, 13 November 2007 17:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:


8.1.7.0.0 - test
9i - production


Why would you have different environments for test and prod?
Other than that, I'd say probably dynamic sql, but you haven't provide much info
Re: Dynamic Row Name [message #280545 is a reply to message #280468] Wed, 14 November 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

anyone recommend the best method to achieve my original post?

What is the need?

Regards
Michel
Re: Dynamic Row Name [message #280688 is a reply to message #280545] Wed, 14 November 2007 09:18 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
Quote:

Why would you have different environments for test and prod?

I have no control over this-

My question is which of the following is the preferred method for using a dynamic column name in term of performance and maintainability:

CREATE OR REPLACE PROCEDURE jtest(
				v_repaircur OUT myVars.CommonCurTyp,
                                v_flag      IN  char
                    			)
IS
begin
declare 
	v_name varchar2(20); 
	BEGIN
  	if v_flag='O' then
  	   v_name:='ordernum';
  	else
  	   v_name:='partnum';
  	end if;
        OPEN v_repaircur FOR ('SELECT ' ||  v_name || ' FROM TO_BE_SHIPPED');    
        END;
END jtest;
/

CREATE OR REPLACE PROCEDURE jtest(
				v_repaircur OUT myVars.CommonCurTyp,
                                v_flag      IN  char
                    			)
IS
begin
declare 
	v_name varchar2(20); 
	BEGIN
  	if v_flag='O' then
  	    OPEN v_repaircur FOR SELECT ordernum FROM TO_BE_SHIPPED; 
  	else
  	    OPEN v_repaircur FOR SELECT partnum FROM TO_BE_SHIPPED; 
  	end if;  
        END;
END jtest;
/


Also, how would I calculate the time spent executing each of these procedures?
Re: Dynamic Row Name [message #280694 is a reply to message #280688] Wed, 14 November 2007 09:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have found the second approach to use less CPU time than the first.

As a brute force approach to timing, write some code that will call each one 10,000 times or so, and use DBMS_UTILITY.GET_TIME (you're on 8.1.7, so I don't think you get the fraction seconds accuracy of TimeStamps) to record the time before and after each run.
Previous Topic: Silly question about PL/SQL queries
Next Topic: Exception Handling problem
Goto Forum:
  


Current Time: Tue Dec 06 06:42:23 CST 2016

Total time taken to generate the page: 0.07986 seconds