Solved

PL/SQL PROCEDURE TOAD

Posted on 2013-11-09
10
478 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 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 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

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.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

821 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