9 Replies Latest reply: Apr 2, 2013 5:16 AM by BEDE RSS

    Can you help me translate it to dymnamic sql

    997232
      Can you help me translate it to dymnamic sql

      merge into spolki t
      using (
      select aktualne_notowania.aspolka_id,
      aktualne_notowania.notowanie * spolki.ilosc_akcji as total
      from aktualne_notowania, spolki
      where aktualne_notowania.aspolka_id = spolki.spolka_id
      ) s
      on (
      t.spolka_id = s.aspolka_id
      )
      when matched
      then
      update
      set t.wartosc_spolki = s.total
        • 1. Re: Can you help me translate it to dymnamic sql
          rp0428
          >
          Can you help me translate it to dymnamic sql

          merge into spolki t
          using (
          select aktualne_notowania.aspolka_id,
          aktualne_notowania.notowanie * spolki.ilosc_akcji as total
          from aktualne_notowania, spolki
          where aktualne_notowania.aspolka_id = spolki.spolka_id
          ) s
          on (
          t.spolka_id = s.aspolka_id
          )
          when matched
          then
          update
          set t.wartosc_spolki = s.total
          >
          Sure - just put quotes around the query and then do
          execute immediate 'my query goes here';
          So what would be the point of using dynmaic sql?
          • 2. Re: Can you help me translate it to dymnamic sql
            997232
            create or replace
            PROCEDURE add_spol
            ( p_nazwa spolki.nazwa%type
            , p_ilosc spolki.ilosc_akcji%type
            , p_notowanie aktualne_notowania.notowanie%type
            )
            IS
            s varchar2(10000);
            aktualne_notowania VARCHAR2(10000);
            spolki varchar2(10000);
            BEGIN
            INSERT INTO spolki (nazwa, ilosc_akcji)
            VALUES(p_nazwa, p_ilosc);
            INSERT INTO aktualne_notowania (notowanie)
            VALUES(p_notowanie);
            s := 'merge into spolki t'
            || ' USING (select aktualne_notowania.notowanie * spolki.ilosc_akcji as total from ' ||aktualne_notowania|| spolki || 'where aktualne_notowania.aspolka_id = spolki.spolka_id ) e'
            || ' ON (t.spolka_id = e.aspolka_id)'
            || ' when matched then'
            || 'update'
            || 'set t.wartosc_spolki = e.total';
            EXECUTE IMMEDIATE s;

            END add_spol;



            I have this procedure and i am trying to use it here but i cant always some mistakes
            • 3. Re: Can you help me translate it to dymnamic sql
              surendra4y
              check out this
              execute immediate'merge into spolki t
              using (
              select aktualne_notowania.aspolka_id,
              aktualne_notowania.notowanie * spolki.ilosc_akcji as total
              from aktualne_notowania, spolki
              where aktualne_notowania.aspolka_id = spolki.spolka_id
              ) s
              on (
              t.spolka_id = s.aspolka_id
              )
              when matched
              then
              update
              set t.wartosc_spolki = s.total'
              • 4. Re: Can you help me translate it to dymnamic sql
                sb92075
                994229 wrote:
                Can you help me translate it to dymnamic sql

                merge into spolki t
                using (
                select aktualne_notowania.aspolka_id,
                aktualne_notowania.notowanie * spolki.ilosc_akcji as total
                from aktualne_notowania, spolki
                where aktualne_notowania.aspolka_id = spolki.spolka_id
                ) s
                on (
                t.spolka_id = s.aspolka_id
                )
                when matched
                then
                update
                set t.wartosc_spolki = s.total
                I don't see anything that is dynamic in SQL above.


                The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
                Then print the variable before passing it to EXECUTE IMMEDIATE.
                COPY the statement & PASTE into sqlplus to validate its correctness.
                • 5. Re: Can you help me translate it to dymnamic sql
                  rp0428
                  >
                  check out this
                  >
                  So you want OP to 'check out' exactly what I told them to do above?

                  Why are you just copying what someone else already said?

                  Don't you have anything useful of your own to contribute?
                  • 6. Re: Can you help me translate it to dymnamic sql
                    surendra4y
                    can you put exception and take log in table and let me know the error
                    • 7. Re: Can you help me translate it to dymnamic sql
                      surendra4y
                      okey thanks ,
                      • 8. Re: Can you help me translate it to dymnamic sql
                        BluShadow
                        How on Earth do you expect this code to work...?
                        create or replace PROCEDURE add_spol
                          (p_nazwa     spolki.nazwa%type
                          ,p_ilosc     spolki.ilosc_akcji%type
                          ,p_notowanie aktualne_notowania.notowanie%type
                          ) IS
                          s                  varchar2(10000);
                          aktualne_notowania VARCHAR2(10000);
                          spolki             varchar2(10000);
                        BEGIN
                          INSERT INTO spolki (nazwa, ilosc_akcji) VALUES(p_nazwa, p_ilosc);
                          INSERT INTO aktualne_notowania (notowanie) VALUES(p_notowanie);
                          s := 'merge into spolki t'
                            || ' USING (select aktualne_notowania.notowanie * spolki.ilosc_akcji as total from ' ||aktualne_notowania|| spolki || 'where aktualne_notowania.aspolka_id = spolki.spolka_id ) e'
                            || ' ON (t.spolka_id = e.aspolka_id)'
                            || ' when matched then'
                            || 'update' 
                            || 'set t.wartosc_spolki = e.total';
                          EXECUTE IMMEDIATE s;
                        END add_spol;
                        You appear to have variables (spolki and aktualne_notowania) that are named the same as table names (based on the INSERT statements) and then you are generating a MERGE statement in a string using the contents of the variables (which have no values at this point) and then referencing specific table names in the merge statement.

                        Look at the value of the S variable that's generated...
                        SQL> set serverout on
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1  DECLARE
                          2    s                  varchar2(10000);
                          3    aktualne_notowania VARCHAR2(10000);
                          4    spolki             varchar2(10000);
                          5  BEGIN
                          6    s := 'merge into spolki t'
                          7      || ' USING (select aktualne_notowania.notowanie * spolki.ilosc_akcji as total from ' ||aktualne_notowania|| spolki || 'where aktualne_notowania.aspolka_id = spolki.spolka_id ) e'
                          8      || ' ON (t.spolka_id = e.aspolka_id)'
                          9      || ' when matched then'
                         10      || 'update'
                         11      || 'set t.wartosc_spolki = e.total';
                         12    dbms_output.put_line(s);
                         13* END add_spol;
                        SQL> /
                        merge into spolki t USING (select aktualne_notowania.notowanie * spolki.ilosc_akcji as total from where aktualne_notowania.aspolka_id = spolki.spolka_id ) e ON (t.spolka_id = e.aspolka_id) when matched thenupdateset t.wartosc_spolki = e.total
                        
                        PL/SQL procedure successfully completed.
                        That isn't a valid MERGE statement.

                        Just what is dynamic in the logic that requires the use of dynamic SQL? It seems that you already know your table names, so there's no need to generate a dynamic statement. And if you don't know the table names... why not?
                        • 9. Re: Can you help me translate it to dymnamic sql
                          BEDE
                          Having N (can't figure out how many) tabels with the same structure doesn't look like good design.
                          Better have only one table instead, having an extra column that will hold the information which in your design is represented through the table name, and which will most likely be included in an unique index.
                          So you may avoid using dynamic SQL, which leads to poor performance.

                          Edited by: BEDE on Apr 2, 2013 12:16 PM