PROC Problem : Select for update

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Oracle 9Ri2 AS 2.1 IA 64
    #1

    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.
    Tags: None
    • Wario
      #2
      Re: PROC Problem : Select for update

      rbenzarti@hotma il.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7fa25ffe. 0408200852.41c1 de37@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;
      /

        Comment

        • J
          #3
          Re: PROC Problem : Select for update

          rbenzarti@hotma il.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7fa25ffe. 0408200852.41c1 de37@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

            Comment

            • Danes
              #4
              Re: PROC Problem : Select for update

              r0cky@insightbb .com (Wario) wrote in message news:<9204a53e. 0408250655.21ce 545a@posting.go ogle.com>...
              rbenzarti@hotma il.com (Oracle 9Ri2 AS 2.1 IA 64) wrote in message news:<7fa25ffe. 0408200852.41c1 de37@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;

                Comment

                • Wario
                  #5
                  Re: PROC Problem : Select for update

                  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

                    Comment

                    Previous template Next
                    Working...