?
Solved

copy with ref cursor

Posted on 2003-03-02
3
Medium Priority
?
788 Views
Last Modified: 2013-12-12
hi, i would like to copy some datas to a log table.
the procedure header is fix!



  TYPE T_RC IS REF CURSOR;


procedure load (vid IN NUMBER,vList OUT T_RC ) IS

how to declare a log_cursor?..

begin

 vQuery:='select username,password from profile where id=vid';

 open VList for vQuery;

for vlistrecord in vlist loop
      insert into log_cursos values (vlistrecord.username,vlistrecord.password);

 end loop;

end load;




sorry or bad English!!

ize-ez-jo-lesz

0
Comment
Question by:ize-ez-jo-lesz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 300 total points
ID: 8056200
You cannot use a FOR loop with a REF CURSOR, you have to OPEN, FETCH and CLOSE explicitly.  Also, you must declare vlistrecord with the appropriate datatypes for the REF CURSOR, i.e. in this case (username,password).

Also, you need to treat vid as a bind variable in the vQuery string - e.g. :vid.  Then bind the vid value with the USING clause of the OPEN.

So your code will be something like:

procedure load (vid IN NUMBER,vList OUT T_RC ) IS
  TYPE vlist_t IS RECORD( username VARCHAR2(30),
                          password VARCHAR2(30) );
  vlistrecord vlist_t;
  vQuery VARCHAR2(200);
begin
  vQuery:='select username,password from profile where id=:vid';

  open VList for vQuery;
  loop
    fetch VList into vlistrecord using vid;
    exit when VList%notfound;
    insert into log_cursos(username,password)
    values (vlistrecord.username,vlistrecord.password);
  end loop;

end load;

0
 
LVL 4

Expert Comment

by:stemu2000
ID: 8061984
here is the correct version:



PROCEDURE load (vid IN NUMBER,vList OUT T_RC ) IS
 vQuery VARCHAR2(200);
 v1  VARCHAR2(200);
 v2  VARCHAR2(200);
BEGIN
 vQuery:='select username,password from profile where id=:vid';

 OPEN VList FOR vQuery USING vid;
 LOOP
   FETCH VList INTO v1,v2;
   EXIT WHEN VList%NOTFOUND;
--   INSERT INTO log_cursos(username,password)
 --  VALUES (v1,v2);
 END LOOP;
 CLOSE VList;
 
 OPEN VList FOR vQuery USING vid;

END load;
/


make sure you ppass bind parameters in the OPEN call, not the FETCH call! - and also to pass back the original cursor after logging it, it is acctually necessary to reopen the cursor!


Cheers, Stefan

0
 
LVL 4

Expert Comment

by:stemu2000
ID: 8061990
ups, forgot to remove the commented out code for inserting:


PROCEDURE load (vid IN NUMBER,vList OUT T_RC ) IS
vQuery VARCHAR2(200);
v1  VARCHAR2(200);
v2  VARCHAR2(200);
BEGIN
vQuery:='select username,password from profile where id=:vid';

OPEN VList FOR vQuery USING vid;
LOOP
  FETCH VList INTO v1,v2;
  EXIT WHEN VList%NOTFOUND;
  INSERT INTO log_cursos(username,password)
  VALUES (v1,v2);
END LOOP;
CLOSE VList;

OPEN VList FOR vQuery USING vid;

END load;
/


Cheers, Stefan
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question