Home » SQL & PL/SQL » SQL & PL/SQL » NOT URGENT - do not answer. Please help PLS -00306  () 1 Vote
NOT URGENT - do not answer. Please help PLS -00306 [message #245170] Fri, 15 June 2007 06:41 Go to next message
kapilk
Messages: 6
Registered: June 2007
Location: delhi
Junior Member

FUNCTION biGetInstallation
(
    pvcTelephoneNo              IN  stnd_formats.telephone_no%TYPE,
    prcInstallationCursor       OUT tInstallationCursor,
    pvcUsrMsg                   OUT stnd_formats.usr_msg%TYPE,
    pvcLogMsg                   OUT stnd_formats.log_msg%TYPE
)
RETURN BINARY_INTEGER
IS
    vcTelephoneNo               stnd_formats.telephone_no%TYPE              := pvcTelephoneNo;
    vcOldTelephoneNo            stnd_formats.telephone_no%TYPE              := NULL;
    vcNewTelephoneNo            stnd_formats.telephone_no%TYPE              := NULL;
    vcInsStdCode                ins_products.ins_std_code%TYPE              := NULL;
    vcInsTel                    ins_products.ins_tel%TYPE                   := NULL;
    rcInstallationCursor        tInstallationCursor;
    vcUsrMsg                    stnd_formats.usr_msg%TYPE                   := NULL;
    vcLogMsg                    stnd_formats.log_msg%TYPE                   := NULL;
    biStatus                    BINARY_INTEGER                              := P_LIBRARY.SUCCESS;
BEGIN
    IF vcTelephoneNo   IS NULL
    THEN
        biStatus := P_LIBRARY.WARNING;
        vcUsrMsg := 'At least one query criteria must be specified';
        vcLogMsg := vcUsrMsg;
    END IF;

    IF  P_LIBRARY.SUCCESS = biStatus
    THEN
        biStatus := p_ingress.iCallOftelConvert
                    (
                        vcTelephoneNo,
                        vcOldTelephoneNo,
                        vcNewTelephoneNo,
                        vcInsStdCode,
                        vcInsTel,
                        vcUsrMsg,
                        vcLogMsg
                    );
    END IF;

    IF P_LIBRARY.SUCCESS = biStatus
    THEN
        biStatus := biInstallationCursor
                    (
                        vcInsStdCode,
                        vcInsTel,
                        rcInstallationCursor,
                        vcUsrMsg,
                        vcLogMsg
                    );
    END IF;

-- If cursor fails to open, passes control to error handler.
    prcInstallationCursor   := rcInstallationCursor;
    pvcUsrMsg               := vcUsrMsg;
    pvcLogMsg               := vcLogMsg;
    RETURN( biStatus );
EXCEPTION
    WHEN OTHERS
    THEN
        biStatus := P_LIBRARY.iReportError
                    (
                        'p_query.biGetInstallation',
                        vcUsrMsg,
                        vcLogMsg,
                        pvcTelephoneNo
                    );
        prcInstallationCursor := rcInstallationCursor;
        pvcUsrMsg       := vcUsrMsg;
        pvcLogMsg       := vcLogMsg;
        RETURN( biStatus );
END biGetInstallation;






I am callin this function as follows :-


SQL> DECLARE
  2  
  3  testw BINARY_INTEGER;
  4  UsrMsg1 stnd_formats.usr_msg%TYPE;
  5  LogMsg1 stnd_formats.log_msg%TYPE;
  6  TYPE tInstallationRec IS RECORD
  7  (
  8      vcInsId                     ins_products.ins_id%TYPE,
  9      vcInsStatus                 ins_products.ins_status%TYPE,
 10      vcInsStatusDesc             v_db_domains.disp_desc%TYPE,
 11      vcAsrNumber                 ins_products.asr_number%TYPE,
 12      vcInsAprProfileCode         ins_products.ins_apr_profile_code%TYPE,
 13      vcCustomerType              sc_lookup.customer_type%TYPE,
 14      vcInsOwnerFullname          bp_names.fullname%TYPE,
 15      vcInsConnMethod             ins_products.ins_conn_method%TYPE,
 16      vcInsContactTtlCode         bp_names.ttl_code%TYPE,
 17      vcInsContactSurname         bp_names.surname%TYPE,
 18      vcInsContactForename        bp_names.forename%TYPE,
 19      vcInsContactTelno           contact_references.cref_id%TYPE,
 20      vcInsContactEmail           contact_references.cref_id%TYPE,
 21      vcInsCAddrLine1             addresses.addr_line1%TYPE,
 22      vcInsCAddrLine2             addresses.addr_line2%TYPE,
 23      vcInsCAddrLine3             addresses.addr_line3%TYPE,
 24      vcInsCAddrLine4             addresses.addr_line4%TYPE,
 25      vcInsCAddrLine5             addresses.addr_line1%TYPE,
 26      vcFcpCode                   frequent_caller_programme.fcp_code%TYPE,
 27      vcProdCode                  ins_products.prod_code%TYPE,
 28      vcProdName                  all_products.prod_name%TYPE,
 29      vcNcosCode                  easy_access_details.ins_ncos_code%TYPE,
 30      vcNcosDesc                  indirect_feature_sets.ifs_description%TYPE,
 31      vcCpsValidationPostCode     cps_details.cpd_reply_slip_no%TYPE,
 32      vcCpsOptionsSelection       cps_details.cpd_cps_options_selection%TYPE,
 33      vcCpsComplexity             cps_details.cpd_complexity%TYPE
 34  );
 35  
 36  TYPE tInstallationCursor IS REF CURSOR RETURN tInstallationRec;
 37  
 38  cur tInstallationCursor;
 39  
 40  telno stnd_formats.telephone_no%TYPE :='01189694923';
 41  
 42  
 43  BEGIN
 44  
 45  testw:=p_query.biGetInstallation(telno,cur,UsrMsg1,LogMsg1);
 46  
 47  END;
 48  /




it is giving the following error :-



testw:=p_query.biGetInstallation(telno,cur,UsrMsg1,LogMsg1);
*
ERROR at line 45:
ORA-06550: line 45, column 8:
PLS-00306: wrong number or types of arguments in call to 'BIGETINSTALLATION'
ORA-06550: line 45, column 1:
PL/SQL: Statement ignored

[Updated on: Fri, 15 June 2007 08:19] by Moderator

Report message to a moderator

Re: URGENT Please help PLS -00306 [message #245173 is a reply to message #245170] Fri, 15 June 2007 06:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Your second parameter is not of the same type.

MHE
Re: URGENT Please help PLS -00306 [message #245175 is a reply to message #245173] Fri, 15 June 2007 06:58 Go to previous messageGo to next message
kapilk
Messages: 6
Registered: June 2007
Location: delhi
Junior Member

The second parameter is tInstallationCursor and thats what I have declared "cur" as
Re: URGENT Please help PLS -00306 [message #245186 is a reply to message #245170] Fri, 15 June 2007 07:23 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
biGetInstallation cannot know about a cursor type declared in an anonymous PL/SQL block. It will have its own definition and you must use that.

In fact, I am not sure how you are getting your function to compile, since I can't see where tInstallationCursor is declared. Is it a standalone function or part of a package ? If so, the declaration for tInstallationCursor must be somewhere in the package and you should refer to that in the anonymous block e.g.

cur package.tInstallationCursor;

This will ensure the types match.
Re: URGENT Please help PLS -00306 [message #245194 is a reply to message #245186] Fri, 15 June 2007 07:31 Go to previous messageGo to next message
kapilk
Messages: 6
Registered: June 2007
Location: delhi
Junior Member

Thanks a lot Cthulhu!!!!!
Thats how I should have done it.
I have used
cur p_query.tInstallationCursor;
and this worked!!!!

But even if I declare it in the anonymous block then why it didnt worked????
Re: URGENT Please help PLS -00306 [message #245198 is a reply to message #245170] Fri, 15 June 2007 07:37 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The definition in the anonymous block is local to that program. Even if it matches column for column the declaration of the cursor in the package, they are still two different things and Oracle will not match them up.
Re: URGENT Please help PLS -00306 [message #245201 is a reply to message #245198] Fri, 15 June 2007 07:38 Go to previous messageGo to next message
kapilk
Messages: 6
Registered: June 2007
Location: delhi
Junior Member

ok!!!
thanks
Re: URGENT Please help PLS -00306 [message #245203 is a reply to message #245198] Fri, 15 June 2007 07:48 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Cthulhu wrote on Fri, 15 June 2007 14:37
they are still two different things and Oracle will not match them up.

Just like I said: different types.

MHE
Previous Topic: Format a query
Next Topic: Select from DUAL OR Assign Variable ??
Goto Forum:
  


Current Time: Sat Dec 10 12:37:33 CST 2016

Total time taken to generate the page: 0.08716 seconds