asked on

PL/SQL PROCEDURE TOAD

Trying to check store procedure with TOAD and got the following error

ORA-06550: line 3, column 14:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 3, column 14:
PL/SQL: Item ignored
Here are the first lines of the function code

FUNCTION getactivitytypesummary_f (
inwrkrstdid IN crm_ref.wrkr.wrkr_std_id%TYPE,
infrmactvdt IN crm.actv.actv_dt%TYPE,
intoactvdt IN crm.actv.actv_dt%TYPE,
InActvNatrCd IN VARCHAR2,
InActvTypeCd IN VARCHAR2,
indstrfyifilter IN VARCHAR2 DEFAULT 'N' ,
inuserprofileid IN crm_ref.wrkr.wrkr_std_id%TYPE,
inownergrp IN VARCHAR2 DEFAULT NULL,
intrackingid IN VARCHAR2,
inmaxrow IN VARCHAR2,
inscope IN VARCHAR2,
inactvdescsize IN VARCHAR2

The type of the crm.actv.actv_dt%TYPE, is DATE
I am sending the following parameters
INFRMACTVDT := TO_DATE('05-MAY-08', 'DD-MON-RR');

Thanks

Alex [***Alex140181***]

I don't think "ORA-06550: line 3, column 14:" refers to the 3rd line of the parameters. Better look for line 3 within the executable block.
So we need a bit more of your function ;-)

Gerwin Jansen

Hi, can you try this (for testing):

infrmactvdt IN DATE,
intoactvdt IN DATE,

does it compile?

Alex [***Alex140181***]

Hi, can you try this (for testing):

infrmactvdt IN DATE,
intoactvdt IN DATE,

does it compile?

if not, then I suppose the schema you're logged into does not know about the crm table (check privileges)

Mark Geerlings

I've often used the "%type" declarations for local variables inside a PL\SQL function or procedure, but I've never tried using that syntax to provide the datatype for an "in" parameter. I'm not sure that Oracle supports using "[table_name].[column_name]%type" in that context.

Which version of the Oracle database do you have?

Alex [***Alex140181***]

I'm not sure that Oracle supports using "[table_name].[column_name]%type" in that context.

No need to google and/or check this: Oracle DOES support this kind of parameter declaration.

IlyaLibov

ASKER

This is working procedure. I just have a problem to test it. I also not sure that line that I could find in the error is right. I mean line 3 column 14

IlyaLibov

ASKER

Also when I tryed to follow Alex1e0181 advise
use IN DATE as data type procedure was not compiled

Alex [***Alex140181***]

Could you please provide the entire function?!

ASKER CERTIFIED SOLUTION

IlyaLibov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IlyaLibov

ASKER

This procedure does not work if I run it from Toad but it is working from junit. probably in Toad i am using wrong parameters. But for now it does not matter for me.

Thanks