1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Calling a package from Oracle

Discussion in 'SQL PL/SQL' started by Aidanor89, May 21, 2014.

  1. Aidanor89

    Aidanor89 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    I have a package in a db called SP_PACKAGE_EXAMPLE
    (Report_Start_Date IN VARCHAR2,
    Report_End_Date IN VARCHAR2,
    ret_cur_OUT OUT curdata)

    If I wanted to write a sql script to call this procedure what argument to I put in for the ret_cur_OUT?

    I know I can use 2 dates for the first 2 arguments.

    Thanks
    Aidan
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    You have to declare a variable having the same type as your OUT argument (in your case "curdata"), for example:

    Code (SQL):
    DECLARE
    l_start_date DATE;
    l_end_date   DATE;
    l_data         curdata;

    BEGIN
    --
    SP_PACKAGE_EXAMPLE(
     l_start_date,
    l_end_date,
    l_data
    );
    --

    END;
    /
    Regards,
    Rajen.
     
  3. Aidanor89

    Aidanor89 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for you reply Rajen.

    I have tried the below but it fails, am I doing something wrong?
    I am very new to pl sql so apologizes for my ignorance.
    Code (SQL):

    DECLARE
    l_start_date DATE;
    l_end_date   DATE;
    l_data         OUT curdata;
     
    BEGIN
    --
    SP_PACKAGE_EXAMPLE(
    '12/may/2014',
    '18/may/2014',
    l_data
    );
    --
     
    END;
    /
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    What error are you getting ?

    I suppose it's linked to the 2 "dates" which you are passing as parameter which are in fact characters and the date format must be incompatible.

    Try TO_DATE('12/may/2014','DD/mon/YYYY') instead - same for the other one.
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    There is no way that would work. You cannot declare l_date to be of type curdata w/o defining what curdata actually is.

    Oracle provides predefined types such as number, varchar, date, etc... and then you have the ability to define your own types, typically for collections. In this case, given the name, curdata would appear to be for a cursor. I would suggest you pick up a book about PL/SQL and read the portions concerning typing and variable declaration. I would recommend "Oracle PL/SQL Programming" via O'Reilly Press.

    If you need to persist in finding out how to define curdata in your Anonymous PL/SQL block...w/o any documentation of the package you're calling...then you'll need to get a hold of the source code of the Package spec/body and look at how curdata is being populated and w/ what. Thay way, it may be possible to figure out what the type definition needs to be.

    HTH

    CJ
     
  6. Aidanor89

    Aidanor89 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I am getting this error when I run the below;
    Code (SQL):
    DECLARE
    l_start_date DATE;
    l_end_date   DATE;
    l_data         curdata;
     
    BEGIN
    --
    pkg_sales.SP_PACKAGE_PMI(
    TO_DATE('12/may/2014','DD/mon/YYYY'),
    TO_DATE('18/may/2014','DD/mon/YYYY'),
    l_data
    );
    --
     
    END;
    /
    [1]: (Error): ORA-06550: line 4, column 16: PLS-00201: identifier 'CURDATA' must be declared ORA-06550: line 4, column 16: PL/SQL: Item ignored ORA-06550: line 11, column 1: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 8, column 1: PL/SQL: Statement ignored
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What it is type curdata and where it is described, in a packet
    pkg_sales?
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    l_data could have been declared as:

    if "curdata" had been a user-defined type (object, collection, ...).
    However, given the error reported (post #6), this is not the case.

    So another option might be a TYPE defined in the Package specs, in which case use the following declaration:
    But I fully agree with CJ on the need to investigate HOW is "curdata" defined ... if you want to use the output of the Packaged procedure in some way which is the purpose of this post (I hope so anyway ...).
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It ISN'T defined which is the source of the problem. Here is what the code should be:


    Code (SQL):

    DECLARE
    l_start_date DATE;
    l_end_date   DATE;
    TYPE curdata IS REF cursor;
    l_data         curdata;
     
    BEGIN
    --
    pkg_sales.SP_PACKAGE_PMI(
    TO_DATE('12/may/2014','DD/mon/YYYY'),
    TO_DATE('18/may/2014','DD/mon/YYYY'),
    l_data
    );
    --
     
    END;
    /
     
     
    Aidanor89 likes this.
  10. Aidanor89

    Aidanor89 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Your solution worked, thank you