C# --> PL/SQL

I would like to convert the c# code below to an ORACLE stored procedure.  It needs to be dynamic... it builds the query based on what the uer is searching on.

I started the procedure but did not know how to concatenate.


Please lead me in the right direction.

thanks.
C#
---
 string sql = "SELECT CASE_NUMBER,ASSIGNED_LAST_NAME||'  '||ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME||'  '||POC_FIRST_NAME POC_NAME, COMMENTS FROM ATTORNEY_TBL WHERE ";

        sb.Append(sql);
        if (txt_poc_fname.Text != "")
        {
            sb.Append("POC_FIRST_NAME LIKE '" + txt_poc_fname.Text.ToUpper() + "%'");
            qry = true;
        }

        if (txt_poc_lname.Text != "")
        {
            if (qry)
            {
                sb.Append("AND POC_LAST_NAME LIKE '" + txt_poc_lname.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("POC_LAST_NAME LIKE '" + txt_poc_lname.Text.ToUpper() + "%'");
            }
            qry = true;
        }

        if (txt_DteIn.Text != "")
        {
            if (qry)
            {
                sb.Append("AND DATE_IN LIKE '" + txt_DteIn.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("DATE_IN LIKE '" + txt_DteIn.Text.ToUpper() + "%'");
            }
            qry = true;
        }

        if (txt_DteDue.Text != "")
        {
            if (qry)
            {
                sb.Append("AND DUE_DATE LIKE '" + txt_DteDue.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("DUE_DATE LIKE '" + txt_DteDue.Text.ToUpper() + "%'");
            }
            qry = true;
        }

        if (txt_asLname.Text != "")
        {
            if (qry)
            {
                sb.Append("AND ASSIGNED_LAST_NAME LIKE '" + txt_asLname.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("ASSIGNED_LAST_NAME LIKE '" + txt_asLname.Text.ToUpper() + "%'");
            }
            qry = true;
        }

        if (txt_asFname.Text != "")
        {
            if (qry)
            {
                sb.Append("AND ASSIGNED_FIRST_NAME LIKE '" + txt_asFname.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("ASSIGNED_FIRST_NAME LIKE '" + txt_asFname.Text.ToUpper() + "%'");
            }
            qry = true;
        }

        if (txt_subject.Text != "")
        {
            if (qry)
            {
                sb.Append("AND SUBJECT LIKE '%" + txt_subject.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("SUBJECT LIKE '%" + txt_subject.Text.ToUpper() + "%'");
            }
            qry = true;
        }

        if (txt_comment.Text != "")
        {
            if (qry)
            {
                sb.Append("AND COMMENTS LIKE '%" + txt_comment.Text.ToUpper() + "%'");
            }
            else
            {
                sb.Append("COMMENTS LIKE '%" + txt_comment.Text.ToUpper() + "%'");
            }
            qry = true;
        }

Oracle SP
---------
CREATE OR REPLACE PROCEDURE OGC.SP_SELECT_CASEbyPARAM
(
    POC_LAST_NAME    IN OGC_TBL.POC_LAST_NAME%TYPE,
    POC_FIRST_NAME  IN OGC_TBL.POC_FIRST_NAME%TYPE,
    DATE_IN  IN OGC_TBL.DATE_IN%TYPE,
    DUE_DATE IN OGC_TBL.DUE_DATE%TYPE,
    ASSIGNED_LAST_NAME IN OGC_TBL.ASSIGNED_LAST_NAME%TYPE,
    ASSIGNED_FIRST_NAME IN OGC_TBL.ASSIGNED_FIRST_NAME%TYPE,
    SUBJECT IN OGC_TBL.SUBJECT%TYPE,
    COMMENTS IN OGC_TBL.COMMENTS%TYPE
)

AS
BEGIN
DECLARE SQL_VAR;
    SQL_VAR := "SELECT CASE_NUMBER,ASSIGNED_LAST_NAME||'  '||ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME||'  '||POC_FIRST_NAME POC_NAME, COMMENTS FROM OGC_TBL WHERE "

    IF POC_LAST_NAME <> "" THEN
        SQL_VAR :          
    END IF;
    
        COMMIT;
END SP_SELECT_CASEbyPARAM;

Open in new window

LVL 5
IsaacSharePoint Client Side DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>I started the procedure but did not know how to concatenate.

create or replace procedure myPROC( txt_poc_fname in varchar2)
is
mySQL varchar2(32000)
...
begin
...
if txt_poc_fname is not null then
  mySQL := mySQL || ' POC_FIRST_NAME LIKE %' ||  txt_poc_fname || '%'
end if;
...
end;
/

show errors
0
HainKurtSr. System AnalystCommented:
here a few lines...

SQL_VAR := "SELECT CASE_NUMBER,ASSIGNED_LAST_NAME || '  ' || ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME || '  ' || POC_FIRST_NAME POC_NAME, COMMENTS FROM OGC_TBL WHERE "

if (txt_poc_fname.Text is Not Null)
  SQL_VAR := SQL_VAR || " POC_FIRST_NAME LIKE '" + UPPER(POC_FIRST_NAME) + "%'";
  qry := True;
End If;
...
0
IsaacSharePoint Client Side DeveloperAuthor Commented:
This is what I have but I get an error.


(S223) Expecting: statement_terminator BEGIN CASE DECLARE END IDENTIFIER IF LOOP

CREATE OR REPLACE PROCEDURE OGC.SP_SELECT_CASEbyPARAM
(
    POC_LAST_NAME    IN OGC_TBL.POC_LAST_NAME%TYPE,
    POC_FIRST_NAME  IN OGC_TBL.POC_FIRST_NAME%TYPE,
    DATE_IN  IN OGC_TBL.DATE_IN%TYPE,
    DUE_DATE IN OGC_TBL.DUE_DATE%TYPE,
    ASSIGNED_LAST_NAME IN OGC_TBL.ASSIGNED_LAST_NAME%TYPE,
    ASSIGNED_FIRST_NAME IN OGC_TBL.ASSIGNED_FIRST_NAME%TYPE,
    SUBJECT IN OGC_TBL.SUBJECT%TYPE,
    COMMENTS IN OGC_TBL.COMMENTS%TYPE
)

AS
BEGIN
DECLARE
mySQL varchar2(32000);
qry boolean;
qry :=False;

    mySQL := "SELECT CASE_NUMBER,ASSIGNED_LAST_NAME||'  '||ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME||'  '||POC_FIRST_NAME POC_NAME, COMMENTS FROM OGC_TBL WHERE "

    IF POC_LAST_NAME IS NOT NULL THEN
        mySQL := mySQL || " POC_LAST_NAME LIKE '" + UPPER(POC_LAST_NAME) + "%' ";  
        qry := True;           
    END IF;
    
    IF POC_FIRST_NAME IS NOT NULL THEN
        IF qry = True
            mySQL := mySQL || " POC_FIRST_NAME LIKE '" + UPPER(POC_FIRST_NAME) + "%' ";   
        Else        
            mySQL := mySQL || "AND POC_FIRST_NAME LIKE '" + UPPER(POC_FIRST_NAME) + "%' ";
        END IF;  
            qry := True;  
    END IF;    
    
    IF DATE_IN IS NOT NULL THEN
        IF qry = True
            mySQL := mySQL || " DATE_IN LIKE '" + DATE_IN + "%' "; 
        ELSE
            mySQL := mySQL || "AND DATE_IN LIKE '" + DATE_IN + "%' ";         
        END IF; 
        qry := True;           
    END IF;  
    
    IF DUE_DATE IS NOT NULL THEN
        IF qry = True
            mySQL := mySQL || " DATE_IN LIKE '" + DUE_DATE + "%' ";  
        ELSE
            mySQL := mySQL || "AND  DATE_IN LIKE '" + DUE_DATE + "%' "; 
        END IF;
        qry := True;           
    END IF;  
    
    IF ASSIGNED_LAST_NAME IS NOT NULL THEN
        IF qry = True
            mySQL := mySQL || " DATE_IN LIKE '" + UPPER(ASSIGNED_LAST_NAME) + "%' ";
        ELSE        
            mySQL := mySQL || "AND  DATE_IN LIKE '" + DUE_DATE + "%' ";
        END IF;   
        qry := True;           
    END IF;  
    
    IF ASSIGNED_FIRST_NAME IS NOT NULL THEN
        IF qry = True
            mySQL := mySQL || " DATE_IN LIKE '" + UPPER(ASSIGNED_FIRST_NAME) + "%' ";  
        ELSE
            mySQL := mySQL || "AND  DATE_IN LIKE '" + UPPER(ASSIGNED_FIRST_NAME) + "%' ";
        END IF
        qry := True;           
    END IF;  
    
    IF SUBJECT IS NOT NULL THEN
        IF qry = True
            mySQL := mySQL || " DATE_IN LIKE '" + UPPER(SUBJECT) + "%' "; 
        ELSE
            mySQL := mySQL || "AND  DATE_IN LIKE '" + UPPER(SUBJECT) + "%' ";
        END IF 
        qry := True;           
    END IF;  
    
    IF COMMENTS IS NOT NULL THEN
        IF qry = True
           mySQL := mySQL || " DATE_IN LIKE '" + UPPER(COMMENTS) + "%' ";  
        ELSE        
           mySQL := mySQL || "AND DATE_IN LIKE '" + UPPER(COMMENTS) + "%' ";
        END IF;
        qry := True;           
    END IF;  
                        
END SP_SELECT_CASEbyPARAM;

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
mySQL := 'SELECT CASE_NUMBER,ASSIGNED_LAST_NAME||''  ''||ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME||''  ''||POC_FIRST_NAME POC_NAME, COMMENTS FROM OGC_TBL WHERE ';


no ending semi-colon and Oracle doesn't like double quotes.
0
IsaacSharePoint Client Side DeveloperAuthor Commented:
Here are the errors I get now when I changed the declare part above to this:
BEGIN
mySQL varchar2(32000);
qry boolean;
DECLARE
-----------------------------------------------------------------------------------------

15/7    PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following:
          := . ( @ % ;
          The symbol ":=" was substituted for "VARCHAR2" to continue.
16/5    PLS-00103: Encountered the symbol "BOOLEAN" when expecting one of the following:
          := . ( @ % ;
          The symbol ":=" was substituted for "BOOLEAN" to continue.
18/5    PLS-00103: Encountered the symbol "=" when expecting one of the following:
          constant exception <an identifier>
          <a double-quoted delimited-identifier> table LONG_ double ref
          char time timestamp interval date binary national character
          nchar
          The symbol "<an identifier>" was substituted for "=" to continue.
20/14   PLS-00114: identifier 'SELECT CASE_NUMBER,ASSIGNED_LA' too long
20/11   PLS-00103: Encountered the symbol "=" when expecting one of the following:
          constant exception <an identifier>
          <a double-quoted delimited-identifier> table LONG_ double ref
          char time timestamp interval date binary national character
          nchar



0
slightwv (䄆 Netminder) Commented:
Sorry... didn't see that one.

Remove DECLARE.  You only need that for anonymous PL/SQL blocks.

Move BEGIN after the variable declarations.


create or replace ...
AS
  mySQL varchar2(32000);
  qry boolean;
  qry :=False;
begin
...
0
IsaacSharePoint Client Side DeveloperAuthor Commented:
Ok.
This is what the first few lines of the SP looks like based on your suggestions.
Now I get this error:

PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table LONG_ double ref
   char time timestam
------------------------------------------------
PLS-00103: Encountered the symbol "MYSQL" when expecting one of the following:

   . ( * @ % & - + / at mod remainder rem then
   <an exponent (**)> and or || multiset
The symbol "then" was substitute
------------------------------------------------
PLS-00103: Encountered the symbol "QRY" when expecting one of the following:

   ;
The symbol ";" was substituted for "QRY" to continue.



CREATE OR REPLACE PROCEDURE OGC.SP_SELECT_CASEbyPARAM
(
    POC_LAST_NAME    IN OGC_TBL.POC_LAST_NAME%TYPE,
    POC_FIRST_NAME  IN OGC_TBL.POC_FIRST_NAME%TYPE,
    DATE_IN  IN OGC_TBL.DATE_IN%TYPE,
    DUE_DATE IN OGC_TBL.DUE_DATE%TYPE,
    ASSIGNED_LAST_NAME IN OGC_TBL.ASSIGNED_LAST_NAME%TYPE,
    ASSIGNED_FIRST_NAME IN OGC_TBL.ASSIGNED_FIRST_NAME%TYPE,
    SUBJECT IN OGC_TBL.SUBJECT%TYPE,
    COMMENTS IN OGC_TBL.COMMENTS%TYPE
)

AS 
  mySQL varchar2(32000); 
  qry boolean; 
  qry :=False; 
begin

    mySQL := 'SELECT CASE_NUMBER,ASSIGNED_LAST_NAME||''  ''||ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME||''  ''||POC_FIRST_NAME POC_NAME, COMMENTS FROM OGC_TBL WHERE ';

Open in new window

0
slightwv (䄆 Netminder) Commented:
oops...

...
  mySQL varchar2(32000);  
  qry boolean;  
begin
  qry :=False;  
...
0
slightwv (䄆 Netminder) Commented:
or

create or replace ...
AS
  mySQL varchar2(32000);
  qry boolean :=False;
begin
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IsaacSharePoint Client Side DeveloperAuthor Commented:
Now I get the following error
PLS-00201: identifier ' POC_LAST_NAME LIKE '' must be declared
PLS-00201: identifier ' POC_FIRST_NAME LIKE '' must be declared
PLS-00201: identifier 'AND POC_FIRST_NAME LIKE '' must be declared
PLS-00201: identifier ' DATE_IN LIKE '' must be declared
ETC....
0
slightwv (䄆 Netminder) Commented:
See if this helps (I actually ran this code).
drop table tab1 purge;
create table tab1(
CASE_NUMBER char(1),
ASSIGNED_LAST_NAME char(1),
ASSIGNED_FIRST_NAME char(1),
CASE_TYPE char(1),
ORIGINATOR char(1),
SUBJECT char(1),
DATE_IN char(1),
DUE_DATE char(1),
POC_LAST_NAME char(1),
POC_FIRST_NAME char(1),
COMMENTS char(1)
);

insert into tab1 values('a','a','a','a','a','a','a','a','a','a','a');
commit;


create or replace procedure junkProc(txt_poc_fname in tab1.poc_first_name%type)
is
	mySQL varchar2(32000);
	myCursor sys_refcursor;
	myRec    tab1%rowtype;
begin

	-- notice I changed the SQL a little to add a default WHERE clause
  mySQL := 'SELECT CASE_NUMBER,ASSIGNED_LAST_NAME,ASSIGNED_FIRST_NAME ASSIGNED_NAME, CASE_TYPE,ORIGINATOR,SUBJECT,DATE_IN,DUE_DATE, POC_LAST_NAME,POC_FIRST_NAME, COMMENTS FROM tab1 WHERE 1=1 ';

		if txt_poc_fname is not null then
			mySQL := mySQL || ' and POC_LAST_NAME like ''%' || txt_poc_fname || '%''';
		end if;

		open myCursor for mySQL;
		loop
			fetch myCursor into myRec;
			exit when myCursor%notfound;

			dbms_output.put_line('FNAME: ' || myRec.poc_first_name);
		end loop;
		close myCursor;
end;
/

show errors

exec junkProc('a');

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.