Solved

Creating cursor for sql statement !!

Posted on 2004-04-16
13
824 Views
Last Modified: 2013-12-11
Hi,

I an new to writing cursors in oracle. I am stuck while creating a cursor for some sql statements.

I have to write the following sql statement using cursor. How should i do it ?

/--------------------------------------------------------------

Insert Into DbSchema.DstTableName( dstcol1, dstcol2, dstcol3, dstcol4, dstcol5, dstcol6, dstcol7, dstcol8 )
( SELECT DbSchema.tableName.nextval, srccol1, srccol2, srccol3, srccol4, nvl(srccol5,0), nvl(srccol6,0), SYSDATE
From
table1 SRC
Where
srccol1 IS NOT NULL AND
NOT EXISTS
(SELECT 1
From
DbSchema.DstTableName Dst
Where
SRC.srccol1 = DST.dstcol1 AND SRC.col2 = DST.col2 )
) ;


----------------------------------------------------------------/

I have written a cursor like this :

/----------------------------------------------------------------

PROCEDURE DEMOPROC
is
    i1 INTEGER := 0;
    CURSOR c1
        IS
        SELECT
            *
        FROM
           SrcTableName SRC
       
BEGIN
    FOR r1 in c1 LOOP

      /*
   
    ( dstcol1, dstcol2, dstcol3, dstcol4, dstcol5, dstcol6, dstcol7, dstcol8 )
( SELECT DbSchema.tableName.nextval, c1.srccol1, c1.srccol2, c1.srccol3, c1.srccol4, nvl(c1.srccol5,0), nvl(c1.srccol6,0), SYSDATE
From
c1
Where
c1.srccol1 IS NOT NULL AND
NOT EXISTS
(SELECT 1
From
DbSchema.DstTableName Dst
Where
c1.srccol1 = DST.dstcol1 AND c1.srccol2 = DST.col2 )
) ;

*/      
 


    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('total rows processed: '||TO_CHAR(i1));
    EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
END;


----------------------------------------------------------------/

When i compile this procedure it gives me error as
(1): PLS-00201: identifier 'C1' must be declared at from c1 in my insert statement.
Can someone please tell me where i m getting wrong. Further i would like to do this in the batch of 1000. How i can achieve that ?

Thanks in the anitcipation that I will get some hint to solve this problem.

Regrads,
0
Comment
Question by:DipehKhakhkhar
  • 6
  • 4
  • 3
13 Comments
 
LVL 3

Accepted Solution

by:
dbms_chu earned 100 total points
ID: 10847886
For simplicity, I will assume that TABLE1 does not contain duplicate records.

Try this pseudo code:

CREATE OR REPLACE PROCEDURE ee_insert IS

   v_loop_ct   pls_integer := 0;
   v_insert_ct   pls_integer := 0;
     
   cursor cursor1 is
                 SELECT srccol1,
                             srccol2,
                             srccol3,
                             srccol4,
                             srccol5,
                             srccol6
                    From table1 SRC
                  Where srccol1 IS NOT NULL
                      AND NOT EXISTS
                                              (SELECT 1
                                                   From DbSchema.DstTableName Dst
                                                  Where SRC.srccol1 = DST.dstcol1
                                                     AND SRC.col2 = DST.col2);
        
BEGIN

  FOR r1 in cursor1
    LOOP

/* add business logic here  */

/* insert record using sequence */

      insert into  DbSchema.DstTableName
      values(DbSchema.tableName.nextval,
                  r1.srccol1,
                  r1.srccol2,
                  r1.srccol3,
                  r1.srccol4,
                   nvl(r1.srccol5,0),
                   nvl(r1.srccol6,0),
                  SYSDATE);
                  
    v_insert_ct := v_insert_ct+1;

/*  commit on a pre-defined interval  */

      if v_loop_ct > 999
      then begin
             commit;
               v_loop_ct := 0;
             end;
      else v_loop_ct := v_loop_ct+1;
    end if;
                        
      end loop;

