Home » SQL & PL/SQL » SQL & PL/SQL » Need opinion in using TO_CHAR  () 1 Vote
Need opinion in using TO_CHAR [message #424771] Mon, 05 October 2009 23:37 Go to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi All,

I have written a stored procedure which should display the records from the table based on the input parameters and if the i/p params are null then all the records from the table should be displayed and will be called from DOT NET script and they need the o/p as a result/record set...

Code using TO_CHAR:
CREATE OR REPLACE PROCEDURE Emp_Sp (		
		In_empid			Emp.eno%TYPE,
		In_ename			Emp.name%TYPE,
                In_MgrId                        Emp.MgrId%TYPE,
		ret_cursor		OUT SYS_REFCURSOR) 
IS  	
BEGIN
    
      OPEN ret_cursor FOR 
       	SELECT eno,
			 name,
               MgrId,
			 CreatedBy,
			 CreatedDate,
			 UpdatedBy,
			 UpdatedDate
		 FROM	Emp		
		WHERE eno LIKE 	NVL(TO_CHAR(In_empid),'%') 
              AND  NVL(mgrid,' ') LIKE NVL(TO_CHAR(In_MgrId),'%')
	    AND  NVL(name,' ') LIKE NVL(l_ename,'%');		
		

END Emp_Sp ;
/


Code with local variables:

CREATE OR REPLACE PROCEDURE Emp_Sp (		
		In_empid			Emp.eno%TYPE,
		In_ename			Emp.name%TYPE,
                In_MgrId                        Emp.MgrId%TYPE,
		ret_cursor		OUT SYS_REFCURSOR) 
IS  	
           l_empid      VARCHAR2(20);
           l_MgrId      VARCHAR2(20);
BEGIN
           l_empid := In_empId;
           l_MgrId := In_MgrId;
     
      OPEN ret_cursor FOR 
       	SELECT eno,
			 name,
               MgrId,
			 CreatedBy,
			 CreatedDate,
			 UpdatedBy,
			 UpdatedDate
		 FROM	Emp		
		WHERE eno LIKE 	NVL(l_empid,'%') 
              AND  NVL(mgrid,' ') LIKE NVL(l_MgrId,'%')
	    AND  NVL(name,' ') LIKE NVL(l_ename,'%');		
		

END Emp_Sp ;
/


I am converting the columns with datatypes other than VARCHAR2 using TO_CHAR function as I can not use them in NVL().
Please need you opinion whether it is good using TO_CHAR() OR declaring local variables with VARCHAR2 datatype and assigning them to the i/p paramters and using the local variables in where condition as in above code.Which is better way pf approach in performance improvement.

Any suggestions will be helpful.

Thanks,
Hemanth
Re: Need opinion in using TO_CHAR [message #424775 is a reply to message #424771] Mon, 05 October 2009 23:50 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Asktom
Re: Need opinion in using TO_CHAR [message #424780 is a reply to message #424771] Tue, 06 October 2009 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A little search would give you the same topic last week.
Please search BEFORE posting.

Regards
Michel
Re: Need opinion in using TO_CHAR [message #424865 is a reply to message #424771] Tue, 06 October 2009 05:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is a classic opportunity for us as a forum to offer additional suggestions to aid you. I speculate that you have not been doing PLSQL coding for a long time because your procedure is a procedure and not a packaged procedure. Even if you are experienced and you simply are offering a slimmed down version of your question for ease of consumption (which is good), others may benefit from a broader discussion of what you are doing so here goes.

First, you should really try to use packages for things. Packages have at least two advantages over standalone code

1) the offer a division of specification and implementation which have ramifications on the underlying mechanics of the database and various efficiencies therein. Most notably is the invalidation aspects of making a change to your procedure. If you change the implementation of your procedures and by this I mean the logic inside it, you will have to recompile it. A recompile of the procedure will cause any objects that rely on it to be marked as invalid. Of course this is a recursive process of sorts such that for every object marked invalid, any object that relies on that object will also be marked as invalid. Thus a recompile for whatever reason to a standalone procedure or function can cause a cascading set of invalidations across your database. Depending upon your version of Oracle and the Software products you use, this may be more or less of a problem. Oracle will automatically recompile invalid objects and/or otherwise do what is necessary to make them valid then next time you try to use them and so code usually continues to work. But this costs time and is a general burden we try not to place on an active database.

A packaged procedure however does not suffer so much from this problem. If you have a procedure or function in a package and you do a change to the package logic and by this I mean a change to the package body only, then you only need to recompile the package body, not the package specification. Since the package specification is where dependencies are hooked, the fact that you did not have to recompile the package specification means that the specification stays untouched, which in turn means all references to the package specification remain valid. No cascading invalidation of objects occurs. Only when you recompile the specification does the chained invalidation happen.

2) more importantly however is that a package offers a way to group code together for management purposes. There is a distinct advantage to having all code related to a particular API for example, contained in one package (or a set of them). It is easier to research problems with the code, and easier to get and understanding of what the system does in general because it is not necessary to jump around between objects when trying to figure something out, if all the code you want is in the same object. Consider the example of a developer researching a bug using the every popular TOAD (or any over similar tool). TOAD will show you the package code and you can easily move around in the code body to see what a called routines looks like. However, if all your code was in standalone procedures then it becomes a nightmare because each time you want to see the logic in a called procedure, you have to navigate to a different code object. It makes researching a problem an order of magnitude harder. So always put your code in a package somewhere.

There are other reasons for using packages as well. Recompiling objects in the right order becomes easier when you are using packages for code. Your DBAs will like this because their delivery scripts will contain no errors because with packages it is usually possible to compile package specifications first. Additionally, using packages lets you create STUB code more readily for your .NET developers. Once you agree on an API specification, you can write your initial package to include a body that contains stubs. In your example you could create a DUMMY SELECT that returns one row. This allows your developers to get started right away on their development because they can interact with the database as soon as you deliver the STUB code. They do not have to wait for you to figure out all the details of the refcursors etc. before they can begin work. In a large or complex front end, this can mean anything for five days to five weeks head start for them which goes a long way toward making deadlines.

Secondly, when you are writing a PLSQL API and your code example suggest you may be doing that, you should consider creating two versions of everything, a function version and a procedure version. Naturally you would want to reuse code inside your own package, so you would create one working component and then wrap it with the other. Offering a function based version that returns your refcursors gives you a very distinct upper hand, it means you can readily test your work better. This should not be underestimated in value. Testing is not something we do real well in IT so any time we can adopt a strategy that aids us to test better, faster, and more completely, it is a good thing. Let me take your code and show you what I mean.

create or replace package pkg_Hemanth123 is

   function Emp_Sp (
                       In_empid         Emp.eno%TYPE,
                       In_ename         Emp.name%TYPE,
                       In_MgrId         Emp.MgrId%TYPE,
                   ) return sys_refcursor;

   procedure Emp_Sp (
                       In_empid         Emp.eno%TYPE,
                       In_ename         Emp.name%TYPE,
                       In_MgrId         Emp.MgrId%TYPE,
                       ret_cursor   OUT SYS_REFCURSOR) 
                    );

end;
/
show errors

create or replace package body pkg_Hemanth123 is

   function Emp_Sp (
                       In_empid         Emp.eno%TYPE,
                       In_ename         Emp.name%TYPE,
                       In_MgrId         Emp.MgrId%TYPE,
                   ) return sys_refcursor is
      rc1 sys_refcursor;
      l_empid      VARCHAR2(20);
      l_MgrId      VARCHAR2(20);
   begin
      l_empid := In_empId;
      l_MgrId := In_MgrId;

      OPEN rc1 FOR 
          SELECT eno,
                 name,
                 MgrId,
                 CreatedBy,
                 CreatedDate,
                 UpdatedBy,
                 UpdatedDate
          FROM   Emp      
          WHERE eno LIKE NVL(l_empid,'%') 
          AND   NVL(mgrid,' ') LIKE NVL(l_MgrId,'%')
          AND   NVL(name,' ') LIKE NVL(l_ename,'%');

      return (rc1);
   end;

   procedure Emp_Sp (
                       In_empid         Emp.eno%TYPE,
                       In_ename         Emp.name%TYPE,
                       In_MgrId         Emp.MgrId%TYPE,
                       ret_cursor   OUT SYS_REFCURSOR) 
                    ) is
   begin
      ret_cursor := emp_sp(in_empid,in_ename,in_mgrid);
   end;

