PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 08/08/2018 15:49:34

joguess
Membre

Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour,

Je dois migrer Oracle vers Postgres.

J'ai des vues Oracle qui font appel à des fonctions comme dbms_output non présente dans postgres.

J' ai donc intégrées les fonctions manquantes avec orafce. J'ai créé l'extension orafce qui a créé les schémas :

     List of schemas
     Name     |  Owner   
--------------+----------
dbms_alert   | moi
dbms_assert  | moi
dbms_output  | moi
dbms_pipe    | moi
dbms_random  | moi
dbms_utility | moi
oracle       | moi
plunit       | moi
plvchr       | moi
plvdate      | moi
plvlex       | moi
plvstr       | moi
plvsubst     | moi
public       | postgres
utl_file     | moi

et les fonctions , notamment :

postgres=# \df dbms_output.
                                         List of functions
   Schema    |     Name     | Result data type |           Argument data types            |  Type 
-------------+--------------+------------------+------------------------------------------+--------
dbms_output | disable      | void             |                                          | normal
dbms_output | enable       | void             |                                          | normal
dbms_output | enable       | void             | buffer_size integer                      | normal
dbms_output | get_line     | record           | OUT line text, OUT status integer        | normal
dbms_output | get_lines    | record           | OUT lines text[], INOUT numlines integer | normal
dbms_output | new_line     | void             |                                          | normal
dbms_output | put          | void             | a text                                   | normal
dbms_output | put_line     | void             | a text                                   | normal
dbms_output | serveroutput | void             | boolean                                  | normal
(9 rows)


Pour tester mes fonctions j'exécute cela :

postgres:~/fonctions$ psql < titi.sql
SET
ERREUR:  erreur de syntaxe sur ou près de « DBMS_OUTPUT »
LINE 2:       DBMS_OUTPUT.ENABLE(5)
              ^
postgres:~/fonctions$ cat titi.sql
set search_path="$user", public, pg_catalog,public,dbms_output;

BEGIN
      DBMS_OUTPUT.ENABLE(5)
      DBMS_OUTPUT.SERVEROUTPUT(TRUE)
      DBMS_OUTPUT.PUT_LINE('Messages enabled')
END;

Donc voila , cela ne marche pas. Je ne sais pas utiliser mes fonctions qui sont pourtant bien créées. Merci de ne pas vous moquer, j'ai bien conscience qu'il me manque une compétence.



Le code des fonctions est disponible ici : https://github.com/orafce/orafce/blob/m … 6--3.7.sql

et par exemple cela focntionne :

postgres=# select DBMS_OUTPUT.ENABLE(5);
ATTENTION:  Limit increased to 2000 bytes.
enable
--------

(1 row)


postgres=# select DBMS_OUTPUT.SERVEROUTPUT(TRUE);
serveroutput
--------------

(1 row)

postgres=# select DBMS_OUTPUT.PUT_LINE('Messages enabled');

Messages enabled
put_line
----------

(1 row)

Hors ligne

#2 08/08/2018 15:53:43

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Pardon le code du fichier titi.sql pour tester ma fonction est le suivant :

set search_path="$user", public, pg_catalog,public,dbms_output;

BEGIN
      DBMS_OUTPUT.SERVEROUTPUT(TRUE);
      DBMS_OUTPUT.PUT_LINE('Messages enabled');
END;


récupéré depuis le site :                 https://www.enterprisedb.com/docs/en/9. … 1.143.html

Hors ligne

#3 08/08/2018 15:58:12

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Et même quand je veux taper le code directement dans la console cela ne marche pas :

postgres=# begin
postgres-# DBMS_OUTPUT.SERVEROUTPUT(TRUE);
ERREUR:  erreur de syntaxe sur ou près de « DBMS_OUTPUT »
LINE 2: DBMS_OUTPUT.SERVEROUTPUT(TRUE);
        ^

Hors ligne

#4 08/08/2018 15:59:28

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

VoIla l'extension que j'ai créé :

postgres=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
plpgsql |       10 |           11 | f              | 1.0        |           |
orafce  |    16385 |         2200 | f              | 3.6        |           |
(2 rows)

et je suis censé utiliser orafce...

Dernière modification par joguess (08/08/2018 15:59:47)

Hors ligne

#5 08/08/2018 16:04:42

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

postgres=# \df+ dbms_output.
                                                                                              List of functions
   Schema    |     Name     | Result data type |           Argument data types            |  Type  | Security | Volatility |  Owner   | Language |        Source code         |          Description         
