I'm attempting to write a cursor that will read data from a table, reformat the data and insert it into another table. I have an existing SQL script that contains a cursor. When I copy the same cursor into my script it doesn't work. I get an error message of ORA-06512: at line 188. Line 188 contains the following "FETCH varial_fib into" I don't know where my problem is since both of the cursors contained are identical.
Here's a copy of portions of my script: (the entire script is attached to this thread)
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
Generally speaking, this kind of a fetch is not recommended. Causes problems such as you have. Why don't you try something like this:
cursor varial_fib is
/* note that I don't declare variables you used to fetch data into them */
for cur_r in varial_fib loop
cur_r.applik_kz || cur_r.firmennummer || cur_rbuchungsperiode;
insert into ...
I *guess* you received an error message because there was incorrect number of fetched fields and corresponding "into" variables, or respective datatypes might be wrong, or ... something else.
I'd suggest you to simplify the way data is manipulated and try to execute your procedure again. Let us know what happened.
Using FETCH into 37 variables really isn't a good idea. Probably you have the wrong number of variables (eg. should be 36 or 38), or some of them are in the wrong order so that it tries to fetch 10 chars into a CHAR(3) variable or whatever. (Sorry, I haven't the stamina to actually count and compare them for you!)
Instead you should use a row variable anchored to the cursor:
Maybe I'll try this another way with VB. I'm looking to write an interface between 2 systems. All I need to do really is read a tablespace and format it into a file for input into the other application. Perhaps this might be more easier to do this in VB rather than with SQL scripts.
What I'm looking to do is a little bit more than just copying the data from one table an to another. I'm looking to read the data from one table and then insert it into another table field in a special format. The newly created table field is then read once more by another SQL script that writes it to a file.
Attached are snippets of the tables that I'm using in tables.zip and the output is in output.txt
You can see the information in the destination table varial_fibu the column "schrieb_string" that contains all the information from the pfib table. The "schrieb_string" rows are read and written into a file (output.txt)
The values in the schrieb_string and likewise the output.txt have to adhere to a format. The file is read as a string. Example the first 2 letters of a entry is seen as "XX" stating the application this must be two letters followed directly by the "firmennummer (company number) this has to be 3 numbers and so on for 93 values. This formatting can be seen in the SQL script.
A value might be 1 or 2 characters in the database but might need to be written into the output file as 8 characters so that the application that reads this file knows where to insert the values in to which fields into the application.
In VB I can read the pfib table and write the output file directly and hopefully less complicated.
I tried your suggestion this time Tony but I get a new error now saying:
" r_fib varial_fib%ROWTYPE;
ERROR at line 172:
ORA-06550: line 172, column 4:
PLS-00103: Encountered the symbol "R_FIB" when expecting one of the following:
. ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
group having intersect minus order start union where connect
The symbol ";" was substituted for "R_FIB" to continue.
ORA-06550: line 222, column 2:
PLS-00103: Encountered the symbol "EXIT" when expecting one of the following:
. ( , % ; limit "
Can you be a little bit more specific as to how I use a cursor record better?
Here's a snippet of where I added your suggestion in my script:
RPAD(NVL(lieferanten_kunden_name,' '),30,' '), -- ku_li_name
fremdw_betrag -- fw_vz_ermittler
where gebucht <> 'J'
FETCH varial_fib into
I tried your suggestions and now I get another set of error messages, but I'm not quite there yet. I get a new set of error messages now:
cursor varial_fib IS
ERROR at line 124:
ORA-06550: line 124, column 9:
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column
ORA-06550: line 173, column 10:
PL/SQL: Item ignored
ORA-06550: line 181, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or
ORA-06550: line 180, column 3:
PL/SQL: SQL Statement ignored