Home » SQL & PL/SQL » SQL & PL/SQL » Jus read this code dealing with cursors and then answer
icon5.gif  Jus read this code dealing with cursors and then answer [message #205068] Thu, 23 November 2006 03:55 Go to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
hi everybody...
jus look at the code below and try to answer my question

declare
a number(4);
b number(10);
cursor emp_cur is
select emp_id from emp_det;

begin
open emp_cur;
if emp_cur%isopen then
loop
fetch emp_cur into a;
exit when emp_cur%notfound;
if emp_cur%found then
update emp_det set emp_ph=&b where emp_id=a;
end if;
end loop;
end if;
close emp_cur;
end;


In the above code the value of b is asked only once and the values get updated with this value throughout the table

i hav tried to undefine and clear the buffer. All my options didnt help out.

jus send ur answers so tat each time the value should be asked from the user. is it possible within cursors.
Re: Jus read this code dealing with cursors and then answer [message #205070 is a reply to message #205068] Thu, 23 November 2006 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, what you're trying to do isn't possible. You can't get interactive communication with the user in that fashion.

The nearest thing I can think of to a solution would be:
SELECT 'update emp_det set emp_ph=&ph_for_'||emp_id||' where emp_id='||emp_id||';'
FROM   emp_det;
This will generate you a long list of Update commands, each of which will have a meaningful substitution parameter name.
If you spool this output to a file, and then execute that file from SQL*Plus, then it will ask for an EMP_PH value for each row.
icon5.gif  Re: Jus read this code dealing with cursors and then answer [message #205077 is a reply to message #205070] Thu, 23 November 2006 04:33 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
may be i create this cursor within a procedure and when i run it

i want the phone no's each time to be asked from the user;

what u have said is to randomly add the emp_id at the last.

kindly help out
Re: Jus read this code dealing with cursors and then answer [message #205080 is a reply to message #205077] Thu, 23 November 2006 04:45 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Sqlplus is NOT the tool for data-entry. It is not meant to run applications in it.
Re: Jus read this code dealing with cursors and then answer [message #205081 is a reply to message #205077] Thu, 23 November 2006 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing that you haven't tried doing what I said, and have managed to misunderstand it as well. This will prompt you for the phone number for each row, and will include the Emp Id in the substitution parameter name.

If you want to run this code from within a procedure, then it's just not going to work the way you want.
You can run it from SQL*Plus, but Pl/Sql doesn't do substitutions in the same way.
Re: Jus read this code dealing with cursors and then answer [message #205089 is a reply to message #205081] Thu, 23 November 2006 05:06 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Can u give any other suggestions so that i can achieve my

requirement but using cursors.

Kindly help it out.
jus help me out [message #205091 is a reply to message #205080] Thu, 23 November 2006 05:09 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
What you have said is correct

but this requirement arises when we connect the frontend with

the server

kindly help it out.
Re: jus help me out [message #205110 is a reply to message #205091] Thu, 23 November 2006 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahhh - here's a new piece of information.

What are you using as a front end?


Regardless, what you'll need to do is either:
1) Collect all the phone numbers that you want into the front end, and then pass these back as a table, and then do the updates from the data contained in the table, or
2) Step through the records in EMP_DET one at a time in the front end, and for each record, gather a phone number and then run an update statement for that record.

There is no way from a server side pl/sql procedure to pause processing, return control to the front end software, gather another value, and continue processing.
Thanks [message #205119 is a reply to message #205110] Thu, 23 November 2006 06:27 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
ya really thanks for that information

that at the server side its not possible to stop the process.


Regards,
Re: TO EXECUTE SPOOL [message #205285 is a reply to message #205070] Fri, 24 November 2006 03:00 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
I HAV USED SPOOL AND GOT THE RESULT IN A FILE

HOW CAN I EXECUTE TAT IN PL/SQL


PLZ HELP ME OUT
Re: TO EXECUTE SPOOL [message #205287 is a reply to message #205285] Fri, 24 November 2006 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can run it from SQL*Plus by using the @ notation.
Your only way of running it from Pl/Sql would be to move the file to the Server, open it using Utl_File, read it in one line at a time and execute it using NDS.

Perhaps if you could provide more detail about what exactly you're trying to do here, then we could have a more productive discussion.
Re: TO EXECUTE SPOOL [message #205307 is a reply to message #205287] Fri, 24 November 2006 04:04 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
My objective is

to get all the records from the table and then update only

certain phone no's by referring to the emp_id.

for ex:

get 1000 records from the table as a cursor

and update only certain ph_nos in it. each time the value is to

be entered by us. i mean the ph_no must be entered by me each time.

hope so u hav understood my objective...

jus help it out.
Re: TO EXECUTE SPOOL [message #205315 is a reply to message #205307] Fri, 24 November 2006 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you plan on using as the front end to enter the data through?
Re: TO EXECUTE SPOOL [message #205330 is a reply to message #205315] Fri, 24 November 2006 04:54 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
we use power builder as a front end...
Re: TO EXECUTE SPOOL [message #205341 is a reply to message #205330] Fri, 24 November 2006 05:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can be of very little help with Powerbuilder I'm afraid.

The solution I would pick would be:
1) Display the Emp_Det records on screen in powerbuilder, with a field to type the phone number into.
2) After each phone number has been typed in, call a procedure on the database, passing it the Emp_Id and the new phone number. This procedure will then update the Emp_Det table for that Emp_id, setting the phone number to the value passed.
3)After this procedure is finished, control will return to Powerbuilder, and the user can enter another phone number.
Re: TO EXECUTE SPOOL [message #205351 is a reply to message #205341] Fri, 24 November 2006 05:41 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
ya wat u hav said is correct

but is it possible to use the cursors instead of calling the
procedure, so that the hit on the database wil be less.
Re: TO EXECUTE SPOOL [message #205358 is a reply to message #205351] Fri, 24 November 2006 06:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The database load for a user interfaced process like this will be minimal.

The other option I'd try doesn't use cursors either.

1) Display the Emp_Det records on screen in powerbuilder, with a field to type the phone number into.
2) After all the phone numbers have been typed in, call a procedure on the database, passing it an array containing all the Emp_Ids and the new phone number entered for each one.
This procedure will look like:
  type emp_id_tab        is table of number index by binary_integer;
  type emp_phone_num_tab is table of number index by binary_integer;  
  
  procedure update_emp (t_emp_id in emp_id_tab
                       ,t_emp_ph in emp_phone_num_tab) is
  begin
    forall idx in t_emp_id.first .. t_emp_id.last
    update emp_test
    set    phone_no = t_emp_ph(idx)
    where  empid = t_emp_id(idx);
    
  end;



Previous Topic: Insert into blob columns
Next Topic: Can we build condition using CASE in WHERE Clause ?
Goto Forum:
  


Current Time: Tue Feb 18 13:40:04 CST 2025