-------------+--------------+------------------+------------------------------------------+--------+----------+------------+----------+----------+----------------------------+-------------------------------
dbms_output | disable      | void             |                                          | normal | invoker  | volatile   | moi | c        | dbms_output_disable        | Disable package functionality
dbms_output | enable       | void             |                                          | normal | invoker  | volatile   | moi | c        | dbms_output_enable_default | Enable package functionality
dbms_output | enable       | void             | buffer_size integer                      | normal | invoker  | volatile   | moi | c        | dbms_output_enable         | Enable package functionality
dbms_output | get_line     | record           | OUT line text, OUT status integer        | normal | invoker  | volatile   | moi | c        | dbms_output_get_line       | Get line from output buffer
dbms_output | get_lines    | record           | OUT lines text[], INOUT numlines integer | normal | invoker  | volatile   | moi | c        | dbms_output_get_lines      | Get lines from output buffer
dbms_output | new_line     | void             |                                          | normal | invoker  | volatile   | moi | c        | dbms_output_new_line       | Put new line char to output
dbms_output | put          | void             | a text                                   | normal | invoker  | volatile   | moi | c        | dbms_output_put            | Put some text to output
dbms_output | put_line     | void             | a text                                   | normal | invoker  | volatile   | moi | c        | dbms_output_put_line       | Put line to output
dbms_output | serveroutput | void             | boolean                                  | normal | invoker  | volatile   | moi | c        | dbms_output_serveroutput   | Set drowing output

Dernière modification par joguess (08/08/2018 17:37:16)

Hors ligne

#6 08/08/2018 18:12:56

rjuju
Administrateur

Re : Problème dans l'intégration des fonctions dbms_output.xxx

joguess a écrit :

Et même quand je veux taper le code directement dans la console cela ne marche pas :

postgres=# begin
postgres-# DBMS_OUTPUT.SERVEROUTPUT(TRUE);
ERREUR:  erreur de syntaxe sur ou près de « DBMS_OUTPUT »
LINE 2: DBMS_OUTPUT.SERVEROUTPUT(TRUE);
        ^

Il manque un point-virgule après le begin, et un select avant le DBMS_OUPUT.  Une fois cela corrigé, cela devrait fonctionner.

Hors ligne

#7 21/08/2018 09:59:07

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour et merci pour votre réponse. Cela fonctionne.

Par contre pouvez m'expliquer svp comment incorporer ce code dans un fichier titi.sql et pour qu'il fonctionne :

set search_path="$user", public, pg_catalog,public,dbms_output;



BEGIN

      DBMS_OUTPUT.ENABLE(5)

      DBMS_OUTPUT.SERVEROUTPUT(TRUE)

      DBMS_OUTPUT.PUT_LINE('Messages enabled')

END;

D'avance merci.

Hors ligne

#8 21/08/2018 10:02:40

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

est ce qu'il faut ecrire le contenu de titi.sql comme cela :

set search_path="$user", public, pg_catalog,public,dbms_output;



BEGIN;

select      DBMS_OUTPUT.ENABLE(5);

select      DBMS_OUTPUT.SERVEROUTPUT(TRUE);

select      DBMS_OUTPUT.PUT_LINE('Messages enabled');

END;

Merci pour votre retour.

Hors ligne

#9 21/08/2018 15:45:06

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Toujours dans le cadre de ma migration de fonctions Oracle vers Postgres... Je dois migrer cette fonction suivante mais cela ne marche pas :

function max_val(val1 bigint, val2 bigint) return bigint is;

J'ai le message d'erreur suivant :

ERREUR:  erreur de syntaxe sur ou près de « bigint »
LINE 34:         function max_val(val1 bigint, val2 bigint) return bi...
                          ^
CONTEXT:  nom de type « max_val(val1 bigint, val2 bigint) return bigint is » invalide


Pouvez vous m'expliquer la raison de cette erreur? Avez-vous une solution ?

D'avance merci.

Hors ligne

#10 21/08/2018 21:21:42

gleu
Administrateur

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Toute fonction s'appelle avec un ordre SQL, donc en effet il faut un SELECT dans le cas des appels à DBMS_OUTPUT.xxx.

Concernant votre deuxième question, quelle est la requête exacte exécutée ? parce que si c'est "function max_val(val1 bigint, val2 bigint) return bigint is;", je n'ai pas le même message d'erreur. De plus, il n'y a pas de clause "is" dans le CREATE FUNCTION. Voir https://docs.postgresql.fr/10/sql-createfunction.html

Hors ligne

#11 23/08/2018 11:14:26

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Voici la fonction Oracle que je dois convertir en postgreSQL :

        function max_val(val1 Number, val2 Number) return Number is
        begin
                if (val1 is null) then return val2; end if;
                if (val2 is null) then return val1; end if;
                if (val1 > val2)  then return val1; end if;
                return val2;
        end max_val;

