• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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
0
IlyaLibov
Asked:
IlyaLibov
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;-)
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Hi, can you try this (for testing):

 infrmactvdt       IN DATE,
 intoactvdt        IN DATE,

does it compile?
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark GeerlingsDatabase AdministratorCommented:
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?
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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.
0
 
IlyaLibovAuthor Commented:
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
0
 
IlyaLibovAuthor Commented:
Also when I tryed to follow Alex1e0181 advise  
use IN DATE as data type procedure was not compiled
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Could you please provide the entire function?!
0
 
IlyaLibovAuthor Commented:
This procedure apparently is working if you call it from junit. It is okay for me now.

Thanks.
0
 
IlyaLibovAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now