This discussion is archived
9 Replies Latest reply: Apr 2, 2013 3:16 AM by BEDE RSS

Can you help me translate it to dymnamic sql

997232 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    okey thanks ,
  • 8. Re: Can you help me translate it to dymnamic sql
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

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