Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate with DML error: column not allowed here
Execute Immediate with DML error: column not allowed here [message #231921] Wed, 18 April 2007 19:47 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
set serverout on

CREATE OR REPLACE Procedure Email_Correction_tbl
( tbl_in IN varchar2 DEFAULT 'NAS_Consolidated_041707',
  tbl_out IN varchar2 DEFAULT 'EMAIL_CORRECTED'
) 
AS

 -- cid integer;
  err_email VARCHAR2(100);
  Is_2 		number;
  is_or 	number;
  is_comma  number;
  err_email_trunc VARCHAR2(100);
  --v_table  VARCHAR2(100);
  --table_out VARCHAR2(100) := tbl_out;
  
  
  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);
  
  sqlCommand sqltype;
  objcur curtype;
  oname VARCHAR2(100);
	
  v_SQL_Statement1 sqltype;--VARCHAR2(2000) DEFAULT NULL; 
  v_SQL_Statement2 sqltype;--VARCHAR2(2000) DEFAULT NULL;
  v_SQL_Statement3 sqltype;--VARCHAR2(400) DEFAULT NULL;

/* 
CURSOR c1 is 
	   SELECT * 
	   FROM tbl_out
	   WHERE opt_outs = 'N'
	   FOR UPDATE OF EMAIL NOWAIT; 	
 */
 
BEGIN

 	 sqlCommand := 'select email FROM ' || tbl_in ||' WHERE opt_outs = ''N''';
     open objcur for sqlCommand;
   

	 --v_table := tbl_in;    	 
	--v_SQL_Statement1 :=  'SELECT email '||' FROM '|| tbl_in || ' WHERE opt_outs = ''N'' ';
     --execute immediate v_SQL_Statement1 ;
	--WHILE (v_SQL_Statement1 IS NOT NULL)
	LOOP 
	--NULL ;

	FETCH objcur INTO oname;
	exit when objcur%notfound;
	  
	--  exit when v_SQL_Statement1 IS NULL; 	  
	  
	  execute immediate sqlCommand ;
	  
	  
	  -- case 3 
	 --DBMS_OUTPUT.PUT_LINE ( 'err_email= ' || err_email );
	 
	 err_email := TRIM(upper(oname));
	 --DBMS_OUTPUT.PUT_LINE ( 'TRIM(cursor_1.email)= ' || err_email );
	 
	 
	 err_email := 
	  CASE 
	  	   -- case 5 
     	  WHEN (INSTR(err_email,'@',1,2) = 0 AND INSTR(err_email, '[', 1, 1) > 0) 
     	  THEN SUBSTR(err_email, instr(err_email, '[', 1, 1) + 1,  instr(err_email, ']', -1, 1) - instr(err_email, '[', 1, 1) -1) 
		  	   
     	   -- case 2 
     	  WHEN (INSTR(err_email,'@',1,2) = 0 AND INSTR(err_email, ',') > INSTR(err_email, '@')) 
     	  THEN TRIM(	  SUBSTR(err_email, 1, INSTR(err_email,',', -1, 1)-1) )
     	  
		   -- case 1 -> 4 
     	  WHEN ( INSTR(err_email,'@',1,2) = 0 AND INSTR(err_email, ',') != 0) 
     	  THEN REPLACE (err_email, ',', '.') || ' OR ' || TRIM( SUBSTR(err_email, INSTR(err_email, ',')+1) )
     
     	  ELSE err_email
	  
	  END ;
	 

 	 is_2 := case when (INSTR(err_email,'@',1,2) > 0) then 1
	 	  	 			 else 0
		     end;
			 
     is_or := case when (INSTR(err_email,' OR ') > 0) then 1
	 	  	 			 else 0
	 	   	 
			 end;			 

	is_comma :=  case when (INSTR(err_email,', ') > 0) then 1
	 	  	 			 else 0
	 	   	 
			 	 end;			  			 

				 			 	 
	 IF is_2 = 1 THEN 
	 		   	DBMS_OUTPUT.PUT_LINE ( 'is_2= ' || is_2 ); 	  
	 		   	 	  IF is_or = 1
					  -- update_insert1 :  ii 
					  THEN GOTO update_insert1;

     				  ELSIF is_comma = 1
					  -- update_insert2 : i 
     				  THEN GOTO update_insert2;
					  
					  ELSE GOTO END_LOOP;
					  end if;			  
	  
	  ELSE GOTO END_LOOP;					  					  					  
 	  end if; 				  

	  DBMS_OUTPUT.PUT_LINE('PRE-UPDATE_INSERT -- email : '||err_email|| ' processed');		
		   
   <<update_insert1>> 
				   err_email_trunc := replace(err_email,' ');
				   DBMS_OUTPUT.PUT_LINE ( 'err_email_trunc= ' || err_email_trunc );
				   err_email := SUBSTR(err_email, 1, instr(err_email, ' ',1,1) -1);
				   DBMS_OUTPUT.PUT_LINE ( 'err_email= ' || err_email );