end;
/
show errors

You will please notice three things about the above code:

1) a package is used
2) there are two versions of the same thing: your original procedure, and a function overload
3) I have placed your original logic in the function and then made the procedure call the function, no duplicate code

So you may ask why bother. Well as I stated before, TESTING. Once you have your function coded, it is much easier to test. Consider that you can do this:

select pkg_Hemanth123.emp_sp(1,2,3) thedata from dual;

You can easily execute this select statement in SQLPLUS and it will execute your function and return the refcursor which SQLPLUS will readily display for you. You can see its results right away. This gives you an simple first line check that what you are doing is correct. It is this ability to immediately get feedback on your work that makes this practice so valuable. It also makes it easier to prove to someone else that the problem is not in your code when things go wrong because if they supply the inputs, you can dump the outputs just as readily.

Of course, some might ask, if you have the function why have the procedure? The answer is in ease of use. .NET and other similar tools have difficulty in returning results from fuctions. As such most developers these days want or need calls to be procedures that return OUT or IN OUT parameters. So you create the procedure overload for those who want to work with the API using procedures, you create the function overload for those who want to work with the API using functions (you being one of the ones that likes functions).

So that is about it for now. Other comments are welcome.

Kevin

[Updated on: Tue, 06 October 2009 05:35]

Report message to a moderator

