Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Unanswered: help in Stored Procedure

    Hi, I am new to Stored Procedures.

    I am getting an error:
    PLS-00306: wrong number or types of arguments in call to '*'

    I have a stored Procedure like this:

    CREATE OR REPLACE PROCEDURE TESTSP IS
    tmpVar NUMBER;
    tmpVar1 NUMBER;
    tmpVar2 NUMBER;
    tmpVar3 LONG;
    tmpVar4 LONG;
    BEGIN
    tmpVar := 0;
    select count(user_id) into tmpVar from oradba.up_user where date_created between '01-JAN-2004' and '31-JAN-2004';
    select count(user_id) into tmpVar1 from oradba.up_user where date_created between '01-DEC-2003' and '31-DEC-2003';
    tmpVar2 := (tmpVar - tmpVar1);
    tmpVar3 := (tmpVar2/tmpVar);
    tmpVar4 := tmpVar3*100;
    DBMS_OUTPUT.PUT_LINE(tmpVar);
    DBMS_OUTPUT.PUT_LINE(tmpVar1);
    DBMS_OUTPUT.PUT_LINE(tmpVar2);
    DBMS_OUTPUT.PUT_LINE(tmpVar3);
    DBMS_OUTPUT.PUT_LINE(tmpVar4);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    Null;
    END TESTSP;

    What's the problem? Can anybody show me an efficient way of doing this SP?

    Thanks a bunch

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: help in Stored Procedure

    Originally posted by durbhakula
    Hi, I am new to Stored Procedures.

    I am getting an error:
    PLS-00306: wrong number or types of arguments in call to '*'

    I have a stored Procedure like this:

    CREATE OR REPLACE PROCEDURE TESTSP IS
    tmpVar NUMBER;
    tmpVar1 NUMBER;
    tmpVar2 NUMBER;
    tmpVar3 LONG;
    tmpVar4 LONG;
    BEGIN
    tmpVar := 0;
    select count(user_id) into tmpVar from oradba.up_user where date_created between '01-JAN-2004' and '31-JAN-2004';
    select count(user_id) into tmpVar1 from oradba.up_user where date_created between '01-DEC-2003' and '31-DEC-2003';
    tmpVar2 := (tmpVar - tmpVar1);
    tmpVar3 := (tmpVar2/tmpVar);
    tmpVar4 := tmpVar3*100;
    DBMS_OUTPUT.PUT_LINE(tmpVar);
    DBMS_OUTPUT.PUT_LINE(tmpVar1);
    DBMS_OUTPUT.PUT_LINE(tmpVar2);
    DBMS_OUTPUT.PUT_LINE(tmpVar3);
    DBMS_OUTPUT.PUT_LINE(tmpVar4);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    Null;
    END TESTSP;

    What's the problem? Can anybody show me an efficient way of doing this SP?

    Thanks a bunch

    Why is tmpvar3 and 4 declared as long? If tmpvar3 is the result of a division, it should be smaller than tmpvar2. I think you are running into problems trying to multiply a long column.

    Either do a to_number(tmpvar3) call
    or declare it as a number.

    Thats my take on that.

  3. #3
    Join Date
    Feb 2004
    Posts
    35
    GREAT..That was it..Thank You

    I put all my expression into 1 line now..

    CREATE OR REPLACE PROCEDURE TESTSP IS
    tmpVar NUMBER;
    tmpVar1 NUMBER;
    tmpVar2 NUMBER;

    BEGIN
    tmpVar := 0;
    select count(user_id) into tmpVar from oradba.up_user where date_created between '01-JAN-2004' and '31-JAN-2004';
    select count(user_id) into tmpVar1 from oradba.up_user where date_created between '01-DEC-2003' and '31-DEC-2003';
    tmpVar2 := ((tmpVar - tmpVar1)/tmpVar) * 100;

    DBMS_OUTPUT.PUT_LINE(tmpVar2);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    Null;
    END TESTSP;

Posting Permissions

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