asked on

PLS-00307: too many declarations of 'TO_CHAR' match this call - WHY???? Whats the fix?

Can anyone tell me why the following works:
SELECT m.value||'\'||'ORA'||ltrim(to_char(p.spid,'00000'))||'.trc'
FROM v$session s, v$process p, v$instance i, v$parameter m
WHERE m.name = 'user_dump_dest' AND p.addr= s.paddr AND userenv('SESSIONID') = s.audsid
AND s.server='DEDICATED';

but the following gives me an annoying PLS-00307: too many declarations of 'TO_CHAR' match this call
error:
CREATE OR REPLACE PROCEDURE rssyncit1 IS
location varchar2(255);
BEGIN
dbms_utility.exec_ddl_statement('alter database backup controlfile to trace');
SELECT iv.traceloc
INTO location
FROM (
SELECT m.value||'\'||'ORA'||ltrim(to_char(p.spid,'09999'))||'.trc' traceloc
FROM v$session s, v$process p, v$instance i, v$parameter m
WHERE m.name = 'user_dump_dest' AND p.addr= s.paddr AND userenv('SESSIONID') = s.audsid
AND s.server='DEDICATED'
) iv;
insert into mycontrol_files values(location);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END rssyncit1;
/

Guy Hengel [angelIII / a3]

what about this:

CREATE OR REPLACE PROCEDURE rssyncit1 IS
location varchar2(255);
BEGIN
dbms_utility.exec_ddl_statement('alter database backup controlfile to trace');
insert into mycontrol_files
SELECT m.value||'\ORA'||ltrim(to_char(p.spid,'00000'))||'.trc' traceloc
FROM v$session s, v$process p, v$instance i, v$parameter m
WHERE m.name = 'user_dump_dest' AND p.addr= s.paddr AND userenv('SESSIONID') = s.audsid
AND s.server='DEDICATED'
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END rssyncit1;
/

ASKER CERTIFIED SOLUTION

Robert Silver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial