object outer does not exist

zain ul abidin

I have been trying to execute this procedure but it is giving this error and I have no idea what is causing it. Its been 2 days and I am still stuck in it. Basically, the procedure should run the query and store the result in a new table. Below is the error I am getting.

ORA-06564: object OUTER does not exist ORA-06512: at "HR.RPG", line 17 ORA-06512: at line 1

and this is my procedure code:

create or replace procedure "rpg"

is

  v_full_name VARCHAR2(500);
  sal varchar(200);
  jobid varchar(100);
  cpct varchar(50);
  mgid varchar(25);
  did varchar(20);
  cid varchar(20);
  rid varchar(20);
  lid varchar(20);

  Begin

  execute immediate 'create table resquery as
  (
  select HR.EMPLOYEES.FIRST_NAME||'' ''||HR.EMPLOYEES.LAST_NAME AS 
 Full_Name,HR.EMPLOYEES.SALARY as sal,HR.EMPLOYEES.JOB_ID as ji,
 HR.EMPLOYEES.COMMISSION_PCT as cmpct,HR.EMPLOYEES.MANAGER_ID as 
 mgid,HR.EMPLOYEES.DEPARTMENT_ID as dep,HR.COUNTRIES_EXTERNAL.COUNTRY_ID as 
 country,HR.DW_REGION.R_ID as region,
 HR.LOCATIONS.LOCATION_ID as loc
 from HR.EMPLOYEES
 join HR.DEPARTMENTS ON 
 HR.EMPLOYEES.DEPARTMENT_ID=HR.DEPARTMENTS.DEPARTMENT_ID
 join HR.LOCATIONS    ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID
 join HR.COUNTRIES_EXTERNAL ON     HR.LOCATIONS.COUNTRY_ID=HR.COUNTRIES_EXTERNAL.COUNTRY_ID
 join HR.DW_REGION    ON HR.COUNTRIES_EXTERNAL.REGION_ID=HR.DW_REGION.R_ID
 where HR.COUNTRIES_EXTERNAL.COUNTRY_ID=''US''
 AND trunc(HR.EMPLOYEES.HIRE_DATE) BETWEEN     TO_DATE(''16/08/2002'',''DD/MM/YYYY'') AND 
 TO_DATE(''07/12/2007'',''DD/MM/YYYY''))';

  end;
  /
zain ul abidin

SOLVED!!!

Actually, I created an OUTER directory when I was logged in with SYSTEM schema and created an external table there. Now I was logged in as HR schema and was using that external table with the outer directory in queries of hr schema which it couldn't find as the directory is in the SYSTEM schema. So I changed the external table references in my current procedure queries which did the job and I also had to give privileges to execute the procedure.

AUTHID CURRENT_USER is

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

HotTag

Archive