Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NVL function in use in PL/SQL

928966Apr 14 2012 — edited Apr 14 2012
The following SQL statement runs fine and returns data. Notice the ,nvl(fld.MULTSELECT,0).

SELECT exs.PK
,fld.Name
,fld.Desc_
,fld.DataTp
,case when rtrim(fld.InlineName) = '' then
NULL
--convert(nvarchar(255), Null)
else fld.InlineName
end InlineName
,fld.UOM
,fld.High
,fld.Low
,fld.CriticalHigh
,fld.CriticalLow
,fld.ChTp
,nvl(fld.MULTSELECT,0)
,fld.Type
,fld.IsProtected
,fld.RecTp
,fld.RecTpMbrSeq
,fld.SrcEtType
,fld.TargetEtType
,fld.FldCat
--,fc.ChFK FldCateoryFK
-- ,dbo.fn_StripCharacters(fld.Name, '^a-z0-9') SrcStagingFld -- strip out all characters except alphanumeric
,nvl(CAST(trim(fld.PropagateToEAV) AS NUMBER(1,0)),1) PropagateToEAV
,CAST(trim(fld.RecTimeStamp) AS NUMBER(1,0)) RecTimeStamp
,CAST(trim(fld.RecTimeStampEnd) AS NUMBER(1,0)) RecTimeStampEnd
,nvl(CAST(trim(fld.EtTypeTimeStampFld) AS NUMBER(1,0)),0) EtTimeStampFld
,nvl(CAST(trim(fld.EtTypeTimeStampEndFld) AS NUMBER(1,0)),0) EtTypeTimeStampEndFld
,nvl(CAST(trim(fld.EtTypeNameFld) AS NUMBER(1,0)),0) EtTypeNameFld
,nvl(CAST(trim(fld.EtTypeNameFldDeId) AS NUMBER(1,0)),0) EtTypeNameFldDeId
,CAST(trim(fld.EtSrcSystRecKey) AS NUMBER(1,0)) EtSrcSystRecKey
,CAST(trim(fld.EtSrcSystRecAlternativeRecKey) AS NUMBER(1,0)) EtSrcSystAlternativeRecKey
,CAST(trim(fld.ParentEtSrcSystRecKey) AS NUMBER(1,0)) ParentEtSrcSystRecKey
,CAST(trim(fld.MainEtRecTp) AS NUMBER(1,0)) MainEtRecTp
FROM Ld_UD_RecTp fld
-- left join MD_FldCat fc on fld.FldCat = fc.Name
left join Md_Fld exs on fld.RecTp = exs.RecTp and fld.Name = exs.Name
left join Aud_ModDataError Err on err.RecTp = fld.RecTp and Err.BatLogId = 1 -- check if the recordtype has any load errors
where nvl(fld.name,'zzzzzzz') <>'zzzzzzz'
and Err.RecTp is Null

When used in a stored procedure as follows, the procedure doesn't compile