L'outil ora2pg me prodtui la fonction suivante :

function max_val(val1 bigint, val2 bigint) return bigint is;
        BEGIN
                if(val1 is null) then return val2; end if;
                if(val2 is null) then return val1; end if;
                if(val1 > val2)  then return val1; end if;
                return val2;
        END;

et c'est cette fonction convertie qui ne fonctionne pas.

Hors ligne

#12 23/08/2018 16:40:04

gleu
Administrateur

Re : Problème dans l'intégration des fonctions dbms_output.xxx

A priori, ça doit être une fonction définie directement dans une autre fonction. Cela n'existe pas dans PostgreSQL et il faut la définir comme toute autre fonction. Voir le lien fourni précédemment.

Hors ligne

#13 12/09/2018 10:08:02

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour merci j'ai résolu l'incident précédemment évoqué.

Toujours dans le cadre de ma migration d'Oracle vers Postgres. J'ai créé l'extension orafce qui contient des fonctions absentes dans Postgres par exemple dbms_output.
Voir mon premier post.

J'ai l'erreur suivante lorsque je tente de créer ma fonction dans Postgres :

ERREUR:  erreur de syntaxe sur ou près de « dbms_output »
LINE 27:     dbms_output.NEW_LINE;

voici le code de ma fonction :


SET client_encoding TO 'UTF8';

SET search_path = toto,dbms_output,oracle,utl_file;
\set ON_ERROR_STOP ON



CREATE OR REPLACE FUNCTION toto.table_to_file ( VAR_NOM_TABLE text, VAR_NOM_FICH text, VAR_CONDITION text DEFAULT NULL, VAR_DELIMITEUR CHAR DEFAULT '@', VAR_DEBUG boolean DEFAULT FALSE ) RETURNS numeric AS $body$
DECLARE

  TabCol CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
                   from USER_TAB_COLUMNS
                   where TABLE_NAME=VAR_NOM_TABLE;
  TabColLigne     RECORD;
  CurseurExecSql  numeric;
  Ignore          numeric;
  NbLigne         numeric;
  TotalLigne      numeric;
  DataLength      numeric;
  ReturnLength    numeric;
  Iteration       numeric;
  AllCol          varchar(10000) := ''''||VAR_NOM_TABLE||':''';
  Requete         varchar(10000);
  Ligne           varchar(32000);
  MAX_CAR_DATA    CONSTANT numeric := 32000;
  MAX_CAR_REQ     CONSTANT numeric := 10000;
  MAX_BUF_UTL     CONSTANT numeric := 1023;
  MAX_BUF_VARCHAR CONSTANT numeric := 2000;
  fh              UTL_FILE.FILE_TYPE;
  I RECORD;

