Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PL/SQL PROCEDURE TOAD

Posted on 2013-11-09
10
Medium Priority
?
490 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

581 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