SQLEXEC

Durée de validité

Extraction et réplication

Description

Utilisez le paramètre SQLEXEC pour exécuter une commande de base de données, une requête ou une procédure stockée dans le contexte du traitement Oracle GoldenGate. SQLEXEC permet à Oracle GoldenGate de communiquer directement avec la base de données pour effectuer toute tâche prise en charge par la base de données. Ce travail peut faire partie du processus de synchronisation, tel que l'extraction de valeurs pour la conversion de colonne, ou être indépendant de l'extraction ou de la réplication de données, comme l'exécution d'une procédure stockée qui exécute une action dans la base de données.

Remarque : SQLEXEC fournit une prise en charge minimale de la mondialisation. Pour utiliser SQLEXEC dans le fichier de paramètres de capture de la capture source, assurez-vous que le jeu de caractères client dans le fichier .prm source est le même ou un surensemble du jeu de caractères de la base de données source.

SQLEXEC fonctionne comme suit :

Remarque : le paramètre SQLEXECONBEFOREIMAGE prend en charge l'exécution de SQLEXEC sur les enregistrements Before Image.

Remarque : le paramètre SQLEXECONBEFOREIMAGE est ignoré dans l'enregistrement de mise à jour de clé primaire ou de mise à jour unifiée lorsque le type EXEC est TRANSACTION, SOURCEROW ou ONCE.

SQLEXEC autonome

Un paramètre SQLEXEC autonome est un paramètre utilisé au niveau racine d'un fichier de paramètres et qui agit indépendamment d'un paramètre TABLE ou MAP. Vous trouverez ci-dessous des instructions pour l'utilisation d'un paramètre SQLEXEC autonome.

Obtenir plus d'informations sur l'utilisation de SQLEXEC autonome

Pour plus d'informations sur l'utilisation de SQLEXEC, reportez-vous à Utilisation de SQLEXEC pour exécuter des commandes, des procédures stockées et des requêtes.

Syntaxe pour SQLEXEC autonome

SQLEXEC
{'call <procedure_name>()' | '<SQL_query>' | '<database_command>'}
[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]
[ONEXIT]
[, THREADS (<threadID>[, <threadID>][, ...][, <thread_range>[, <thread_range>][, ...])]

'Appelez <procedure_name ()'>

Indique le nom d'une procédure stockée à exécuter. Placez l'instruction entre apostrophes. Le mot-clé call est requis. Voici un exemple d'exécution d'une procédure avec SQLEXEC autonome :

SQLEXEC 'call prc_job_count ()'

<'SQL_query'>

Spécifie le nom d'une requête à exécuter. Placez l'interrogation entre apostrophes. Indiquez des noms d'objet sensibles à la casse dans le même format que celui requis par la base de données. Voici un exemple d'exécution d'une requête avec SQLEXEC autonome :

SQLEXEC ' select x from dual '

Pour une requête multiligne, utilisez les guillemets simples sur chaque ligne. Pour obtenir de meilleurs résultats, saisissez un espace après chaque devis de début et avant chaque devis de fin (ou au moins avant chaque devis de fin).

<'database_command'>

Exécute une commande de base de données. Voici un exemple d'exécution d'une commande de base de données avec SQLEXEC autonome :

SQLEXEC 'SET TRIGGERS OFF'

EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}

Entraîne l'exécution d'une procédure ou d'une requête stockée autonome à un intervalle défini, par exemple :

SQLEXEC 'call prc_job_count ()' EVERY 30 SECONDS

L'intervalle doit être un entier positif entier.

ONEXIT

Exécute l'instruction SQL lors de l'arrêt progressif des processus d'extraction ou d'exécution. Par exemple :

SQLEXEC 'call prc_job_count ()' ONEXIT

