Forum Stats

  • 3,769,695 Users
  • 2,253,008 Discussions
  • 7,875,155 Comments

Discussions

Getting compilation error : pls-00201: identifier 'file_type' must be declared

rjsosi
rjsosi Member Posts: 221 Bronze Badge
edited Oct 5, 2021 8:50PM in SQL & PL/SQL

Hi,

We're using Oracle 12c,

We compiled the specs and body for the Oracle OS_COMMAND package on our Dev platform and the package compiled just fine.

We then compiled it on our UAT system and we get the following errors:

xdt827db01_cdb621a1> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
 ---------- ------------------------------ ---------- ----------
 3 PDBSLD02A READ WRITE NO
 xdt827db01_cdb621a1> @OS_Command_1.sql

Warning: Package created with compilation errors.

xdt827db01_cdb621a1> show error
 Errors for PACKAGE SYS.OS_COMMAND:

LINE/COL ERROR
 -------- -----------------------------------------------------------------
 3/3 PL/SQL: Declaration ignored
 3/43 PLS-00201: identifier 'FILE_TYPE' must be declared
 5/3 PL/SQL: Declaration ignored
 5/35 PLS-00201: identifier 'FILE_TYPE' must be declared
 xdt827db01_cdb621a1> @OS_Command_2.sql

Warning: Package Body created with compilation errors.

xdt827db01_cdb621a1> show error
 Errors for PACKAGE BODY SYS.OS_COMMAND:

LINE/COL ERROR
 -------- -----------------------------------------------------------------
 0/0 PL/SQL: Compilation unit analysis terminated
 1/18 PLS-00304: cannot compile body of 'OS_COMMAND' without its
 specification

1/18 PLS-00905: object SYS.OS_COMMAND is invalid
 xdt827db01_cdb621a1>

Same code, different system. I even had the guy take the exact code from the ticket he used to install the procedure on the DEV box and it still gave the error. We changed nothing.

Also I made sure he ran the specs package first and then the body package.

