[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

PL/SQL PROCEDURE TOAD

Posted on 2013-11-09
10
Medium Priority
?
486 Views
Last Modified: 2013-12-05
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
Comment
Question by:IlyaLibov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39636049
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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39636082
Hi, can you try this (for testing):

 infrmactvdt       IN DATE,
 intoactvdt        IN DATE,

does it compile?
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39636161
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39636449
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39637312
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
 

Author Comment

by:IlyaLibov
ID: 39640525
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
 

Author Comment

by:IlyaLibov
ID: 39640531
Also when I tryed to follow Alex1e0181 advise  
use IN DATE as data type procedure was not compiled
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39640896
Could you please provide the entire function?!
0
 

Accepted Solution

by:
IlyaLibov earned 0 total points
ID: 39687520
This procedure apparently is working if you call it from junit. It is okay for me now.

Thanks.
0
 

Author Closing Comment

by:IlyaLibov
ID: 39697792
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question