Home » RDBMS Server » Server Administration » sql suspension (Oracle 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production, AIX 5.2)
sql suspension [message #423806] |
Mon, 28 September 2009 07:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ducasio
Messages: 38 Registered: November 2003
|
Member |
|
|
Hi everybody,
i am telling that i am not a newbye in oracle sql but now i have given up fronting this issue. The problem i am having is the suspension of a query execution. i explain better. I have a script like this:
--------------
connect my_user/my_password
whenever sqlerror exit 1
create table tab_1 as select ... from ... where ...
;
create table tab_2 as select ... from ... where ...
;
--------------------------
this script i called from sqlplus in unix aix like this:
-----------------
sqlplus -s /nolog <<eof
@my_script
eof
-----------------
the problem i am having is that after execution of the first query and the creation of tab_1 it doesn't start the creation of tab_2. And the most strange is that i don't have the feedback "Table created" after the creation of tab_1. This script runs dayly and sometimes runs without problems and sometimes gives the rpoblem i just described.
So i would really appreciate any idea from all of you.
Thank you in advance,
ducasio
|
|
|
|
Re: sql suspension [message #423813 is a reply to message #423806] |
Mon, 28 September 2009 07:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ducasio
Messages: 38 Registered: November 2003
|
Member |
|
|
Hi Kevin,
i have used the WHENEVER statement because i wanted to manage the errors in unix ksh and to interrupt the execution of the rest of the sql script. Actually the whenever statement is not trigered, otherwise the script would end. It never ends and the session remains active. The first table is created but no feedback is given. The second part of the script doesn't start. And as i told, it does NOT happen always.
I am getting mad from this.
|
|
|
|
|
Re: sql suspension [message #423822 is a reply to message #423813] |
Mon, 28 September 2009 08:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ducasio
Messages: 38 Registered: November 2003
|
Member |
|
|
i am not using authetification as sysdba
ok the sql script is this one:
-----------
WHENEVER SQLERROR EXIT 1
CONNECT pippo_user/pippo_pwd@db_sid
WHENEVER SQLERROR CONTINUE
DROP TABLE TAB_1;
DROP TABLE TAB_2;
CREATE INDEX TABLE_PIL_IDX ON TABLE_PIL (ID_TAB)
;
WHENEVER SQLERROR EXIT 1
CREATE TABLE TAB_1 AS
SELECT /* ORDERED FULL(A) FULL(B) FULL(C) FULL(CO) FULL(CLI) FULL(GRSGR) */
DISTINCT C.X_OM_INVARIANT_CODE AS UTEN_COD,
CLI.X_CC_STATO_MIGRAZIONE AS STATO_MIGRAZIONE,
DECODE(C.X_OM_IMSI,NULL,NULL,'22201'||C.X_OM_HLR_SIM||C.X_OM_IMSI) AS IMSI,
CO.AGREE_NUM AS CODICE_CONTRATTO,
A.AVAIL_DT AS DATA_INI_OI,
A.REQ_SHIP_DT AS DATA_END_OI,
C.START_DT AS DATA_INI_ASS,
C.END_DT AS DATA_END_ASS,
TO_NUMBER(C.VERSION) AS VERSIONE,
B.REF_NUMBER_4 AS BUNDLED,
C.SERIAL_NUM AS PREFISSO_NUMERO,
C.X_OM_F_TIFB_NUMERO AS NUMERO,
C.INSTALL_DT,
C.X_OM_HLR AS HLR,
C.X_OM_ICCID AS ICCID,
CO.X_OM_PRICE_LST_ID,
C.X_OM_NMU AS NMU,
C.X_OM_RC_DISCOUNT AS SCT_UTE,
C.X_OM_NRC_DISCOUNT AS SCT_PROF,
A.STATUS_CD AS STATO_OI,
C.STATUS_CD AS STATO_ASS,
A.LAST_UPD AS DTA_MOD_OI,
C.LAST_UPD AS DTA_MOD_ASS,
B.PART_NUM,
B.NAME,
B.TYPE,
C.X_OM_ASSET_TYPE,
C.TYPE_CD,
GRSGR.X_OM_GROUP_ID AS GRUPPO,
GRSGR.ITEM_GROUP_NAME,
DECODE(C.X_OM_DESTINATION,'AFFARI','A','RESIDENZIALE','F') AS USO,
CO.X_OM_BILLDETAILS,
C.X_OM_CAUSALE AS COD_CAUSALE,
C.X_OM_LIVELLO_FRODE AS LIVELLO_FRODE,
C.X_OM_TIPO_SOSP AS TIPO_SOSPENSIONE
FROM SIEBEL.S_ORDER_ITEM A,
SIEBEL.S_PROD_INT B,
SIEBEL.S_ASSET C,
SIEBEL.S_DOC_AGREE CO,
SIEBEL.S_ORG_EXT CLI,
(SELECT GR.* FROM SIEBEL.S_AGREE_ITEM GR WHERE X_OM_AGR_ITEM_TYPE = 'GRUPPI E SOTTOGRUPPI') GRSGR,
TABLE_PIL PILOTA
WHERE C.PROD_ID = B.ROW_ID
AND C.CUR_AGREE_ID = CO.ROW_ID
AND B.TYPE IN ('ABBONAMENTO','XBASE','XPLUS','PABX','CARRIER SELECTION')
AND B.PROD_CD = 'PROFILO TARIFFARIO' --GSM + EXTENTION
AND CO.TARGET_OU_ID = CLI.ROW_ID
AND CLI.X_CC_STATO_MIGRAZIONE IN ('MIGRATO MOBILE','INSERITO')
AND CO.ROW_ID = GRSGR.DOC_AGREE_ID (+)
AND C.STATUS_CD IN ('ATTIVO','SOSPESO','DA ATTIVARE','CESSATO')
AND A.ASSET_INTEG_ID(+) = C.INTEGRATION_ID
AND A.X_OM_VERSIONE(+) = NVL(C.VERSION,0)
AND C.SERIAL_NUM = PILOTA.CHIAVE
;
CREATE TABLE TAB_2 AS
SELECT /* ORDERED FULL(A) FULL(C) FULL(P) FULL(PX) FULL(CO) FULL(CLI) */
CLI.X_CC_STATO_MIGRAZIONE AS STATO_MIGRAZIONE,
CO.AGREE_NUM AS CODICE_CONTRATTO,
CASE
WHEN P.SUB_TYPE_CD = 'ADDITIONAL NUMBER'
THEN (SELECT SERIAL_NUM FROM S_ASSET PA WHERE ROW_ID = C.ROOT_ASSET_ID)
WHEN P.SUB_TYPE_CD = '2IN1'
THEN (SELECT SERIAL_NUM FROM S_ASSET PA WHERE ROW_ID = C.ROOT_ASSET_ID)
WHEN P.PART_NUM = 'STS_08_00000179'
THEN (SELECT SERIAL_NUM FROM S_ASSET PA WHERE ROW_ID = C.ROOT_ASSET_ID)
ELSE C.SERIAL_NUM
END AS NUMERO,
TO_CHAR(A.AVAIL_DT, 'YYYY-MM-DD') AS DATA_INI_OI,
TO_CHAR(A.REQ_SHIP_DT, 'YYYY-MM-DD') AS DATA_END_OI,
TO_CHAR(C.START_DT, 'YYYY-MM-DD') AS DATA_INI_ASS,
TO_CHAR(C.END_DT, 'YYYY-MM-DD') AS DATA_END_ASS,
PX.ATTRIB_44 AS TIPO_SERV,
PX.ATTRIB_01 AS SOTTOTIPO_SERV,
PX.ATTRIB_42 AS NATURA_SERV,
C.X_OM_RC_DISCOUNT AS SCT_UTE,
C.X_OM_NRC_DISCOUNT AS SCT_PROF,
A.STATUS_CD AS STATO_OI,
C.STATUS_CD AS STATO_ASS,
TO_CHAR(A.LAST_UPD, 'YYYY-MM-DD') AS DTA_MOD_OI,
TO_CHAR(C.LAST_UPD, 'YYYY-MM-DD') AS DTA_MOD_ASS,
P.PART_NUM,
P.SUB_TYPE_CD AS SOTTOTIPOLOGIA,
P.NAME,
CASE
WHEN p.SUB_TYPE_CD = '2IN1'
THEN C.SERIAL_NUM
ELSE ''
END AS NUMERO_2IN1,
CASE
WHEN p.SUB_TYPE_CD = 'ADDITIONAL NUMBER'
THEN C.SERIAL_NUM
ELSE ''
END AS NUMERO_AGGIUNTIVO,
DECODE(C.X_OM_IMSI,NULL,NULL,'22201'||C.X_OM_HLR_SIM||C.X_OM_IMSI) AS IMSI,
C.X_OM_ICCID AS ICCID,
CASE
WHEN p.PART_NUM = 'STS_08_00000179'
THEN C.SERIAL_NUM
ELSE ''
END AS NUMERO_OLO,
(SELECT PROD.NAME
FROM S_ASSET PA,
S_PROD_INT PROD
WHERE PA.ROW_ID = C.PAR_ASSET_ID
AND PA.PROD_ID = PROD.ROW_ID
AND PROD.TYPE = 'ONE PACK') AS LABEL_PACK
FROM SIEBEL.S_ORDER_ITEM A,
SIEBEL.S_ASSET C,
SIEBEL.S_PROD_INT P,
SIEBEL.S_PROD_INT_X PX,
SIEBEL.S_DOC_AGREE CO,
SIEBEL.S_ORG_EXT CLI,
TABLE_PIL PILOTA
WHERE C.PROD_ID = P.ROW_ID
AND P.TYPE = 'SERVIZIO SUPPLEMENTARE'
AND P.ROW_ID = PX.ROW_ID
AND C.CUR_AGREE_ID = CO.ROW_ID
AND CO.TARGET_OU_ID = CLI.ROW_ID
AND CLI.X_CC_STATO_MIGRAZIONE IN ('MIGRATO MOBILE','INSERITO')
AND C.INTEGRATION_ID = A.ASSET_INTEG_ID(+)
AND NVL(C.VERSION, 0) < A.X_OM_VERSIONE(+)
AND C.SERIAL_NUM = PILOTA.CHIAVE
and nvl(C.END_DT,to_date('01/01/2070','DD/MM/YYYY'))>=to_date('01/04/2009','DD/MM/YYYY')
;
--------------------
the shell with whom i run the sql script is:
---------------
export FILE_SQL='pippo.sql'
echo "[$SHELLNAME] Start Execution $FILE_SQL il `date '+%d/%m/%Y %T'`"
sqlplus /nolog <<eof
@$CTL_PATH/SQL/$FILE_SQL
eof
if test $? -ne 0
then
echo "[ERR $SHELLNAME] Execution $FILE_SQL"
exit
fi
echo "[$SHELLNAME] End Execution $FILE_SQL il `date '+%d/%m/%Y %T'`"
----------------------------
the log produced is:
-------------------------
[CRMA_NBS.sh] Start Execution pippo.sql il 28/09/2009 07:02:12
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Sep 28 07:02:12 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
DROP TABLE TAB_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP TABLE TAB_2
*
ERROR at line 1:
ORA-00942: table or view does not exist
Index created.
-------------------------------
As you see the feedback is enabled. I want to make clear that not always the script remains suspended.
|
|
|
|
Re: sql suspension [message #423825 is a reply to message #423824] |
Mon, 28 September 2009 08:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
ducasio
Messages: 38 Registered: November 2003
|
Member |
|
|
thank you for the suggestion, i am gonna provide for trying it in an anomynous pl/sql block. Anyway, just for information, the sqlplus session does not fall off. I can see it with ps -ef|grep sqlplus. What i find weird is that i can't trap the error.
I am trying now to use dinamic pl/sql in an anonym block.
thank you
|
|
|
Goto Forum:
Current Time: Mon Jul 08 21:19:28 CDT 2024
|