470,632 Members | 2,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

PROC Problem : Select for update

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
4 13602
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
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
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
> >
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.

Similar topics

4 posts views Thread by Trevor Bishop | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.