Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Unanswered: How to call a package from VB6?

    Error statement: wrong number or types of arguments in call ( error when i call package below in VB6)

    ---------- Declare Type Object Table ---------------
    CREATE OR REPLACE TYPE OOS_Percentage_Table AS OBJECT(
    categoryid varchar2(30),
    store_OOS varchar2(30),
    ketqua number);

    --------- Declare Type Table ------------
    CREATE OR REPLACE TYPE OOS_Percentage_Table_Temp IS TABLE OF OOS_Percentage_Table;


    ------- Create Package ---------
    CREATE OR REPLACE PACKAGE oos_pkg
    AS
    --type oos_cursor is ref cursor;

    TYPE OOS_REF IS REF CURSOR;


    PROCEDURE Prc_OOS_Percentage(mstore IN varchar2,mdatefrom IN varchar2,mdateto IN varchar2,param OUT OOS_REF);

    END oos_pkg;


    --------- Create Package Body -----------------
    CREATE OR REPLACE PACKAGE BODY oos_pkg
    AS
    PROCEDURE Prc_OOS_Percentage(mstore IN varchar2,mdatefrom IN varchar2,mdateto IN varchar2,param OUT OOS_REF)
    IS
    l_data_cursor OOS_Percentage_Table_Temp:= OOS_Percentage_Table_Temp(); -- Khai bao bien va khoi tao gia tri ban dau.
    CURSOR cat_cursor IS SELECT mc_id FROM nav_category; --declare cat_cursor
    mcat varchar2(30); -- Khai bao bien.
    mcatname varchar2(30); -- Khai bao bien.
    nreason1 number; -- Khai bao bien.
    nreason2 number; -- Khai bao bien.
    ntongreason1 number; -- Khai bao bien.
    ntongreason2 number; -- Khai bao bien.
    mketqua number; -- Khai bao bien.
    BEGIN

    OPEN cat_cursor; -- Open Cat_cursor.
    mketqua:=0; -- gan gia tri 0 cho bien ket qua

    -- neu nhu khong co gia tri mdateto >> Daily Report.
    IF mdateto IS NULL THEN

    LOOP
    l_data_cursor.EXTEND; -- cap phat buong nho.
    FETCH cat_cursor INTO mcatname; -- fetch cat_cursor vao mcat, mcatname.
    EXIT WHEN cat_cursor%NOTFOUND; -- Dieu kien ngat.

    SELECT count(*) INTO nreason1 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND mc_id=mcatname
    AND OOS_M Not In ('R1','R2')
    AND OOS_M IS NOT NULL
    AND OOS_M LIKE 'R%'
    AND D_CHECK=to_date(mdatefrom,'dd-mm-yyyy');


    SELECT count(*) INTO nreason2 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND mc_id=mcatname
    AND OOS_A Not In ('R1','R2')
    AND OOS_A IS NOT NULL
    AND OOS_A LIKE 'R%'
    AND D_CHECK=to_date(mdatefrom,'dd-mm-yyyy');

    SELECT count(*) INTO ntongreason1 FROM nav_oos_REPORTS
    WHERE (S_ID=mstore)
    AND mc_id=mcatname
    AND OOS_M IS NOT NULL
    AND (D_CHECK=to_date(mdatefrom,'dd-mm-yyyy'));


    SELECT count(*) INTO ntongreason2 FROM nav_oos_REPORTS
    WHERE (S_ID=mstore)
    AND mc_id=mcatname
    AND OOS_A IS NOT NULL
    AND (D_CHECK=to_date(mdatefrom,'dd-mm-yyyy'));

    IF nreason1 = 0 AND nreason2 = 0 THEN
    mketqua:= 0;
    ELSE
    mketqua := (nreason1 + nreason2) / (ntongreason1 + ntongreason2);
    END IF;
    l_data_cursor(l_data_cursor.count):= OOS_Percentage_Table(mcatname, 'OOS%', mketqua);
    END LOOP;

    ELSE -- neu nhu co gia tri dateto >>> Weekly report
    loop
    l_data_cursor.EXTEND;
    FETCH cat_cursor INTO mcatname;
    EXIT WHEN cat_cursor%NOTFOUND; -- dieu kien ket thuc fetch data_cursor

    SELECT count(*) INTO nreason1 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND mc_id=mcatname
    AND OOS_M Not In ('R1','R2')
    AND OOS_M IS NOT NULL
    AND OOS_M LIKE 'R%'
    AND D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy');

    SELECT count(*) INTO nreason2 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND mc_id=mcatname
    AND OOS_A Not In ('R1','R2')
    AND OOS_A IS NOT NULL
    AND OOS_A LIKE 'R%'
    AND D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy');

    SELECT count(*) INTO ntongreason1 FROM nav_oos_REPORTS
    WHERE (S_ID=mstore)
    AND mc_id=mcatname
    AND OOS_M IS NOT NULL
    AND (D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy'));

    SELECT count(*) INTO ntongreason2 FROM nav_oos_REPORTS
    WHERE (S_ID=mstore)
    AND mc_id=mcatname
    AND OOS_A IS NOT NULL
    AND (D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy'));

    IF nreason1 = 0 AND nreason2 = 0 THEN
    mketqua:= 0;

    ELSE
    mketqua := (nreason1 + nreason2) / (ntongreason1 + ntongreason2);
    END IF;
    l_data_cursor(l_data_cursor.count):= OOS_Percentage_Table(mcatname, mstore, mketqua);
    -- exit when.
    END LOOP;

    SELECT count(*) INTO nreason1 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND OOS_M Not In ('R1','R2')
    AND OOS_M IS NOT NULL
    AND OOS_M LIKE 'R%'
    AND D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy');

    SELECT count(*) INTO nreason2 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND OOS_A Not In ('R1','R2')
    AND OOS_A IS NOT NULL
    AND OOS_A LIKE 'R%'
    AND D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy');

    SELECT count(*) INTO ntongreason1 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND OOS_M IS NOT NULL
    AND D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy');

    SELECT count(*) INTO ntongreason2 FROM nav_oos_REPORTS
    WHERE S_ID=mstore
    AND OOS_A IS NOT NULL
    AND D_CHECK>=to_date(mdatefrom,'dd-mm-yyyy')
    AND D_CHECK<=to_date(mdateto,'dd-mm-yyyy');

    IF nTongReason1 = 0 AND nTongReason2 = 0 THEN
    mketqua := 0;
    ELSE
    mketqua := (nReason1 + nReason2) / (nTongReason1 + nTongReason2);

    END IF;
    l_data_cursor(l_data_cursor.count):= OOS_Percentage_Table('TOTAL', mstore, mketqua);
    END IF;
    CLOSE cat_cursor;
    OPEN param FOR SELECT * FROM TABLE(CAST(l_data_cursor AS OOS_Percentage_Table_Temp));
    END Prc_OOS_Percentage;
    END oos_pkg;

    I want to get a recordset for right result. I compiled this package successfully. Please, help me implement it!

    thanks!

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    But show us how far have you got it in VB6, show us what you're using and what you've tried.. like, they are a couple of ways of doing this in VB6, show us what you are using...

Posting Permissions

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