/* once the loop ends, commit and display the total number of records inserted  */
      
      commit;
      --dbms_output  /*display insert count*/
      
   EXCEPTION
     WHEN NO_DATA_FOUND THEN   Null;
     WHEN OTHERS THEN
       -- dbms_output
       RAISE;
END ee_insert;
/

0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10848384
hi

the statements given by dbms_chu will work fine

regards
annamalai
0
 
LVL 1

Author Comment

by:DipehKhakhkhar
ID: 10859946
Hi,

Thanks for replying and providing solution to solve the problem. I have few questions in your mentioned solution :

1) My original insert statement is

/--------------------------------------------------------------

Insert Into DbSchema.DstTableName( dstcol1, dstcol2, dstcol3, dstcol4, dstcol5, dstcol6, dstcol7, dstcol8 )
( SELECT DbSchema.tableName.nextval, srccol1, srccol2, srccol3, srccol4, nvl(srccol5,0), nvl(srccol6,0), SYSDATE
From
table1 SRC
Where
srccol1 IS NOT NULL AND
NOT EXISTS
(SELECT 1
From
DbSchema.DstTableName Dst
Where
SRC.srccol1 = DST.dstcol1 AND SRC.col2 = DST.col2 )
) ;


----------------------------------------------------------------/


and you have not mentioned anything about how i should embed my where clause using cursor. You have used insert into (..) values (...) but no where clause. Can you please tell me how do i write the above statement using cursor.

2) You are incrementing v_insert_ct i.e.

 v_insert_ct := v_insert_ct+1;

and checking on v_loop_ct

/*  commit on a pre-defined interval  */

     if v_loop_ct > 999
     then begin
            commit;
             v_loop_ct := 0;
           end;
     else v_loop_ct := v_loop_ct+1;
    end if;

I didn't understand this. Are you using insert count anywhere else ? Can you please clarify this ?

3)  What is this datatype pls_integer ?

4) Why can't i use collection in my select statement in the cursor declaration rather than having each columns of the table ?

I am new to cursor so kindly bear with my simple questions.
Thanks once again!!

Regards,
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10860522
I'll try to respond to each of your questions:

(1)  The idea is that you will use a cursor to retrieve the records that you are interested in.  Once you have created and opened the cursor, then you can begin looping through the cursor and process the records.  

I placed the where clause from your original insert statement in to the select statement of the cursor.

In this process the insert is processing one record per iteration.  No need for a where clause in the insert statement.

(2) The v_loop_ct controls when a commit is issued.  v_insert_ct is used to count the total number of inserts.  It is used to display a message showing total inserts.

(3) pls_integer.  The Oracle documentation (plsql user guide) claims that pls_integer datatype is more efficient than using number is some cases.  I always use pls_integer for counters.

(4) I think you are referring to %rowtype as opposed to plsql collections?  
You can use %rowtype.  I listed the columns because it was simple.

0
 
LVL 1

Author Comment

by:DipehKhakhkhar
ID: 10862908
Hi,

Thanks for explaining everything I asked for. Highly appreciated.

Well you deserver points no doubt about it but I would like to know few things and it would be of great help if you answer these questions.

1) I am having insert, update and delte statements having same where clause as i mentioned above. But the performance was poor when i was having many tables like this and each having many rows(records) like this(right now in my development enviornment not having full fledged table). So i thought of using cursor to do this. But after running cursor with insert statement as mentioned in you solution i noticed that there was no great improvment. It took 1.6seconds to the task while only insert statement was faster than the cursor (it took 0.6 seconds). So i would like to know I am going rigth way or not ? Right now only insert is there, my idea was taking 1000 records at a time and doing insert, delete and update on it rather than havign three sql statement one for each update, delete and insert. So if i have update and delete script togather with insert, will that be faster in overall performance.

2) What else would you suggest to improve performance.

Thanks once again for helping.

