Home » SQL & PL/SQL » SQL & PL/SQL » How to translate PSQL to Oracle PL/SQL
How to translate PSQL to Oracle PL/SQL [message #299510] Tue, 12 February 2008 05:04 Go to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

I have PSQL SP and I would like to translate it to work with Oracle database, can someone help me to solve this?

Here is PSQL:

CREATE PROCEDURE SP_ADM_ZALIHA (
    SESIJA INTEGER,
    OBJEKAT INTEGER,
    ARTIKAL INTEGER,
    KCENA DOUBLE PRECISION,
    ULAZ INTEGER)
AS
BEGIN

  IF (NOT EXISTS (SELECT SESIJA, OBJEKAT, ARTIKAL FROM ZALIHA WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL)) THEN
    INSERT INTO ZALIHA (SESIJA, OBJEKAT, ARTIKAL) VALUES ( :SESIJA, :OBJEKAT, :ARTIKAL);

  UPDATE ZALIHA SET UKOLICINA = (SELECT SUM(UKOLICINA) FROM KARTART WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL),
                    IKOLICINA = (SELECT SUM(IKOLICINA) FROM KARTART WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL),
                    PROSNCENA = (SELECT COALESCE(SUM(UNVRED-INVRED), 0) / (CASE COALESCE(SUM(UKOLICINA-IKOLICINA), 0) WHEN 0 THEN 1 ELSE SUM(UKOLICINA-IKOLICINA) END) FROM KARTART WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL),
                    VALPROSNCENA = (SELECT COALESCE(SUM(UNVRED-INVRED), 0) / (CASE COALESCE(SUM(UKOLICINA-IKOLICINA), 0) WHEN 0 THEN 1 ELSE SUM(UKOLICINA-IKOLICINA) END) FROM KARTART WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL)
  WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL;

  IF (ULAZ > 0) THEN
    UPDATE ZALIHA SET PRODKCENA = :KCENA, VALPRODKCENA = :KCENA
    WHERE SESIJA = :SESIJA AND OBJEKAT = :OBJEKAT AND ARTIKAL = :ARTIKAL;

END^

SET TERM ; ^

GRANT SELECT,INSERT,UPDATE ON ZALIHA TO PROCEDURE SP_ADM_ZALIHA;

GRANT SELECT ON KARTART TO PROCEDURE SP_ADM_ZALIHA;

GRANT EXECUTE ON PROCEDURE SP_ADM_ZALIHA TO SYSDBA;


Thanks in advance...
Re: How to translate PSQL to Oracle PL/SQL [message #299514 is a reply to message #299510] Tue, 12 February 2008 05:14 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
It might help if you tell us what your procedure is meant to do.
Re: How to translate PSQL to Oracle PL/SQL [message #299527 is a reply to message #299510] Tue, 12 February 2008 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Move EXISTS (SELECT ...) out of the IF-THEN-ELSE; something like
SELECT count(*)
  INTO l_postoji
  FROM ZALIHA 
  WHERE SESIJA = :SESIJA 
    AND OBJEKAT = :OBJEKAT 
    AND ARTIKAL = :ARTIKAL;

  IF l_postoji > 0 THEN
     /* do whatever you do here; UPDATE, INSERT, ... */
  END IF;

  ...
Re: How to translate PSQL to Oracle PL/SQL [message #299573 is a reply to message #299527] Tue, 12 February 2008 08:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
It looks to me like a job for ta ta da daaaa MERGE, but I was wanting some confirmation as to the purpose first.
Re: How to translate PSQL to Oracle PL/SQL [message #299804 is a reply to message #299510] Wed, 13 February 2008 04:34 Go to previous messageGo to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

I try with LittleFoot solution and it is work fine, but I'll try with MERGE and I'll post a result of my work!

Thanks to all
Re: How to translate PSQL to Oracle PL/SQL [message #323424 is a reply to message #299510] Wed, 28 May 2008 08:05 Go to previous messageGo to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

Here is how my trigre and procedure. First little explain of my problem. I have table kartarts which is store data about some boxes with serial numbers and table zalihas which is store summary data about this boxes group by some criteria.

when I insert data into table kartarts (which is store data about every box separatly) trigger is activated after every row is inserted. Trigger souce is:

CREATE OR REPLACE TRIGGER yuhor.kartarts_ao
   AFTER INSERT OR DELETE OR UPDATE
   ON yuhor.kartarts
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   IF INSERTING
   THEN
      adm_zalihas (:NEW.sesija,
                   :NEW.objekat,
                   :NEW.artikal,
                   :NEW.oznaka,
                   :NEW.smestaj
                  );
   ELSIF UPDATING
   THEN
      adm_zalihas (:OLD.sesija,
                   :OLD.objekat,
                   :OLD.artikal,
                   :OLD.oznaka,
                   :OLD.smestaj
                  );
   ELSIF DELETING
   THEN
      adm_zalihas (:OLD.sesija,
                   :OLD.objekat,
                   :OLD.artikal,
                   :OLD.oznaka,
                   :OLD.smestaj
                  );
   END IF;
END;
/


This trigger call procedure which will summ data from table kartarts and refresh table zalihas (which is represent summary of table kartarts by some citeria). Procedure source is:

CREATE OR REPLACE PROCEDURE adm_zalihas (
   sesija    kartarts.sesija%TYPE,
   objekat   kartarts.objekat%TYPE,
   artikal   kartarts.artikal%TYPE,
   oznaka    kartarts.oznaka%TYPE,
   smestaj   kartarts.smestaj%TYPE
)
IS
   l_postoji   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO l_postoji
     FROM zalihas
    WHERE sesija = sesija
      AND objekat = objekat
      AND artikal = artikal
      AND oznaka = oznaka
      AND smestaj = smestaj;

   IF l_postoji = 0
   THEN
      INSERT INTO zalihas
                  (sesija, objekat, artikal, oznaka, smestaj, ukolicina,
                   ikolicina, kolicina, ukolicinax, ikolicinax, kolicinax)
         SELECT   sesija, objekat, artikal, oznaka, smestaj, SUM (ukolicina),
                  SUM (ikolicina), SUM (ukolicina - ikolicina),
                  SUM (ukolicinax), SUM (ikolicinax),
                  SUM (ukolicinax - ikolicinax)
             FROM kartarts
            WHERE sesija = sesija
              AND objekat = objekat
              AND artikal = artikal
              AND oznaka = oznaka
              AND smestaj = smestaj
         GROUP BY sesija, objekat, artikal, oznaka, smestaj;
   END IF;
END adm_zalihas;
/


after all when I run some insert query Oracle raise eror:

ORA-04091: table YUHOR.KARTARTS is mutating, trigger/function may not see it
ORA-06512: at "YUHOR.ADM_ZALIHAS", line 22
ORA-06512: at "YUHOR.KARTARTS_AO", line 4
ORA-04088: error during execution of trigger 'YUHOR.KARTARTS_AO'


I was read a lot of webpages about first error and I can't find how to solve this problem. If someone can represent my problem from this post and have some idea I'll be happy!

Thanks for advance...
Re: How to translate PSQL to Oracle PL/SQL [message #323477 is a reply to message #323424] Wed, 28 May 2008 09:49 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/tkyte/Mutate/index.html
Previous Topic: how to set prompt off
Next Topic: how can i unit 3 sql query to one query?
Goto Forum:
  


Current Time: Mon Dec 05 08:45:54 CST 2016

Total time taken to generate the page: 0.06558 seconds