Re: Need opinion in using TO_CHAR [message #425055 is a reply to message #424865] Wed, 07 October 2009 05:56 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
@Kevin

Thank you very much for your valuable inputs.
Yeah..I have been using Package in which I have declared the below procedure(hence provided only the procedure).

And regarding the query I have asked for...please let me know is it advisable to use the function and then calling the function from the stored procedure in terms of performance.

If so I will implement the same as the earlier code is taking longer time as expected.

Once again many thanks for your suggestions.

Thanks,
Hemanth

Re: Need opinion in using TO_CHAR [message #425057 is a reply to message #424771] Wed, 07 October 2009 06:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks for the chance to let others who read this post see the comments.

See everyone how Hemanth123 took the time to create a minimual example that shows what he was talking about. This usually helps others figure out a problem because there is less code to muddle through.

Kudos Hemanth123.

I am not really sure what your last question is asking. Let me point this out...

When you issue this statement.

      OPEN ret_cursor FOR 
       	SELECT eno,
			 name,
               MgrId,
			 CreatedBy,
			 CreatedDate,
			 UpdatedBy,
			 UpdatedDate
		 FROM	Emp		
		WHERE eno LIKE 	NVL(l_empid,'%') 
              AND  NVL(mgrid,' ') LIKE NVL(l_MgrId,'%')
	    AND  NVL(name,' ') LIKE NVL(l_ename,'%');		

Oracle will execute something similar to this statement:

      OPEN ret_cursor FOR 
       	SELECT eno,
			 name,
               MgrId,
			 CreatedBy,
			 CreatedDate,
			 UpdatedBy,
			 UpdatedDate
		 FROM	Emp		
		WHERE eno LIKE 	R NVL(TO_CHAR(l_empid),'%')
              AND  NVL(mgrid,' ') LIKE NVL(TO_CHAR(l_MgrId,'%'))
	    AND  NVL(name,' ') LIKE NVL(TO_CHAR(l_ename,'%'));

Which as you can see is the same as what you supplied in your other query. Oracle must convert the parameter/variable in the NVL expression to a string before it can use it because the DEFUALT value of the NVL expression is a string. So there is no difference between the two statements in your examples.

Kevin

[Updated on: Wed, 07 October 2009 06:06]

Report message to a moderator

Re: Need opinion in using TO_CHAR [message #425066 is a reply to message #425057] Wed, 07 October 2009 06:34 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
@Kevin

Please clarify me the below points:

1) Regarding performance which is better converting the passed i/p to string using TO_CHAR directly or declaring the local variables and assigning the same to i/p params and using them in the where clause.
2) you have suggested using function and calling it from procedure.
So regarding the better performance which is better..having only stored procedure in package which has the main query or main query being declared in a function and calling it from procedure(in package).

Kindly let me know the better approach regarding the same.

Thanks,
hemanth
Re: Need opinion in using TO_CHAR [message #425070 is a reply to message #424771] Wed, 07 October 2009 06:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The WHERE claues should always contain explicit data type conversions unless you have a very unusual situation which I have never seen so pass the data as the data is and put the TO_CHAR inside the sql.

As for the function.

execute p1 will be a couple if milliseconds faster than execute f1 in which f1 does execute p1. Of course this should be obvious, you have an additional four lines of code to execute.

However the real focus on the use of the function should be because it supports easier testing. Any additional overhead from using the extra four lines of code and making another call are more than outweighed by your ability to test your embedded sql independently of your process.

You will never notice any performance difference between using the function or not and the benefit you get if huge for using it to test your sql code and refcursor content.

Kevin
Re: Need opinion in using TO_CHAR [message #425077 is a reply to message #424771] Wed, 07 October 2009 07:12 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi Kevin,

What if we have more than 5 columns(to the max 10 columns) which needs to be explicitly convert to string using TO_CHAR?
Is it advisable to use the same method of converting the i/p parameters using TO_CHAR() and use them in where clause.

Will it effect the performance.

Thanks,
Hemanth
Re: Need opinion in using TO_CHAR [message #425086 is a reply to message #424771] Wed, 07 October 2009 07:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
As I explained, for your sql statement, oracle will do a to_char on your parameters if you do not to_char them yourself somewhere along the way, so one way or the other the to_char is going to happen.

what you should do is:

1) do not convert items when passing them as parameters. Parameters are data, they should be treated as data which means you respect their datatypes when using them.

2) when conversion is required in sql, always explictly provided it.

If you have questions about performance, build tests to see what the performance is for different pieces of code. Remember to account for sql caching by running your tests multiple times and throwing out the most expensive run.

Kevin
Re: Need opinion in using TO_CHAR [message #425089 is a reply to message #425086] Wed, 07 October 2009 07:49 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi Kevin,

Thank you very much for your suggestions.
The reason why I am using TO_CHAR is I can not use other than Character fields in NVL(column_name,'%') function.
So most of the input parameters are INT, so in order to fetch all the records from the table if the passed parameters are NULL...I am using NVL(field,'%').

Hence I am explicitly converting all the Integer passed i/p parameters using TO_CHAR() for the query to fetch all the records.

Please let me know for any other possibility.

Thanks,
Hemanth
Re: Need opinion in using TO_CHAR [message #425091 is a reply to message #424771] Wed, 07 October 2009 07:51 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I understand what you are doing. It is in fact a somewhat common practice.

If you want to make your sql go faster, there are two options open to you.

1) look into function based indexes
2) make your data not null on its tables and use some default value instead of null. Then you have a simple lookup with a null parameter defaulting to your default.

Obviously #2 is much more work because it is a paradigm shift of sorts in the way you handle nulls in your system.

Good luck, Kevin
Previous Topic: insert using row no
Next Topic: Need special sort for a nation table
Goto Forum:
  


Current Time: Sun Dec 04 04:57:06 CST 2016

Total time taken to generate the page: 0.27924 seconds