OraFAQ Forum: SQL & PL/SQL » Orackle Package

Home » SQL & PL/SQL » SQL & PL/SQL » Orackle Package

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Orackle Package [message #38047]

Thu, 14 March 2002 23:51

roy tilakraj
Messages: 2
Registered: February 2002

Junior Member

I have the following procedure where I pass Fieldnames and values to generate a query. how can i mprove on this ?

PROCEDURE SearchCustomers(strFieldName IN Varchar2, strFieldValue IN Varchar2,
intInclude IN Number, curCustomer OUT CustomerCursor,
intRetCode OUT Number, strMessage OUT Varchar2)
IS

BEGIN

If strFieldName='NAME' Then
if intInclude = 0 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(NAME) = Upper(strFieldValue);
elsif intInclude = 1 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(NAME) LIKE '%' || Upper(strFieldValue) || '%';
else
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE SOUNDEX(NAME) = SOUNDEX(strFieldValue);
end if;

ElsIf strFieldName='POSTCODE' Then
if intInclude = 0 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(POSTCODE) = Upper(strFieldValue);
elsif intInclude = 1 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(POSTCODE) LIKE '%' || Upper(strFieldValue) || '%';
else
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE SOUNDEX(POSTCODE) = SOUNDEX(strFieldValue);
end if;

ElsIf strFieldName='E1' Then
if intInclude = 0 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(E1) = Upper(strFieldValue);
elsif intInclude = 1 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(E1) LIKE '%' || Upper(strFieldValue) || '%';
else
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Soundex(E1) = SOUNDEX(strFieldValue);
end if;

ElsIf strFieldName='TOWN' Then
if intInclude = 0 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(TOWN) = Upper(strFieldValue);
elsif intInclude = 1 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(TOWN) LIKE '%' || Upper(strFieldValue) || '%';
else
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE SOUNDEX(TOWN) = SOUNDEX(strFieldValue);
end if;

ElsIf strFieldName='CUSTOMER_ID' Then
if intInclude = 0 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(CUSTOMER_ID) = Upper(strFieldValue);
elsif intInclude = 1 then
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE Upper(CUSTOMER_ID) LIKE '%' || Upper(strFieldValue) || '%';
else
OPEN curCustomer FOR
SELECT Customer_Id, Company, Initials, Name, Town, Postcode
FROM Customer
WHERE SOUNDEX(CUSTOMER_ID) = SOUNDEX(strFieldValue);
end if;

End If;

intRetCode := 10;

-- - - - - - -- - - - - - - - -- - - - - - -
EXCEPTION
When No_Data_Found Then intRetCode := 100;
When Storage_Error Then intRetCode := -6500;
When TimeOut_On_Resource Then intRetCode := -51;
When Too_Many_Rows Then intRetCode := -1422;
When Value_Error Then intRetCode := -6502;
When Others Then intRetCode := -1;

END;

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

END;

Report message to a moderator

Re: Oracle Package [message #38054 is a reply to message #38047]

Fri, 15 March 2002 07:50

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Handle everything through dynamic SQL and lose the exception handling for exceptions that will never be raised with select statements.

create or replace procedure SearchCustomers(  
  strFieldName IN Varchar2,  
  strFieldValue IN Varchar2,  
  intInclude IN Number,  
  curCustomer OUT CustomerCursor,  
  intReturnCode OUT Number)
is
  v_sql  varchar2(1000);
begin  
  v_sql := 'select customer_id, company, initials, name, town, postcode' ||
           '  from customer' ||
           ' where ';
 
  if intInclude = 0 then
    v_sql := v_sql || ' upper(' || strFieldName || ') = upper(:value)';
  elsif intInclude = 1 then
    v_sql := v_sql || ' upper(' || strFieldName || ') like ''%'' || upper(:value) || ''%''';
  elsif intInclude = 2 then
    v_sql := v_sql || ' soundex(' || strFieldName || ') = soundex(:value)';
  end if;            
 
  open curCustomer for v_sql using strFieldValue;     
 
  intReturnCode := 10;
exception  
  when others then    
    intReturnCode := -1;  
end;
/

Report message to a moderator

Previous Topic:	segmentation fault from libclntsh.a
Next Topic:	SQL Statement Help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:57:51 CDT 2024