PL/SQL How to Call another Procedure

Question

I'm Having problem in calling my DateTimeSP. My error Log:

PLS:00201 identifier 'Call.DateTimeSP' must be declared; statement ignored..

What's wrong with my calling statement, is my format right?

CREATE OR REPLACE
PROCEDURE "DeviceAssignSP" (iMachine IN VARCHAR2, oDevice OUT VARCHAR2, oRDO OUT VARCHAR2, oRCO OUT VARCHAR2, oUID OUT VARCHAR2, oDate OUT VARCHAR2, oTime OUT VARCHAR2, oSession OUT VARCHAR2, oNow OUT VARCHAR2)
AS
BEGIN

    SELECT a.DEVICE_CODE, a.RDO_CODE, a.RCO_CODE, b.USER_ID 
        INTO oDevice, oRDO, oRCO, oUID 
            FROM tbl_user_assign_device a 
                INNER JOIN tbl_user_information b ON a.PERSONNEL_NO = b.PERSONNEL_NO
                    WHERE DEVICE_CODE = iMachine AND USRDEVICE_STATUS = 'A' AND b.USER_STATUS = 'A' AND a.RDO_CODE = b.RDO_CODE AND rownum <= 1;

    IF oRDO IS NULL AND oRCO IS NULL THEN

        SELECT a.DEVICE_CODE, '000', '000000', b.USER_ID
            INTO oDevice, oRDO, oRCO, oUID
                FROM tbl_user_assign_device a 
                    INNER JOIN tbl_user_information b ON a.PERSONNEL_NO = b.PERSONNEL_NO
                        WHERE DEVICE_CODE = iMachine AND USRDEVICE_STATUS = 'A' AND b.USER_STATUS = 'A' AND rownum <= 1;

    ELSIF oRCO IS NULL THEN

        SELECT a.DEVICE_CODE, a.RDO_CODE, '000000', b.USER_ID
            INTO oDevice, oRDO, oRCO, oUID
                FROM tbl_user_assign_device a 
                    INNER JOIN tbl_user_information b ON a.PERSONNEL_NO = b.PERSONNEL_NO
                        WHERE DEVICE_CODE = iMachine AND USRDEVICE_STATUS = 'A' AND b.USER_STATUS = 'A' AND a.RDO_CODE = b.RDO_CODE AND rownum <= 1;

    END IF;

    SELECT SEST_VAL 
        INTO oSession 
            FROM tbl_session_timeout 
                WHERE SEST_STATUS = 'A';

 Call."DateTimeSP"('DATE', oDate);

 Call."DateTimeSP"('TIME', oTime);

 Call."DateTimeSP"('NOWFORMAT', oNow);

END;

the DateTimeSP is outside of the procedure.. how can i implement that call? — DreamBigAlvin, Dec 04 '13 at 00:41
Did you try call your procedure like: call DateTimeSP('DATE', oDate); — Alexander Myshov, Dec 04 '13 at 01:45
i also tried that and an error log PL: 00103 DATETIMESP expecting an character symbol... — DreamBigAlvin, Dec 04 '13 at 03:22

score 1 · Accepted Answer · edited Dec 04 '13 at 05:48

You don't need call to call one stored procedure from another.

CREATE OR REPLACE
PROCEDURE "DeviceAssignSP" (
  iMachine IN VARCHAR2, 
  oDevice OUT VARCHAR2, 
  oRDO OUT VARCHAR2, 
  oRCO OUT VARCHAR2, 
  oUID OUT VARCHAR2, 
  oDate OUT VARCHAR2, 
  oTime OUT VARCHAR2, 
  oSession OUT VARCHAR2, 
  oNow OUT VARCHAR2)
AS
BEGIN
  <<your SQL statements>>

  "DateTimeSp"( 'DATE', oDate );
  "DateTimeSp"( 'TIME', oTime);
  "DateTimeSp"( 'NOWFORMAT', oNow );
END;

Since you appear to be new to PL/SQL, a few comments, though...

Using case sensitive identifiers (procedure names in double quotes) is very uncommon. SQL and PL/SQL generally intended to be case-insensitive. It's perfectly reasonable to use mixed case by convention when naming and calling procedures but forcing everyone to use case-sensitive identifiers and to litter their code with double-quotes is generally less than ideal.
The formatting of you SQL statements is unique. And that's probably not a good thing. There are a few different conventions for how to format SQL in PL/SQL-- using any of them would make more sense than just indenting every line more and more.
A procedure that has 8 output parameters seems problematic-- it's very easy to forget or mix-up the order of that many parameters. Maybe you want to return a record type or an object type?
A procedure should modify data. If you are just doing calculations, those generally belong in functions (which makes it even more likely that you want a function that returns a record or an object rather than a function with a bunch of OUT parameters).

PL/SQL How to Call another Procedure

1 Answers1