Cheers!!
Dipesh
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10864606
WOW, these questions could easily be separated into two or three new posts.
I don't know anything about your application other than the details that you have mentioned in this thread, so you will understand if these suggestions don't fit.

I'll respond to both points.
(1)  "I am having insert, update and delte statements having same where clause"...
It sounds like your goal was to reduce the number of times that you re-read the same records in the subselect.  That looks like a good place to start.
From a performance perspective, I have had better results using SET processing as opposed to CURSOR processing.  


(2) Take a look at "FORALL" and "MERGE".

Before investing too much time trying out various pl/SQL changes, start monitoring your app using SQL Trace and TKPROF.

Good Luck!

>>> DBMS_CHU
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:DipehKhakhkhar
ID: 10864740
Hi,

Thanks once again for replying and informing me about few things.

What do you mean by this
"From a performance perspective, I have had better results using SET processing as opposed to CURSOR processing.   "

Do you ment to use SET instead of INSERT ? Is this what you meant ? If so how can i use SET replacing INSERT ?


I am using set for updates but stuck while writing a cursor equivalent for it. If the orginal update statement is something like this

=============================================================
Update DbSchema.DstTableName
               SET ( dstcol1, dstcol2, dstcol3, dstcol4, dstcol5)
                =
                ( SELECT srccol1,srccol2, nvl(srccol3,0), nvl(srccol,0), SYSDATE
                From
                    SrcTableName src
                Where
                    src.scrCol6= dst.Col6 AND SRC.srcCol7 = DST.dstCol7
                )
            WHERE EXISTS (
                SELECT
                    1
                From
                   SrcTableName SRC
                Where
                    SRC.srcCol6 = DST.dstCol6 AND SRC.srcCol7 = DST.dstCol7 ) ;

=============================================================

How i will be able to write this in cursor ?

Well the WHERE EXISTS clause is same as it was for insert (and as a matter of fact for delete also its the same). How i will be able to incorporare the first where clause in the update statement above in my cursor.

I hope i m not bothering you with my question.

I am using sql navigator and noting down times with that.

Thanks in the anticipation that you will provide me some solution to sovle this problem.

Regards,
Dipesh
0
 
LVL 1

Author Comment

by:DipehKhakhkhar
ID: 10864785
Hi,

Well i am able to write the update statement in the cursor but I am having different where clause in one of my statment(i.e. update) and two same where clause in two statements (insert and delete).

I mean I am having where exists condition which is there in the insert statement in the delete statement as well, so if i put this condition in the cursor declaration then half of my purpose will be solved. But I am having where not exists condition in my update. So will i incorporate this into my cursort statements.

Is it possible in cursor or not ?

Thanks once again !
Regards,
Dipesh
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10865375
u have write 2 separate where conditions, one for update and one for insert and delete statements

annamalai.
0
 
LVL 1

Author Comment

by:DipehKhakhkhar
ID: 10870096
How do i do that ?
Do i have to write those in my cursor declaration or inside proecdure block.

Your input is highly appreciated.
Regards,
Dipesh
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10871947
I came across this thread this morning.  It discusses a similar question on how to update.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10154317452243

0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10874545
hi there


yes u have write 2 cursors one with the where clause for insert and delete
and another cursor for update like the one given below

declare
/** for records having same where condition for delete and insert **/
cursor c1 is
select ....
where ....

/*** for records having different where condition for update **/
cursor c2 is
select ....
where ....
for update of field1, field2;

begin
   for i in c1
   loop
      insert .....
      commit;
      delete......
      commit;
   end loop;
 
   for i in c2
   loop
      update. ......
      commit;
   end loop;
end;
/

regards
annamalai
   
0
 
LVL 1

Author Comment

by:DipehKhakhkhar
ID: 10878915
Hi,

Thanks for replying.
This won't give me performance improvement.
I can't use merge since i m using 8i.
I can not take risk of paraller processing as it sometimes goes into spinning.

Is there some other way to acheive what I am looking for ?

I am trying hard to improve performance and need advice and ideas to do that.
Thanks once again.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now