OraFAQ Forum: SQL & PL/SQL » select into from a rec var to a table

Home » SQL & PL/SQL » SQL & PL/SQL » select into from a rec var to a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select into from a rec var to a table [message #41839]

Wed, 05 February 2003 06:24

LISA DANCKERT
Messages: 2
Registered: February 2003

Junior Member

I am running the following code. Runs once but I get an error when I run it again:
ORA-06550: line 110, column 10:
PLS-00403: expression 'my_table' cannot be used as an INTO-target of a SELECT/FETCH statement
ORA-06550: line 67, column 6:
PL/SQL: SQL Statement ignored

Here is the code:

DECLARE
CURSOR cur_dcsc IS
SELECT dcsc.a,
dd.b,
db.c,
ai.d,
ip.e,
pop.f,
po.g,
ip_pop.h
FROM t_dcsc dcsc,
t_db db,
t_ip_pop ip_pop,
t_ai ai,
t_ip ip,
t_dd dd,
t_pop pop,
t_po po;

cur_dcsc_rec cur_dcsc%ROWTYPE;

BEGIN

OPEN cur_dcsc;
LOOP
FETCH cur_dcsc INTO cur_dcsc_rec;
EXIT WHEN cur_dcsc%NOTFOUND;
SELECT dcsc.a,
dd.b,
db.c,
ai.d,
ip.e,
pop.f,
po.g,
ip_pop.h
INTO my_table
FROM t_dcsc dcsc,
t_db db,
t_ip_pop ip_pop,
t_ai ai,
t_ip ip,
t_dd dd,
t_pop pop,
t_po po
WHERE dcsc.key = dd.key
AND dcsc.key = db.key
AND dcsc.key = ip_pop.key
AND ip_pop.key = ip.key
AND ip_pop.key = ai.key
AND dcsc.key = pop.key
AND pop.key = po.key(+)
END LOOP;
COMMIT;
CLOSE cur_dcsc;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There are no rows');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There are several rows');

END;

/

Report message to a moderator

Re: select into from a rec var to a table [message #41842 is a reply to message #41839]

Wed, 05 February 2003 11:04

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Lisa, are you just trying to insert the result of the query into the table my_table?

Report message to a moderator

Re: select into from a rec var to a table [message #41846 is a reply to message #41839]

Wed, 05 February 2003 12:28

LISA DANCKERT
Messages: 2
Registered: February 2003

Junior Member

Yes. I understand you cannot use select into - a table. You must use insert. Is this the case?

Report message to a moderator

Re: select into from a rec var to a table [message #41849 is a reply to message #41846]

Wed, 05 February 2003 13:02

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Right, so you don't need any procedural logic (cursor, fetch, loop, etc.) at all. It is just:

insert
  into my_table
 select dcsc.a,
        dd.b,
        db.c,
        ai.d,
        ip.e,
        pop.f,
        po.g,
        ip_pop.h
   from t_dcsc dcsc,
        t_db db,
        t_ip_pop ip_pop,
        t_ai ai,
        t_ip ip,
        t_dd dd,
        t_pop pop,
        t_po po
  where dcsc.key = dd.key
    and dcsc.key = db.key
    and dcsc.key = ip_pop.key
    and ip_pop.key = ip.key
    and ip_pop.key = ai.key
    and dcsc.key = pop.key
    and pop.key = po.key(+);

Report message to a moderator

Previous Topic:	How to Place a Variable in Select query?
Next Topic:	Oracle Error - Urgent

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 21 13:21:03 CDT 2026