13 Replies Latest reply: Feb 28, 2013 9:45 AM by _jum RSS

    select from all tables in schema

    don123
      hi, i am trying to x,y from tables in a schema, getting "missing expression" error, working with oracle 11g.


      declare

      v_sql varchar2(4000);
      v_x number;
      v_y number;
      v_n number;

      begin

      for rec in (select table_name as table_name from all_tables where table_name like '%AM_%' ORDER BY 1) loop


      v_sql := 'select a.idnumber, t.x, t.y, table(sdo_util.getvertices(a.geometry)) t FROM '||rec.table_name ||' a';
      EXECUTE IMMEDIATE v_sql INTO v_n, v_x, v_y;
      dbms_output.put_line(v_n||v_x||v_y);


      end loop;


      end;
        • 1. Re: select from all tables in schema
          Stefan Jager
          Hi Don,
            DECLARE
            rec  ALL_TABLES%ROWTYPE;
          That should fix it, not tested though. You may also need to change your SELECT statement, but try the ROWTYPE first.

          HTH,
          Stefan
          • 2. Re: select from all tables in schema
            don123
            stefan, tried with rowtype, the error is same.


            ORA-00936: missing expression
            ORA-06512: at line 10
            • 3. Re: select from all tables in schema
              _jum
              Here is a (may be) working example:
              SET SERVEROUTPUT ON SIZE 900000;
              
              DECLARE
                v_sql   VARCHAR2 (4000);
                v_x     NUMBER;
                v_y     NUMBER;
                v_n     NUMBER;
              BEGIN
                FOR rec IN (  SELECT table_name AS table_name
                                FROM all_tables
                               WHERE table_name LIKE '%AM_%'
                            ORDER BY 1)
                LOOP
                  v_sql :=
                    'select a.idnumber, t.x, t.y FROM '
                    || rec.table_name || ' a,  table(sdo_util.getvertices(a.geometry)) t where rownum=1';
              
                  EXECUTE IMMEDIATE v_sql INTO v_n, v_x, v_y;
              
                  DBMS_OUTPUT.put_line (v_n || v_x || v_y);
                END LOOP;
                
              END;
              You correctly fetch the table_names and bring them into the LOOP.
              But you'll normally get a lot of t.x and t.y by sdo_util.getvertices() and so need another LOOP to handle them.
              • 4. Re: select from all tables in schema
                don123
                hi, it seems ok, given some output and stopped, there is another error....

                *
                ERROR at line 1:
                ORA-01403: no data found
                ORA-06512: at line 16

                regards
                • 5. Re: select from all tables in schema
                  _jum
                  May be that in this TABLE no DATA exist ?
                  • 6. Re: select from all tables in schema
                    don123
                    hi, thanks, are we limiting only one row by using rownum=1 in where clause ??

                    v_sql := 'select a.idnumber, t.x, t.y FROM ' || rec.table_name || ' a, table(sdo_util.getvertices(a.geometry)) t where rownum=1';


                    regards
                    • 7. Re: select from all tables in schema
                      _jum
                      Yes, as I stated this is necessary, because sdo_util.getvertices gets "normally" a lot of coordinates and
                      EXECUTE IMMEDIATE v_sql INTO v_n, v_x, v_y;
                      can only query on row.
                      If you want to get all rows, you need another inner LOOP.
                      • 8. Re: select from all tables in schema
                        don123
                        hi, thanks

                        when i remove dynamic sql and selected x,y from single table then the cursor is fetching normally and working fine.
                        is it basically limitation to use dynamic sql ? if yes what is the alternative to select x,y from multiple tables in a schema at once?
                        • 9. Re: select from all tables in schema
                          _jum
                          No, this is no limitation of dynamic SQL. Please give us a test example for your table and data to help you.
                          • 10. Re: select from all tables in schema
                            don123
                            hi two rows, with idnum and geometry columns...




                            IDNUM
                            -----
                            GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                            --------------------------------------------------------------------------------

                            GD8

                            SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR

                            AY(-.48230432, 51.4645609, -.47600566, 51.464582, -.47206108, 51.4645953, -.4654

                            6537, 51.4646174, -.46423724, 51.4646216, -.45892656, 51.4646394, -.45671873, 51

                            .4646468, -.45007509, 51.4646691, -.4487052, 51.4646737, -.44809122, 51.4646758,

                            -.44748667, 51.4646778, -.44118568, 51.4646989, -.44038184, 51.4647016, -.43534

                            624, 51.4647186, -.43415307, 51.4647226, -.43413338, 51.4647226, -.43410223, 51.

                            4647227, -.43408667, 51.4647228, -.43408688, 51.4648537, -.43408691, 51.4648694,

                            -.43408703, 51.4649432, -.43408705, 51.4649579, -.43408717, 51.4650319, -.4340872,
                            51.4650481, -.43408741, 51.465177, -.4341061, 51.4651769, -.43413411, 51.465

                            1768, -.43415863, 51.4651767, -.43493934, 51.4651741, -.43724392, 51.4651664, -.

                            4381469, 51.4651633, -.43876878, 51.4651612, -.44038073, 51.4651558, -.4409811,
                            51.4651538, -.44732658, 51.4651325, -.44759329, 51.4651316, -.44870078, 51.46512

                            79, -.45213755, 51.4651164, -.45482423, 51.4651073, -.45795448, 51.4650968, -.46

                            041684, 51.4650886, -.46194096, 51.4650834, -.46348669, 51.4650783, -.46492913,
                            51.4650734, -.46744722, 51.465065, -.47410127, 51.4650426, -.47616935, 51.465035

                            7, -.48152654, 51.4650177, -.48230505, 51.4650151, -.48235217, 51.4650149, -.482

                            35215, 51.4650018, -.48235145, 51.4645722, -.48235143, 51.4645607, -.48230432, 51.4645609))



                            IDNUM
                            -----
                            GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                            --------------------------------------------------------------------------------


                            GD4


                            SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR

                            AY(-.48497261, 51.4772685, -.48209892, 51.4772786, -.48097228, 51.4772825, -.474

                            22571, 51.477306, -.47275437, 51.4773112, -.46898013, 51.4773243, -.46659847, 51

                            .4773326, -.46535204, 51.477337, -.4624971, 51.4773469, -.46135428, 51.4773509,
                            -.45879869, 51.4773598, -.45693251, 51.4773663, -.45570651, 51.4773706, -.453196

                            56, 51.4773794, -.45006644, 51.4773903, -.44747333, 51.4773993, -.44686181, 51.4774014, -.44408948,
                            51.4774111, -.4389499, 51.477429, -.43830451, 51.4774313, -.

                            4346623, 51.477444, -.43398218, 51.4774464, -.43331459, 51.4774487, -.43326608,
                            51.4774489, -.43326612, 51.4775874, -.43326612, 51.4776043, -.43326616, 51.47774

                            86, -.43326616, 51.4777641, -.43326619, 51.4779031, -.4333147, 51.4779029, -.472

                            655, 51.4777657, -.47413516, 51.4777605, -.48497272, 51.4777227, -.48502579, 51.

                            4777226, -.48502579, 51.4777106, -.48502568, 51.4772809, -.48502568, 51.4772684,

                            -.48497261, 51.4772685))
                            • 11. Re: select from all tables in schema
                              _jum
                              Here is a working example, very similar to your problem:
                              DROP   TABLE test_am;
                              
                              CREATE TABLE test_am
                               (idnumber VARCHAR2(20),
                                geometry SDO_GEOMETRY);
                                
                              INSERT INTO test_am
                                VALUES ( 'GD8', SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                                  SDO_ORDINATE_ARRAY(-.48230432, 51.4645609, -.47600566, 51.464582, -.47206108, 51.4645953,-.46546537, 51.4646174, -.46423724, 51.4646216)));
                                  
                              INSERT INTO test_am
                                VALUES ( 'GD4', SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), 
                                  SDO_ORDINATE_ARRAY(-.48497261, 51.4772685, -.48209892, 51.4772786, -.48097228, 51.4772825, -.47422571, 51.477306, -.47275437, 51.4773112, -.46898013, 51.4773243)));
                                  
                              COMMIT;
                              
                              DROP   TABLE test_am1;
                              
                              CREATE TABLE test_am1
                               (idnumber VARCHAR2(20),
                                geometry SDO_GEOMETRY);
                                
                              INSERT INTO test_am1
                                VALUES ( 'ID1', sdo_geometry(2001, NULL, SDO_POINT_TYPE(  100, 300,  0), NULL,NULL)); 
                              
                              INSERT INTO test_am1
                                VALUES ( 'ID2', sdo_geometry(2001, NULL, SDO_POINT_TYPE(  101, 301,  0), NULL,NULL)); 
                              
                              COMMIT;
                              
                              SET SERVEROUTPUT ON SIZE 900000;
                              
                              DECLARE
                                
                                cur2   SYS_REFCURSOR;
                              
                                --only for fetching implizit CURSOR cur1
                                CURSOR t2 IS SELECT CAST('' AS VARCHAR2(20))idnumber, CAST(0 AS NUMBER) x, CAST(0 AS NUMBER) y FROM dual;
                                rec2   t2%ROWTYPE;     
                              
                              BEGIN
                              
                                --implizit CURSOR
                                FOR rec1 IN 
                                 (SELECT table_name AS table_name
                                    FROM all_tables
                                   WHERE table_name LIKE 'TEST_AM%'
                                   ORDER BY 1)
                              
                                --inner LOOP
                                LOOP
                                 
                                  DBMS_OUTPUT.put_line (rec1.table_name);
                              
                                  OPEN cur2 FOR 'SELECT a.idnumber, t.x, t.y FROM '|| rec1.table_name || ' a,  table(sdo_util.getvertices(a.geometry)) t';
                                  LOOP
                                    FETCH cur2 INTO rec2;
                                    EXIT WHEN cur2%NOTFOUND;
                                    dbms_output.put_line('ID='||rec2.idnumber||' X='||rec2.x||' Y='||rec2.Y);
                                  END LOOP;
                                CLOSE cur2;
                              
                                END LOOP;
                                
                              END;
                              
                              Table dropped.
                              Table created.
                              1 row created.
                              1 row created.
                              Commit complete.
                              Table dropped.
                              Table created.
                              1 row created.
                              1 row created.
                              Commit complete.
                              TEST_AM
                              ID=GD8 X=-,48230432 Y=51,4645609
                              ID=GD8 X=-,47600566 Y=51,464582
                              ID=GD8 X=-,47206108 Y=51,4645953
                              ID=GD8 X=-,46546537 Y=51,4646174
                              ID=GD8 X=-,46423724 Y=51,4646216
                              ID=GD4 X=-,48497261 Y=51,4772685
                              ID=GD4 X=-,48209892 Y=51,4772786
                              ID=GD4 X=-,48097228 Y=51,4772825
                              ID=GD4 X=-,47422571 Y=51,477306
                              ID=GD4 X=-,47275437 Y=51,4773112
                              ID=GD4 X=-,46898013 Y=51,4773243
                              TEST_AM1
                              ID=ID1 X=100 Y=300
                              ID=ID2 X=101 Y=301
                              PL/SQL procedure successfully completed.
                              If correct, please award points.
                              • 12. Re: select from all tables in schema
                                don123
                                hi, many thanks for the help..it worked..

                                can you explain the role of following.

                                CURSOR t2 IS SELECT CAST('' AS VARCHAR2(20))idnumber, CAST(0 AS NUMBER) x, CAST(0 AS NUMBER) y FROM dual;
                                rec2 t2%ROWTYPE;


                                regards
                                • 13. Re: select from all tables in schema
                                  _jum
                                  ...
                                    --only for fetching implizit CURSOR cur1
                                    CURSOR t2 IS SELECT CAST('' AS VARCHAR2(20))idnumber, CAST(0 AS NUMBER) x, CAST(0 AS NUMBER) y FROM dual;
                                    rec2   t2%ROWTYPE;     
                                  ...
                                  For the implicit CURSOR cur1 you can't define a FETCH-variable with CUR1%ROWTYPE.
                                  So I defined an explicit CURSOR t2, bringing me the types for the wanted result columns:
                                  IDNUMBER (VARCHAR2(20)), X (NUMBER)and Y (NUMBER)
                                  From this cursor t2 I can define a t2%ROWTYPE VARIABLE to FETCH the results.

                                  Please award points for the solution.