Is there anything we missed

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    Accepted Answer

    Hi, @rjsosi

    So, there is a user-defined data type called file_type on your Dev system, but not on UAT. How did you create it in Dev? Did you do the same thing in UAT? Was there some error that occured during installation on UAT?

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    edited Oct 5, 2021 9:07PM

    Hi, @rjsosi

    Is there anything we missed

    You forgot to post the code. Since all the errors occur in the first 5 lines of the package body, it should be easy for you to make another package that mimics the behavior of those 5 lines, including the error. Post the CREATE PACKAGE and CREATE PACKAGE BODY statements for that simplified package.

    The package may be referencing some package (or table) that is installed on your Dev system, but not installed (or not valid) on your UAT system.

  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge
    edited Oct 5, 2021 10:07PM
    OK, First the Specs. Seriously though, we changed nothing.
    
    CREATE OR REPLACE package SLDBATCH.os_command authid current_user is
     pkg_version varchar2(100) := '1.0';
     procedure set_working_dir (p_workdir in file_type);
     procedure clear_working_dir;
     function get_working_dir return FILE_TYPE;
    
     procedure clear_environment;
     procedure set_env_var(p_env_name in varchar2, p_env_value in varchar2);
     procedure remove_env_var(p_env_name in varchar2);
     function get_env_var(p_env_name in varchar2) return varchar2;
     procedure load_env;
     procedure load_env(p_env_name in varchar2);
    
     procedure use_custom_env;
     procedure use_default_env;
    
     procedure set_Shell(p_shell_path in varchar2, p_shell_switch in varchar2);
     function get_shell return varchar2;
     procedure set_exec_in_shell;
     procedure set_exec_direct;
    
     /* the following functions execute the command "p_command" with
      * the content of "p_stdin" for the standard input (stdin).
      */
     function exec_CLOB(p_command in varchar2, p_stdin in blob) return clob;
     /* ... for commands expecting binary input and returning text */
     function exec_CLOB(p_command in varchar2, p_stdin in clob) return clob;
     /* ... for commands expecting text input and returning text */
     function exec_BLOB(p_command in varchar2, p_stdin in blob) return blob;
     /* ... for commands expecting binary input and returning binary output */
     function exec_BLOB(p_command in varchar2, p_stdin in clob) return blob;
     /* ... for commands expecting text input and returning binary output */
    
     /* the following two functions execute just the command "p_command"; no
      * content is piped into the standard input. */
     function exec_CLOB(p_command in varchar2) return Clob;
     /* ... for commands returning text output */
     function exec_BLOB(p_command in varchar2) return blob;
     /* ... for commands returning binary output */
    
     function exec(p_command in varchar2, p_stdin in blob) return number;
     function exec(p_command in varchar2, p_stdin in clob) return number;
     function exec(p_command in varchar2) return number;
    
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in clob) return number;
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in blob) return number;
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in blob) return number;
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in clob) return number;
     function exec(p_command in varchar2, p_stdout in clob) return number;
     function exec(p_command in varchar2, p_stdout in blob) return number;
    
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in clob, p_stderr in clob) return number;
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in blob, p_stderr in blob) return number;
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in blob, p_stderr in blob) return number;
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in clob, p_stderr in clob) return number;
     function exec(p_command in varchar2, p_stdout in clob, p_stderr in clob) return number;
     function exec(p_command in varchar2, p_stdout in blob, p_stderr in blob) return number;
    end os_command;
    /
    

    Now the body:

    CREATE OR REPLACE package body SYS.os_command is
     procedure set_working_dir (p_workdir in file_type)
     is language java name 'ExternalCall.setWorkingDir(oracle.sql.STRUCT)';
     procedure clear_working_dir
     is language java name 'ExternalCall.clearWorkingDir()';
     function get_working_dir return FILE_TYPE
     is language java name 'ExternalCall.getWorkingDir() return oracle.sql.STRUCT';
    
    
     procedure clear_environment
     is language java name 'ExternalCall.clearEnv()';
     procedure set_env_var(p_env_name in varchar2, p_env_value in varchar2)
     is language java name 'ExternalCall.addEnvVar(java.lang.String, java.lang.String)';
    
     procedure remove_env_var(p_env_name in varchar2)
     is language java name 'ExternalCall.removeEnvVar(java.lang.String)';
     function get_env_var(p_env_name in varchar2) return varchar2
     is language java name 'ExternalCall.getEnvVar(java.lang.String) return java.lang.String';
     procedure load_env
     is language java name 'ExternalCall.loadEnv()';
     procedure load_env(p_env_name in varchar2)
     is language java name 'ExternalCall.loadEnv(java.lang.String)';
    
     procedure use_custom_env
     is language java name 'ExternalCall.activateEnv()';
     procedure use_default_env
     is language java name 'ExternalCall.deactivateEnv()';
    
    
     procedure set_Shell(p_shell_path in varchar2, p_shell_switch in varchar2)
     is language java name 'ExternalCall.setShell(java.lang.String, java.lang.String)';
    
     function get_shell return varchar2
     is language java name 'ExternalCall.getShell() return java.lang.String';
    
     procedure set_exec_in_shell
     is language java name 'ExternalCall.useShell()';
    
     procedure set_exec_direct
     is language java name 'ExternalCall.useNoShell()';
    
    
     function exec_CLOB(p_command in varchar2, p_stdin in blob) return clob
     is language java name 'ExternalCall.execClob(java.lang.String, oracle.sql.BLOB) return oracle.sql.CLOB';
    
     function exec_CLOB(p_command in varchar2, p_stdin in clob) return clob
     is language java name 'ExternalCall.execClob(java.lang.String, oracle.sql.CLOB) return oracle.sql.CLOB';
    
     function exec_BLOB(p_command in varchar2, p_stdin in blob) return blob
     is language java name 'ExternalCall.execBlob(java.lang.String, oracle.sql.BLOB) return oracle.sql.BLOB';
    
     function exec_BLOB(p_command in varchar2, p_stdin in clob) return blob
     is language java name 'ExternalCall.execBlob(java.lang.String, oracle.sql.CLOB) return oracle.sql.BLOB';
    
     function exec_CLOB(p_command in varchar2) return Clob
     is language java name 'ExternalCall.execClob(java.lang.String) return oracle.sql.CLOB';
    
     function exec_BLOB(p_command in varchar2) return blob
     is language java name 'ExternalCall.execBlob(java.lang.String) return oracle.sql.BLOB';
    
     function exec(p_command in varchar2, p_stdin in blob) return number
     is language java name 'ExternalCall.exec(java.lang.String, oracle.sql.BLOB) return int';
    
     function exec(p_command in varchar2, p_stdin in clob) return number
     is language java name 'ExternalCall.exec(java.lang.String, oracle.sql.CLOB) return int';
    
     function exec(p_command in varchar2) return number
     is language java name 'ExternalCall.exec(java.lang.String) return int';
    
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in clob) return number
     is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.CLOB, oracle.sql.CLOB) return int';
    
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in blob) return number
     is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.CLOB, oracle.sql.BLOB) return int';
    
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in blob) return number
     is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB) return int';
    
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in clob) return number
     is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.BLOB, oracle.sql.CLOB) return int';
    
     function exec(p_command in varchar2, p_stdout in clob) return number
     is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.CLOB) return int';
    
     function exec(p_command in varchar2, p_stdout in blob) return number
     is language java name 'ExternalCall.execOut(java.lang.String, oracle.sql.BLOB) return int';
    
    
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in clob, p_stderr in clob) return number
     is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.CLOB, oracle.sql.CLOB, oracle.sql.CLOB) return int';
     function exec(p_command in varchar2, p_stdin in clob, p_stdout in blob, p_stderr in blob) return number
     is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.CLOB, oracle.sql.BLOB, oracle.sql.BLOB) return int';
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in blob, p_stderr in blob) return number
     is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB, oracle.sql.BLOB) return int';
     function exec(p_command in varchar2, p_stdin in blob, p_stdout in clob, p_stderr in clob) return number
     is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.CLOB) return int';
     function exec(p_command in varchar2, p_stdout in clob, p_stderr in clob) return number
     is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.CLOB, oracle.sql.CLOB) return int';
     function exec(p_command in varchar2, p_stdout in blob, p_stderr in blob) return number
     is language java name 'ExternalCall.execOutErr(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB) return int';
    end os_command;
    /
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    Accepted Answer

    Hi, @rjsosi

    So, there is a user-defined data type called file_type on your Dev system, but not on UAT. How did you create it in Dev? Did you do the same thing in UAT? Was there some error that occured during installation on UAT?

  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge

    Yup. That was it. I found an entire object called FILE_TYPE defined as a TYPE.

    It needs to be installed on UAT as well.

    That will do it.

    Thanks!