SQLEXEC

Valido per

Estrai e Replicat

Descrizione

Utilizzare il parametro SQLEXEC per eseguire una stored procedure, una query o un comando di database nel contesto dell'elaborazione di Oracle GoldenGate. SQLEXEC consente a Oracle GoldenGate di comunicare direttamente con il database per eseguire qualsiasi lavoro supportato dal database. Questo lavoro può far parte del processo di sincronizzazione, ad esempio il recupero dei valori per la conversione delle colonne, oppure può essere indipendente dall'estrazione o dalla replica dei dati, ad esempio l'esecuzione di una stored procedure che esegue un'azione all'interno del database.

Nota: SQLEXEC fornisce un supporto minimo per la globalizzazione. Per utilizzare SQLEXEC nel file dei parametri di acquisizione dell'acquisizione di origine, assicurarsi che il set di caratteri client nel file .prm di origine sia lo stesso o un superset del set di caratteri del database di origine.

SQLEXEC funziona come segue:

Nota: il parametro SQLEXECONBEFOREIMAGE supporta l'esecuzione di SQLEXEC nei record Prima dell'immagine.

Nota: il parametro SQLEXECONBEFOREIMAGE viene ignorato nel record di aggiornamento PK o di aggiornamento unificato quando il tipo EXEC è TRANSACTION, SOURCEROW o ONCE.

SQLEXEC standalone

Un parametro SQLEXEC standalone è quello utilizzato al livello radice di un file di parametri e agisce indipendentemente da un parametro TABLE o MAP. Di seguito sono riportate le linee guida per l'uso di un parametro SQLEXEC standalone.

Ulteriori informazioni sull'utilizzo di SQLEXEC standalone

Per ulteriori informazioni sull'utilizzo di SQLEXEC, vedere Utilizzare SQLEXEC per eseguire comandi, stored procedure e query.

Sintassi per SQLEXEC standalone

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

'chiamare <procedure_name ()'>

Specifica il nome di una stored procedure da eseguire. Racchiudere l'istruzione tra apici. È richiesta la parola chiave call. Di seguito è riportato un esempio di come eseguire una procedura con SQLEXEC standalone.

SQLEXEC 'call prc_job_count ()'

<'SQL_query'>

Specifica il nome di una query da eseguire. Racchiudere l'interrogazione tra apici. Specificare i nomi degli oggetti con distinzione tra maiuscole e minuscole nello stesso formato richiesto dal database. Di seguito è riportato un esempio di come eseguire una query con SQLEXEC standalone.

SQLEXEC ' select x from dual '

Per un'interrogazione su più righe, utilizzare le virgolette singole su ogni riga. Per risultati ottimali, digitare uno spazio dopo ogni virgoletta iniziale e prima di ogni virgoletta finale (o almeno prima di ogni virgoletta finale).

<'database_command'>

Esegue un comando di database. Di seguito è riportato un esempio di come eseguire un comando di database con SQLEXEC standalone.

SQLEXEC 'SET TRIGGERS OFF'

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

Causa l'esecuzione di una procedura o di una query memorizzata standalone in un intervallo definito, ad esempio:

SQLEXEC 'call prc_job_count ()' EVERY 30 SECONDS

L'intervallo deve essere un intero positivo.

ONEXIT

Esegue l'istruzione SQL quando il processo Extract o Replicat si arresta in modo normale. Ad esempio:

SQLEXEC 'call prc_job_count ()' ONEXIT

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

Esegue SQLEXEC solo per il thread o per i thread specificati di un Replicat coordinato.

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

Specifica un thread ID o una lista separata da virgole di thread nel formato threadID, threadID, threadID.

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

Specifica un intervallo di thread in formato threadIDlow-threadIDhigh o una lista di intervalli separata da virgole nel formatothreadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

La combinazione di questi formati, ad esempio threadID, threadID, threadIDlow-threadIDhigh, è consentita.

Se non viene utilizzata alcuna clausola THREADS, l'istruzione SQL viene eseguita da tutti i thread configurati per questo gruppo Replicat dal comando ADD REPLICAT. Tuttavia, se l'istruzione SQL soddisfa i criteri per una transazione barriera, l'intera istruzione SQLEXEC viene elaborata dal thread 0 indipendentemente dal mapping thread effettivo.

