Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    46

    Unanswered: Can we set a size of in/out paramter in oracle stored procedure

    Hi,

    I m fetching a employee information of the company when i am sending the All Employee Ids as input parameter in the stored procedure it is giving me error msg that Buffor is too small,

    Please tell me how to handle this error.

    i am working on oracle9i & 10g.

    below is the SP:


    CREATE OR REPLACE PROCEDURE Prc_Attendance_Summary
    (
    P_employeeid IN VARCHAR,
    P_year IN INT,
    P_month IN INT,
    P_company_id IN INT,
    P_division_id IN INT,
    P_branch_id IN VARCHAR,
    RCT1 IN OUT Globalpkg.RCT1
    )
    AS

    --exec prc_Attendance_summary '918,919',2008,10,79,74,'147,148'
    employeeids VARCHAR(4000);
    branchids VARCHAR(4000);


    EmpId INT;
    BranchId INT ;
    Holiday INT ;
    date1 DATE ;
    DAY INT;

    CURSOR curEmp IS

    SELECT TBL_EMPLOYEES.EmployeeId, TBL_OFFICIALS.WorkLocation_Id AS BranchId FROM TBL_EMPLOYEES
    inner Join TBL_OFFICIALS ON TBL_EMPLOYEES.EmployeeId=TBL_OFFICIALS.EmployeeId AND TBL_EMPLOYEES.ISACTIVE=0 AND TBL_EMPLOYEES.ISSETTLEMENT=0
    WHERE TBL_EMPLOYEES.EmployeeId IN (SELECT * FROM TABLE(Split(P_employeeid)))
    AND TBL_OFFICIALS.WorkLocation_Id IN (SELECT * FROM TABLE(Split(P_branch_id)));

    BEGIN


    --set @employeeids = @employeeid + ','
    --set @branchids = @branch_id + ','

    --Create Table #tblIO ( BranchId Int , EmployeeId Int , DayNo Varchar(10) , InTime DateTime , OutTime DateTime , Status Varchar(5))


    EXECUTE IMMEDIATE 'TRUNCATE TABLE tblIO';
    COMMIT;


    EmpId:= 0;
    BranchId:= 0;

    date1:=TO_DATE((CAST(P_year AS VARCHAR2) || '/' || CAST(P_month AS VARCHAR2) || '/' || '01' ),'YYYY/MM/DD');
    date1:=ADD_MONTHS(date1,1);
    date1:=date1-1;
    DAY:=TO_CHAR(date1,'DD');


    OPEN curEmp ;

    LOOP

    FETCH curEmp INTO EmpId,BranchId;

    EXIT WHEN curEmp %NOTFOUND;



    INSERT INTO TBLIO
    SELECT BranchId AS BranchId , empid AS EmployeeId , TO_CHAR(dat.DayNo,'MM/DD/YYYY') AS DayNo ,
    IOM.Inout_In AS InTime , iom.Inout_Out AS OutTime ,

    CASE
    WHEN
    IOM.Inout_In IS NULL AND (Fn_Isleave(dat.dayno,EmpId))=1 THEN 'LV'
    WHEN
    IOM.Inout_In IS NULL AND (Fn_Isholiday(dat.dayno,BranchId))=1 THEN 'HO'
    WHEN
    IOM.Inout_In IS NULL AND (Fn_Isweeklyoff(dat.dayno,BranchId))=1 THEN 'WO'
    WHEN
    IOM.Inout_In IS NULL AND (Fn_Isoutdoor(dat.dayno,EmpId))=1 THEN 'OD'
    WHEN
    IOM.Inout_In IS NULL AND (Fn_CheckJoiningDate ( dat.dayno,empid))=0 THEN 'NA'

    ELSE CASE WHEN IOM.Inout_In IS NULL THEN 'AB' ELSE 'PR' END
    END AS Status


    FROM INOUTMSTR iom
    right Join (SELECT TO_DATE(CAST(P_year AS VARCHAR2(20)) || '/' || CAST(P_month AS VARCHAR2(20))|| '/' || CAST(dayno AS VARCHAR2(20)),'YYYY/MM/DD') AS dayno FROM TBL_DAYS WHERE dayno <= DAY) dat
    ON dat.dayno = TRUNC(iom.inout_date) AND iom.employeeid = empid;


    END LOOP;

    CLOSE curEmp ;
    --Deallocate curEmp


    OPEN RCT1 FOR

    SELECT EMP.EMPLOYEEID , EMP.FirstName || ' ' || EMP.LastName AS EmployeeName,br.Name AS Branch,
    SUM(CASE WHEN INTIME IS NULL THEN 0 ELSE 1 END) AS PR,
    SUM(CASE WHEN INTIME IS NULL AND STATUS = 'AB' THEN 1 ELSE 0 END) AS AB,
    SUM(CASE WHEN INTIME IS NULL AND STATUS = 'HO' THEN 1 ELSE 0 END) AS HO,
    SUM(CASE WHEN INTIME IS NULL AND STATUS = 'WO' THEN 1 ELSE 0 END) AS WO,
    SUM(CASE WHEN INTIME IS NULL AND STATUS = 'OD' THEN 1 ELSE 0 END) AS OD,
    SUM(CASE WHEN INTIME IS NULL AND STATUS = 'LV' THEN 1 ELSE 0 END) AS LV ,
    SUM(CASE WHEN INTIME IS NULL AND STATUS = 'NA' THEN 1 ELSE 0 END) AS NA
    FROM TBLIO tio
    Inner Join TBL_EMPLOYEES emp ON tio.EmployeeId=emp.EmployeeId
    inner Join TBL_OFFICIALS offi ON emp.EmployeeId = offi.EmployeeId
    inner Join TBL_BRANCHMASTER br ON offi.Worklocation_Id=br.ID
    GROUP BY EMP.EMPLOYEEID , emp.FirstName || ' ' || emp.LastName,br.Name

    ORDER BY emp.FirstName || ' ' || emp.LastName;


    --Drop Table #tblIO

    END Prc_Attendance_Summary;
    /





    Regards
    prasad

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    So you can get it to run successfully with a certain set of parms, or it always bombs?

    BTW, you haven't shown us what you're passing into the procedure when you call it, so it's tough to tell what might be causing the problem. You seem to be thinking that it's the VARCHAR2 that's being filled to the brim. These are implicitly declared as VARCHAR2(32767), though. Are you really passing in more characters than that?

    --=Chuck

  3. #3
    Join Date
    Jun 2009
    Posts
    46
    Yes i am passing more than 1000 employeeid seprated with comma in the parameter and employeeid is start with 1000.

    Regard's
    prasad

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How can we reproduce problem & then formulate solution?
    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Why don't you push all those values into a GLOBAL TEMPORARY TABLE, and then reference that table in your procedure:

    Code:
    select *
    from table
    where id in (select id from my_table_of_ids);
    --=cf

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Unlike sql server, oracle really doesn't need temporary tables, why not simply setup a view and reference it?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    He's passing in a string which is longer than 32K characters, which is a comma-delimited list of ID's. Sounds like a good time to turn to a global temporary table to hold those values.

    I guess I could've expanded on the comment (since his example code has some commented out T-SQL) saying that "Global Temporary Tables" are persistent objects in the database, but their data is session-specific.

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I think it would be dumb, but the actual answer to your question would be to declare your comma separated list param as a CLOB.

    A far better solution would be to pass in a table of numbers.

    Code:
    CREATE TYPE ID_TABLE IS TABLE OF NUMBER(8);
    
    CREATE OR REPLACE PROCEDURE Prc_Attendance_Summary
    (
    P_employeeids IN ID_TABLE,
    P_year IN INT,
    P_month IN INT,
    P_company_id IN INT,
    P_division_id IN INT,
    P_branch_id IN ID_TABLE,
    RCT1 IN OUT Globalpkg.RCT1
    )
    That will not only solve your varchar size limit, it will eliminate all of the text parsing.

  9. #9
    Join Date
    Aug 2009
    Posts
    262
    from your procedure i assume you have came from sql-server enviroment and have been writing procedures n t-sql .

    you donot write interactive application directly in stored procedure. you do not write a bulk load of sql statements and execute them block-wise wrapped in a procedure.


    Ask Tom Home

    learn it from there.

    kindly do not take my saying as criticizing, i am only telling this for your own benefit.

  10. #10
    Join Date
    Jun 2009
    Posts
    46
    Hi,

    Yes it is true that i am converting sql SP into Oracle SP.
    and also i am not a expert in oracle,
    but i got a work a have to complete it and i m seeking a help from you to resolve the issue.

    I am sending the value in Parameter as below :

    and when i am executing procedure from application or directly from database then it is giving me error that buffor is too small.

    CREATE OR REPLACE PROCEDURE Prc_Attendance_Summary
    (
    P_employeeid IN VARCHAR:=(1001,1002,1003,1004,1005,1006,.......... .2000)
    P_year IN INT,
    P_month IN INT,
    P_company_id IN INT,
    P_division_id IN INT,
    P_branch_id IN VARCHAR,
    RCT1 IN OUT Globalpkg.RCT1
    )
    AS
    begin

    End Prc_Attendance_Summary;
    /

    Regard's
    Prasad

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    Try the above suggestion

    Code:
    CREATE OR REPLACE PROCEDURE Prc_Attendance_Summary
    ( 
    P_employeeid IN CLOB:=(1001,1002,1003,1004,1005,1006,.......... .2000) 
    P_year IN INT, 
    P_month IN INT, 
    P_company_id IN INT, 
    P_division_id IN INT, 
    P_branch_id IN VARCHAR2, 
    RCT1 IN OUT Globalpkg.RCT1 
    ) 
    AS 
    begin
    
    End Prc_Attendance_Summary;
    /

  12. #12
    Join Date
    Aug 2009
    Posts
    262
    a simple varchar to varchar2 .

    nice catch .

Posting Permissions

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