Home » SQL & PL/SQL » SQL & PL/SQL » Prolem in executing the code. (9i,)
Prolem in executing the code. [message #318489] Wed, 07 May 2008 00:49 Go to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

I am facing the problem while executing the code I am calling executing the code
DIFF_UTILS.PRE_PROCESS_UPDATE (2, 0, 'DIFF_UTILS.PRE_PROCESS_UPDATE', 'MXM_ILLUST','MXT_ILLUST','MXH_ILLUST','ILLUSID'); which calls the procedure written below.. where as MXM_ILLUST, MXT_ILLUST,MXH_ILLUST are table names and illusid is the primay key which is used for the differencing of the data

PROCEDURE pre_process_update (p_ProcessID IN process_log.process_id%TYPE,
p_LogLevel IN process_log.log_level%TYPE,
p_ObjectName IN process_log.object_name%TYPE,
p_MasterTable IN VARCHAR2,
p_CurrentTable IN VARCHAR2,
p_HistoryTable IN VARCHAR2,
p_Keys IN VARCHAR2) IS
p_columns VARCHAR2(4000);
p_column VARCHAR2(4000);
CNT NUMBER:= 0;
BEGIN
log_utils.log_start(p_ProcessID, p_LogLevel, 'DIFF_UTILS.PRE_PROCESS_UPDATE');

SELECT data_utils.column_list(p_MasterTable, 0, 0, 'FEEDNAME',data_utils.EXCLUDE, NULL, NULL, 0) INTO p_columns FROM DUAL;

SELECT INSTR(p_Keys,',') INTO CNT FROM DUAL;

IF CNT = 0 THEN
SELECT LTRIM(REPLACE(',' || p_columns ,',', ',A.'),',') INTO p_column FROM DUAL;

EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO ' || p_HistoryTable ||
'( SELECT ' || p_column || ' FROM ' || p_MasterTable || ' A INNER JOIN ' ||
p_CurrentTable || ' B ON A.' || p_Keys || ' = B.' || p_Keys || ')';

ELSE
EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO ' || p_HistoryTable ||
'( SELECT ' || p_columns || ' FROM ' || p_MasterTable || ' WHERE (' || p_Keys ||
') IN (SELECT ' || p_Keys || ' FROM ' || p_CurrentTable || '))';
END IF;

log_utils.log_end(p_ProcessID, p_LogLevel, 'DIFF_UTILS.PRE_PROCESS');
END pre_process_update;



I am getting the error at the RED LINE shown above in code...............



ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u700/oradata/pubint/control01.ctl'

ORA-27041: unable to open file

SVR4 Error: 24: Too many open files

Additional information: 3

ORA-06512: at "CHRY_STAGE_STG.DIFF_UTILS", line 1080

ORA-06512: at "CHRY_STAGE_STG.PKG_GROUP_IMG_CROSS_REFERENCE", line 199

ORA-06512: at line 1



[Updated on: Wed, 07 May 2008 00:50]

Report message to a moderator

Re: Prolem in executing the code. [message #318506 is a reply to message #318489] Wed, 07 May 2008 01:10 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The main problem is not in the code (that I didn't read) but in the error:
ORA-27041: unable to open file
SVR4 Error: 24: Too many open files

Increase number of allowed opened files for a user at OS level (see ulimit).

Regards
Michel
Previous Topic: IP of Oracle server
Next Topic: Creating a Report....
Goto Forum:
  


Current Time: Sat Dec 10 20:33:12 CST 2016

Total time taken to generate the page: 0.08961 seconds