tjcw_logPath varchar2(100);alter system set utl_file_dir = tjcw_logPath ; --需要DBA权限Procedure PRO_OutputLog
( sUser IN VARCHAR2,
sComp IN VARCHAR2,
sMsgID IN VARCHAR2 )
IS
tjcw_logPath varchar2(100);
str varchar2(50);
begin
select name into tjcw_logPath
from v$parameter
where name = 'background_dump_dest'; str:='alter system set utl_file_dir ='||tjcw_logPath ;
execute immediate str;
utl_file.fopen(tjcw_logPath,'tjcw_log.log','W');
dbms_output.put_line(sUser '||' sComp '||' sMsgID );
utl_file.fclose(':alert_loc');
end ;
( sUser IN VARCHAR2,
sComp IN VARCHAR2,
sMsgID IN VARCHAR2 )
IS
tjcw_logPath varchar2(100);
str varchar2(50);
begin
select name into tjcw_logPath
from v$parameter
where name = 'background_dump_dest'; str:='alter system set utl_file_dir ='||tjcw_logPath ;
execute immediate str;
utl_file.fopen(tjcw_logPath,'tjcw_log.log','W');
dbms_output.put_line(sUser '||' sComp '||' sMsgID );
utl_file.fclose(':alert_loc');
end ;
execute immediate str USING tjcwadmin ;----》此句有错,怎么解决啊?
tjcwadmin已经是DBA啊!
show error;命令查看
有这样的语句吗?using 后应该是参数,不应该加用户名吧。我的建议:
在参数中先设好utl_file_dir参数
或
1.在tjcwadmin用户下建一个过程修改这个参数,然后赋Exceute权限给这个用户.
2.调用这个过程来修改参数,如果不希望永久修改,用alter system ...scope=memory
tjcw_logPath := 'c:\';
str :='alter system set utl_file_dir ='||tjcw_logPath ;
execute immediate str;
报以下错:
Compilation errors for PACKAGE BODY TJCWADMIN.CW_CKD_JMGPS_PCKError: PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时:
:=.(@%;
Line: 702
Text: execute immediate str;
( sUser IN VARCHAR2,
sComp IN VARCHAR2,
sMsgID IN VARCHAR2 )
IS
begin
set trimspool on;
spool testlog.txt;
dbms_output.put_line(sUser || sComp || sMsgID ||'error !');
spool off;
end ;我用这种方法写,编译也不能通过!报错为:
Compilation errors for PACKAGE BODY TJCWADMIN.CW_CKD_JMGPS_PCKError: PLS-00103: 出现符号"TRIMSPOOL"在需要下列之一时:
transaction
Line: 712
Text: set trimspool on;
spool testlog.txt;
这些命令是在sql*plus里面的命令
在oracle8.0.5中:
用dbms_sql来实现,例
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
spool testlog.txt;
spool off;
Procedure PRO_OutputLog
( sUser IN VARCHAR2,
sComp IN VARCHAR2,
sMsgID IN VARCHAR2 )
IS
tjcw_logPath varchar2(100);
v_file UTL_FILE.FILE_TYPE;
begin
tjcw_logPath := 'd:\'; v_file := utl_file.fopen(tjcw_logPath,'tjcw_log.txt','A');
utl_file.put_line(v_file, sUser || sComp || sMsgID );
utl_file.fclose(v_file);
end ;编译能通过,但在oracle SQL WorkSheet下运行以下调用代码:begin
CW_CKD_JMGPS_PCK.PRO_OutputLog ('zhao','computer','test ok!');
end;
会报错:
ORA-06510: PL/SQL:无法处理的用户自定义异常事件
ORA-06512: 在"SYS.UTL_FILE", line 98
ORA-06512: 在"SYS.UTL_FILE", line 157
ORA-06512: 在"TJCWADMIN.CW_CKD_JMGPS_PCK", line 702
ORA-06512: 在line 2请问是存储过程的哪个地方出错了?
http://www.csdn.net/develop/article/22/22402.shtm
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql.htm#ARPLS058
v_file := utl_file.fopen(tjcw_logPath,'tjcw_log.txt','A');
错误是:
ORA-06510: PL/SQL:无法处理的用户自定义异常事件
ORA-06512: 在"SYS.UTL_FILE", line 98
ORA-06512: 在"SYS.UTL_FILE", line 157
ORA-06512: 在"TJCWADMIN.CW_CKD_JMGPS_PCK", line 702
ORA-06512: 在line 2
请问怎么解决?
v_file := utl_file.fopen(tjcw_logPath,'tjcw_log.txt','A');
错误是:
ORA-06510: PL/SQL:无法处理的用户自定义异常事件
ORA-06512: 在"SYS.UTL_FILE", line 98
ORA-06512: 在"SYS.UTL_FILE", line 157
ORA-06512: 在"TJCWADMIN.CW_CKD_JMGPS_PCK", line 702
ORA-06512: 在line 2
请问怎么解决?