Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201: identifier 'TO_NCHAR' must be declared
PLS-00201: identifier 'TO_NCHAR' must be declared [message #306811] Sun, 16 March 2008 23:28 Go to next message
dcsim23
Messages: 1
Registered: March 2008
Location: Singapore
Junior Member
Hi all,

I'm new to this. Can anyone tell me why I'm getting the "PLS-00201: identifier 'TO_NCHAR' must be declared" error?

CREATE OR REPLACE FUNCTION "Data"."GETDATA" (
inOBID IN VARCHAR2,
inAttr IN VARCHAR2,
inType IN VARCHAR2,
inEffDate IN VARCHAR2 DEFAULT '',
inConfig IN VARCHAR2 DEFAULT 'NTE'
)
RETURN VARCHAR2
IS
retValue VARCHAR2(1000);
mTemp VARCHAR2(1);
BEGIN

BEGIN
IF inEffDate IS NULL THEN
SELECT sa.n1strvalue INTO retValue
FROM n3objattr oa, n2spcattr sa, n2clsattr ca
WHERE ca.n1attrclassname = inAttr
AND oa.rightrel = sa.obid
AND sa.n1attrclassobid = ca.obid
AND oa.leftrel = inOBID
AND (oa.n1terminationdate IS NULL)
AND oa.n1config LIKE inConfig || '%'
;

ELSE
SELECT sa.n1strvalue INTO retValue
FROM n3objattr oa, n2spcattr sa, n2clsattr ca
WHERE ca.n1attrclassname = inAttr
AND oa.rightrel = sa.obid
AND sa.n1attrclassobid = ca.obid
AND oa.leftrel = inOBID
AND oa.n1creationdate <= inEffDate
AND ((oa.n1terminationdate IS NULL) OR oa.n1terminationdate > inEffDate)
AND oa.n1config LIKE inConfig || '%'
;
END IF;


EXCEPTION
WHEN TOO_MANY_ROWS THEN
retValue := '****>1 Row****';
WHEN OTHERS THEN
retValue := '';
END;

IF inType = 'ATTRNUM' THEN
IF retValue IS NULL THEN
retValue := '0';
ELSE
BEGIN
retValue := TO_NCHAR(TO_NUMBER(retValue));
EXCEPTION
WHEN OTHERS THEN
retValue := '0';
END;
END IF;
END IF;

IF inType = 'ATTRYNL' THEN
IF (retValue IS NOT NULL) THEN
mTemp := SUBSTR(retValue, 1, 1);
retValue := '';
IF mTemp = '1' THEN retValue := 'Y'; END IF;
IF mTemp = 'Y' THEN retValue := 'Y'; END IF;
IF mTemp = 'y' THEN retValue := 'Y'; END IF;
IF mTemp = '+' THEN retValue := 'Y'; END IF;
END IF;
END IF;


RETURN retValue;
END getspfdata;

Thanks
dcsim23
Re: PLS-00201: identifier 'TO_NCHAR' must be declared [message #306846 is a reply to message #306811] Mon, 17 March 2008 01:52 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also use SQL*Plus, copy and paste your session, give line numbers and at which line there is the error...

I get no error:
SQL> declare v varchar2(10); 
  2  begin
  3    v := to_nchar(1);
  4  end;
  5  /

PL/SQL procedure successfully completed.


Regards
Michel

[Updated on: Mon, 17 March 2008 01:55]

Report message to a moderator

Previous Topic: How can I deal with this exception"buffer flow" when I use cursor.
Next Topic: SEND Query Output By email
Goto Forum:
  


Current Time: Sat Feb 08 13:09:35 CST 2025