--				   DBMS_OUTPUT.PUT_LINE('email: '||TO_CHAR(instr(err_email_trunc, 'OR',1,1) +1 )|| ' inserted');
			   
			       v_SQL_Statement1 :=  'insert into  tbl_out  values (SUBSTR(err_email_trunc, instr(err_email_trunc, ''OR'',1,1) +2,' ||
				    'length(err_email_trunc)- ( instr(err_email_trunc, ''OR'',1,1) +1)), ''N'')' ;
					
					
					execute immediate (v_SQL_Statement1);
					
--				   DBMS_OUTPUT.PUT_LINE ( 'email: ' || SUBSTR(err_email_trunc, instr(err_email_trunc, 'OR',1,1) +1 ) || ' inserted');
				   --DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT1: '||SQL%ROWCOUNT);
				  -- COMMIT;
				   GOTO END_LOOP;
				   
		   	  
	<<update_insert2>>
				   
				   /* first part of the email string has to be parsed after the insertion is done */
				   --err_email := SUBSTR(err_email, 1, instr(err_email, ',',1,1) -1);			  
				   DBMS_OUTPUT.PUT_LINE('email: '||SUBSTR(replace(err_email,' '), instr(replace(err_email,' '), ',',1,1) +1 )|| ' inserted');
				   
				   v_SQL_Statement2 := 'insert into tbl_out values (SUBSTR(replace(err_email,'' ''), instr(replace(err_email,'' ''), '','',1,1) +1 ), ''N'')';
				   DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT2: '||SQL%ROWCOUNT);	
				   
				   execute immediate (v_SQL_Statement2);
				   err_email := SUBSTR(err_email, 1, instr(err_email, ',',1,1) -1);				   		  
				   --COMMIT;
				   GOTO END_LOOP;
	 

	 <<END_LOOP>>

	 DBMS_OUTPUT.PUT_LINE('email: '||err_email|| ' corrected');

	 v_SQL_Statement3 := 'INSERT INTO '|| tbl_out ||' values ( err_email, ''N'')';
	 DBMS_OUTPUT.PUT_LINE ( 'v_SQL_Statement3= ' || v_SQL_Statement3 );
	 execute immediate (v_SQL_Statement3);

	 
	 DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: '||SQL%ROWCOUNT);

	 

	 END LOOP; 

	 CLOSE objcur;
 	 COMMIT;
	  
END;


I kept getting the following error:
ORA-00984: column not allowed here
ORA-06512: at "DM_METRICS.EMAIL_CORRECTION_TBL", line 179
ORA-06512: at line 2


Please help.

[Updated on: Wed, 18 April 2007 19:48]

Report message to a moderator

