Home » SQL & PL/SQL » SQL & PL/SQL » dbms_sql.parse or execute immediate gives buffer too small error (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
dbms_sql.parse or execute immediate gives buffer too small error [message #407085] Mon, 08 June 2009 06:59 Go to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member

Hi,
I try to create a package using dynamic sql i create a string statement and try to execute it but it gives me an error message as
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The package which i try to create has 48 insert statements and each has more than 5000 characters is there any other way to do it rather than execute immediate or dbms_sql.parse ?
  • Attachment: wbh.txt
    (Size: 16.22KB, Downloaded 137 times)
Re: dbms_sql.parse or execute immediate gives buffer too small error [message #407093 is a reply to message #407085] Mon, 08 June 2009 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know how you actually want to create it as you don't share with us what you do but dbms_sql accepts an array of varchar2, so you can create anything of (about) any size.

Regards
Michel
Re: dbms_sql.parse or execute immediate gives buffer too small error [message #407096 is a reply to message #407093] Mon, 08 June 2009 07:36 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
I didn't post my query since its too long. Here it is.
Note: Formatted using code formatter but it seems it didnt work because of string statement.

CREATE OR REPLACE PROCEDURE TEMP_VESI.CREATE_WBH_CELLSTS_PROC3
AS
XFIELDS VARCHAR2(32000);
XSTATEMENT VARCHAR2(32000);
XAGGREGATE_FIELDS VARCHAR2(32000);
XDATE_RESULT VARCHAR2(4000);
XDATA_DATE_RESULT VARCHAR2(4000);
XNE_RESULT VARCHAR2(4000);
c NUMBER;
BEGIN
LOADER_CREATION.GET_LOCAL_TABLE_FIELDS('CELLSTS',XFIELDS);
LOADER_CREATION.GET_AGGREGATION_FIELDS('CELLSTS','_DA_CELL',XAGGREGATE_FIELDS,XDATE_RESULT,XDATA_DATE_RESULT,XNE_RESULT);
c := dbms_sql.open_cursor();
DBMS_SQL.PARSE(c,
'
CREATE OR REPLACE PROCEDURE CELLSTS_7WBH_CELL(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',1 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',2 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',3 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',4 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_7WBH_BTS(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',5 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',6 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',7 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',8 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_7WBH_BSC(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',9 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',10 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',11 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',12 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_7WBH_CLS(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',13 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',14 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',15 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',16 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_7WBH_RGN(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',17 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',18 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',19 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',20 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_7WBH_NW(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',21 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',22 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',23 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',24 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_7 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_5WBH_CELL(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',25 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',26 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',27 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',28 TYPE FROM TEMP_NORTHI.CELLSTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_5WBH_BTS(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',29 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',30 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',31 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',32 TYPE FROM TEMP_NORTHI.CELLSTS_H_BTS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_5WBH_BSC(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',33 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',34 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',35 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',36 TYPE FROM TEMP_NORTHI.CELLSTS_H_BSC E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_5WBH_CLS(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',37 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',38 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',39 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',40 TYPE FROM TEMP_NORTHI.CELLSTS_H_CLS E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24;
COMMIT; END; PROCEDURE CELLSTS_5WBH_RGN(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',41 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',42 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',43 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',44 TYPE FROM TEMP_NORTHI.CELLSTS_H_RGN E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24;
COMMIT; END;
PROCEDURE CELLSTS_5WBH_NW(XDATA_DATE IN DATE) AS
BEGIN
INSERT /*+ APPEND */ INTO TEMP_NORTHI.WBH_CELLSTS('||XFIELDS||',BH_TYPE)
SELECT '||XAGGREGATE_FIELDS||',45 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',46 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_TRAFF_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+7)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',47 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.TCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24
UNION ALL
SELECT '||XAGGREGATE_FIELDS||',48 TYPE FROM TEMP_NORTHI.CELLSTS_H_NW E, TEMP_NORTHI.WEEK_BH_5 B
WHERE E.NETWORK_ID=B.NETWORK_ID AND TO_CHAR(E.FRAGMENT_DATE,''HH24'')=B.CCH_ATT_BH
AND B.FRAGMENT_DATE BETWEEN XDATA_DATE AND (XDATA_DATE+5)+23/24;
COMMIT; END;
END CALCULATE_CELLSTS_WBH;
',DBMS_SQL.NATIVE);
dbms_sql.close_cursor(c);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(c);
RAISE;
END;
/
Re: dbms_sql.parse or execute immediate gives buffer too small error [message #407098 is a reply to message #407096] Mon, 08 June 2009 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't mean you post it inline, you were able to post the procedure itself, you could post the above one instead (in the file).

This does not change my answer, use array version of dbms_sql.

Regards
Michel
Re: dbms_sql.parse or execute immediate gives buffer too small error [message #407107 is a reply to message #407098] Mon, 08 June 2009 08:28 Go to previous message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
I already posted the procedure in wbh.txt ? But you asked me to do it. And i did it in another way (inline). I think you didnt see it. Thanks for you I will try the array version.
Previous Topic: find difference of two numbers in oracle sql
Next Topic: Invalid state when running first time after compile, error with schema name, permissns proc vs user
Goto Forum:
  


Current Time: Sun Dec 11 07:53:03 CST 2016

Total time taken to generate the page: 0.12685 seconds