BEGIN
    RAISE NOTICE '%% to file %%',  'Copying Table ', VAR_NOM_TABLE, VAR_NOM_FICH, ' ...';
    dbms_output.NEW_LINE;
    /* ----------------------------------------------------------- */
    /* Construction de la chaine de carcatere contenant l'ensemble */
    /* des noms des colonnes de la table VAR_NOM_TABLE             */
    /* Verification du depassement de longueur des donnees         */
    /* Verification des types : LONG, RAW : non supporte           */
    /* ----------------------------------------------------------- */
    DataLength := 0;
    open TabCol;
    LOOP
        fetch TabCol into TabColLigne;
        IF NOT FOUND THEN EXIT; END IF; -- apply on TabCol
        IF(POSITION('LONG' in TabColLigne.DATA_TYPE) != 0) OR (POSITION('RAW' in TabColLigne.DATA_TYPE) != 0)
        THEN
            AllCol := AllCol || '||''' || VAR_DELIMITEUR || '''';
            RAISE NOTICE 'WARNING: Can''t ouput for %', TabColLigne.COLUMN_NAME;
            RAISE NOTICE '         in table %', VAR_NOM_TABLE;
            RAISE NOTICE '         Because type is RAW, LONG RAW or LONG';
            DBMS_OUTPUT.NEW_LINE;
        ELSE
            DataLength := DataLength + TabColLigne.DATA_LENGTH + 1;
            AllCol := AllCol || '||' || TabColLigne.COLUMN_NAME || '||''' || VAR_DELIMITEUR || '''';
        END IF;
    END LOOP;
    close TabCol;
    /* ------------------------------------------------- */
    /* Verification pbme eventuel de longueur de donnees */
    /* ------------------------------------------------- */
    IF DataLength > MAX_BUF_VARCHAR
    THEN
        RAISE NOTICE 'WARNING: Possible buffer overflow...! ';
        RAISE NOTICE '         Data length : %', DataLength;
        RAISE NOTICE '         Max buffer  : %', MAX_CAR_DATA;
        DBMS_OUTPUT.NEW_LINE;
    END IF;
    /* ----------------------------------------------------- */
    /* Construction de la requete a executer                 */
    /* Si demande d'infos. de DEBUG, affichage de la requete */
    /* ----------------------------------------------------- */
    Requete := 'SELECT '||AllCol||' from '||VAR_NOM_TABLE||' '||VAR_CONDITION;
    IF VAR_DEBUG
    THEN
        fh := UTL_FILE.FOPEN( '/tmp', 'DEBUG.log', 'w' );
        UTL_FILE.PUT( fh, 'REQUETE : ' );
        UTL_FILE.PUT_LINE( fh, Requete );
        UTL_FILE.PUT( fh, 'LONGUEUR TOTALE DES DONNEES(octets) : ' );
        UTL_FILE.PUT_LINE( fh, DataLength );
        UTL_FILE.FCLOSE( fh );
    END IF;
    /* -------------------------------------------------------------- */
    /* Execution de la requete et ecriture de chaque ligne du curseur */
    /* resultat dans le fichier specifie en parametre dans le rep.    */
    /* /tmp                                                           */
    /* -------------------------------------------------------------- */
    CurseurExecSql := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE( CurseurExecSql, Requete, DBMS_SQL.V7 );
    DBMS_SQL.DEFINE_COLUMN( CurseurExecSql, 1, Ligne, MAX_CAR_DATA );
    Ignore := DBMS_SQL.EXECUTE( CurseurExecSql );
    TotalLigne := 0;
    fh := UTL_FILE.FOPEN( '/tmp', VAR_NOM_FICH, 'a' );
    LOOP
        NbLigne := DBMS_SQL.FETCH_ROWS( CurseurExecSql );
        IF NbLigne > 0
        THEN
            TotalLigne := NbLigne + TotalLigne;
            DBMS_SQL.COLUMN_VALUE( CurseurExecSql, 1, Ligne );
            Iteration := FLOOR( LENGTH(Ligne) / MAX_BUF_UTL );
            FOR I IN 1..Iteration LOOP
                UTL_FILE.PUT( fh, REPLACE(SUBSTR(Ligne, I, MAX_BUF_UTL), CHR(10), ' '));
            END LOOP;
            UTL_FILE.PUT_LINE( fh, REPLACE(SUBSTR(Ligne, Iteration+1, LENGTH(Ligne)-(Iteration*MAX_BUF_UTL)), CHR(10), ' '));
        ELSE
            EXIT;
        END IF;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR( CurseurExecSql );
    UTL_FILE.fclose( fh );
    RAISE NOTICE '%%',  TotalLigne, ' Lines.';
    DBMS_OUTPUT.NEW_LINE;
    RETURN TotalLigne;
    /* ----------------------- */
    /* Gestion des EXCEPTIONS  */
    /* ----------------------- */
    EXCEPTION
      when UTL_FILE.INVALID_PATH then
           RAISE NOTICE 'ERREUR: chemin invalide -> /tmp';
           RETURN 0;
      when UTL_FILE.INVALID_FILEHANDLE then
           RAISE NOTICE 'ERREUR: File Handle';
           RETURN 0;
      when UTL_FILE.INVALID_MODE then
           RAISE NOTICE 'ERREUR: ouverture fichier';
           RETURN 0;
      when UTL_FILE.INVALID_OPERATION then
           RAISE NOTICE 'ERREUR: operation invalide';
           RETURN 0;
      when UTL_FILE.WRITE_ERROR then
           RAISE NOTICE 'ERREUR: ecriture fichier';
           RETURN 0;
      when OTHERS then
           IF DBMS_SQL.IS_OPEN( CurseurExecSql )
           THEN
               DBMS_SQL.CLOSE_CURSOR( CurseurExecSql );
           END IF;
           RAISE;
           RETURN 0;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
STABLE;


voici mon search_path :

                 search_path                 
----------------------------------------------
toto, public, oracle, dbms_output, utl_file



Pouvez-vous m'aider svp?

Dernière modification par joguess (25/09/2018 09:50:06)

Hors ligne

#14 12/09/2018 10:16:07

gleu
Administrateur

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Vous ne pouvez pas appeler une fonction en indiquant juste son nom. Vous devez utiliser l'ordre SQL SELECT. C'est clairement montré dans les exemples de dbms_output (voir https://github.com/orafce/orafce#package-dbms_output).

Hors ligne

#15 17/09/2018 15:52:58

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour, Merci pour votre réponse, cela marche.

J'ai un autre problème dans la création d'une fonction.



Je suis bien en 9.6. la fonction current_setting existe. cela vient peut etre de la version d'ora2pg qui n'est pas à jour sur mon poste ?

Dernière modification par joguess (25/09/2018 09:55:19)

Hors ligne

#16 17/09/2018 19:32:28

gleu
Administrateur

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Quand vous faîtes un SELECT ... INTO .... dans plpgsql, ce langage pense que ce qu'il y a après le INTO est un nom de variable, donc là avec un nom de fonction, il est paumé et ne sait pas quoi faire. Et j'avoue que moi aussi, je suis paumé. Je ne comprends pas ce que vous tentez de faire ??

Hors ligne

#17 19/09/2018 09:16:00

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour,



J'utilise ora2pg pour convertir le Pl/sql en plpgsql ...

Dernière modification par joguess (25/09/2018 09:55:46)

Hors ligne

#18 19/09/2018 10:44:55

gilles
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour,

Si vous constatez des problèmes avec avec la conversion par Ora2Pg il faut commencer par mettre à jour votre installation avec le dernier code en développement. Pour cela vous pouvez utiliser "git pull" ou télécharger le code avec un "wget  https://github.com/darold/ora2pg/archive/master.zip". Il y a des mise à jour presque toutes les nuits. Ensuite si vous rencontrez toujours un problème le mieux est de remonter l'erreur sur https://github.com/darold/ora2pg/issues pour être sur que la correction soit effectuée. Je ne passe que très rarement sur le forum.

Ceci dit pour votre problème de conversion, voici ce que traduit la version en cours de développement:

 $ ora2pg -t PACKAGE -c test-scripts/ora2pg.conf -i topic28262.sql
 $ cat output.sql

DROP SCHEMA IF EXISTS pkg_crayon CASCADE;
CREATE SCHEMA pkg_crayon;


CREATE OR REPLACE FUNCTION pkg_crayon.longueurfabrice (crayon CR_CRAYON.nom_crayon%type) RETURNS VOID AS $body$
BEGIN
    select type into STRICT TypCray from type_crayon where nom_crayon = crayon;
    if TypCray = 'FABRICE' then
     begin
     select LONG_COL_COMB into STRICT long_comb from CR_FABRICE where nom_fabrice = crayon;
     end;
    end if;
END;

$body$
LANGUAGE PLPGSQL
 STABLE;

CREATE OR REPLACE FUNCTION pkg_crayon.get (crayon CR_CRAYON.nom_crayon%type) RETURNS VOID AS $body$
BEGIN
  long_comb := -1;
  long_plenum_i := -1;
  long_plenum_s := -1;
  Longueur(crayon);
  PlenumS(crayon);
  PlenumI(crayon);
END;

$body$
LANGUAGE PLPGSQL
;

J'imagine que long_comb, long_plenum_i, long_plenum_s sont des variables globales mais comme il n'y a pas la description du package dans mon fichier d'entrée topic28262.sql, mais que le package boby, Ora2pg n'en a pas la connaissance. Sur votre export elles devraient être détectées.

Cordialement,

--
Gilles Darold
http://www.darold.net/

Dernière modification par gilles (19/09/2018 10:47:48)

Hors ligne

#19 20/09/2018 14:05:56

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Bonjour,

Merci beaucoup pour votre retour sur ma fonction et oui je vais tester la nouvelle version d'ora2pg.


Cordialement,

Hors ligne

#20 20/09/2018 14:07:35

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

...

Dernière modification par joguess (25/09/2018 09:56:16)

Hors ligne

#21 24/09/2018 12:36:25

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

J'aimerais reproduire ce que vous avez fait mais je n'y arrive pas .

Pouvez vous me donner le contenu de votre fichier topic28262.sql svp ? ainsi que celui de votre fichier de configuration ora2pg.conf

merci.

Hors ligne

#22 24/09/2018 13:50:38

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

...

Dernière modification par joguess (25/09/2018 09:56:43)

Hors ligne

#23 24/09/2018 13:54:44

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

...

Dernière modification par joguess (25/09/2018 09:56:58)

Hors ligne

#24 24/09/2018 14:04:35

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

$ ora2pg -v
Ora2Pg v19.0

Hors ligne

#25 24/09/2018 14:15:32

joguess
Membre

Re : Problème dans l'intégration des fonctions dbms_output.xxx

Pouvez vous me communiquer la procédure pour migrer des packages Oracle en Postgres svp ?

D'avance ;merci

Hors ligne

Pied de page des forums