| What am I making wrong [message #423942] |
Tue, 29 September 2009 05:59  |
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 #423946 is a reply to message #423942] |
Tue, 29 September 2009 06:06  |
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' 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 #423955 is a reply to message #423952] |
Tue, 29 September 2009 06:17 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
