Solved

Creating cursor for sql statement !!

Posted on 2004-04-16
13
829 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
[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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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