oraclebindprocedure

Sending parameters to a stored procedure - PLS-00049


create or replace PROCEDURE TABULA.GetTorimByDoctor (
   DOCTORID IN CHAR,
   CUSTOMERID IN CHAR,
   DEFTYPE IN CHAR,
   p_out_rec        OUT sys_refcursor
) AS
BEGIN
   SELECT
    to_date(TO_DATE('01-01-1988 00:00', 'dd-mm-yyyy hh24:mi') + numtodsinterval(w.curdate, 'minute'),'dd-mm-yyyy') AS qdate,
    to_char(trunc(sysdate) + h.stime / 24 / 60, 'hh24:mi') AS stime,
    to_char(trunc(sysdate) +(h.stime +(
        CASE
            WHEN((macdent$mac_cyaskill.durdef <> 0)) THEN
                (macdent$mac_cyaskill.durdef)
...

I get an error of

LINE/COL  ERROR
--------- -------------------------------------------------------------
80/27     PLS-00049: 'DOCTORID'  bad bind variable
126/27    PLS-00049: 'CUSTOMERID' bad bind variable
127/23    PLS-00049: 'DEFTYPE'  bad bind variable
Errors: check compiler log

Solution

  • Who knows, you didn't post the whole code, but I suspect that you preceded parameters' names with a colon within the procedure (lines 80, 126, 127).

    Here's an illustration of what you (probably) did:

    SQL> CREATE OR REPLACE PROCEDURE gettorimbydoctor (doctorid IN CHAR)
      2  AS
      3     l_cnt  NUMBER;
      4  BEGIN
      5     SELECT COUNT (*)
      6       INTO l_cnt
      7       FROM DUAL
      8      WHERE 'a' = :doctorid;         --> colon here
      9  END;
     10  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show err
    Errors for PROCEDURE GETTORIMBYDOCTOR:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/17     PLS-00049: bad bind variable 'DOCTORID'
    SQL>
    

    If that's so, remove colon:

    SQL> CREATE OR REPLACE PROCEDURE gettorimbydoctor (doctorid IN CHAR)
      2  AS
      3     l_cnt  NUMBER;
      4  BEGIN
      5     SELECT COUNT (*)
      6       INTO l_cnt
      7       FROM DUAL
      8      WHERE 'a' = doctorid;
      9  END;
     10  /
    
    Procedure created.
    
    SQL>