5 Replies Latest reply: Mar 29, 2014 1:28 AM by JustinCave RSS

    Problem with executing procedure

    Sagar K N

      Hi Guys, I'm having problem while calling procedure, but it wont show errors while executing it.

      I'm posting my code please guide me...

       

      Create Or Replace Procedure Report_Main(A  In Varchar2 :=1 )

      As

      Empno Namelist;

      Empname Namelist;

      Dt Datelist;

      Begin

      Execute Immediate Q'[Select Distinct B.Empno, B.Empname, A.Dt From(With Qry1 As

      ( Select Rdate From Report1 Where Empno = "M-336" And To_Char(Rdate,'Mm') = To_Char(Sysdate,'Mm') And To_Char(Sysdate,'Yyyy')=To_Char(Rdate,'Yyyy')

         Union All

         Select Pdt From Meeting1 Where Empno= "M-336" And To_Char(Pdt,'Mm')= To_Char(Sysdate,'Mm') And To_Char(Sysdate,'Yyyy')=To_Char(Pdt,'Yyyy')

         Union All

         Select  D.Hdate From Trusmart.Loginfo A, States C, Holidaymastb D

      Where A.Empno ="M-336"

      And To_Char(D.Hdate,'Mm') = To_Char(Sysdate,'Mm')

      And A.State = C.States

      And D.Statesid = C.Statesid

      And To_Char(Sysdate,'Yyyy')=To_Char(Hdate,'Yyyy')

      )

      Select Dt

      From

      (Select Min_Date+(Level-1) Dt From

      (Select Min(Rdate) Min_Date,

              Max(Rdate) Max_Date

      From Qry1)

      Connect By Level <= (Max_Date - Min_Date) +1) Qry2

      Where Not Exists(Select 1

                       From Qry1 Q1

                       Where Q1.Rdate = Qry2.Dt)

                       And Trim(To_Char(Dt,'Day'))!= 'Sunday'

      Order By Dt) A, Empma B

      Where B.Empno = "M-336"

      Order By A.Dt]'

      Bulk Collect Into Empno,Empname,Dt;

      If Sql%Found Then

      Report_Mail_Proc( Fromad =>'<services@mycompany.com>' ,

      Toad => '<sagar.kn@mycompany.com>' ,

      Empno => Empno,

      Empname => Empname,

      Dt => Dt );

      End If;

      Exception When No_Data_Found Then

      Null;

      End;

      /

       

      Create Or Replace Procedure Report_Mail_Proc(Fromad In Varchar2,

      Toad In Varchar2,

      Subject In Varchar2 :='Reporting Date Notification',

      Message In Varchar2 :='    Dear Team,' ,

      Empno In Namelist,

      Empname In Namelist,

      Dt In Datelist )

      As

      Smtphost Varchar(255) := 'mycompany.com' ;

      A Utl_Smtp.Connection;

      Begin

      A :=Utl_Smtp.Open_Connection(Smtphost,25);

      Utl_Smtp.Helo(A,Smtphost);

      Utl_Smtp.Mail(A,Fromad);

      Utl_Smtp.Rcpt(A,Toad);

      Utl_Smtp.Open_Data(A);

      Utl_Smtp.Write_Data(A,'Date: '|| To_Char(Sysdate,'DD/MM/YYYY HH24:MI:SS') || Utl_Tcp.Crlf  );

      Utl_Smtp.Write_Data(A,'From: '||Fromad|| Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,'To: '||Toad||Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,'Subject: '||Subject||Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,'  Date:   '||To_Char(Sysdate,'DD/MM/YYYY HH24:MI:SS')||Utl_Tcp.Crlf );

      Utl_Smtp.Write_Data(A,'  From:   '||Substr(Substr(Fromad,2),1,Length(Substr(Fromad,2))-1)||Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,'  To:   '||Substr(Substr(Toad,2),1,Length(Substr(Toad,2))-1)||Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,Message||Utl_Tcp.Crlf||Utl_Tcp.Crlf||  Q'[   Below are the dates not reported my an employee : ]' ||Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,Utl_Tcp.Crlf);

      Utl_Smtp.Write_Data(A,'    EMPLOYEE_NAME      '||' - '||'    EMPLOYEE_NAME    '||' - '||'      DATE_NOT_REPORTED       '||Utl_Tcp.Crlf);

      For I In 1..Dt.Count

      Loop

      Utl_Smtp.Write_Data(A, '     '||Empno(I)||'                '||' - '||'    '||Empname(I)||'                 '||' - '||'      '||To_Char(Dt(I),'DD/MM/YYYY')||Utl_Tcp.Crlf);

      End Loop;

         Utl_Smtp.Write_Data(A,Utl_Tcp.Crlf );

        Utl_Smtp.Write_Data (A,'    Text message '||Utl_Tcp.Crlf );

         Utl_Smtp.Write_Data(A,Utl_Tcp.Crlf );

         Utl_Smtp.Write_Data (A,'    Best Regards,

          Sagar K N

         '||Utl_Tcp.Crlf );

      Utl_Smtp.Close_Data(A);

      Utl_Smtp.Quit(A);

      Exception

      When Others Then

      Utl_Smtp.Quit(A);

      Raise;

      End;

      /

       

       

      I get the following error:

       

      ORA-06550: line 1, column 7:

      PLS-00201: identifier 'REPORT_MAIL' must be declared

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

       

       

      when i execute "user_errors" i get no errors of the respective procdedure.

      Am i not allowed to use "Execute Immediate" statement like i have used?

       

       

      please guide me.

       

      Thanks in advance.

        • 1. Re: Problem with executing procedure
          JustinCave

          I'm confused.

           

          According to your error message, you have an identifier REPORT_MAIL that is not declared.  According to the code you posted, you have no such identifier anywhere in the code.  So either you're posting the wrong error message or the wrong code.

           

          You also say that you are using EXECUTE IMMEDIATE.  But the code you've posted isn't using EXECUTE IMMEDIATE.

           

          Justin

          • 2. Re: Problem with executing procedure
            Sagar K N

            I'm Sorry I have posted wrong error, the error says :

            ORA-00904: "M-336": invalid identifier

            ORA-06512: at "SIPLLOCAL.REPORT_MAIN", line 7

            ORA-06512: at line 1

             

            I'm sorry Justin Cave, I got it.

             

            Thank You

            • 3. Re: Problem with executing procedure
              JustinCave

              The immediate error is that you're using double quotes around M-336.  Assuming that is supposed to be a string that you're using in a comparison, you'd want to use single quotes.

               

              There doesn't appear to be any reason to use dynamic SQL in the first place.  It appears to merely be adding complexity to your code and turning compilation errors into runtime errors.

               

              If you're determined to use dynamic SQL for some reason, it always makes sense to print out the SQL statement you've built before executing it.  It's far easier to debug that way.

               

              Finally, if you're going to post a bunch of code to the forums, it would help immensely to format it using the tools in the editor so that white space is preserved and the code is easy to read.

               

              Justin

              • 4. Re: Problem with executing procedure
                Sagar K N

                Thanks a lot Justin Cave, the above code was just a test, instead of hard coding the value "M-336" i want it to appear dynamically so I'm using that method.

                And about posting code in formatted manner, I'll keep that in mind next time

                • 5. Re: Problem with executing procedure
                  JustinCave

                  I'm not sure that I understand the use of dynamic SQL.  There would be no reason to use dynamic SQL when a string value changes.  Use static SQL, assign the value you want to a local variable, and use that variable in your query.

                   

                  Justin