Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    38

    Unanswered: procedure view is not exist

    hi friend,
    can any one let me know y this procedure in not running,view is not exist.yet the query give me result.but in procedure there is no view.can any one suggest me ans?
    my code as given below...


    create or replace procedure gps_procedure(branchcode in varchar2,
    group1 in varchar2,
    fromdate in varchar2,
    gps_code out varchar2
    ) as



    cursor vishwas is

    with a as(
    SELECT pm.code, pm.name, pm.designation, SUM(pe.VALUE) as total
    FROM PMS_EMP_SALITEM_DET pe
    join pms_employee pm on pe.EMPID = pm.id
    WHERE pe.SALARYITEMID IN (5)
    AND (pe.EMPID IN (SELECT pi.id
    from pms_employee pi
    where pi.INSTITUTECODE = branchcode))
    AND PAYROLLID IN
    (SELECT pp.ID
    FROM PMS_PAYROLL pp
    WHERE (pp.YEAR =
    (to_char(to_date(fromdate, 'mm-YYYY'), 'YYYY')) and
    (pp.month =
    (to_char(to_date(fromdate, 'mm-YYYY'), 'mm'))))

    )
    group by pm.name, pm.code, pm.designation, pe.empid), b as (SELECT pm.code,
    pm.name,
    pm.designation,
    SUM(pe.VALUE) as total2
    FROM PMS_EMP_SALITEM_DET pe
    join pms_employee pm on pe.EMPID =
    pm.id
    WHERE pe.SALARYITEMID IN (78)
    AND (pe.EMPID IN
    (SELECT pi.id
    from pms_employee pi
    where pi.INSTITUTECODE =
    branchcode
    and pi.batchid =
    (select id
    from pms_batch
    where name =
    group1
    and institutecode =
    branchcode)) OR
    pe.EMPID IN
    (SELECT pi.id
    from pms_employee pi
    where pi.INSTITUTECODE =
    branchcode))
    AND PAYROLLID IN
    (SELECT pp.ID
    FROM PMS_PAYROLL pp
    WHERE (pp.YEAR =
    (to_char(to_date(fromdate,
    'mm-YYYY'),
    'YYYY')) and
    (pp.month =
    (to_char(to_date(fromdate,
    'mm-YYYY'),
    'mm'))))

    )
    group by pm.name,
    pm.code,
    pm.designation,
    pe.empid)
    select a.code
    into gps_code
    from a
    inner join b on a.code = b.code;

    vishwas_rec pms_employee%rowtype;
    begin


    for vishwas_rec in vishwas
    loop

    dbms_output.put_line('vishwas in test before updation3');
    DBMS_OUTPUT.PUT_LINE((vishwas_rec.code) );
    end loop;


    end;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    privileges acquired via ROLE do not apply within named PL/SQL named procedures.

    this can be tested by first doing
    SQL> SET ROLE NONE
    SQL> -- now issue SELECT
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2011
    Posts
    38
    hi friend i have created a procedure and i want to take the value of cursor into the out parameter which is declare in procedure body..


    my code is given below..
    create or replace procedure gps_procedure(branchcode in varchar2,
    group1 in varchar2,
    fromdate in varchar2,
    gps_code out pms_employee%rowtype,
    gps_name out pms_employee%rowtype,
    gps_designation out pms_employee%rowtype,
    total out PMS_EMP_SALITEM_DET%rowtype,
    total2 out PMS_EMP_SALITEM_DET%rowtype,
    total3 out PMS_EMP_SALITEM_DET%rowtype) as

    gps_code1 varchar2(20);
    user_group varchar2(20);
    user_fromdate varchar2(20);


    begin
    gps_procedure.gps_code1 := branchcode;
    gps_procedure.user_group := group1;
    gps_procedure.user_fromdate := fromdate;
    if (gps_procedure.user_group = 'null') then

    declare
    cursor pms_employee is with a as(
    SELECT pm.code, pm.name, pm.designation, SUM(pe.VALUE) as total
    FROM PMS_EMP_SALITEM_DET pe
    join pms_employee pm on pe.EMPID = pm.id
    WHERE pe.SALARYITEMID IN (5)
    AND (pe.EMPID IN
    (SELECT pi.id
    from pms_employee pi
    where pi.INSTITUTECODE = gps_procedure.gps_code1
    and pi.batchid =
    (select id
    from pms_batch
    where name = gps_procedure.user_group
    and institutecode = gps_procedure.gps_code1)))
    AND PAYROLLID IN
    (SELECT pp.ID
    FROM PMS_PAYROLL pp
    WHERE (pp.YEAR = (to_char(to_date(gps_procedure.user_fromdate,
    'mm-YYYY'),
    'YYYY')) and
    (pp.month = (to_char(to_date(gps_procedure.user_fromdate,
    'mm-YYYY'),
    'mm'))))

    )
    group by pm.name, pm.code, pm.designation, pe.empid), b as (SELECT pm.code,
    pm.name,
    pm.designation,
    SUM(pe.VALUE) as total2
    FROM PMS_EMP_SALITEM_DET pe
    join pms_employee pm on pe.EMPID =
    pm.id
    WHERE pe.SALARYITEMID IN (78)
    AND (pe.EMPID IN
    (SELECT pi.id
    from pms_employee pi
    where pi.INSTITUTECODE =
    gps_procedure.gps_code1
    and pi.batchid =
    (select id
    from pms_batch
    where name =
    gps_procedure.user_group
    and institutecode =
    gps_procedure.gps_code1))

    )
    AND PAYROLLID IN
    (SELECT pp.ID
    FROM PMS_PAYROLL pp
    WHERE (pp.YEAR =
    (to_char(to_date(gps_procedure.user_fromdate,
    'mm-YYYY'),
    'YYYY')) and
    (pp.month =
    (to_char(to_date(gps_procedure.user_fromdate,
    'mm-YYYY'),
    'mm'))))

    )
    group by pm.name,
    pm.code,
    pm.designation,
    pe.empid)
    select a.code,
    a.name,
    a.designation,
    a.total,
    b.total2,
    (a.total + b.total2) as total3
    from a
    inner join b on a.code = b.code;

    pms_employee_rec pms_employee%rowtype;
    begin

    IF NOT pms_employee%ISOPEN THEN
    OPEN pms_employee;
    END IF;
    dbms_output.put_line('vishwas in test before updation4');
    loop
    fetch pms_employee
    into pms_employee_rec;
    --EXIT WHEN pms_employee%NOTFOUND;
    dbms_output.put_line('vishwas in test before updation3');

    DBMS_OUTPUT.PUT_LINE((pms_employee_rec.code ||pms_employee_rec.name || pms_employee_rec.designation
    || pms_employee_rec.total || pms_employee_rec.total2|| pms_employee_rec.total3));

    end loop;

    end;

    end if;
    end;



    in place of " into pms_employee_rec" this i want to write all the all the out parameter but it will not take..
    can any one suggest me ans...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You are member of this forum for almost a year now; how come you didn't learn how to post nicer messages? Here I mean "why don't you use [code] tags which will preserve formatting?" (of course, you need to format code first). Have a look here.

    Now: as of OUT parameters: I doubt that this is really what you meant to do. For example, this:
    Code:
    gps_code out pms_employee%rowtype
    means that the GPS_CODE parameter should be of the same type as the WHOLE RECORD ("rowtype") in the PMS_EMPLOYEE table. I don't think that this is correct.

    You didn't post table's description, but imagine that PMS_EMPLOYEE table contains GPS_CODE column. Then you would put it as
    Code:
    gps_code out pms_employee.gps_code%type
    See the difference?

    Have a look at "PL/SQL User's Guide and Reference" and "Application Developer's Guide - Fundamentals" books, both in the "Most popular" section of the documentation page.

  5. #5
    Join Date
    Jul 2011
    Posts
    38
    thanks friend for ur quick response

    yes friend u r right.this was my mistake to declare the procedure.but my question is that how i will take my output which was come from the cursor in out parameter wich is declare in procedure.
    for example..
    i declare

    gps_code out pms_employee%type,
    gps_name out pms_employee%type,
    gps_designation out pms_employee%type,
    total out PMS_EMP_SALITEM_DET%type,
    total2 out PMS_EMP_SALITEM_DET%type,
    total3 out PMS_EMP_SALITEM_DET%type)

    this is the out parameter.
    ok.

    and some value come in the cursor by select statement(i.e)

    pm.code,
    pm.designation,
    pe.empid)
    select a.code,
    a.name,
    a.designation,
    a.total,
    b.total2,
    (a.total + b.total2) as total3
    from a
    inner join b on a.code = b.code;


    ok..this is value which cursor contain and i want to take this value in my out parameter likegps_code ....


    how this happen?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I still think that your procedure's parameter declaration is wrong.

    Anyway: here's an example of how you might do that. Note that cursor is rather "stupid" option here as you could do the same with a single SELECT statement, selecting values directly into the parameters.

    A procedure:
    Code:
    SQL> create or replace procedure prc_test
      2    (par_empno     in  emp.empno%type,
      3     par_ename     out emp.ename%type,
      4     par_job       out emp.job%type
      5    )
      6  is
      7    cursor c1 is
      8      select ename, job
      9       from emp
     10       where empno = par_empno;
     11    c1_r c1%rowtype;
     12  begin
     13    open c1;
     14    fetch c1 into par_ename, par_job;
     15    close c1;
     16  end;
     17  /
    
    Procedure created.
    This is what I want from the procedure (I'll pass EMPNO and want ENAME and JOB):
    Code:
    SQL> select empno, ename, job
      2  from emp
      3  where ename = 'ADAMS';
    
         EMPNO ENAME      JOB
    ---------- ---------- ---------
          7876 ADAMS      CLERK
    Testing:
    Code:
    SQL> declare
      2    l_ename emp.ename%type;
      3    l_job   emp.job%type;
      4  begin
      5    prc_test(7876, l_ename, l_job);
      6
      7    dbms_output.put_line('Ename = ' || l_ename);
      8    dbms_output.put_line('Job   = ' || l_job);
      9  end;
     10  /
    Ename = ADAMS
    Job   = CLERK
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Obviously, it is designed to return single values. You could, though, loop while calling the procedure, such as
    Code:
    SQL> declare
      2    l_ename emp.ename%type;
      3    l_job   emp.job%type;
      4  begin
      5    for cur_r in (select empno from emp
      6                  where deptno = 30
      7                 )
      8    loop
      9      prc_test(cur_r.empno, l_ename, l_job);
     10
     11      dbms_output.put_line('Ename = ' || l_ename);
     12      dbms_output.put_line('Job   = ' || l_job);
     13    end loop;
     14  end;
     15  /
    Ename = ALLEN
    Job   = SALESMAN
    Ename = WARD
    Job   = SALESMAN
    Ename = MARTIN
    Job   = SALESMAN
    Ename = BLAKE
    Job   = MANAGER
    Ename = TURNER
    Job   = SALESMAN
    Ename = JAMES
    Job   = CLERK
    
    PL/SQL procedure successfully completed.
    
    SQL>
    but I, somehow, think that ref cursor is what you really are looking for (though, I might be wrong). Anyway, here's an example:
    Code:
    SQL> create or replace procedure prc_test
      2    (par_deptno     in  emp.deptno%type,
      3     par_employee   out sys_refcursor
      4    )
      5  is
      6  begin
      7    open par_employee for
      8      select ename, job
      9     from emp
     10     where deptno = par_deptno;
     11  end;
     12  /
    
    Procedure created.
    
    SQL> var l_emp refcursor;
    SQL> begin
      2    prc_test(30, :l_emp);
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> print l_emp;
    
    ENAME      JOB
    ---------- ---------
    ALLEN      SALESMAN
    WARD       SALESMAN
    MARTIN     SALESMAN
    BLAKE      MANAGER
    TURNER     SALESMAN
    JAMES      CLERK
    
    6 rows selected.
    
    SQL>
    Now you have some examples; I believe that you should read documentation I pointed you to.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data,
    we can not compile, run or test posted code.

    What you have done is the equivalent to me showing you a picture of my car,
    reporting it does not go, & asking to you to tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jul 2011
    Posts
    38
    thanks friend
    for ur quick response.

    and thanks for give me new idea..(create one procedure for single statement and use that procedure to get many reult)
    and with ur help i m able to solve my problem.
    Last edited by vishwas; 06-09-12 at 03:26.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •