title: Bind variable "D" not declared
body: <p>I am trying to run a sql file in sqlplus. This sql file simply adds a package and some stored procedures to the database. It is written using the specification/body separation convention. However, it appears that the package declaration part is problematic, leading to the body part failing. In fact, I am not sure if the "Binding variable" part is even relevant. Any help would be appreciated, thanks. </p>
  
  <pre><code>SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 20 11:41:36 2017
  
  Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  
  
  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  and Real Application Testing options
  
  SQL&gt; set timing on;
  SQL&gt; set exitcommit off;
  SQL&gt; 
  SQL&gt; REM Output Log
  SQL&gt; REM All script output is SPOOLed here.
  SQL&gt; REM Recommend not to change or use SPOOL in other locations
  SQL&gt; REM If you do break the spool chain, then consider using SPOOL APPEND to reestablish
  SQL&gt; 
  SQL&gt; PROMPT ------------------------------------------------------------;
  ------------------------------------------------------------
  SQL&gt; PROMPT PROCESSING: D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\Create_table.sql;
  PROCESSING: D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\Create_table.sql
  SQL&gt; PROMPT ------------------------------------------------------------;
  ------------------------------------------------------------
  SQL&gt; PROMPT COMMAND: @"D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\Create_table.sql" ;
  COMMAND: @"D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\Create_table.sql"
  SQL&gt; TIMING START;
  SQL&gt; @"D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\Create_table.sql" ;
  SQL&gt; DECLARE
    2    L_CNT PLS_INTEGER;
    3    lv_stmt varchar2(32767);
    4  BEGIN
    5    --Check if the table already exists
    6    SELECT COUNT(0)
    7    INTO L_CNT
    8    FROM ALL_TABLES T
    9    WHERE T.TABLE_NAME = 'DEVOPS_UC_SERVER_REG_TABLE'
   10        AND T.OWNER = 'IT';
   11    IF L_CNT &gt; 0 THEN
   12       dbms_output.put_line('The table already exists');
   13      RETURN;
   14    END IF;
   15  
   16    --Check if the constraint already exists
   17    SELECT COUNT(0)
   18    INTO L_CNT
   19    FROM ALL_CONSTRAINTS C
   20    WHERE C.CONSTRAINT_NAME = 'DEVOPS_UC_Server_Reg_PKEY'
   21        AND C.OWNER = 'IT';
   22    IF L_CNT &gt; 0 THEN
   23       dbms_output.put_line('The primary key constraint already exists');
   24      RETURN;
   25    END IF;
   26  
   27    dbms_output.put_line('Creating table...');
   28  
   29  
   30    /*
   31   this code is auto-generated from DBMS_METADATA. It was accessed using the following query:
   32  
   33  SELECT
   34  DBMS_METADATA.GET_DDL( 'TABLE','DEVOPS_UC_SERVER_REG_TABLE','IT')
   35  FROM DUAL;
   36  
   37   so long as the tables do not exist, and the PRIMARY KEY NAME does not already exist
   38   then this will create the table with the primary key constraint.
   39   otherwise, you will run into ORA-0095: name already used by existing object (if table exists)
   40   or ORA-02264: name already used by an existing constraint
   41   TODO: make the table name and primary key constraint name variables for ease of use/robustness
   42  */
   43    lv_stmt:=q'[
   44    CREATE TABLE "IT"."DEVOPS_UC_SERVER_REG_TABLE"
   45     (    "AGENTNAME" VARCHAR2(100 CHAR) NOT NULL ENABLE,
   46      "SERVERNAME" VARCHAR2(100 CHAR) NOT NULL ENABLE,
   47      "DATELASTDEPLOYED" TIMESTAMP (6) WITH TIME ZONE,
   48      "TECHNOLOGY" VARCHAR2(100 CHAR) NOT NULL ENABLE,
   49      "ISSETUP" VARCHAR2(10 CHAR),
   50       CONSTRAINT "DEVOPS_UC_Server_Reg_PKEY" PRIMARY KEY ("AGENTNAME", "SERVERNAME", "TECHNOLOGY")
   51    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
   52    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   53    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   54    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   55    TABLESPACE "IT"  ENABLE
   56     ) SEGMENT CREATION IMMEDIATE
   57    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
   58   NOCOMPRESS LOGGING
   59    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   60    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   61    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   62    TABLESPACE "IT" ]';
   63  
   64      execute immediate lv_stmt;
   65  dbms_output.put_line('Table created successfully');
   66  
   67  END;
   68  COMMIT;
   69  PROMPT ;
   70  PROMPT STATUS: SUCCESS;
   71  TIMING STOP;
   72  PROMPT ;
   73  PROMPT ------------------------------------------------------------;
   74  PROMPT PROCESSING: D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\UC_Server_Reg.pck.sql;
   75  PROMPT ------------------------------------------------------------;
  </code></pre>
  
  <p>I believe the issue probably stems from these lines and below: </p>
  
  <pre><code> 76  PROMPT COMMAND: @"D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\UC_Server_Reg.pck.sql" ;
       77  TIMING START;
       78  @"D:\ucd-agent\var\work\devops.urbancode.db.ServerRegistry-OracleScripts\1.0.0.31-a23aaeb-workdir\IT\UC_Server_Reg.pck.sql" ;
       78  create or replace package it.DEVOPS_UC_SERVER_REGISTRY is
       79  
       80  
       81  PROCEDURE RetrieveUCServerRegTable
       82  (
       83            RC1 IN OUT SYS_REFCURSOR
       84  );
       85  
       86  PROCEDURE ExtraStoredProc
       87  (
       88            RC2 IN OUT SYS_REFCURSOR
       89  );
       90  
       91  PROCEDURE ExtraStoredProc2
       92  (
       93            RC2 IN OUT SYS_REFCURSOR
       94  );
       95  
       96  PROCEDURE InsertUCServerRegistryEntry
       97  (
       98      inAgentName IN VARCHAR,
       99      inIsSetup IN VARCHAR,
      100      inServerName IN VARCHAR,
      101      inTechnology IN VARCHAR
      102  );
      103  
      104  end;
      105  /
      SP2-0552: Bind variable "D" not declared.
      Elapsed: 00:00:00.00
      SQL&gt; 
      SQL&gt; show errors
      No errors.
      SQL&gt; 
      SQL&gt; CREATE OR REPLACE PACKAGE BODY IT.DEVOPS_UC_SERVER_REGISTRY AS
        2  
        3  PROCEDURE RetrieveUCServerRegTable
        4  (
        5            RC1 IN OUT SYS_REFCURSOR
        6  
        7  )
        8  IS
        9  BEGIN
       10    OPEN RC1 FOR
       11         SELECT *
       12         FROM it.DEVOPS_UC_SERVER_REG_TABLE ;
       13  END RetrieveUCServerRegTable;
       14  
       15  PROCEDURE ExtraStoredProc
       16  (
       17            RC2 IN OUT SYS_REFCURSOR
       18  
       19  )
       20  IS
       21  BEGIN
       22    OPEN RC2 FOR
       23         SELECT *
       24         FROM it.DEVOPS_UC_SERVER_REG_TABLE
       25         WHERE agentName='appsrvdev11.otpp.com';
       26  END ExtraStoredProc;
       27  
       28  PROCEDURE ExtraStoredProc2
       29  (
       30            RC2 IN OUT SYS_REFCURSOR
       31  
       32  )
       33  IS
       34  BEGIN
       35    OPEN RC2 FOR
       36         SELECT *
       37         FROM it.DEVOPS_UC_SERVER_REG_TABLE
       38         WHERE agentName='ucdagentdev01.otpp.com';
       39  END ExtraStoredProc2;
       40  
       41  
       42  PROCEDURE InsertUCServerRegistryEntry
       43  (
       44      inAgentName IN VARCHAR,
       45      inIsSetup IN VARCHAR,
       46      inServerName IN VARCHAR,
       47      inTechnology IN VARCHAR
       48  )IS
       49  
       50  BEGIN
       51  
       52  MERGE INTO IT.DEVOPS_UC_SERVER_REG_TABLE
       53    USING DUAL ON (IT.DEVOPS_UC_SERVER_REG_TABLE.agentName = inAgentName AND serverName = inServerName AND
       54               technology = inTechnology)
       55   WHEN MATCHED THEN
       56     UPDATE SET DATELASTDEPLOYED = SYSDATE,
       57            isSetup = inIsSetup
       58   WHEN NOT MATCHED THEN
       59     INSERT( agentName, isSetup, serverName , TechnoLOGY, DATELASTDEPLOYED)
       60       VALUES( inAgentName, inIsSetup, inServerName, inTechnology, SYSDATE );
       61  
       62  
       63  END InsertUCServerRegistryEntry;
       64  END ;
       65  /
  
      Warning: Package Body created with compilation errors.
  
      Elapsed: 00:00:00.45
      SQL&gt; show errors
      Errors for PACKAGE BODY IT.DEVOPS_UC_SERVER_REGISTRY:
  
      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      0/0  PL/SQL: Compilation unit analysis terminated
      1/17     PLS-00201: identifier 'DEVOPS_UC_SERVER_REGISTRY' must be
           declared
  
      1/17     PLS-00304: cannot compile body of 'DEVOPS_UC_SERVER_REGISTRY'
           without its specification
  
      SQL&gt; COMMIT;
  
      Commit complete.
  </code></pre>