Re: Execute Immediate with DML error: column not allowed here [message #231931 is a reply to message #231921] Wed, 18 April 2007 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Anyone who uses EXECUTE IMMEDIATE to do DML deserves all the challenges they face for using the wrong tool for the job.
>Please help.
Only give valid SQL statements to EXECUTE IMMEDIATE & then no syntax errors will occur.
Re: Execute Immediate with DML error: column not allowed here [message #231965 is a reply to message #231921] Wed, 18 April 2007 23:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Seems, this statement
v_SQL_Statement1 :=  'insert into  tbl_out  values (SUBSTR(err_email_trunc, instr(err_email_trunc, ''OR'',1,1) +2,' ||
				    'length(err_email_trunc)- ( instr(err_email_trunc, ''OR'',1,1) +1)), ''N'')' ;
is not correct, as tbl_out is a variable identifier, not a table name.

In addition, your pl/sql variables are not visible in executed sql statement. Instead of hardcoding their values, I would use binding, eg.
v_SQL_Statement1 :=  'insert into '|| tbl_out ||' values (:email, :opt_outs)' ;
 EXECUTE IMMEDIATE v_SQL_Statement1 USING SUBSTR(err_email_trunc, instr(err_email_trunc, 'OR',1,1) +2,
				    length(err_email_trunc)- ( instr(err_email_trunc, 'OR',1,1) +1)), 'N';

You can use the same statement in other EXECUTE IMMEDIATE statements, only change values in USING part.
Re: Execute Immediate with DML error: column not allowed here [message #231985 is a reply to message #231921] Thu, 19 April 2007 00:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@dpong: Next time you post a piece of code, please have the decency to remove the commented pieces (except for actual comments of course). We want your example as small as possible (but still a working example!), without all the gibberish.
Re: Execute Immediate with DML error: column not allowed here [message #232017 is a reply to message #231985] Thu, 19 April 2007 01:51 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Sorry about that. I wanted to let the ones who read the post knew what I had tried and did not work. But yes, you're correct. It'll be far much easier to read if the codes are without unused portions of codes.

Re: Execute Immediate with DML error: column not allowed here [message #232097 is a reply to message #232017] Thu, 19 April 2007 07:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, it is always good to show that you tried (otherwise, we will accuse you of lazyness Wink).
Hint for the next time: if you scatter your code with dbms_outputs (which is not bad, as long as you are in the debug stage; remove them before going to production!!), it could help a great deal if you provide the outcome of those as well!
Re: Execute Immediate with DML error: column not allowed here [message #232204 is a reply to message #231965] Thu, 19 April 2007 15:32 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
While the previous problem was solved, I immediately got another problem with IF THEN execute immediate ;END IF;
CREATE OR REPLACE Procedure Email_Correction
( tbl_in IN varchar2 DEFAULT 'NAS_Consolidated_041707',
  tbl_out IN varchar2 DEFAULT 'EMAIL_CORRECTED'
) 
AS

   err_email VARCHAR2(100);
  Is_2 		number;
  is_or 	number;
  is_comma  number;
  err_email_trunc VARCHAR2(100);

  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);
  
  sqlCommand sqltype;
  objcur curtype;
  oname VARCHAR2(100);
	
  v_SQL_Statement1 sqltype;--VARCHAR2(2000) DEFAULT NULL; 
  v_SQL_Statement2 sqltype;--VARCHAR2(2000) DEFAULT NULL;
  v_SQL_Statement3 sqltype;--VARCHAR2(400) DEFAULT NULL;


BEGIN

 	 sqlCommand := 'select email FROM ' || tbl_in ||' WHERE opt_outs = ''N''';
     open objcur for sqlCommand;
   	 
	 /* 
	 DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||tbl_out||
	 ' (email VARCHAR2(100),'||
	 ' OPT_OUTS VARCHAR2(1) default ''N'' );'
	 );
	 */
	 
	 IF tbl_out <> 'EMAIL_CORRECTED'
	 THEN execute immediate ('CREATE TABLE '||tbl_out||
	 ' (email VARCHAR2(100),'||
	 ' OPT_OUTS VARCHAR2(1) default ''N'' );');
	 END IF;
	 execute immediate ('TRUNCATE TABLE '|| tbl_out);

ORA-06550: line 3, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.

[Updated on: Thu, 19 April 2007 16:49]

Report message to a moderator

Re: Execute Immediate with DML error: column not allowed here [message #232257 is a reply to message #231921] Thu, 19 April 2007 23:20 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As stated in documentation (third paragraph):
Quote:
When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end inside the quotation mark.

Follow it.
icon14.gif  Re: Execute Immediate with DML error: column not allowed here [message #232300 is a reply to message #232257] Fri, 20 April 2007 01:31 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
I already found out that bug. Thanks for the confirmation tho'.

Previous Topic: Help with the stored proc
Next Topic: sql help
Goto Forum:
  


Current Time: Sun Dec 04 18:36:14 CST 2016

Total time taken to generate the page: 0.08075 seconds