Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: ORA-00984: column not allowed here
PL/SQL: ORA-00984: column not allowed here [message #378797] Thu, 01 January 2009 23:19 Go to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
HI ,
I CERATED A PROCEDURE


execute dbms_application_info.set_client_info(3);

spool D:\existingcust\existingcust.log;
create or replace procedure existing_cpf is

	V_COUNT NUMBER;
	V_PAYEE_NAME VARCHAR2(150);
	V_CUSTOMER_NAME VARCHAR2(150);
	V_REFERENCE VARCHAR2(10);
	V_ATTRIBUTE1 VARCHAR2(10);
	V_CPF_NO VARCHAR2(10);
	V_UEN_NO VARCHAR2(10);
	

begin

 V_COUNT := 0; 
	  	
         
   		declare CURSOR c1 IS
		
         select a.payee_name,c.party_name,b.orig_system_reference,b.attribute1,a.cpf_no,a.uen_no 
		 into v_payee_name,v_customer_name,v_reference,v_attribute1,v_cpf_no,v_uen_no
	from FMSUPLOAD.ttas_invoice a,ar.hz_cust_accounts b,ar.hz_parties c
	where b.party_id=c.party_id and 
	a.payee_name=c.party_name and
	a.cpf_no!= b.orig_system_reference and
	b.attribute1 is null;
	 
begin
	if (V_COUNT >= 1) then
	

	
			FOR reccust IN c1 LOOP
					 dbms_output.put_line('cpf number is not correct '||'reccust.V_CPF_NO'|| ' Found.' );
					 dbms_output.put_line(CHR(5) ||'************************************************************************' );
			         dbms_output.put_line(CHR(5) );
			         dbms_output.put_line(CHR(5) ||'            Error found in data. Correct the data and then       ' );
			         dbms_output.put_line(CHR(5) ||'                   Run Load TTAS Invoice once again.                ' );
			         dbms_output.put_line(CHR(5) );
			         dbms_output.put_line(CHR(5) ||'************************************************************************' );
		     END LOOP;
		

	
	else
	
        
        FOR reccustS IN c1 LOOP
    	   Insert into UEN_TEMP (CUSTOMER_NAME,CPF_NO,UEN_NO) values (recustS.v_customer_name,recustS.v_reference,recustS.v_uen_no);
		   dbms_output.put_line('success');
		 End loop;
  end if;
END;
end;
/
COMMIT;

accept dummy prompt "Press any Key to Continue...."
spool off;
exit;

WHILE  I COMPLILE THE PROCEDURE IM GETTING ERROR THIS COLUMN NOT ALLOWED HERE.



I CREATED A TABLE UEN_TEMP

CUSTOMER_NAME VARCHAR2(150)
CPF_NO  VARCHAR2(10)
UEN_NO  VARCHAR2(10)



SO please SOLVE THIS ISSUE FOR ME.

im using toad to exec.
and again when i comment the insert statement and run the procedure
and exec procedure then im getting pl/sql procedure completed successfully but im not getting the output.
how to see output.


[mod-edit: added code tags; next time please add them yourself]

[Updated on: Thu, 01 January 2009 23:41]

Report message to a moderator

Re: PL/SQL: ORA-00984: column not allowed here [message #378799 is a reply to message #378797] Thu, 01 January 2009 23:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The problem is in this line:

Quote:

values (recustS.v_customer_name,recustS.v_reference,recustS.v_uen_no)



You either need to use variables like v_customer_name or use cursor values like recustS.party_name.

There may be other errors or more efficient methods. That is just the first thing that I saw.

Re: PL/SQL: ORA-00984: column not allowed here [message #378802 is a reply to message #378797] Thu, 01 January 2009 23:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you expect v_count to ever become > 0?
dbms_output.put_line('cpf number is not correct '||'reccust.V_CPF_NO'|| ' Found.' );

this is the same as
dbms_output.put_line('cpf number is not correct reccust.V_CPF_NO Found.' );
You simply concatenate three literals.

Also, you can not do a select into in a cursor definition

[Updated on: Thu, 01 January 2009 23:46]

Report message to a moderator

Re: PL/SQL: ORA-00984: column not allowed here [message #378803 is a reply to message #378797] Thu, 01 January 2009 23:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
May I know, where did you come to INTO clause in CURSOR definition?
It is not correct in this context.

Before using cursors, it would be good to have a look into PL/SQL User's Guide and Reference to see how they shall be used. This book is part of Oracle documentation, available e.g. online on http://tahiti.oracle.com/.

But, in this case, you do not need to use cursor at all. You can simply
INSERT INTO UEN_TEMP (CUSTOMER_NAME,CPF_NO,UEN_NO)
SELECT <appropriate column names>
FROM <the rest of SQL statement in cursor>


[Edit: Added the last remark]

[Updated on: Fri, 02 January 2009 00:07]

Report message to a moderator

Re: PL/SQL: ORA-00984: column not allowed here [message #378808 is a reply to message #378799] Fri, 02 January 2009 00:15 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
error got resolved but.
values not inserting in the table how to do that.
Re: PL/SQL: ORA-00984: column not allowed here [message #378809 is a reply to message #378808] Fri, 02 January 2009 00:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Did you fix all three of the problems that were pointed out? You need to post a copy and paste of a run of your revised code from SQL*Plus, along with any errors that you get. Please refer to the forum guidelines for how to post a complete question.

Re: PL/SQL: ORA-00984: column not allowed here [message #378811 is a reply to message #378808] Fri, 02 January 2009 00:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
radhavijaym wrote on Fri, 02 January 2009 07:15
error got resolved but.

May I know how?
Quote:
values not inserting in the table how to do that.

Use values from cursor represented by cursor column name.
Or, if this is not homework assignment on cursor usage, rewrite it as I suggested in my previous post.
Re: PL/SQL: ORA-00984: column not allowed here [message #378812 is a reply to message #378803] Fri, 02 January 2009 00:26 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
i used cursor because if count is >1 i want to display a msg
if count less than 1 then i want to insert values but
now i removed for loop for else condition but now values are not inserting but when i exec select * from uen_temp
my row numbers are increasing but the values are not displaying
im sending u the screen shot.
  • Attachment: uen_temp.pdf
    (Size: 314.08KB, Downloaded 481 times)
Re: PL/SQL: ORA-00984: column not allowed here [message #378814 is a reply to message #378811] Fri, 02 January 2009 00:32 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
hi
i had wrote how u told but still compiling success fully and printing the "success" but when i do select * from uen_temp still im getting same values are not displaying but row size is 3

create or replace procedure existing_cpf is

V_COUNT NUMBER;



begin

V_COUNT := 0;


declare CURSOR c1 IS

select a.payee_name,c.party_name,b.orig_system_reference,b.attribute1,a.cpf_no,a.uen_no
from FMSUPLOAD.ttas_invoice a,ar.hz_cust_accounts b,ar.hz_parties c
where b.party_id=c.party_id and
a.payee_name=c.party_name and
a.cpf_no != b.orig_system_reference and
b.attribute1 is null;

begin
if (V_COUNT > 0) then



FOR reccust IN c1 LOOP
dbms_output.put_line('cpf number is not correct '||'reccust.V_CPF_NO'|| ' Found.' );
dbms_output.put_line(CHR(5) ||'************************************************************************' );
dbms_output.put_line(CHR(5) );
dbms_output.put_line(CHR(5) ||' Error found in data. Correct the data and then ' );
dbms_output.put_line(CHR(5) ||' Run Load TTAS Invoice once again. ' );
dbms_output.put_line(CHR(5) );
dbms_output.put_line(CHR(5) ||'************************************************************************' );
END LOOP;



else


-- FOR reccust IN c1 LOOP
INSERT INTO UEN_TEMP (CUSTOMER_NAME,CPF_NO,UEN_NO)
SELECT c.party_name,b.orig_system_reference,a.uen_no
FROM FMSUPLOAD.ttas_invoice a,ar.hz_cust_accounts b,ar.hz_parties c
where b.party_id=c.party_id and
a.payee_name=c.party_name and
a.cpf_no = b.orig_system_reference and
b.attribute1 is null;
dbms_output.put_line('success');
--End loop;
end if;
END;
end;


please help me.
Re: PL/SQL: ORA-00984: column not allowed here [message #378818 is a reply to message #378797] Fri, 02 January 2009 00:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
please help me.

Maybe you shall learn how to debug the code.
1) Issue the SELECT statement in TOAD to see its result
2) Study trace messages by DBMS_OUTPUT. They are available in special tab in the bottom. You shall firstly enable it by special button before anything is shown. Have a look at TOAD's help (F1).
3) Check the table before the procedure execution. The NULL values may reside in the table from some previous procedure execution(s). If they are not important, DELETE them or TRUNCATE the table before procedure execution.
Re: PL/SQL: ORA-00984: column not allowed here [message #378824 is a reply to message #378814] Fri, 02 January 2009 00:55 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
hi,
values are inserting in the uen_temp table but now what happend
v_count is not activating.
ie i had values for first condition

select distinct a.payee_name,c.party_name,b.orig_system_reference,b.attribute1,a.cpf_no,a.uen_no
from FMSUPLOAD.ttas_interface_invoice a,ar.hz_cust_accounts b,ar.hz_parties c
where b.party_id=c.party_id and
a.payee_name=c.party_name and
a.cpf_no != b.orig_system_reference and
b.attribute1 is null;


but my cursor is direclty going to else condition ie v_count is always being zero so please help me how my cusrsor goes to if(count>1) loop how my count values will be increased is there any error in declaring count variable or is there any issue in assiaging it.

please help me its urgent.

my cursor must go into this loop

if (V_COUNT >= 1) then



FOR reccust IN c1 LOOP
dbms_output.put_line('cpf number is not correct '||'reccust.V_CPF_NO'|| ' Found.' );
dbms_output.put_line(CHR(5) ||'************************************************************************' );
dbms_output.put_line(CHR(5) );
dbms_output.put_line(CHR(5) ||' Error found in data. Correct the data and then ' );
dbms_output.put_line(CHR(5) ||' Run Load TTAS Invoice once again. ' );
dbms_output.put_line(CHR(5) );
dbms_output.put_line(CHR(5) ||'************************************************************************' );
END LOOP;
Re: PL/SQL: ORA-00984: column not allowed here [message #378831 is a reply to message #378824] Fri, 02 January 2009 01:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
but my cursor is direclty going to else condition ie v_count is always being zero so please help me how my cusrsor goes to if(count>1) loop how my count values will be increased is there any error in declaring count variable or is there any issue in assiaging it.

Yes, you assign V_COUNT zero in the beginning. This is the only assignment you make, so it is always zero. Maybe you shall specify where it should be assigned; but you may use the assignment operator (:=) to do so.
Re: PL/SQL: ORA-00984: column not allowed here [message #378843 is a reply to message #378824] Fri, 02 January 2009 01:37 Go to previous message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
issue was solved

i forgot to write this line.
v_count := v_count+1
Previous Topic: run sql queries as a script
Next Topic: How can we do it. (merged)
Goto Forum:
  


Current Time: Wed Dec 07 05:08:26 CST 2016

Total time taken to generate the page: 0.08916 seconds