SQLEXEC in un parametro TABLE o MAP

Un parametro SQLEXEC in un parametro TABLE o MAP può essere utilizzato per eseguire una stored procedure o query che esegue o non accetta parametri. Di seguito sono riportate le dipendenze e le limitazioni di SQLEXEC quando vengono utilizzate in un'istruzione MAP o TABLE.

Sintassi di SQLEXEC in TABLE o MAPPA

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>]

Esegue una stored procedure.

SPNAME <procedure_name>

Specifica il nome della procedura da eseguire.

L'esempio seguente mostra una singola esecuzione di una stored procedure denominata lookup. In questo caso, viene utilizzato il nome effettivo della procedura. Non è necessario un nome logico.

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

ID <logical_name>

Definisce un nome logico facoltativo per la procedura. Ad esempio, i nomi logici per una procedura denominata lookup possono essere lookup1, lookup2 e così via. Utilizzare questa opzione per eseguire la procedura più volte all'interno di un'istruzione MAP. Una procedura può essere eseguita fino a 20 volte per ogni istruzione MAP. ID non è necessario quando si esegue una sola procedura.

L'esempio seguente mostra l'uso dell'opzione ID per abilitare più esecuzioni di una stored procedure che recupera i valori da una tabella di ricerca. I valori vengono mappati alle colonne target.

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 , QUERY ' '

Esegue una query.

ID <logical_name>

Definisce un nome logico per la query. Per estrarre i valori dai risultati della query è necessario un nome logico. <ID logical_name> fa riferimento ai valori di colonna restituiti dalla query.

QUERY '<SQL_query>'

Specifica la sintassi della query SQL da eseguire sul database. La query può restituire risultati con un'istruzione SELECT oppure eseguire un'istruzione INSERT, UPDATE o DELETE. Un'istruzione SELECT deve restituire solo una riga. Se vengono restituite più righe, viene elaborata solo la prima. Non specificare una clausola INTO ... per qualsiasi query SELECT statements.The deve essere un linguaggio di query standard valido per il database su cui viene eseguita. La maggior parte delle query richiede segnaposto per i parametri di input. Il modo in cui i parametri vengono specificati nella query dipende dal tipo di database, come indicato di seguito.

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

La query deve essere contenuta in una riga, all'interno di virgolette singole. I segni di citazione non sono necessari per il nome di un parametro per qualsiasi database.

Gli esempi riportati di seguito illustrano l'uso di una query SQLEXEC per le query Oracle e SQL Server, rispettivamente.

Esempio 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);

Esempio di 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

Definisce se la procedura o la query accetta o meno i parametri e, in caso affermativo, mappa i parametri all'origine di input. Utilizzare una clausola PARAMS o NOPARAMS.

OPTIONAL REQUIRED

Determina se la procedura o la query viene eseguita quando mancano i valori dei parametri.

OPTIONAL indica che non è necessario un valore di parametro per l'esecuzione dell'istruzione SQL. Se nell'operazione del database manca una colonna di origine obbligatoria o se una funzione di conversione colonna non può essere completata correttamente perché manca una colonna di origine, l'istruzione SQL viene eseguita comunque. OPTIONAL è l'impostazione predefinita per tutti i database ad eccezione di Oracle. Per Oracle, quando si recupera la definizione della stored procedure viene determinato automaticamente se un parametro è facoltativo o meno.

REQUIRED indica che deve essere presente un valore di parametro. Se il valore del parametro non è presente, l'istruzione SQL non verrà eseguita.

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

Mappa il nome di un parametro a una colonna o a una funzione che fornisce l'input. I seguenti tipi di dati sono supportati da SQLEXEC per i parametri di input e output.

<parameter_name> è uno dei seguenti:

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> è il nome di una colonna di origine che fornisce l'input. Per impostazione predefinita, se la colonna specificata non è presente nel log (poiché il record contiene solo i valori delle colonne aggiornate), il parametro assume qualsiasi valore predefinito specificato dalla procedura o dalla query per il parametro.

