Re: PL/SQL error

Morten <c960901_at_aix5.kbar.dtu.dk> instructed their monkeys to type:

I'll annotate your code for ease:

>DECLARE
> v_docTable VARCHAR(32);

this is not needed, see below

>CURSOR c_docTypeCursor IS
> SELECT table_name
> FROM doc_type;
>
>BEGIN
> FOR v_docTable IN c_docTypeCursor

cursor for loops use an undefined subscript, which is a record datatype of the cursor%rowtype, so using v_doctable here is invalid, unless you remove the declaration of v_docTable.

> LOOP
> INSERT INTO document (id, type)
> SELECT v_docTable.id, doc_type.id
> FROM v_docTable, doc_type

using a variable in a from clause like this is invalid, you will need to use dynamic SQL, read up on the DBMS_SQL package

> WHERE doc_type.table_name = v_docTable;
> END LOOP;
> CLOSE c_docTypeCursor;

you don't need to close a cursor after a cursor for loop

> COMMIT;
>END;
Mark Styles
Oracle developer and DBA
http://www.lambic.co.uk/company Received on Fri Jul 02 1999 - 08:40:42 CDT