create or replace PROCEDURE MD_Ld_Flds
(
v_BatLogID IN NUMBER DEFAULT NULL
)
AS
BEGIN
MERGE INTO MD_Fld targ
USING (SELECT exs.PK
,fld.Name
,fld.Desc_
,fld.DataTp
,case when rtrim(fld.InlineName) = '' then
NULL
--convert(nvarchar(255), Null)
else fld.InlineName
end InlineName
,fld.UOM
,fld.High
,fld.Low
,fld.CriticalHigh
,fld.CriticalLow
,fld.ChTp
,nvl(fld.MULTSELECT,0)
,fld.Type
,fld.IsProtected
,fld.RecTp
,fld.RecTpMbrSeq
,fld.SrcEtType
,fld.TargetEtType
,fld.FldCat
--,fc.ChFK FldCateoryFK
-- ,dbo.fn_StripCharacters(fld.Name, '^a-z0-9') SrcStagingFld -- strip out all characters except alphanumeric
,nvl(CAST(trim(fld.PropagateToEAV) AS NUMBER(1,0)),1) PropagateToEAV
,CAST(trim(fld.RecTimeStamp) AS NUMBER(1,0)) RecTimeStamp
,CAST(trim(fld.RecTimeStampEnd) AS NUMBER(1,0)) RecTimeStampEnd
,nvl(CAST(trim(fld.EtTypeTimeStampFld) AS NUMBER(1,0)),0) EtTimeStampFld
,nvl(CAST(trim(fld.EtTypeTimeStampEndFld) AS NUMBER(1,0)),0) EtTypeTimeStampEndFld
,nvl(CAST(trim(fld.EtTypeNameFld) AS NUMBER(1,0)),0) EtTypeNameFld
,nvl(CAST(trim(fld.EtTypeNameFldDeId) AS NUMBER(1,0)),0) EtTypeNameFldDeId
,CAST(trim(fld.EtSrcSystRecKey) AS NUMBER(1,0)) EtSrcSystRecKey
,CAST(trim(fld.EtSrcSystRecAlternativeRecKey) AS NUMBER(1,0)) EtSrcSystAlternativeRecKey
,CAST(trim(fld.ParentEtSrcSystRecKey) AS NUMBER(1,0)) ParentEtSrcSystRecKey
,CAST(trim(fld.MainEtRecTp) AS NUMBER(1,0)) MainEtRecTp
FROM Ld_UD_RecTp fld
-- left join MD_FldCat fc on fld.FldCat = fc.Name
left join Md_Fld exs on fld.RecTp = exs.RecTp and fld.Name = exs.Name
left join Aud_ModDataError Err on err.RecTp = fld.RecTp and Err.BatLogId = v_BatLogID -- check if the recordtype has any load errors
where nvl(fld.name,'zzzzzzz') <>'zzzzzzz'
and Err.RecTp is Null -- Exclude recordtypes which had errors
) src on (src.PK = Targ.PK)
when matched then
update Set Targ.Name =src.Name
,Targ.Desc_ =src.Desc_
,Targ.DataTp =src.DataTp
,Targ.InlineName =src.InlineName
,Targ.UOM =src.UOM
,Targ.High =src.High
,Targ.Low =src.Low
,Targ.CriticalHigh =src.CriticalHigh
,Targ.CriticalLow =src.CriticalLow
,Targ.ChTp =src.ChTp
,Targ.MultSelect =src.MultSelect
,Targ.Type =src.Type
,Targ.IsProtected =src.IsProtected
,Targ.RecTp =src.RecTp
,Targ.RecTpMbrSeq =src.RecTpMbrSeq
,Targ.SrcEtType =src.SrcEtType
,Targ.TargetEtType =src.TargetEtType
,Targ.FldCat =src.FldCat
--,Targ.SrcStagingFld =src.SrcStagingFld
,Targ.PropagateToEAV =src.PropagateToEAV
,Targ.RecTimeStamp =src.RecTimeStamp
,Targ.RecTimeStampEnd =src.RecTimeStampEnd
,Targ.EtTimeStampFld =src.EtTimeStampFld
,Targ.EtTimeStampEndFld =src.EtTypeTimeStampEndFld
,Targ.EtNameFld =src.EtTypeNameFld
,Targ.EtNameFldDeId =src.EtTypeNameFldDeId
,Targ.EtSrcSystRecKey =src.EtSrcSystRecKey
,Targ.EtSrcSystAlternativeRecKey=src.EtSrcSystAlternativeRecKey
,Targ.ParentEtSrcSystRecKey =src.ParentEtSrcSystRecKey
,Targ.MainEtRecTp =src.MainEtRecTp
,targ.LastModified = SYSDATE
when not matched then --target
insert (Name
,Desc_
,DataTp
,InlineName
,UOM
,High
,Low
,CriticalHigh
,CriticalLow
,ChTp
,MultSelect
,Type
,IsProtected
,RecTp
,RecTpMbrSeq
,SrcEtType
,TargetEtType
,FldCat
--,SrcStagingFld
,PropagateToEAV
,RecTimeStamp
,RecTimeStampEnd
,EtTimeStampFld
,EtTimeStampEndFld
,EtNameFld
,EtNameFldDeId
,EtSrcSystRecKey
,EtSrcSystAlternativeRecKey
,ParentEtSrcSystRecKey
,MainEtRecTp
,LastModified)
values (src.Name
,src.Desc_
,src.DataTp
,src.InlineName
,src.UOM
,src.High
,src.Low
,src.CriticalHigh
,src.CriticalLow
,src.ChTp
,src.MultSelect
,src.Type
,src.IsProtected
,src.RecTp
,src.RecTpMbrSeq
,src.SrcEtType
,src.TargetEtType
,src.FldCat
--,src.SrcStagingFld
,src.PropagateToEAV
,src.RecTimeStamp
,src.RecTimeStampEnd
,src.EtTimeStampFld
,src.EtTypeTimeStampEndFld
,src.EtTypeNameFld
,src.EtTypeNameFldDeId
,src.EtSrcSystRecKey
,src.EtSrcSystAlternativeRecKey
,src.ParentEtSrcSystRecKey
,src.MainEtRecTp
,SYSDATE);
END;
/

When you show errors you get the following;

7/7 PL/SQL: SQL Statement ignored
125/8 PL/SQL: ORA-00904: 'SRC"."MULTSELECT": invalid identifier

If I compile the procedure without the NVL, the procedure compiles with no errors.

We're running Oracle 11g
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2012
Added on Apr 14 2012
3 comments
1,485 views