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

execution of plsql proc

DECLARE
    o_rc       NUMBER;
    o_reason   VARCHAR2(200);
BEGIN
    DBMS_OUTPUT.enable(100000);
    cq_pdr_send_email_notify.pdr_send_email_pr('pdr_program',
                                               1699380,
                                               'DEAL_EXPIRE_5_DAYS',
                                               1,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL
                                              );

    DBMS_OUTPUT.put_line(o_rc || o_reason);
END;
after running this im getting the error
Execution (10: 5): ORA-06550: line 6, column 5:
PLS-00201: identifier 'CQ_PDR_SEND_EMAIL_NOTIFY.PDR_SEND_EMAIL_PR' must be declared
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
0
thota198
Asked:
thota198
  • 8
  • 5
1 Solution
 
sdstuberCommented:
either the procedure doesn't exist

or you don't have execute privilege to run it
0
 
thota198Author Commented:
i have entered the wrong pkg name but changed it and run DECLARE
    o_rc       NUMBER;
    o_reason   VARCHAR2(200);
BEGIN
    DBMS_OUTPUT.enable(100000);
    cq_pdr_send_email_notify_pkg.pdr_send_email_pr('pdr_program',
                                               1699380,
                                               'DEAL_EXPIRE_5_DAYS',
                                               1,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL
                                              );

    DBMS_OUTPUT.put_line(o_rc || o_reason);
END;

its showing the error
Execution (10: 52): ORA-06550: line 6, column 52:
PLS-00363: expression 'pdr_program' cannot be used as an assignment target
ORA-06550: line 7, column 48:
PLS-00363: expression '1699380' cannot be used as an assignment target
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
0
 
sdstuberCommented:
the first and second parameters of your procedure must be OUT parameters

declare a variable for those, assign the values you have and then pass the variables rather than the literals.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
thota198Author Commented:
DECLARE
    o_rc       NUMBER;
    o_reason   VARCHAR2(200);
    o_rc := c_zero;
    o_reason := c_success;
BEGIN
     
    DBMS_OUTPUT.enable(100000);
    cq_pdr_send_email_notify_pkg.pdr_send_email_pr(c_zero, c_success, 'pdr_program',
                                               1699380,
                                               'DEAL_EXPIRE_5_DAYS',
                                               1,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL
                                              );

    DBMS_OUTPUT.put_line(o_rc || o_reason);
END;

after i run i get the error
Execution (8: 10): ORA-06550: line 4, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table LONG_ double ref
   char time timestamp interval date binary national character
   nchar
The symbol "<an identifier>" was substituted for "=" to continue.
ORA-06550: line 5, column 14:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table LONG_ double ref
   char time timestamp interval date binary national charac
0
 
sdstuberCommented:
either put the assignments with the declarations

or in the executable portion of the block (i.e. after the begin)

DECLARE
    o_rc       NUMBER := c_zero;
    o_reason   VARCHAR2(200) := c_success;
BEGIN


or something like this....

DECLARE
    o_rc       NUMBER;
    o_reason   VARCHAR2(200);
BEGIN
    o_rc := c_zero;
    o_reason := c_success;


your next error will be the c_zero and c_success constants aren't defined

0
 
thota198Author Commented:


DECLARE
    o_rc       NUMBER := c_zero;
    o_reason   VARCHAR2(200) :=c_success;
 
i get this error after i declared the variables as u suggested in the beginning
Execution (6: 26): ORA-06550: line 2, column 26:
PLS-00201: identifier 'C_ZERO' must be declared
ORA-06550: line 2, column 16:
PL/SQL: Item ignored
ORA-06550: line 3, column 32:
PLS-00201: identifier 'C_SUCCESS' must be declared
ORA-06550: line 3, column 16:
PL/SQL: Item ignored
ORA-06550: line 8, column 52:
PLS-00201: identifier 'C_ZERO' must be declared
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored
ORA-06550: line 20, column 26:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 20, column 5:
PL/SQL: Statement ignored
0
 
sdstuberCommented:
as noted in the previous post,  you have not defined your constants

you must declare them prior to assigning them to a variable
0
 
HainKurtSr. System AnalystCommented:
please post the declaration part of this sp

cq_pdr_send_email_notify_pkg.pdr_send_email_pr(

(is it really sp, may be it is a function?)
0
 
sdstuberCommented:
constants are declared like this..


c_myconstant   CONSTANT INTEGER := 1234;
c_my_other_constant  CONSTANT varchar2(4) := 'test'
0
 
sdstuberCommented:
HainKurt,

if it was a function they would have gotten something like this...


ORA-06550: line 2, column 1:
PLS-00221: 'CQ_PDR_SEND_EMAIL_NOTIFY_PKG.PDR_SEND_EMAIL_PR' is not a procedure or is undefined
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored



0
 
thota198Author Commented:
DECLARE
    c_zero constant integer := 0;
    c_success constant varchar(10) := 'success';
    o_rc       NUMBER := c_zero;
    o_reason   VARCHAR2(200) :=c_success;
 
BEGIN
     
    DBMS_OUTPUT.enable(100000);
    cq_pdr_send_email_notify_pkg.pdr_send_email_pr(o_rc, o_reason, 'pdr_program',
                                               1699380,
                                               'DEAL_EXPIRE_5_DAYS',
                                               1,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL
                                              );

    DBMS_OUTPUT.put_line(o_rc || o_reason);
END;
 the above proc was generated successfully how can i check the output
0
 
sdstuberCommented:
enable dbms_output capture and run the procedure

if using sql*plus

SQL> set serveroutput on


if using some other tool then it will depend on that tool

0
 
sdstuberCommented:
also note, the above code doesn't create a procedure

it's an anonymous pl/sql block
0
 
thota198Author Commented:
i run the proc in sqlplus its showing

100ORA-01403: no data found

PL/SQL procedure successfully completed
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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