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  |
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 #424865 is a reply to message #424771] |
Tue, 06 October 2009 05:27  |
 |
Kevin Meade
Messages: 2103
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 |
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 |
|
Kevin Meade
Messages: 2103
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 |
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 #425077 is a reply to message #424771] |
Wed, 07 October 2009 07:12 |
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 #425089 is a reply to message #425086] |
Wed, 07 October 2009 07:49 |
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 |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 19:19:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
