Actually I figured out the problem. See I was runing to_char() on a varchar and not a number.
Odd though that it only fails in a function. My solution was to
change it like so: Note that I added a, to_number() function to convert the varchar to a number first.
CREATE OR REPLACE PROCEDURE rssyncit1 IS
location varchar2(255);
BEGIN
dbms_utility.exec_ddl_stat
SELECT iv.traceloc
INTO location
FROM (
SELECT m.value||'\'||'ORA'||ltrim
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;
Main Topics
Browse All Topics
by: angelIIIPosted on 2007-04-03 at 12:26:16ID: 18846122
what about this:
CREATE OR REPLACE PROCEDURE rssyncit1 IS
location varchar2(255);
BEGIN
dbms_utility.exec_ddl_stat
insert into mycontrol_files
SELECT m.value||'\ORA'||ltrim(to_
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;
/