Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

copy with ref cursor

Posted on 2003-03-02
3
Medium Priority
?
792 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
  • 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

581 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