Solved

PL/SQL PROCEDURE TOAD

Posted on 2013-11-09
10
477 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
10 Comments
 
LVL 13

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 37

Expert Comment

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

 infrmactvdt       IN DATE,
 intoactvdt        IN DATE,

does it compile?
0
 
LVL 13

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 13

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 13

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now