Makhali
Reputation: 41
Create View but get Warning: compiled but with compilation error in oracle
When i create a view I am getting this warning in Toad:
Warning: compiled but with compilation error in oracle
This is my script:
CREATE OR REPLACE FORCE VIEW APPS.KMI_OKP_TRACK_V
(
NO_OKP,
LINE,
FBD_PSD,
FBD_ASD,
FBD_PCD,
FBD_ACD,
FBD_DD,
FBD_BCD,
MIX_PSD,
MIX_ASD,
MIX_PCD,
MIX_ACD,
MIX_DD,
MIX_BCD,
BPR_PSD,
BPR_ASD,
BPR_PCD,
BPR_ACD,
BPR_DD,
BPR_BCD,
BLD_PSD,
BLD_ASD,
BLD_PCD,
BLD_ACD,
BLD_DD,
BLD_BCD,
CAN_PSD,
CAN_ASD,
CAN_PCD,
CAN_ACD,
CAN_DD,
CAN_BCD,
BAG_PSD,
BAG_ASD,
BAG_PCD,
BAG_ACD,
BAG_DD,
BAG_BCD,
DPG_PSD,
DPG_ASD,
DPG_PCD,
DPG_ACD,
DPG_DD,
DPG_BCD,
SAC_PSD,
SAC_ASD,
SAC_PCD,
SAC_ACD,
SAC_DD,
SAC_BCD,
BPE_PSD,
BPE_ASD,
BPE_PCD,
BPE_ACD,
BPE_DD,
BPE_BCD
)
AS
SELECT DISTINCT XX.NO_OKP,
XX.LINE --,XX.CREATION_DATE
,
FBD.FBD_PSD,
FBD.FBD_ASD,
FBD.FBD_PCD,
FBD.FBD_ACD,
FBD.FBD_DD,
FBD.FBD_BCD,
MIX.MIX_PSD,
MIX.MIX_ASD,
MIX.MIX_PCD,
MIX.MIX_ACD,
MIX.MIX_DD,
MIX.MIX_BCD,
BPR.BPR_PSD,
BPR.BPR_ASD,
BPR.BPR_PCD,
BPR.BPR_ACD,
BPR.BPR_DD,
BPR.BPR_BCD,
BLD.BLD_PSD,
BLD.BLD_ASD,
BLD.BLD_PCD,
BLD.BLD_ACD,
BLD.BLD_DD,
BLD.BLD_BCD,
CAN.CAN_PSD,
CAN.CAN_ASD,
CAN.CAN_PCD,
CAN.CAN_ACD,
CAN.CAN_DD,
CAN.CAN_BCD,
BAG.BAG_PSD,
BAG.BAG_ASD,
BAG.BAG_PCD,
BAG.BAG_ACD,
BAG.BAG_DD,
BAG.BAG_BCD,
DPG.DPG_PSD,
DPG.DPG_ASD,
DPG.DPG_PCD,
DPG.DPG_ACD,
DPG.DPG_DD,
DPG.DPG_BCD,
SAC.SAC_PSD,
SAC.SAC_ASD,
SAC.SAC_PCD,
SAC.SAC_ACD,
SAC.SAC_DD,
SAC.SAC_BCD,
BPE.BPE_PSD,
BPE.BPE_ASD,
BPE.BPE_PCD,
BPE.BPE_ACD,
BPE.BPE_DD,
BPE.BPE_BCD
FROM ( SELECT DISTINCT
RTRIM (
SUBSTR (BATCH_NO, 4, 4),
'A'
)
no_okp,
ATTRIBUTE1 LINE --,CREATION_DATE
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date,
'MM/DD/YYYY') IN
(TO_CHAR (
SYSDATE - 7,
'MM/DD/YYYY'
),
TO_CHAR (
SYSDATE - 8,
'MM/DD/YYYY'
))
ORDER BY CREATION_DATE DESC) XX
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (
SUBSTR (BATCH_NO, 4, 4),
'A'
)
no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE FBD_PSD,
ACTUAL_START_DATE FBD_ASD,
PLAN_CMPLT_DATE FBD_PCD,
ACTUAL_CMPLT_DATE FBD_ACD,
DUE_DATE FBD_DD,
BATCH_CLOSE_DATE FBD_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date,
'MM/DD/YYYY') IN
(TO_CHAR (
SYSDATE - 7,
'MM/DD/YYYY'
),
TO_CHAR (
SYSDATE - 8,
'MM/DD/YYYY'
))
AND attribute1 = 'FAT BLEND'
ORDER BY CREATION_DATE DESC) FBD
ON XX.NO_OKP = FBD.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4),
'A')
no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE MIX_PSD,
ACTUAL_START_DATE MIX_ASD,
PLAN_CMPLT_DATE MIX_PCD,
ACTUAL_CMPLT_DATE MIX_ACD,
DUE_DATE MIX_DD,
BATCH_CLOSE_DATE MIX_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date,
'MM/DD/YYYY') IN
(TO_CHAR (
SYSDATE - 7,
'MM/DD/YYYY'
),
TO_CHAR (
SYSDATE - 8,
'MM/DD/YYYY'
))
AND attribute1 = 'MIX'
ORDER BY CREATION_DATE DESC) MIX
ON XX.NO_OKP = MIX.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4),
'A')
no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE BPR_PSD,
ACTUAL_START_DATE BPR_ASD,
PLAN_CMPLT_DATE BPR_PCD,
ACTUAL_CMPLT_DATE BPR_ACD,
DUE_DATE BPR_DD,
BATCH_CLOSE_DATE BPR_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date,
'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7,
'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8,
'MM/DD/YYYY'))
AND attribute1 = 'BASE POWDER'
ORDER BY CREATION_DATE DESC) BPR
ON XX.NO_OKP = BPR.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A')
no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE BLD_PSD,
ACTUAL_START_DATE BLD_ASD,
PLAN_CMPLT_DATE BLD_PCD,
ACTUAL_CMPLT_DATE BLD_ACD,
DUE_DATE BLD_DD,
BATCH_CLOSE_DATE BLD_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date,
'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7,
'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8,
'MM/DD/YYYY'))
AND attribute1 = 'BLEND'
ORDER BY CREATION_DATE DESC) BLD
ON XX.NO_OKP = BLD.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A')
no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE CAN_PSD,
ACTUAL_START_DATE CAN_ASD,
PLAN_CMPLT_DATE CAN_PCD,
ACTUAL_CMPLT_DATE CAN_ACD,
DUE_DATE CAN_DD,
BATCH_CLOSE_DATE CAN_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date,
'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7,
'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8,
'MM/DD/YYYY'))
AND attribute1 = 'CANNING'
ORDER BY CREATION_DATE DESC) CAN
ON XX.NO_OKP = CAN.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE BAG_PSD,
ACTUAL_START_DATE BAG_ASD,
PLAN_CMPLT_DATE BAG_PCD,
ACTUAL_CMPLT_DATE BAG_ACD,
DUE_DATE BAG_DD,
BATCH_CLOSE_DATE BAG_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7,
'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8,
'MM/DD/YYYY'))
AND attribute1 = 'BAG'
ORDER BY CREATION_DATE DESC) BAG
ON XX.NO_OKP = BAG.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE DPG_PSD,
ACTUAL_START_DATE DPG_ASD,
PLAN_CMPLT_DATE DPG_PCD,
ACTUAL_CMPLT_DATE DPG_ACD,
DUE_DATE DPG_DD,
BATCH_CLOSE_DATE DPG_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7, 'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8, 'MM/DD/YYYY'))
AND attribute1 = 'DUMPING'
ORDER BY CREATION_DATE DESC) DPG
ON XX.NO_OKP = DPG.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE SAC_PSD,
ACTUAL_START_DATE SAC_ASD,
PLAN_CMPLT_DATE SAC_PCD,
ACTUAL_CMPLT_DATE SAC_ACD,
DUE_DATE SAC_DD,
BATCH_CLOSE_DATE SAC_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7, 'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8, 'MM/DD/YYYY'))
AND attribute1 IN ('SACHET D', 'SACHET A')
ORDER BY CREATION_DATE DESC) SAC
ON XX.NO_OKP = SAC.NO_OKP
LEFT OUTER JOIN
( SELECT DISTINCT
RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp,
attribute1 Line,
DECODE (batch_status,
'1', 'Inspect',
'2', 'Release',
'3', 'Complete',
'Close')
Batch_status,
CREATION_DATE,
PLAN_START_DATE BPE_PSD,
ACTUAL_START_DATE BPE_ASD,
PLAN_CMPLT_DATE BPE_PCD,
ACTUAL_CMPLT_DATE BPE_ACD,
DUE_DATE BPE_DD,
BATCH_CLOSE_DATE BPE_BCD
FROM GME_BATCH_HEADER
WHERE ORGANIZATION_ID = 84
AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN
(TO_CHAR (SYSDATE - 7, 'MM/DD/YYYY'),
TO_CHAR (SYSDATE - 8, 'MM/DD/YYYY'))
AND attribute1 = 'SACHET E'
ORDER BY CREATION_DATE DESC) BPE
ON XX.NO_OKP = BPE.NO_OKP
ORDER BY xx.no_okp ASC;
Where is the fault ?how to get the specific error code ?
Upvotes: 1
Views: 4417
Answers (3)
William Robertson
Reputation: 16001
To find the specific errors, either:
- Test it without the
forcekeyword. - Test it without the
create view ... aspart, i.e. test theselectpart on its own. - In SQL*Plus or compatible tools, execute the following command while connected as the APPS user:
show errors view kmi_okp_track_v(or include the schema if executing from a DBA account) - Query
user_errorswhile connected as the APPS user (ordba_errorsif executing from a DBA account).
Example:
SQL> create or replace force view demo_view as
2 select nosuchcolumn from dual;
Warning: View created with compilation errors.
Test without the force keyword:
SQL> create or replace view demo_view as
2 select nosuchcolumn from dual;
select nosuchcolumn from dual
*
ERROR at line 2:
ORA-00904: "NOSUCHCOLUMN": invalid identifier
Testing the select statement on its own:
SQL> select nosuchcolumn from dual;
select nosuchcolumn from dual
*
ERROR at line 1:
ORA-00904: "NOSUCHCOLUMN": invalid identifier
Using show errors:
SQL> show errors view demo_view
Errors for VIEW DEMO_VIEW:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-00904: "NOSUCHCOLUMN": invalid identifier
Querying user_errors view:
SQL> col text format a70
SQL> select text from user_errors e
2 where e.name = 'DEMO_VIEW'
3 order by e.sequence;
TEXT
----------------------------------------------------------------------
ORA-00904: "NOSUCHCOLUMN": invalid identifier
For deployment scripts, I would include the force keyword and show errors at the end.
Upvotes: 1
kara
Reputation: 3455
As Barbaros said your query look good.
But here a problem i ran into - perhaps it's your problem, too.
-- This will work
CREATE OR REPLACE FORCE VIEW MySchema.MyView
(
MyColumn
)
AS
SELECT "MyColumn" FROM myTable;
-- This won't - because of the delimiter (';') in our variable myquery
DECLARE
myquery VARCHAR2(2000) :=
'CREATE OR REPLACE FORCE VIEW MySchema.MyView
(
MyColumn
)
AS
SELECT "MyColumn" FROM myTable;'
;
BEGIN
EXECUTE IMMEDIATE myquery;
END;
If you EXECUTE IMMEDIATE a query, it must not have a delimiter - neither ; nor /. This is not allowed because you always have to run a single statement, not a list of statement seperated by a delimiter.
The correct version is this:
-- This won't - because of the delimiter (';') in our variable myquery
DECLARE
myquery VARCHAR2(2000) :=
'CREATE OR REPLACE FORCE VIEW MySchema.MyView
(
MyColumn
)
AS
SELECT "MyColumn" FROM myTable' -- no ';' here
;
BEGIN
EXECUTE IMMEDIATE myquery;
END;
Fun-fact: Your View will be created and will work after a recompile.
Upvotes: 0
Barbaros Özhan
Reputation: 65218
There's no problem with your syntax, and you may create this view in your current user, provided the schema prefix APPS. is removed from CREATE OR REPLACE FORCE VIEW APPS.KMI_OKP_TRACK_V.
If you need to create this view from your current schema, you need more prefixes for GME_BATCH_HEADER and the creation privilege should be granted for APPS schema.
Upvotes: 0
Related Questions
- Oracle Error When Creating View - ORA-01720
- Invalid View cannot be compiled
- View creating error. Failed: Warning: Execution is completed with a warning
- error on statement execution when creating a view
- Getting Error while creating a view in oracle DB
- creating a view SQL, always getting error
- Create a view error
- Creating views in SQL and receiving error
- Ora-00904 - Error with creating a View
- Column ambiguously defined Error on Creating a view