Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00984: column not allowed here
ORA-00984: column not allowed here [message #421306] Mon, 07 September 2009 07:12 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have written the below package while execute it will give the below error.


ORA-00984: column not allowed here
ORA-06512: at "SCIADMIN.PKG_CRM_COMMON", line 57
ORA-06512: at "SCIADMIN.PKG_CRM_CUST_DATA_INTERFACE", line 151
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 2



pkg_crm_common.pro_crm_log_error(
'CRM_INT_LOG',
'Handling crm_data_chk_exp exception - CRM Process Not Completed Properly',
NULL,
NULL,
'NA'
);


CREATE OR REPLACE PACKAGE pkg_crm_common AS
 PROCEDURE pro_crm_log_error
  ( 
	   p_log_name		     IN	varchar2,
	   p_log_desc		     IN	varchar2,
	   p_crm_sqlerr		     IN	varchar2,
	   p_crm_sqlmsg		     IN varchar2,
	   p_crm_comments		 IN varchar2

  ) ;

END pkg_crm_common ;
/

CREATE OR REPLACE PACKAGE BODY pkg_crm_common AS

	PROCEDURE pro_crm_log_error
	(
	   p_log_name		     IN	varchar2,
	   p_log_desc		     IN	varchar2,
	   p_crm_sqlerr		     IN	varchar2,
	   p_crm_sqlmsg		     IN varchar2,
	   p_crm_comments		 IN varchar2
	) 
	AS
		PRAGMA AUTONOMOUS_TRANSACTION;
        l_sql          varchar2(4000);
		pro_date	   timestamp ;
		seq_val		   varchar2(100);
		
		v_log_name  varchar2(300);
		
	BEGIN
	

select to_char(sysdate,'DD-MON-YYYY:HH12:MI:SS')
into pro_date
from dual;

v_log_name := pro_crm_log_error.p_log_name ;

select crm_int_log_seq.NEXTVAL into seq_val from dual ;


		l_sql :='INSERT INTO temp_crm_log_details(CRM_LOG_ID,CRM_LOG_NAME) VALUES '||'('||seq_val||','||v_log_name||')' ;
--		l_sql :=||','||v_log_name;		
--        l_sql :=l_sql||' ('||seq_val; 
--		l_sql :=l_sql||' '||'(p1)';
		
		dbms_output.put_line('sql stmt is '||l_sql);
		
		execute immediate l_sql ; --	using v_log_name ;
					 
--        EXECUTE IMMEDIATE l_sql USING p_log_name, p_log_desc, p_crm_sqlerr, p_crm_sqlmsg, p_crm_comments;


/*		l_sql :='INSERT INTO temp_crm_log_details(CRM_LOG_NAME,CRM_LOG_DESC,CRM_SQLERR,CRM_SQLMSG,CRM_COMMENTS) VALUES' ;		
--        l_sql :=l_sql||' ('||seq_val; 
		l_sql :=l_sql||'(p1,p2,p3,p4,p5)';
		
		dbms_output.put_line('sql stmt is '||l_sql);
		
		execute immediate 
					 
--        EXECUTE IMMEDIATE l_sql USING p_log_name, p_log_desc, p_crm_sqlerr, p_crm_sqlmsg, p_crm_comments;*/

		COMMIT;
EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			RAISE;
	END pro_crm_log_error;

END pkg_crm_common;
/
Re: ORA-00984: column not allowed here [message #421308 is a reply to message #421306] Mon, 07 September 2009 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Use SQL*Plus and give ALL code
2/ Which lines are 57 and 151 one
3/ You didn't give the proceure that raises the error.

Conclusion: we can't help.

Regards
Michel
Re: ORA-00984: column not allowed here [message #421309 is a reply to message #421306] Mon, 07 September 2009 07:17 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you use dbms_output to display the dynamic string the error should be obvious.
But why are you using dynamic sql here? you don't need it.

Also don't select sysdate from dual and can use a direct assignment.
Re: ORA-00984: column not allowed here [message #421310 is a reply to message #421309] Mon, 07 September 2009 07:21 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Error:	ORA-06510: unhandled user-defined exception
Cause:	You tried to execute a block of code that raised a 
        user-defined exception, but there was no exception 
        block code to handle this exception.
Action:	The options to resolve this Oracle error are:
    1. Remove the user-defined exception from the code.
    2. Add exception code to handle the user-defined exception.




Re: ORA-00984: column not allowed here [message #421312 is a reply to message #421310] Mon, 07 September 2009 07:35 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Nice Very Happy
Re: ORA-00984: column not allowed here [message #421367 is a reply to message #421306] Mon, 07 September 2009 21:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
ORA-00984: column not allowed here


You get this error during parsing. As oracle parses code it looks at all the little pieces. Many of those pieces are some kind of "parameter" (aka. token). I use the term parameter loosly here. In evaluating a parameter, oracle must determine what it is. Is it a plsql local variable? a package global variable? a system environment variable (eg. psuedo column like USER)?, or a column in a table? There may in fact be lots of "kinds" that the parser must walk through but in general columns is the last thing it thinks of.

In many situations if the parser cannot figure out what "kind" of parameter a thing is, it will assume it is a reference to a column from some table, even if there is no table to be found in the code snippet causing the error. In certain situations, using a column from a table is invalid so you get this error.

The most common cause of this error is not that you actually used a column from some table in a place where is should not be referenced, but rather you spelled something wrong and so oracle could not find it to identify what kind of parameter is is and so Oracle defaulted its "kind" to column and thus figured you were make reference to a column.

Check the spelling of variable names and parameter names in the code causing the error to see if indeed you made such a mistake.

Good luck, Kevin
Re: ORA-00984: column not allowed here [message #421609 is a reply to message #421367] Wed, 09 September 2009 07:09 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thanks you very much for yours guidances i have resolved the issue.
Re: ORA-00984: column not allowed here [message #421716 is a reply to message #421306] Thu, 10 September 2009 01:19 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
You know, I am getting pretty tired of people with this attitude.

Don't take this personnally, but why did you come here?

You wanted people to share their knowledge with you so you could get an answer to your problem?

Then you get an answer to your problem and you choose not to share your new knowledge with other people so they can get the same answer to their problem...

If you figured out a solution then post it so everyone else can benefit. This site only works because people share and right now you are not sharing.

Excuse me if I sound a little piffed but I am. You are maybe the fourth guy this week who got his answer and then didn't bother to post it for everyone else.

Please post your solution or tell us why the issue is resolved.

Kevin
Previous Topic: How to Bypass "ORA-01722: invalid number"
Next Topic: Simulator (merged 2)
Goto Forum:
  


Current Time: Tue Sep 27 21:12:54 CDT 2016

Total time taken to generate the page: 0.07201 seconds