| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: dbms_sql URGENT
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_000_01BFFB36.91C9F200
Content-Type: text/plain;
charset="iso-8859-1"
-----Original Message-----
From: Jamadagni, Rajendra [mailto:rajendra.jamadagni_at_espn.com]
Sent: Monday, July 31, 2000 11:32 AM
To: 'ORACLE-L_at_fatcity.com'; 'jalves_at_mdic.gov.br'
Subject: RE: dbms_sql URGENT
Try posting your code .... that might help solve problem ...
-----Original Message-----
From: Jorge Luiz Alves [mailto:jalves_at_mdic.gov.br]
Sent: Friday, July 28, 2000 4:19 PM
To: Multiple recipients of list ORACLE-L
Subject: dbms_sql URGENT
Hi all,
I wrote a storage procedure that create a update statement, parse and execute using dbms_sql. My problem consist that, if i take a genated sql statment and execute in the sqlplus it runs quikly and wait for a commit command, but i the storage procedure it takes a long time like two hours and do nothing. What probably happen. If someone could help me...
Thanks in advance.
Jorge Alves.
-- Author: Jorge Luiz Alves INET: Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Mon Jul 31 2000 - 16:30:35 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). ------_=_NextPart_000_01BFFB36.91C9F200 Content-Type: application/octet-stream; name="carga_LISTA.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="carga_LISTA.sql" PROCEDURE PR_CHARGE_EXP as cursor find_aggs_c is select table_name, column_name from all_tab_columns where owner =3D 'TESTE' and table_name like 'AGEXP%' and column_name like 'ID%' and table_name not like '%CAP%' and table_name not like '%BLOCK%' order by table_name, column_id; wmonth number(2) :=3D 1; wtable_i varchar2(30); wtable varchar2(30); wcolumn varchar2(30); wsql varchar2(10000); wsql_col_src varchar2(4000); wsql_col_trg varchar2(4000); wsql_trg varchar2(4000); wsql_from varchar2(4000); wsql_where varchar2(4000); wsql_where_comp varchar2(1000); wsql_group varchar2(4000); wsql_trg varchar2(4000); wsai char(3) :=3D 'NAO'; bSTATE_CODEfer number(5) :=3D 0; BEGIN delete looking; commit; wsai :=3D 'NAO'; OPEN find_aggs_c; FETCH find_aggs_c INTO wtable_i, wcolumn; LOOP /* -------------------------------------------------------- */ /* At this point, the procedure make the update statement */ /* -------------------------------------------------------- */ wtable :=3D wtable_i; wsql :=3D 'Update TESTE.'||wtable||' a set ('; wsql_from :=3D ' from CROSS_REFERENCE T1, '; wsql_where :=3D ' where ('; wsql_group :=3D ' group by '; wsql_col_trg :=3D null; wsql_col_src :=3D null; wsql_where_comp :=3D null; WHILE wtable =3D wtable_i LOOP IF wcolumn =3D 'ID_PRODUCT' THEN wsql_from :=3D wsql_from || 'DWH_ALICE.PRODUCT T2, '; IF wsql_col_trg IS NULL THEN wsql_col_trg :=3D 'ID_PRODUCT, DC_PRODUCT'; ELSE wsql_col_trg :=3D wsql_col_trg || ', ID_PRODUCT, = DC_PRODUCT'; END IF; IF wsql_col_src IS NULL THEN wsql_col_src :=3D 'T1.ID_PRODUCT, = NVL(T2.DC_PRODUCT,'||'''*** Update Lookup Tables ***'''||') PRODUCT'; ELSE wsql_col_src :=3D wsql_col_src || ', T1.ID_PRODUCT, = NVL(T2.DC_PRODUCT,'||'''*** Update Lookup Tables ***'''||') PRODUCT'; END IF; IF wsql_where IS NULL THEN wsql_where :=3D '(T1.ID_PRODUCT =3D = T2.ID_PRODUCT(+))'; ELSE wsql_where :=3D wsql_where || ' AND (T1.ID_PRODUCT = =3D T2.ID_PRODUCT(+))'; END IF; IF wsql_where_comp IS NULL THEN wsql_where_comp :=3D 'T1.ID_PRODUCT =3D = a.ID_PRODUCT'; ELSE wsql_where_comp :=3D wsql_where_comp || ' AND = T1.ID_PRODUCT =3D a.ID_PRODUCT'; END IF; IF wsql_group IS NULL THEN wsql_group :=3D 'T1.ID_PRODUCT, = NVL(T2.DC_PRODUCT,'||'''*** Update Lookup Tables ***'''||')'; ELSE wsql_group :=3D wsql_group || ', T1.ID_PRODUCT, = NVL(T2.DC_PRODUCT,'||'''*** Update Lookup Tables ***'''||')'; END IF; ELSIF wcolumn =3D 'ID_ENTERPRISE' THEN wsql_from :=3D wsql_from || = 'DWH_ALICE.IMPORT_EXPORT_BR T3, '; IF wsql_col_trg IS NULL THEN wsql_col_trg :=3D 'ID_ENTERPRISE, NM_ENTERPRISE'; ELSE wsql_col_trg :=3D wsql_col_trg || ', ID_ENTERPRISE, = NM_ENTERPRISE'; END IF; IF wsql_col_src IS NULL THEN wsql_col_src :=3D 'T1.ID_ENTERPRISE, = NVL(T3.NM_ENTERPRISE,'||'''*** Update Lookup Tables ***'''||') IE'; ELSE wsql_col_src :=3D wsql_col_src || ', = T1.ID_ENTERPRISE, NVL(T3.NM_ENTERPRISE,'||'''*** Update Lookup Tables = ***'''||') IE'; END IF; IF wsql_where IS NULL THEN wsql_where :=3D '(T1.ID_IMP_EXP =3D = T3.ID_ENTERPRISE(+))'; ELSE wsql_where :=3D wsql_where || ' AND =
(T1.ID_IMP_EXP =3D T3.ID_ENTERPRISE(+))';
END IF; IF wsql_where_comp IS NULL THEN wsql_where_comp :=3D 'T1.ID_ENTERPRISE =3D = a.ID_IMP_EXP'; ELSE wsql_where_comp :=3D wsql_where_comp || ' AND = T1.ID_ENTERPRISE =3D a.ID_IMP_EXP'; END IF; IF wsql_group IS NULL THEN wsql_group :=3D 'T1.ID_IMP_EXP, = NVL(T3.NM_ENTERPRISE,'||'''*** Update Lookup Tables ***'''||')'; ELSE wsql_group :=3D wsql_group || ', T1.ID_IMP_EXP, = NVL(T3.NM_ENTERPRISE,'||'''*** Update Lookup Tables ***'''||')'; END IF; ELSIF wcolumn =3D 'ID_COUNTRY' THEN wsql_from :=3D wsql_from || = 'DWH_ALICE.COUNTRY T4, '; IF wsql_col_trg IS NULL THEN wsql_col_trg :=3D 'ID_COUNTRY, DC_COUNTRY'; ELSE wsql_col_trg :=3D wsql_col_trg || ', ID_COUNTRY, = DC_COUNTRY'; END IF; IF wsql_col_src IS NULL THEN wsql_col_src :=3D 'T1.ID_COUNTRY, = NVL(T4.DC_COUNTRY,'||'''*** Update Lookup Tables ***'''||') COUNTRY'; ELSE wsql_col_src :=3D wsql_col_src || ', T1.ID_COUNTRY, = NVL(T4.DC_COUNTRY,'||'''*** Update Lookup Tables ***'''||') COUNTRY'; END IF; IF wsql_where IS NULL THEN wsql_where :=3D '(T1.ID_COUNTRY =3D = T4.ID_COUNTRY(+))'; ELSE wsql_where :=3D wsql_where || ' AND (T1.ID_COUNTRY = =3D T4.ID_COUNTRY(+)) AND '; END IF; IF wsql_where_comp IS NULL THEN wsql_where_comp :=3D 'T1.ID_COUNTRY =3D = a.ID_COUNTRY'; ELSE wsql_where_comp :=3D wsql_where_comp || ' AND = T1.ID_COUNTRY =3D a.ID_COUNTRY'; END IF; IF wsql_group IS NULL THEN wsql_group :=3D 'T1.ID_COUNTRY, = NVL(T4.DC_COUNTRY,'||'''*** Update Lookup Tables ***'''||')'; ELSE wsql_group :=3D wsql_group || ', T1.ID_COUNTRY, = NVL(T4.DC_COUNTRY,'||'''*** Update Lookup Tables ***'''||')'; END IF; ELSIF wcolumn =3D 'ID_STATE_CODE' THEN wsql_from :=3D wsql_from || = 'DWH_ALICE.STATE_CODE T5, '; IF wsql_col_trg IS NULL THEN wsql_col_trg :=3D 'ID_STATE_CODE, DC_STATE_CODE'; ELSE wsql_col_trg :=3D wsql_col_trg || ', ID_STATE_CODE, = DC_STATE_CODE'; END IF; IF wsql_col_src IS NULL THEN wsql_col_src :=3D 'T1.ID_STATE_CODE, = NVL(T5.DC_STATE_CODE,'||'''*** Update Lookup Tables ***'''||') = STATE_CODE'; ELSE wsql_col_src :=3D wsql_col_src || ', = T1.ID_STATE_CODE, NVL(T5.DC_STATE_CODE,'||'''*** Update Lookup Tables = ***'''||') STATE_CODE'; END IF; IF wsql_where IS NULL THEN wsql_where :=3D '(T1.ID_STATE_CODE =3D = T5.ID_NUM_STATE_CODE(+))'; ELSE wsql_where :=3D wsql_where || ' AND =
(T1.ID_STATE_CODE =3D T5.ID_NUM_STATE_CODE(+))';
END IF; IF wsql_where_comp IS NULL THEN wsql_where_comp :=3D 'T1.ID_STATE_CODE =3D = a.ID_STATE_CODE'; ELSE wsql_where_comp :=3D wsql_where_comp || ' AND = T1.ID_STATE_CODE =3D a.ID_STATE_CODE'; END IF; IF wsql_group IS NULL THEN wsql_group :=3D 'T1.ID_STATE_CODE, = NVL(T5.DC_STATE_CODE,'||'''*** Update Lookup Tables ***'''||')'; ELSE wsql_group :=3D wsql_group || ', T1.ID_STATE_CODE, = NVL(T5.DC_STATE_CODE,'||'''*** Update Lookup Tables ***'''||')'; END IF; ELSIF wcolumn =3D 'ID_PORTO' THEN wsql_from :=3D wsql_from || = 'DWH_ALICE.PORTO T6, '; IF wsql_col_trg IS NULL THEN wsql_col_trg :=3D 'ID_PORTO, DC_PORTO'; ELSE wsql_col_trg :=3D wsql_col_trg || ', ID_PORTO, = DC_PORTO'; END IF; IF wsql_col_src IS NULL THEN wsql_col_src :=3D 'T1.ID_PORTO, = NVL(T6.DC_PORTO,'||'''*** Update Lookup Tables ***'''||') PORTO'; ELSE wsql_col_src :=3D wsql_col_src || ', T1.ID_PORTO, = NVL(T6.DC_PORTO,'||'''*** Update Lookup Tables ***'''||') PORTO'; END IF; IF wsql_where IS NULL THEN wsql_where :=3D '(T1.ID_PORTO =3D T6.ID_PORTO(+))'; ELSE wsql_where :=3D wsql_where || ' AND (T1.ID_PORTO = =3D T6.ID_PORTO(+))'; END IF; IF wsql_where_comp IS NULL THEN wsql_where_comp :=3D 'T1.ID_PORTO =3D a.ID_PORTO'; ELSE wsql_where_comp :=3D wsql_where_comp || ' AND = T1.ID_PORTO =3D a.ID_PORTO'; END IF; IF wsql_group IS NULL THEN wsql_group :=3D 'T1.ID_PORTO, = NVL(T6.DC_PORTO,'||'''*** Update Lookup Tables ***'''||')'; ELSE wsql_group :=3D wsql_group || ', T1.ID_PORTO, = NVL(T6.DC_PORTO,'||'''*** Update Lookup Tables ***'''||')'; END IF; ELSIF wcolumn =3D 'ID_WAY' THEN wsql_from :=3D wsql_from || = 'DWH_ALICE.WAY_TRANSPORTE T7, '; IF wsql_col_trg IS NULL THEN wsql_col_trg :=3D 'ID_WAY, DC_WAY'; ELSE wsql_col_trg :=3D wsql_col_trg || 'ID_WAY, = DC_WAY, '; END IF; IF wsql_col_src IS NULL THEN wsql_col_src :=3D 'T1.ID_WAY, = nvl(T7.DC_WAY_TRANSPORTE,'||'''*** Update Lookup Tables ***'''||') = WAY'; ELSE wsql_col_src :=3D wsql_col_src || ', T1.ID_WAY, = nvl(T7.DC_WAY_TRANSPORTE,'||'''*** Update Lookup Tables ***'''||') = WAY'; END IF; IF wsql_where IS NULL THEN wsql_where :=3D '(T1.ID_WAY =3D = T7.ID_WAY_TRANSPORTE(+))'; ELSE wsql_where :=3D wsql_where || ' AND (T1.ID_WAY =3D = T7.ID_WAY_TRANSPORTE(+))'; END IF; IF wsql_where_comp IS NULL THEN wsql_where_comp :=3D 'T1.ID_WAY =3D a.ID_WAY'; ELSE wsql_where_comp :=3D wsql_where_comp || ' AND = T1.ID_WAY =3D a.ID_WAY'; END IF; IF wsql_group IS NULL THEN wsql_group :=3D 'T1.ID_WAY, = NVL(T7.DC_WAY_TRANSPORTE,'||'''*** Update Lookup Tables ***'''||')'; ELSE wsql_group :=3D wsql_group || ', T1.ID_WAY, = NVL(T7.DC_WAY_TRANSPORTE,'||'''*** Update Lookup Tables ***'''||')'; END IF; END IF; FETCH find_aggs_c INTO wtable, wcolumn; IF find_aggs_c%NOTFOUND THEN wsai :=3D 'SIM'; EXIT; END IF; END LOOP; wsql :=3D wsql || wsql_col_trg; wsql :=3D wsql || 'ANOT_JAN_QTDE, ANOT_JAN_PESO, ANOT_JAN_VALOR, = '; wsql :=3D wsql || 'ANOT_FEV_QTDE, ANOT_FEV_PESO, ANOT_FEV_VALOR, = '; wsql :=3D wsql || 'ANOT_MAR_QTDE, ANOT_MAI_PESO, ANOT_MAR_VALOR, = '; wsql :=3D wsql || 'ANOT_ABR_QTDE, ANOT_ABR_PESO, ANOT_ABR_VALOR, = '; wsql :=3D wsql || 'ANOT_MAI_QTDE, ANOT_MAR_PESO, ANOT_MAI_VALOR, = '; wsql :=3D wsql || 'ANOT_JUN_QTDE, ANOT_JUN_PESO, ANOT_JUN_VALOR, = '; wsql :=3D wsql || 'ANOT_JUL_QTDE, ANOT_JUL_PESO, ANOT_JUL_VALOR, = '; wsql :=3D wsql || 'ANOT_AGO_QTDE, ANOT_AGO_PESO, ANOT_AGO_VALOR, = '; wsql :=3D wsql || 'ANOT_SET_QTDE, ANOT_SET_PESO, ANOT_SET_VALOR, = '; wsql :=3D wsql || 'ANOT_OUT_QTDE, ANOT_OUT_PESO, ANOT_OUT_VALOR, = '; wsql :=3D wsql || 'ANOT_NOV_QTDE, ANOT_NOV_PESO, ANOT_NOV_VALOR, = '; wsql :=3D wsql || 'ANOT_DEZ_QTDE, ANOT_DEZ_PESO, ANOT_DEZ_VALOR, = '; wsql :=3D wsql || 'ANOT_TOTAL_QTDE, ANOT_TOTAL_PESO, = ANOT_TOTAL_VALOR) =3D (Select '||wsql_col_src; wsql :=3D wsql || 'SUM(NVL(JAN_QTDE,0)), SUM(NVL(JAN_PESO,0)), = SUM(NVL(JAN_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(FEV_QTDE,0)), SUM(NVL(FEV_PESO,0)), = SUM(NVL(FEV_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(MAR_QTDE,0)), SUM(NVL(MAI_PESO,0)), = SUM(NVL(MAR_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(ABR_QTDE,0)), SUM(NVL(ABR_PESO,0)), = SUM(NVL(ABR_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(MAI_QTDE,0)), SUM(NVL(MAR_PESO,0)), = SUM(NVL(MAI_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(JUN_QTDE,0)), SUM(NVL(JUN_PESO,0)), = SUM(NVL(JUN_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(JUL_QTDE,0)), SUM(NVL(JUL_PESO,0)), = SUM(NVL(JUL_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(AGO_QTDE,0)), SUM(NVL(AGO_PESO,0)), = SUM(NVL(AGO_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(SET_QTDE,0)), SUM(NVL(SET_PESO,0)), = SUM(NVL(SET_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(OUT_QTDE,0)), SUM(NVL(OUT_PESO,0)), = SUM(NVL(OUT_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(NOV_QTDE,0)), SUM(NVL(NOV_PESO,0)), = SUM(NVL(NOV_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(DEZ_QTDE,0)), SUM(NVL(DEZ_PESO,0)), = SUM(NVL(DEZ_FOB,0)), '; wsql :=3D wsql || 'SUM(NVL(TOTAL_QTDE,0)), = SUM(NVL(TOTAL_PESO,0)), SUM(NVL(ANOT_TOTAL_VALOR,0))'; wsql :=3D wsql || RTRIM(wsql_from,', '); wsql :=3D wsql || RTRIM(wsql_where,' AND ') || ') AND '; wsql :=3D wsql || RTRIM(wsql_where_comp,' AND ');
 |
 |