Home » SQL & PL/SQL » SQL & PL/SQL » What am I making wrong
What am I making wrong [message #423942] Tue, 29 September 2009 05:59 Go to next message
nacogxe
Messages: 4
Registered: September 2009
Junior Member
Hi,
I am new in PL SQL and I canĀ“t find the error in this script. Thanks in advance

begin
declare
v_tablename varchar(100);

cursor c_jahr is select distinct jahr from soldwh.PIVOTING4_KART4;

begin


for r_jahr in c_jahr loop

v_tablename:= 'soldwh.sol_f_kore_plan_'||to_char(r_jahr.jahr);

dbms_output.put_line (v_tablename);

EXECUTE IMMEDIATE 'DELETE FROM' || :v_tablename
|| 'WHERE EXISTS ( select pivoting5_kart4.kostenart_key'
|| 'from soldwh.pivoting5_KART4'
|| 'where pivoting5_kart4.KOSTENART_KEY = :v_tablename.KOSTENART_KEY'
|| 'and pivoting5_kart4.KOSTENSTELLE_KEY = :v_tablename.KOSTENSTELLE_KEY'
|| 'and pivoting5_kart4.PERIODE_KEY = :v_tablename.PERIODE_KEY'
|| 'and pivoting5_kart4.MANDANT_KEY = :v_tablename.PERIODE_KEY)';


EXECUTE IMMEDIATE 'Insert into' || :v_tablename
|| '(select * from soldwh.PIVOTING4_KART4)'
|| 'INSERT INTO (PERIODE_KEY,BUCHUNG_BETRAG,WAEHRUNG,MANDANT_KEY,KOSTENSTELLE_KEY,KOSTENART_KEY)'
|| 'SELECT PERIODE_KEY,BUCHUNG_BETRAG,WAEHRUNG,MANDANT_KEY,KOSTENSTELLE_KEY,KOSTENART_KEY'
|| 'FROM soldwh.pivoting5_KART4';


--select * from &v_tablename;

commit;

end loop;

end;
end;
Re: What am I making wrong [message #423945 is a reply to message #423942] Tue, 29 September 2009 06:05 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,


It would be much better if you can post the error which you are getting.

Smile
Soni
Re: What am I making wrong [message #423946 is a reply to message #423942] Tue, 29 September 2009 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would help if you told us a little about the error, such as what the error message is.

Looking at the code, I notice:
1) You use Varchar as a datatype when you should be using varchar2
2) You have lines like
where pivoting5_kart4.KOSTENART_KEY = :v_tablename.KOSTENART_KEY'
when you need to have
where pivoting5_kart4.KOSTENART_KEY = '||v_tablename||'.KOSTENART_KEY'

3) The second sql statement that you're writing makes no sense at all - it looks like you've concatenated two different insert statements together.
4) You're committing inside the loop
5) You've got an unnecessary BEGIN/END wrapped round the whole code

[typo]

[Updated on: Tue, 29 September 2009 06:09]

Report message to a moderator

Re: What am I making wrong [message #423947 is a reply to message #423942] Tue, 29 September 2009 06:06 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
And you haven't mentioned what is your problem or the error returned by Oracle.

regards,
Delna
Re: What am I making wrong [message #423948 is a reply to message #423942] Tue, 29 September 2009 06:08 Go to previous messageGo to next message
nacogxe
Messages: 4
Registered: September 2009
Junior Member
Sorry Embarassed
the error is ORA-01008 : Not all variables bound below
Re: What am I making wrong [message #423950 is a reply to message #423948] Tue, 29 September 2009 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be caused by item 2 on my list.
Re: What am I making wrong [message #423951 is a reply to message #423942] Tue, 29 September 2009 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the : in front of v_tablename.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: What am I making wrong [message #423952 is a reply to message #423946] Tue, 29 September 2009 06:13 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
JRowbottom sir,

Quote:
1) You use Varchar as a datatype when you should be using varchar2


Which can not be the problem.

SQL>r
  1  declare
  2     var varchar(100);
  3  begin
  4     var := 'This is test';
  5     dbms_output.put_line(var);
  6* end;
This is test

PL/SQL procedure successfully completed.


regards,
Delna
Re: What am I making wrong [message #423953 is a reply to message #423952] Tue, 29 September 2009 06:17 Go to previous messageGo to next message
nacogxe
Messages: 4
Registered: September 2009
Junior Member
Thanks
Re: What am I making wrong [message #423955 is a reply to message #423952] Tue, 29 September 2009 06:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I didn't say it was the problem, it's merely a problem.
From the documentation:Quote:
VARCHAR Datatype

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.
Re: What am I making wrong [message #423956 is a reply to message #423955] Tue, 29 September 2009 06:28 Go to previous messageGo to next message
nacogxe
Messages: 4
Registered: September 2009
Junior Member
I know.
Thanks.
I am workint on it.
Re: What am I making wrong [message #423957 is a reply to message #423955] Tue, 29 September 2009 06:34 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok. Thanks for that reminding that line.

regards,
Delna
Previous Topic: decode function help
Next Topic: Query with CONNECT BY PRIOR
Goto Forum:
  


Current Time: Tue Dec 06 12:25:30 CST 2016

Total time taken to generate the page: 0.06495 seconds