THREADS (<threadID>[, <threadID>][, ...][, <thread_range>[, <thread_range>][, ...])

Exécute SQLEXEC uniquement pour les threads spécifiés d'une réplication coordonnée.

<threadID>[, <threadID>][, ...]

Indique un thread ID ou une liste de threads séparés par une virgule au format threadID, threadID, threadID.

[, <thread_range>[, <thread_range>][, ...]

Indique une plage de threads au formatthreadIDlow-threadIDhigh ou une liste de plages délimitées par une virgule au formatthreadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

Vous pouvez combiner ces formats. Par exemple, threadID, threadID, threadIDlow-threadIDhigh.

Si aucune clause THREADS n'est utilisée, l'instruction SQL est exécutée par tous les threads configurés pour ce groupe de réplication par la commande ADD REPLICAT. Toutefois, si l'instruction SQL répond aux critères d'une transaction barrière, l'ensemble de l'instruction SQLEXEC est traité par le thread 0, quel que soit le mapping de thread réel.

SQLEXEC dans un paramètre TABLE ou MAP

Un paramètre SQLEXEC dans un paramètre TABLE ou MAP peut être utilisé pour exécuter une requête ou une procédure stockée qui accepte ou non des paramètres. Les dépendances et restrictions SQLEXEC sont les suivantes lorsqu'elles sont utilisées dans une instruction MAP ou TABLE :

Syntaxe de SQLEXEC dans TABLE ou MAP

SQLEXEC (
{SPNAME <procedure_name>[, ID <logical_name>] |
   ID <logical_name>, QUERY ' <SQL_query> '}
{, PARAMS [OPTIONAL | REQUIRED] <parameter_name> = {<source_column> | <OGG_function>} |
   NOPARAMS}
[, AFTERFILTER | BEFOREFILTER]
[, ALLPARAMS {OPTIONAL | REQUIRED}]
[, ERROR {IGNORE | REPORT | RAISE | FINAL | FATAL}]
[, EXEC {MAP | ONCE | TRANSACTION | SOURCEROW}][, MAXVARCHARLEN <bytes>]
[, PARAMBUFSIZE <bytes>]
[, TRACE]
[, ...]
[, BEFORE_col1 = @BEFORE(col1),
)

SPNAME <procedure_name>[, ID <logical_name>]

Exécute une procédure stockée.

SPNAME <procedure_name>

Indique le nom de la procédure à exécuter.

L'exemple suivant présente une exécution unique d'une procédure stockée nommée lookup. Dans ce cas, le nom réel de la procédure est utilisé. Un nom logique n'est pas nécessaire.

SQLEXEC (SPNAME lookup), PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup.param1);

ID <logical_name>

Définit un nom logique facultatif pour la procédure. Par exemple, les noms logiques d'une procédure nommée lookup peuvent être lookup1, lookup2, etc. Utilisez cette option pour exécuter la procédure plusieurs fois dans une instruction MAP. Une procédure peut être exécutée jusqu'à 20 fois par instruction MAP. ID n'est pas requis lors de l'exécution d'une procédure une seule fois.

L'exemple suivant illustre l'utilisation de l'option ID pour activer plusieurs exécutions d'une procédure stockée qui obtient des valeurs à partir d'une table de consultation. Les valeurs sont mises en correspondance avec les colonnes cible.

SQLEXEC (SPNAME lookup, ID lookup1, &
  PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
  PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, current_residence_state_long = lookup
1.long_name, & birth_state_long = lookup2.long_name);

`ID , REQUÊTE ' '

Exécute une requête.

ID <logical_name>

Définit un nom logique pour la requête. Un nom logique est requis pour extraire les valeurs des résultats de la requête. <ID logical_name> référence les valeurs de colonne renvoyées par la requête.

QUERY '<SQL_query>'

Indique la syntaxe de requête SQL à exécuter sur la base de données. La requête peut renvoyer des résultats avec une instruction SELECT ou exécuter une instruction INSERT, UPDATE ou DELETE. Une instruction SELECT ne doit renvoyer qu'une seule ligne. Si plusieurs lignes sont renvoyées, seule la première ligne est traitée. N'indiquez pas de clause INTO ... pour une requête SELECT statements.The. Le langage de requête standard de la base de données sur laquelle elle est exécutée doit être valide. La plupart des requêtes nécessitent des espaces réservés pour les paramètres d'entrée. La manière dont les paramètres sont spécifiés dans l'interrogation dépend du type de base de données, comme suit :

'SELECT NAME FROM ACCOUNT WHERE SSN = :SSN AND ACCOUNT = :ACCT'
'SELECT NAME FROM ACCOUNT WHERE SSN = ? AND ACCOUNT = ?'

La requête doit être contenue sur une ligne, entre apostrophes. Les guillemets ne sont pas obligatoires autour d'un nom de paramètre pour une base de données.

Les exemples suivants illustrent l'utilisation d'une requête SQLEXEC pour les requêtes Oracle et SQL Server, respectivement.

Exemple Oracle :

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (ID lookup, &
  QUERY 'select desc_col into desc_param from lookup_table &
  where code_col = :code_param', &
  PARAMS (code_param = account_code)), &
  COLMAP (newacct_id = account_id, newacct_val = lookup.desc_param);

Exemple SQL Server :

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (ID lookup, &
  QUERY 'select desc_col into desc_param from lookup_table &
  where code_col = ?', &
  PARAMS (p1 = account_code)), &
  COLMAP (newacct_id = account_id, &
  newacct_val = lookup.desc_param);

PARAMS [OPTIONAL | REQUIRED] <parameter_name> = {<source_column> | <OGG_function>} | NOPARAMS

Définit si la procédure ou la requête accepte ou non les paramètres et, si oui, met en correspondance les paramètres avec la source d'entrée. Vous devez utiliser une clause PARAMS ou NOPARAMS.

OPTIONAL REQUIRED

Détermine si la procédure ou la requête s'exécute lorsque des valeurs de paramètre sont manquantes.

OPTIONAL indique qu'aucune valeur de paramètre n'est requise pour l'exécution de l'instruction SQL. Si une colonne source obligatoire est manquante dans l'opération de base de données ou si une fonction de conversion de colonne ne peut pas aboutir car une colonne source est manquante, le code SQL s'exécute quand même. OPTIONAL est la valeur par défaut pour toutes les bases de données à l'exception d'Oracle. Pour Oracle, le fait qu'un paramètre soit facultatif ou non est automatiquement déterminé lors de l'extraction de la définition de procédure stockée.

REQUIRED indique qu'une valeur de paramètre doit être présente. Si la valeur du paramètre n'est pas présente, l'instruction SQL ne sera pas exécutée.

<parameter_name> = {<source_column> | <OGG_function>}

Met en correspondance le nom d'un paramètre avec une colonne ou une fonction qui fournit l'entrée. Les types de données suivants sont pris en charge par SQLEXEC pour les paramètres d'entrée et de sortie.

<parameter_name> correspond à l'un des éléments suivants :

SQLEXEC (ID appphone, QUERY ' select per_type from ps_personal_data '
    ' where emplid = :vemplid '
    ' and per_status = 'N' and per_type = 'A' ',
    PARAMS (vemplid = emplid)),
TOKENS (applid = @GETVAL(appphone.per_type));
SQLEXEC (ID appphone, QUERY ' select per_type from ps_personal_data '
    ' where emplid = ? '
    ' and per_status = 'N' and per_type = 'A' ',
    PARAMS (p1 = emplid)),
TOKENS (applid = @GETVAL(appphone.per_type));

<source_column> est le nom d'une colonne source qui fournit l'entrée. Par défaut, si la colonne spécifiée n'est pas présente dans le journal (car l'enregistrement contient uniquement les valeurs des colonnes qui ont été mises à jour), le paramètre suppose toute valeur par défaut spécifiée par la procédure ou la requête pour le paramètre.

<OGG_function> est le nom d'une fonction de conversion de colonne Oracle GoldenGate qui s'exécute pour fournir l'entrée. Reportez-vous à Fonctions de mapping de table et de colonne.

Pour transmettre les valeurs de sortie de la procédure stockée ou de la requête en tant qu'entrée à une clause FILTER ou COLMAP, utilisez la syntaxe suivante :

{<procedure_name> | <logical_name>}.<parameter>

Où :

Les exemples suivants s'appliquent à un ensemble de tables source et cible Oracle et à une table de consultation. Ces exemples montrent comment les paramètres des tables sont transmis pour une seule instance d'une procédure stockée et pour plusieurs instances d'une procédure stockée.

Client de table source :

custid                     Number
current_residence_state    Char(2)
birth_state                Char(2)

Table cible cust_extended :

custid                          Number
current_residence_state_long    Varchar(30)
birth_state_long                Varchar(30)

Table de consultation state_lookup

abbreviation     Char(2)
long_name        Varchar(30)

L'exemple suivant illustre l'utilisation d'une procédure stockée qui s'exécute une fois pour obtenir une valeur à partir de la table de consultation. Lors du traitement des enregistrements à partir de la table cust, Oracle GoldenGate exécute la procédure stockée lookup avant d'exécuter la correspondance de colonnes. Le paramètre long_name de la procédure accepte les entrées de la valeur column.The source birth_state est mis en correspondance avec la colonne cible birth_state_long dans l'instruction COLMAP.

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

L'exemple suivant illustre l'utilisation de l'option ID pour activer plusieurs exécutions d'une procédure stockée qui obtient des valeurs à partir d'une table de consultation. Les valeurs sont mises en correspondance avec les colonnes cible.

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, ID lookup1, &
PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, current_residence_state_long = lookup1.long_name, & birth_state_long = lookup2.long_name);
AFTERFILTER BEFOREFILTER

Utilisez AFTERFILTER et BEFOREFILTER pour indiquer quand exécuter la procédure stockée ou la requête en relation avec la clause FILTER d'une instruction MAP.

AFTERFILTER

Entraîne l'exécution du code SQL après l'instruction FILTER. Cela vous permet d'ignorer la surcharge liée à l'exécution de l'instruction SQL, sauf si le filtre réussit. Il s'agit de la fonction par défaut.

BEFOREFILTER

Entraîne l'exécution de l'instruction SQL avant l'instruction FILTER, afin que les résultats puissent être utilisés dans le filtre.

Voici un exemple utilisant BEFOREFILTER.

SQLEXEC (SPNAME check, NOPARAMS, BEFOREFILTER)
ALLPARAMS [OPTIONAL REQUIRED]

Utilisez ALLPARAMS en tant que règle globale qui détermine si tous les paramètres indiqués doivent être présents pour que la procédure stockée ou la requête puisse être exécutée. Les règles pour les paramètres individuels établis dans la clause PARAMS remplacent l'ensemble de règles global par ALLPARAMS.

OPTIONAL

Permet à l'instruction SQL d'exécuter si tous les paramètres sont présents ou non. Il s'agit de la fonction par défaut.

REQUIRED

Nécessite que tous les paramètres soient présents pour l'exécution du code SQL.

Voici un exemple utilisant OPTIONAL.

SQLEXEC (SPNAME lookup,
PARAMS (long_name = birth_state, short_name = state),
ALLPARAMS OPTIONAL)
ERROR {IGNORE REPORT RAISE FINAL FATAL}

Utilisez ERROR pour définir une réponse aux erreurs associées à la requête ou à la procédure stockée. Sans traitement explicite des erreurs, le processus Oracle GoldenGate abandonne les erreurs. Assurez-vous que vos procédures renvoient des erreurs au processus et spécifiez les réponses avec ERROR.

Avec Oracle GoldenGate 21c, la fonctionnalité vous permet d'indiquer différents comportements en fonction du type d'erreur SQL.

L'exemple suivant illustre un processus ER d'abandon lorsque le code d'erreur SQL 1403 ou 1405 est détecté lors de l'exécution de la recherche de procédure stockée. Toutes les autres erreurs sont signalées et la réplication continue.

SQLEXEC ( SPNAME lookup,
  PARAMS( long_name = birth_state, short_name = state),
  ERROR REPORT, ERROR FATAL (1403, 1405) );

IGNORE

Entraîne Oracle GoldenGate à ignorer toutes les erreurs associées à la procédure stockée ou à la requête et à poursuivre le traitement. Toute extraction de paramètre résultante entraîne des conditions "colonne manquante". Il s'agit de la fonction par défaut.

REPORT

Garantit que toutes les erreurs associées à la procédure stockée ou à la requête sont signalées au fichier de rebut. Ce rapport est utile pour rechercher la cause de l'erreur. Il inclut à la fois une description d'erreur et la valeur des paramètres transmis à et depuis la procédure ou la requête. Oracle GoldenGate poursuit le traitement après avoir signalé l'erreur.

RAISE

Traite les erreurs en fonction des règles définies par un paramètre REPERROR. Oracle GoldenGate poursuit le traitement d'autres procédures stockées ou requêtes associées à l'instruction MAP en cours avant de traiter l'erreur.

FINAL

Est similaire à RAISE, sauf qu'en cas d'erreur associée à une procédure ou une requête, les procédures stockées restantes et les requêtes sont ignorées. Le traitement des erreurs est appelé immédiatement après l'erreur.

FATAL

Entraîne l'abandon immédiat d'Oracle GoldenGate lorsqu'une erreur associée à une procédure ou une requête se produit.

EXEC {MAP ONCE TRANSACTION SOURCEROW}

Utilisez EXEC pour contrôler la fréquence d'exécution d'une procédure stockée ou d'une requête dans une instruction MAP, ainsi que la durée pendant laquelle les résultats sont considérés comme valides, lors de l'extraction des paramètres de sortie.

MAP

Exécute la procédure ou l'interrogation une fois pour chaque mapping de table source-cible pour lequel elle est indiquée. L'utilisation de MAP rend les résultats non valides pour les mappings suivants qui ont la même table source. MAP est la valeur par défaut.

L'exemple suivant montre l'utilisation incorrecte de la valeur par défaut de MAP. Comme MAP est la valeur par défaut, il n'est pas nécessaire qu'elle soit explicitement répertoriée dans l'instruction SQLEXEC. Dans cet exemple, une table source est mise en correspondance dans des paramètres MAP distincts avec deux tables cible différentes. Dans ce cas, les résultats ne sont valides que pour le premier mapping. Les résultats de la procédure lookup ont expiré au moment de l'exécution du deuxième paramètre MAP, et le deuxième paramètre MAP entraîne une condition "colonne manquante". Pour l'implémenter correctement afin que chaque élément MAP renvoie des résultats valides, SOURCEROW doit être utilisé.

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup.param2);

MAP sales.srctab, TARGET sales.targtab2, &
COLMAP (targcol2 = lookup.param2);

ONCE

Exécute la procédure ou la requête une fois au cours de l'exécution d'Oracle GoldenGate, lors du premier appel de l'instruction MAP associée. Les résultats restent valides tant que le traitement reste en cours d'exécution.

Ce qui suit est un exemple d'utilisation de ONCE.

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state), EXEC ONCE), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

TRANSACTION

Exécute la procédure ou l'interrogation une fois par transaction source. Les résultats restent valides pour toutes les opérations de la transaction.

Ce qui suit est un exemple d'utilisation de TRANSACTION.

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state), EXEC TRANSACTION), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

SOURCEROW

Exécute la procédure ou l'interrogation une fois par opération de ligne source. Utilisez cette option lorsque vous synchronisez une table source avec plusieurs tables cible, de sorte que les résultats de la procédure ou de la requête soient appelés pour chaque mapping source-cible.

Ce qui suit est un exemple d'utilisation de SOURCEROW. Dans ce cas, la seconde mappe renvoie une valeur valide car la procédure s'exécute sur chaque opération de ligne source.

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol), EXEC SOURCEROW), &
COLMAP (targcol = lookup.param2);

MAP sales.srctab, TARGET sales.targtab2, &
COLMAP (targcol2 = lookup.param2);

MAXVARCHARLEN <bytes>

Utilisez MAXVARCHARLEN pour indiquer la longueur d'octet maximale allouée pour la valeur de sortie d'un paramètre dans une procédure ou une requête stockée. Au-delà de ce maximum, les valeurs de sortie sont tronquées. La valeur par défaut est de 255 octets sans clause MAXVARCHARLEN explicite. La plage de valeurs valide est comprise entre 50 et 32767 octets.

L'exemple suivant limite la longueur en octets des valeurs de sortie à 100.

MAXVARCHARLEN 100

PARAMBUFSIZE <bytes>

Utilisez PARAMBUFSIZE pour indiquer le nombre maximal d'octets autorisés pour la mémoire tampon qui stocke les informations de paramètre SQLEXEC, y compris les paramètres d'entrée et de sortie. La valeur par défaut est de 10 000 octets sans clause PARAMBUFSIZE explicite. La plage de valeurs valide va de 1000 à 2000000 octets. Oracle GoldenGate émet un avertissement chaque fois que la mémoire allouée aux paramètres est inférieure à 500 octets de la valeur maximale.

Dans l'exemple suivant, le tampon est porté à 15 000 octets.

PARAMBUFSIZE 15000
TRACE {ALL ERROR}

Utilisez TRACE pour consigner les paramètres d'entrée et de sortie SQLEXEC dans le fichier de rapport.

Voici un exemple de fichier de rapport avec la fonction de trace SQLEXEC activée :

Input parameter values...
LMS_TABLE: INTERACTION_ATTR_VALUES
   KEY1: 2818249
  KEY2: 1
Report File:
From Table MASTER.INTERACTION_ATTR_VALUES to MASTER.INTERACTION_ATTR_VALUES:
       #  inserts:      0
       #  updates:      0
       #  deletes:      0
       #  discards:     1

  Stored procedure GGS_INTERACTION_ATTR_VALUES:
       attempts:        2
       successful:      0

ALL

Ecrit les paramètres d'entrée et de sortie pour chaque appel de la procédure ou de la requête dans le fichier de rapport. Il s'agit de la fonction par défaut.

ERROR

Ecrit les paramètres d'entrée et de sortie pour chaque appel de la procédure ou de la requête dans le fichier d'état uniquement après une erreur SQL.