<OGG_function> è il nome di una funzione di conversione delle colonne di Oracle GoldenGate che viene eseguita per fornire l'input. Vedere "Funzioni di mapping delle tabelle e delle colonne".

Per passare i valori di output dalla stored procedure o dalla query come input a una clausola FILTER o COLMAP, utilizzare la sintassi seguente:

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

Dove:

Gli esempi riportati di seguito si applicano a un set di tabelle di origine e di destinazione Oracle e a una tabella di ricerca. Questi esempi mostrano come vengono passati i parametri per le tabelle per una singola istanza di una stored procedure e più istanze di una stored procedure.

Cliente tabella di origine:

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

Tabella di destinazione cust_extended:

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

Tabella di ricerca state_lookup

abbreviation     Char(2)
long_name        Varchar(30)

L'esempio seguente mostra l'uso di una stored procedure che viene eseguita una sola volta per ottenere un valore dalla tabella di ricerca. Durante l'elaborazione dei record dalla tabella cust, Oracle GoldenGate esegue la stored procedure lookup prima di eseguire la mappa delle colonne. Il parametro long_name nella procedura accetta l'input dal valore column.The di origine birth_state e viene mappato alla colonna di destinazione birth_state_long nell'istruzione 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'esempio seguente mostra l'uso dell'opzione ID per abilitare più esecuzioni di una stored procedure che recupera i valori da una tabella di ricerca. I valori vengono mappati alle colonne target.

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

Utilizzare AFTERFILTER e BEFOREFILTER per specificare quando eseguire la stored procedure o query in relazione alla clausola FILTER di un'istruzione MAP.

AFTERFILTER

Causa l'esecuzione dell'istruzione SQL dopo l'istruzione FILTER. Ciò consente di saltare il sovraccarico dell'esecuzione dell'istruzione SQL a meno che il filtro non riesca. Si tratta dello stato predefinito.

BEFOREFILTER

Causa l'esecuzione dell'istruzione SQL prima dell'istruzione FILTER, in modo che i risultati possano essere utilizzati nel filtro.

Di seguito è riportato un esempio che utilizza BEFOREFILTER.

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

Utilizzare ALLPARAMS come regola globale che determina se tutti i parametri specificati devono essere presenti per l'esecuzione della stored procedure o della query. Le regole per i singoli parametri stabiliti nella clausola PARAMS sostituiscono il set di regole globale con ALLPARAMS.

OPTIONAL

Consente all'istruzione SQL di eseguire se sono presenti o meno tutti i parametri. Si tratta dello stato predefinito.

REQUIRED

Richiede la presenza di tutti i parametri per l'esecuzione dell'istruzione SQL.

Di seguito è riportato un esempio che utilizza OPTIONAL.

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

Utilizzare ERROR per definire una risposta agli errori associati alla stored procedure o query. Senza la gestione esplicita degli errori, il processo Oracle GoldenGate si interrompe in base agli errori. Assicurarsi che le procedure restituiscano errori al processo e specificare le risposte con ERROR.

Con Oracle GoldenGate 21c, la funzionalità consente di specificare comportamenti diversi in base al tipo di errore SQL.

L'esempio seguente illustra un processo di interruzione ER quando viene rilevato il codice di errore SQL 1403 o 1405 durante l'esecuzione della ricerca della stored procedure. Tutti gli altri errori vengono segnalati e la replica continua.

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

IGNORE

Fa in modo che Oracle GoldenGate ignori tutti gli errori associati alla stored procedure o alla query e continui l'elaborazione. L'estrazione dei parametri risultante comporta condizioni "colonna mancante". Si tratta dello stato predefinito.

REPORT

Garantisce che tutti gli errori associati alla stored procedure o alla query vengano segnalati al file di scarto. Il report è utile per tracciare la causa dell'errore. Include sia una descrizione dell'errore che il valore dei parametri passati alla procedura o alla query. L'elaborazione di Oracle GoldenGate continua dopo aver segnalato l'errore.

RAISE

Gestisce gli errori in base alle regole impostate da un parametro REPERROR. Oracle GoldenGate continua a elaborare altre stored procedure o query associate all'istruzione MAP corrente prima di elaborare l'errore.

