PROC Problem : Select for update

[Oracle 9Ri2 AS 2.1 IA 64]

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.

[Wario]

rb*******@hotma il.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7f******* *************** ****@posting.go ogle.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;
/

[J]

rb*******@hotma il.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7f******* *************** ****@posting.go ogle.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

[Danes]

r0***@insightbb .com (Wario) wrote in message news:<92******* *************** ****@posting.go ogle.com>...

rb*******@hotma il.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7f******* *************** ****@posting.go ogle.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;

[Wario]

> >

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