This discussion is archived
13 Replies Latest reply: Feb 28, 2013 7:45 AM by _jum RSS

select from all tables in schema

don123 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    May be that in this TABLE no DATA exist ?
  • 6. Re: select from all tables in schema
    don123 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    ...
      --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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points