Home » SQL & PL/SQL » SQL & PL/SQL » Cursor (oracle 10g)
Cursor [message #362130] Mon, 01 December 2008 02:58 Go to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Hi,
I have a doubt in this query.pls find the query below.....

create or replace procedure TEST_s1 as
cursor c_f is
select MIN_P,MAX_AP,D_sno,d_ml from Table where rownum < 5;
v_a char(15);
v_b char(15);
v_c varchar(32);
v_d varchar(24);
begin
open c_f;
loop
fetch c_f into v_a,v_b,v_c,v_d;
exit when c_f%notfound;
end loop;
close c_f;
end;

Actually,I need a procedure for this statement

Insert into tablename(select MIN_P,MAX_AP,D_sno,d_ml from Table where cond)

whether this can be used using the cursor concept????pls let me know as it has millions of record?



my doubt is:
1.whether in cursor , "Insert into ..."can be done??? pls let me know how it can be done???
2.and in the above cursor..how can i check whether the rows are fetched and placed in the v_a and how to use v_a?

Thanks,
Sowmya
Re: Cursor [message #362134 is a reply to message #362130] Mon, 01 December 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

1.
Why doing with a PL/SQL curosr something that can be done with a SQL statement?
Otherwise you use "insert values".

2.
They are placed, trust Oracle.
How to use v_a? like any other variable.


Regards
Michel
Re: Cursor [message #362140 is a reply to message #362134] Mon, 01 December 2008 03:22 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thanks for your reply.
But could you please clarify this? whether in the cursor concept..
Insert into tablename( select b.a,b.b,c.c,c.d from table b,table c
where b.sno =d.sno groupby b.snno,b.mno); can be written
Asof now I have written the above statement which is in braces as cursor.
And how can i do the "insert into tablename"??? I am doing this to convert into package.

Thanks,
Sowmya
Re: Cursor [message #362154 is a reply to message #362140] Mon, 01 December 2008 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer the most important question: why do you want to replace a quick process by a very slow one?

You already do almost all things in your first code: fetch the values now insert them with "insert values".

Regards
Michel
Re: Cursor [message #362155 is a reply to message #362140] Mon, 01 December 2008 03:53 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sowmyaa,

I do hope this has no relation to your earlier post:
need to convert a set of sql scripts into pl\sql program which needs to be triggered automatically
So the answers there didn't help much?

Regards,
Jo
Re: Cursor [message #362166 is a reply to message #362155] Mon, 01 December 2008 04:18 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
It helped me so much....
I created package which contains all statments) as per the mail.....
Thanks,
Sowmya

As,I am changing this one to pl\sql....it takes time...becoz it needs to tbe autoamted!

My doubt is, I fetched the values and placed it in the variables V_a,V_b,V_c and v_d...
Now I want to insert these values into another table
How it can be done? I tried in...but coudn't find it out...!
Will be helpful to me if you can let me know the solution....

Note:
I need to do
insert into TableB
( select MIN_P,MAX_AP,D_sno,d_ml from TableA where rownum < 5);
I wrote the select part in curosr and how to do the insert into part????
Hope ,the question is clear now!

Thanks,
Sowmya
Re: Cursor [message #362181 is a reply to message #362166] Mon, 01 December 2008 04:53 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sowmyaa,
In case you didn't understand Michel's quote:
Quote:

You already do almost all things in your first code: fetch the values now insert them with "insert values".



You can do this by using the general syntax of INSERT Statement
INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);

Here replace the value-1, value-2, etc... with your variable names (V_a, V_b, etc...). Place this insert statement inside your loop.

But why do you have to do it in "break-downs" when you can simply achieve this (as you only suggested) in a single query:
insert into TableB
( select MIN_P,MAX_AP,D_sno,d_ml from TableA where rownum < 5);


Hope this helps.
Regards,
Jo

[Updated on: Mon, 01 December 2008 04:53]

Report message to a moderator

Re: Cursor [message #362199 is a reply to message #362130] Mon, 01 December 2008 05:29 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member

Hi,
Thank u so much for your replies.....
as I told u...i need this in pl\sql...that's y i'm doing this instead of the single statment....
Could you please chk the below..whether it's correct..but i'm getting error....
I want to select form 2 tables(table1) and insert into the other table(table2)...whether this can be done?

create or replace procedure TEST_s
as
cursor c_f is
select M_AP,M_AS,DC_SNO,DC_MOL from T_Table1 where rownum <5;
v_a char(15);
v_b char(15);
v_c varchar2(32);
v_d varchar2(24);
begin
open c_f;
loop;
INSERT INTO T_Table2
(Mn_AP, M_AS,Dc_SNO,DC_MOL)
VALUES
(v_a,v_b,v_c,v_d);
exit when c_f%not found;
end loop;
close c_f;
end;
Re: Cursor [message #362201 is a reply to message #362199] Mon, 01 December 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 01 December 2008 10:12
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel



And DON'T use IM speak.

Re: Cursor [message #362206 is a reply to message #362199] Mon, 01 December 2008 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is a serious failure in either communication or comprehension here.

If this query works:
insert into TableB
( select MIN_P,MAX_AP,D_sno,d_ml from TableA where rownum < 5);

and you wish to have it as an anonymous pl/sql block, it would be:
BEGIN
  insert into TableB
  ( select MIN_P,MAX_AP,D_sno,d_ml from TableA where rownum < 5);
END;
/


If you wished to make it into a procedure, it would be:
CREATE OR REPLACE PROCEDURE proc_name AS
BEGIN
  insert into TableB
  ( select MIN_P,MAX_AP,D_sno,d_ml from TableA where rownum < 5);
END;
/


Why, in the name of Cthulhu and all his little Deep Ones would you want to convert it into a cursor loop, unless you actively wanted everything to run slower.
Re: Cursor [message #362209 is a reply to message #362130] Mon, 01 December 2008 05:49 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
thank u !....
Actually I wanted it to make in SP's.

Re: Cursor [message #362215 is a reply to message #362130] Mon, 01 December 2008 05:53 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thank u...
But the tableA contents are now in the cursor C_f as 4 variables (v_a,v_b,v_c,V_d)
I wanted that value to be inserted into TableB....
That's my question from the first!
CREATE OR REPLACE PROCEDURE proc_name AS
BEGIN
insert into TableB
( select MIN_P,MAX_AP,D_sno,d_ml from TableA where rownum < 5);
END;



Re: Cursor [message #362216 is a reply to message #362199] Mon, 01 December 2008 05:54 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sowmyaa,

What is the error you are getting? I like the others am still confused why you need this way of INSERTs.

Anyways you forgot to fetch the records from the cursor before inserting them into the table. First do a FETCH then do an INSERT.

Anyways, JRowbottom's explanation might have made sense to you by now.

Regards,
Jo

[*** Added*** ]
Didn't see OP's other reply.

[Updated on: Mon, 01 December 2008 05:56]

Report message to a moderator

Re: Cursor [message #362224 is a reply to message #362130] Mon, 01 December 2008 06:24 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thank u so much...
It worked for me



Thanks and REgards,
Sowmya
Re: Cursor [message #362393 is a reply to message #362130] Tue, 02 December 2008 03:19 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Hi

Im confused while seeing this thread..Without fetching inserting then whats the need of using cursors ??
What i think at the first sight is you can go for FOR LOOP with RECORDS...
Re: Cursor [message #362407 is a reply to message #362393] Tue, 02 December 2008 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're not using a cursor, then what exactly are you planning to loop through.

Single SQL statements are the most performant and simplest solution to this problem.
Re: Cursor [message #362410 is a reply to message #362130] Tue, 02 December 2008 03:55 Go to previous message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
dbms_output.put_line(<variables>)
Previous Topic: Need help to write query and Help to write SQL (2 threads merged by bb)
Next Topic: balance calculation
Goto Forum:
  


Current Time: Thu Dec 08 02:22:47 CST 2016

Total time taken to generate the page: 0.11603 seconds