By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,494 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,494 IT Pros & Developers. It's quick & easy.

PROC Problem : Select for update

P: n/a
Hello,
I am working with Oracle 9Ri2 version on Linux AS 2.1 for IA64.

I have a problem when i try to lanch the following program :
EXEC SQL PREPARE MyStmt FROM
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR
UPDATE;

EXEC SQL DECLARE MyCursor CURSOR FOR MyStmt;

EXEC SQL OPEN MyCursor;

EXEC SQL DESCRIBE MyStmt into q_APP;
EXEC SQL FETCH MyCursor into :Var1;

EXEC SQL CLOSE MyCursor;

When I don't use the PREPARE Statement i don't have problem. But when
i add the PREPARE statement program crached when it try to Open Cursor
( EXEC SQL OPEN MyCursor)

Thank you for your help.
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
rb*******@hotmail.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7f**************************@posting.google. com>...
Hello,
I am working with Oracle 9Ri2 version on Linux AS 2.1 for IA64.

I have a problem when i try to lanch the following program :
EXEC SQL PREPARE MyStmt FROM
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR
UPDATE;

EXEC SQL DECLARE MyCursor CURSOR FOR MyStmt;

EXEC SQL OPEN MyCursor;

EXEC SQL DESCRIBE MyStmt into q_APP;
EXEC SQL FETCH MyCursor into :Var1;

EXEC SQL CLOSE MyCursor;

When I don't use the PREPARE Statement i don't have problem. But when
i add the PREPARE statement program crached when it try to Open Cursor
( EXEC SQL OPEN MyCursor)

Thank you for your help.


Try using PL/SQL

declare
cursor work_cur is
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR UPDATE;
begin
for x in work_cur loop
update tab1 set champ1 = 'test'
where current of work_cur;

end loop;

commit;
end;
/
Jul 19 '05 #2

P: n/a
J
rb*******@hotmail.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7f**************************@posting.google. com>...
Hello,
I am working with Oracle 9Ri2 version on Linux AS 2.1 for IA64.

I have a problem when i try to lanch the following program :
EXEC SQL PREPARE MyStmt FROM
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR
UPDATE;

EXEC SQL DECLARE MyCursor CURSOR FOR MyStmt;

EXEC SQL OPEN MyCursor;

EXEC SQL DESCRIBE MyStmt into q_APP;
EXEC SQL FETCH MyCursor into :Var1;

EXEC SQL CLOSE MyCursor;

When I don't use the PREPARE Statement i don't have problem. But when
i add the PREPARE statement program crached when it try to Open Cursor
( EXEC SQL OPEN MyCursor)

Thank you for your help.


You don't need a prepare as u are not using any bind variables.
Also i wonder why u are using describe . its not needed.

Best Regards
J
Jul 19 '05 #3

P: n/a
r0***@insightbb.com (Wario) wrote in message news:<92**************************@posting.google. com>...
rb*******@hotmail.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7f**************************@posting.google. com>...
Hello,
I am working with Oracle 9Ri2 version on Linux AS 2.1 for IA64.

I have a problem when i try to lanch the following program :
EXEC SQL PREPARE MyStmt FROM
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR
UPDATE;

EXEC SQL DECLARE MyCursor CURSOR FOR MyStmt;

EXEC SQL OPEN MyCursor;

EXEC SQL DESCRIBE MyStmt into q_APP;
EXEC SQL FETCH MyCursor into :Var1;

EXEC SQL CLOSE MyCursor;

When I don't use the PREPARE Statement i don't have problem. But when
i add the PREPARE statement program crached when it try to Open Cursor
( EXEC SQL OPEN MyCursor)

Thank you for your help.


Try using PL/SQL

declare
cursor work_cur is
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR UPDATE;
begin
for x in work_cur loop
update tab1 set champ1 = 'test'
where current of work_cur;

end loop;

commit;
end;
/

Why use the declare?

begin
for x in (
SELECT CHAMP1
FROM TAB1
WHERE CHAMP2="4"
AND CHAMP3 = "C" FOR UPDATE
)
loop
update tab1 set champ1 = 'test'
where current of work_cur;

end loop;

commit;
end;
Jul 19 '05 #4

P: n/a
> >
Try using PL/SQL

declare
cursor work_cur is
SELECT CHAMP1 FROM TAB1 WHERE CHAMP2="4" AND CHAMP3 = "C" FOR UPDATE;
begin
for x in work_cur loop
update tab1 set champ1 = 'test'
where current of work_cur;

end loop;

commit;
end;
/

Why use the declare?

begin
for x in (
SELECT CHAMP1
FROM TAB1
WHERE CHAMP2="4"
AND CHAMP3 = "C" FOR UPDATE
)
loop
update tab1 set champ1 = 'test'
where current of work_cur;

end loop;

commit;
end;


Danes,

I like declares. In this case I use it to add readability, as long as
performance is not affected. Your suggestion works great though.

Wario
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.