FINAL

È simile a RAISE, ad eccezione del fatto che quando si verifica un errore associato a una procedura o a una query, le procedure e le query memorizzate rimanenti vengono ignorate. L'elaborazione degli errori viene richiamata immediatamente dopo l'errore.

FATAL

Causa a Oracle GoldenGate l'interruzione immediata in caso di errore associato a una procedura o a una query.

EXEC {MAP ONCE TRANSACTION SOURCEROW}

Utilizzare EXEC per controllare la frequenza di esecuzione di una stored procedure o query in un'istruzione MAP e la durata di validità dei risultati in caso di estrazione dei parametri di output.

MAP

Esegue la procedura o la query una volta per ogni mappa della tabella di destinazione di origine per la quale è stata specificata. L'uso di MAP rende i risultati non validi per le mappe successive che hanno la stessa tabella di origine. MAP è l'impostazione predefinita.

L'esempio seguente mostra l'uso errato dell'impostazione predefinita MAP. Poiché MAP è il valore predefinito, non è necessario che sia elencato in modo esplicito nell'istruzione SQLEXEC. In questo esempio, una tabella di origine viene mappata in parametri MAP separati a due tabelle di destinazione diverse. In questo caso, i risultati sono validi solo per il primo mapping. I risultati della procedura lookup scadono al momento dell'esecuzione del secondo parametro MAP e il secondo parametro MAP restituisce una condizione "mancante colonna". Per implementare correttamente questo in modo che ogni MAP restituisca risultati validi, è necessario utilizzare SOURCEROW.

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

Esegue la procedura o la query una sola volta durante l'esecuzione di Oracle GoldenGate al primo richiamo dell'istruzione MAP associata. I risultati rimangono validi finché il processo rimane in esecuzione.

Di seguito è riportato un esempio di utilizzo di 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

Esegue la procedura o la query una volta per ogni transazione di origine. I risultati rimangono validi per tutte le operazioni della transazione.

Di seguito è riportato un esempio di utilizzo di 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

Esegue la procedura o la query una volta per ogni operazione della riga di origine. Utilizzare questa opzione quando si sincronizza una tabella di origine con più tabelle di destinazione, in modo che i risultati della procedura o della query vengano richiamati per ogni mapping di origine-destinazione.

Di seguito è riportato un esempio di utilizzo di SOURCEROW. In questo caso, la seconda mappa restituisce un valore valido perché la procedura viene eseguita su ogni operazione di riga di origine.

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>

Utilizzare MAXVARCHARLEN per specificare la lunghezza massima in byte allocata per il valore di output di qualsiasi parametro in una stored procedure o query. Oltre questo valore massimo, i valori di output vengono troncati. L'impostazione predefinita è 255 byte senza una clausola MAXVARCHARLEN esplicita. L'intervallo di valori valido è compreso tra 50 e 32767 byte.

L'esempio seguente limita la lunghezza in byte dei valori di output a 100.

MAXVARCHARLEN 100

PARAMBUFSIZE <bytes>

Utilizzare PARAMBUFSIZE per specificare il numero massimo di byte consentiti per il buffer di memoria che memorizza le informazioni sui parametri SQLEXEC, inclusi sia i parametri di input che quelli di output. L'impostazione predefinita è 10.000 byte senza una clausola PARAMBUFSIZE esplicita. L'intervallo di valori valido è compreso tra 1000 e 2000000 byte. Oracle GoldenGate invia un'avvertenza ogni volta che la memoria allocata per i parametri non supera i 500 byte del valore massimo.

L'esempio seguente aumenta il buffer a 15.000 byte.

PARAMBUFSIZE 15000
TRACE {ALL ERROR}

Utilizzare TRACE per registrare i parametri di input e output SQLEXEC nel file di report.

Di seguito è riportato un file di report di esempio con il trace SQLEXEC abilitato.

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

Scrive i parametri di input e output per ogni richiamo della procedura o della query nel file di report. Si tratta dello stato predefinito.

ERROR

Scrive i parametri di input e output per ogni richiamo della procedura o della query nel file di report solo dopo che si è verificato un errore SQL.