Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL error

Re: PL/SQL error

From: Mark Styles <styles-nospam_at_lambic.co.uk>
Date: Fri, 02 Jul 1999 13:40:42 GMT
Message-ID: <377cc095.18435368@news.intra.bt.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US