1 2 Previous Next 18 Replies Latest reply on Feb 26, 2007 9:27 AM by 94799

    Ansi SQL for "select ... for update nowait"

    401787
      Hi, All,

      I have a sql
      select ... for update nowait

      My boss wants it to be ANSI compliant.
      I am not familiar with ANSI SQL.

      What should be the syntax in Ansi?

      Thanks a lot!
        • 1. Re: Ansi SQL for "select ... for update nowait"
          94799
          It is ANSI compliant.
          • 2. Re: Ansi SQL for "select ... for update nowait"
            William Robertson
            Padders beat me to it.
            SQL> set flagger full

            SQL> SELECT DECODE(deptno,1,2,3) FROM dept;
            SELECT DECODE(deptno,1,2,3) FROM dept
                                                *
            ERROR at line 1:
            ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
            ORA-06550: line 2, column 8:
            PLS-01416: Use of <id> (<value>...) here


            SQL> SELECT * FROM dept FOR UPDATE NOWAIT;

                DEPTNO DNAME          LOC
            ---------- -------------- -------------
                    10 ACCOUNTING     NEW YORK
                    20 RESEARCH       DALLAS
                    30 SALES          CHICAGO
                    40 OPERATIONS     BOSTON

            4 rows selected.

            SQL>
            • 3. Re: Ansi SQL for "select ... for update nowait"
              480544
              < select ... for update nowait

              maybe the ... part isn't :)
              • 4. Re: Ansi SQL for "select ... for update nowait"
                APC
                It is ANSI compliant.
                The FIPS flagger doesn't agree...
                SQL> select ename, sal from emp for update of sal;
                ENAME             SAL
                ---------- ----------
                SPENCER           800
                VERREYNNE        1600
                VAN WIJK         1250
                MAINGUY          2975
                KISHORE          1250
                BARRY            2850
                BOEHMER          2695
                PADFIELD         3000
                SCHNEIDER        5500
                GASPAROTTO       1500
                CAVE             1100
                CLARKE            950
                JAFFAR           3000
                ROBERTSON        1430

                14 rows selected.

                SQL> alter session set flagger=full;

                Session altered.

                SQL> select ename, sal from emp for update of sal;
                select ename, sal from emp for update of sal
                                                           *
                ERROR at line 1:
                ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
                ORA-06550: line 2, column 42:
                PLS-01421: FOR UPDATE clause


                SQL>
                Cheers, APC
                • 5. Re: Ansi SQL for "select ... for update nowait"
                  APC
                  Okay, so what isn't complient is the specification of the columns....
                  SQL> select ename, sal from emp for update of sal nowait;
                  select ename, sal from emp for update of sal nowait
                                                                    *
                  ERROR at line 1:
                  ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
                  ORA-06550: line 2, column 42:
                  PLS-01421: FOR UPDATE clause


                  SQL> select ename, sal from emp for update nowait;
                  ENAME             SAL
                  ---------- ----------
                  SPENCER           800
                  VERREYNNE        1600
                  VAN WIJK         1250
                  MAINGUY          2975
                  KISHORE          1250
                  BARRY            2850
                  BOEHMER          2695
                  PADFIELD         3000
                  SCHNEIDER        5500
                  GASPAROTTO       1500
                  CAVE             1100
                  CLARKE            950
                  JAFFAR           3000
                  ROBERTSON        1430

                  14 rows selected.

                  SQL>
                  Cheers, APC
                  • 6. Re: Ansi SQL for "select ... for update nowait"
                    94799
                    The FIPS flagger doesn't agree...
                    Well yes for your example but then you didn't try the statement the OP posted (I see you have corrected this already) ;-)

                    However I notice that FOR UPDATE SKIP LOCKED also gets through the flagger which I rather expected to be proprietary Oracle syntax.

                    Probably what your boss is asking though is not whether the statement is ANSI compliant but whether it will run on any database the client happens to have a license for.
                    • 7. Re: Ansi SQL for "select ... for update nowait"
                      William Robertson
                      What I want to know is why Padfield earns twice as much as Robertson.
                      • 8. Re: Ansi SQL for "select ... for update nowait"
                        94799
                        Perhaps because Robertson is unable to accurately multiply 1430 by 2?
                        • 9. Re: Ansi SQL for "select ... for update nowait"
                          John Spencer
                          Andrew:

                          I resent having the lowest salary :-)

                          John
                          • 10. Re: Ansi SQL for "select ... for update nowait"
                            480544
                            Forget Padders, Laurent's raking it in!!
                            • 11. Re: Ansi SQL for "select ... for update nowait"
                              William Robertson
                              > Perhaps because Robertson is unable to accurately multiply 1430 by 2?

                              Maybe it just includes the Smartarse bonus.
                              • 12. Re: Ansi SQL for "select ... for update nowait"
                                94799
                                I resent having the lowest salary :-)
                                You think you feel bad - Jens, Eric and Warren didn't even make the table ;-)

                                I think we are going to need to see the MGR and JOB columns.
                                • 13. Re: Ansi SQL for "select ... for update nowait"
                                  ABB
                                  What I want to know is why Padfield earns twice as
                                  much as Robertson.
                                  You forgot to mention "for half of the work" (you have twice the forum posts).

                                  Though it wasn't too long ago that you were also sitting on 1,000 posts. 1,500 additional posts in three months is quite some going. They should make you an ACE ;o)

                                  Oh I now see it's nearly 1,700 additional posts. William's post counter is like a ticker. I'm going to use it as a sequence or a clock or something.
                                  • 14. Re: Ansi SQL for "select ... for update nowait"
                                    APC
                                    I resent having the lowest salary :-)
                                    Sorry John, it's probably due to the exchange rate of sterling against the canuck dollar right now.
                                    SQL> select empno, ename, sal, job, mgr from emp;
                                         EMPNO ENAME             SAL JOB              MGR
                                    ---------- ---------- ---------- --------- ----------
                                          7369 SPENCER           800 CLERK           7902
                                          7499 VERREYNNE        1600 SALESMAN        7698
                                          7521 VAN WIJK         1250 SALESMAN        7698
                                          7566 MAINGUY          2975 MANAGER         7839
                                          7654 KISHORE          1250 SALESMAN        7698
                                          7698 BARRY            2850 MANAGER         7839
                                          7782 BOEHMER          2695 MANAGER         7839
                                          7788 PADFIELD         3000 ANALYST         7566
                                          7839 SCHNEIDER        5500 PRESIDENT
                                          7844 GASPAROTTO       1500 SALESMAN        7698
                                          7876 CAVE             1100 CLERK           7788
                                          7900 CLARKE            950 CLERK           7698
                                          7902 JAFFAR           3000 ANALYST         7566
                                          7934 ROBERTSON        1430 CLERK           7782

                                    14 rows selected.

                                    SQL>
                                    Cheers, APC
                                    1 2 Previous Next