4 Replies Latest reply on Feb 5, 2014 6:24 PM by rp0428

    Need help on below procedure.

    3050d5ea-07a7-4d00-ba74-013e3b861a69

      I am getting below compilation error.

      My SP is below :-

      create or replace procedure getfinelist(personid IN varchar2(50),

                                              veichleregid IN varchar2(50),

                                              vmonth IN varchar2(10))

      Is

      declare

      type rec_data is record (personname varchar2(50),

                               vregno varchar2(50),

                               violationdate varchar2(10),

                               vrule varchar2(20),

                               finepay number(100,2),

                               total number(100,2));

      report rec_data;

      testcur sys_refcursor;

       

       

      begin

      open testcur for select a.personname,b.vregno,to_date(c.vtime,'dd-mm-yyyy'),c.vrule,d.finepay,sum(d.finepay) over (order by d.FINEPAY) bulk collect into rec_data

      from person a,veichle_reg b,violation c,ruletype d

      where a.personid=b.personid

      and   b.vregno=c.vregno

      and   c.vrule=d.rulename

      and   extract(month from c.vtime)=vmonth

      and a.PERSONID=personid;

      loop

      fetch testcur into report;

      dbms_output.put_line(report.personname,report.vregno,report.violationdate,report.vrule,report.finepay,report.total)

      exit when testcur%NOTFOUND;

      end loop;

      exit;

       

      Please help..

      Thanks in Advance !!

        • 1. Re: Need help on below procedure.
          thatJeffSmith-Oracle

          This is a PL/SQL question, not a SQL Developer question.

           

          But, the errors are telling you what's wrong - when you declare your inputs, just say VARCHAR2 - when the compiler sees the (50) it doesn't know what you're talking about. That's why it's repeated 2 more times, once for each varchar2 input you have.

          • 2. Re: Need help on below procedure.
            Partha Sarathy S

            Below are the errors in your code.

             

            1) do not use size for datatypes used in parameters.

            2) Use semicolon to end statements

            3) DBMS_OUTPUT.PUT_LINE procedure can have only one parameter. So pass it as a single string

            4) Number precision is 1..38


            See the modified code

            create or replace procedure getfinelist(personid IN varchar2,

                                                    veichleregid IN varchar2,

                                                    vmonth IN varchar2)

            Is

            type rec_data is record (personname varchar2(50),

                                     vregno varchar2(50),

                                     violationdate varchar2(10),

                                     vrule varchar2(20),

                                     finepay number,

                                     total number);

            report rec_data;

            testcur sys_refcursor;

            begin

            open testcur for select a.personname,b.vregno,to_date(c.vtime,'dd-mm-yyyy'),c.vrule,d.finepay,sum(d.finepay) over (order by d.FINEPAY) bulk collect into rec_data

            from person a,veichle_reg b,violation c,ruletype d

            where a.personid=b.personid

            and   b.vregno=c.vregno

            and   c.vrule=d.rulename

            and   extract(month from c.vtime)=vmonth

            and a.PERSONID=personid;

            loop

            fetch testcur into report;

            dbms_output.put_line(report.personname||','||report.vregno||','||report.violationdate||','||report.vrule||','||report.finepay||','||report.total);

            exit when testcur%NOTFOUND;

            end loop;

            end;

            • 3. Re: Need help on below procedure.
              3050d5ea-07a7-4d00-ba74-013e3b861a69

              Hi All,

               

              Thank you soo much for your help. But perth the below code is not populating the data, rather returning only four ,(comma).

               

              create or replace procedure getfinelist(personid IN varchar2,

                                                      veichleregid IN varchar2,

                                                      vmonth IN varchar2)

              Is

              type rec_data is record (personname varchar2(50),

                                       vregno varchar2(50),

                                       violationdate varchar2(10),

                                       vrule varchar2(20),

                                       finepay number,

                                       total number);

              report rec_data;

              --testcur sys_refcursor;

              begin

              declare

              cursor cc IS

              select a.personname,b.vregno,to_date(c.vtime,'dd-mm-yyyy'),c.vrule,d.finepay,sum(d.finepay) over (order by d.FINEPAY)  into report

              from person a,veichle_reg b,violation c,ruletype d

              where a.personid=b.personid

              and   b.vregno=c.vregno

              and   c.vrule=d.rulename

              and   extract(month from c.vtime)=vmonth;

              --and a.personid=personid;

              begin

              --loop

              --fetch cc into report;

              for i in cc loop

              --insert into record values(personname.cc(i),vregno.cc(i),violationdate.cc(i),vrule.cc(i),finepay.cc(i),total.cc(i));

              dbms_output.put_line(report.personname||','||report.vregno||','||report.violationdate||','||report.vrule||','||report.finepay||','||report.total);

              return;

              exit when cc%NOTFOUND;

              end loop;

              end;

              end getfinelist;

               

              Please help.

              Thanks a lot friends..

              1 person found this helpful
              • 4. Re: Need help on below procedure.

                Please help.

                If you want further help you have to post your question in the SQL and PL/SQL forum.

                https://community.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

                 

                Mark this thread ANSWERED. This forum is ONLY for sql developer questions/issue.

                1 person found this helpful