Home » SQL & PL/SQL » SQL & PL/SQL » what datatype is used to handle a very large string
what datatype is used to handle a very large string [message #297900] |
Mon, 04 February 2008 03:39 |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
How can i Pass a very large string as an input to the Procedure;
e.g., ARP6705584,70;ARP6705586,70;ARP6705587,70;ARP6705591,70;ARP6705600,70;......................
I found the length of that input string
select length('ARP6705584,70;ARP6705586,70;ARP6705587,70......') from dual +
select length('ARP6705560,70;ARP6705561,70;ARP6705562,70;ARP6705563,70;ARP6705564,70;ARP6705568,70;ARP6705582,70......')
from dual +
select length('ARP6705524,70;ARP6705527,70;ARP6705528,70;ARP6705542,70......') from dual
and it's length =10102 and this length may vary depending upon the search criteria.
I can not use long data type because this string never fits in the long datatype range.
so,here what data type should I used to handle this scenario.
Can i use a Varray if yes Please let me know who to acieve this.
PS: Currently i am using long data type which can handle a upto some length/size.
Thanks,
[Updated on: Mon, 04 February 2008 03:41] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: what datatype is used to handle a very large string [message #297951 is a reply to message #297900] |
Mon, 04 February 2008 05:38 |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Thanks Again,
AS you said earlier, I create a type PLCY_NBR_LIST_TYPE which contains the long string.
Step 2: I am a passing that variable to HLDNG_SRCH_PROC Procedure.
Step 3: Which internally calls GET_PARTY_INFO_PROC
Step 4:GET_PARTY_INFO_PROC have a function which loads the data into table type.
Problem: I am facing a problem in the Function SPLIT_STRING.
I am unable to understand how to handle this.
CREATE or replace PACKAGE BODY HLDNG_SRCH_PKG111 IS
PROCEDURE HLDNG_SRCH_PROC(PLCY_NBR_LIST_IN IN PLCY_NBR_LIST_TYPE,
PARTY_INFO_OUT OUT SYS_REFCURSOR,
PROC_STATUS_OUT OUT SYS_REFCURSOR) IS
--- Table Types
PARTY_INFO_TAB HLDNG_SRCH_INFO_OBJ_TBL_TYPE := HLDNG_SRCH_INFO_OBJ_TBL_TYPE();
PROC_STATUS_TAB PACKAGE_STATUS_INF_TBL_TYPE := PACKAGE_STATUS_INF_TBL_TYPE();
-- Declare Local variables
V_PLCY_CNT NUMBER DEFAULT 0;
V_PROC_STATUS_CD VARCHAR2(1);
V_PROC_ERROR_DESC VARCHAR2(500);
-- Declare Exceptions
INTERNAL_EXCEPTION EXCEPTION;
BEGIN
-- Get party Info
GET_PARTY_INFO_PROC(SPLIT_STRING(PLCY_NBR_LIST_IN),
PARTY_INFO_TAB, V_PROC_STATUS_CD, V_PROC_ERROR_DESC);
IF (V_PROC_STATUS_CD = 'S') THEN
OPEN PARTY_INFO_OUT FOR
SELECT *
FROM TABLE(CAST(PARTY_INFO_TAB AS HLDNG_SRCH_INFO_OBJ_TBL_TYPE))
ORDER BY PLCY_NBR,
CO_CD,
PARTY_ROLE_CD;
PROC_STATUS_TAB.EXTEND;
PROC_STATUS_TAB(PROC_STATUS_TAB.LAST) := PACKAGE_STATUS_INF_OBJ_TYPE(V_PROC_STATUS_CD,
V_PROC_ERROR_DESC,
V_PLCY_CNT);
OPEN PROC_STATUS_OUT FOR
SELECT * FROM TABLE(CAST(PROC_STATUS_TAB AS PACKAGE_STATUS_INF_TBL_TYPE));
END IF;
PROCEDURE GET_PARTY_INFO_PROC(PLCY_NBR_TBL_IN IN HLDNG_SRCH_INFO_OBJ_TBL_TYPE,
PARTY_INFO_TAB IN OUT HLDNG_SRCH_INFO_OBJ_TBL_TYPE,
PROC_STATUS_CD OUT VARCHAR2,
PROC_ERROR_DESC OUT VARCHAR2) IS
-- Get Insured Information
CURSOR INSURED_PARTY_INFO_CUR IS
SELECT *
FROM (SELECT A.PLCY_NBR,
A.CO_CD,
'I' || A.PRIMRY_SECDRY_LIFE_IND PARTY_ROLE_CD,
B.LAST_NAME || ',' || B.FIRST_NAME || ',' || B.MIDDLE_NAME || ',' || B.SUFFIX_NAME FULL_NAME,
ROW_NUMBER() OVER(PARTITION BY A.PLCY_NBR, A.CO_CD, A.CLNT_REF_NBR ORDER BY A.CLNT_REF_NBR) SEQ
FROM CLEAR_DAILY_FACT_VW A,
CLNT_DIM B
WHERE A.CLNT_REF_NBR = B.CLNT_REF_NBR AND
(A.PLCY_NBR, A.CO_CD) IN
(SELECT PLCY_NBR,
CO_CD
FROM TABLE(CAST(PLCY_NBR_TBL_IN AS HLDNG_SRCH_INFO_OBJ_TBL_TYPE))))
WHERE SEQ = 1;
BEGIN
-- Populate Insured Information
FOR REC IN INSURED_PARTY_INFO_CUR
LOOP
PARTY_INFO_TAB.EXTEND;
PARTY_INFO_TAB(PARTY_INFO_TAB.LAST) := HLDNG_SRCH_INFO_OBJ_TYPE(REC.PLCY_NBR,
REC.CO_CD,
REC.PARTY_ROLE_CD,
REC.FULL_NAME);
END LOOP;
PROC_STATUS_CD := 'S';
EXCEPTION
WHEN OTHERS THEN
PROC_STATUS_CD := 'F';
PROC_ERROR_DESC := 'Error Occured in GET_PARTY_INFO_PROC. SQL Error Code = ' || SQLCODE ||
' Error Msg : ' || SUBSTR(SQLERRM, 1, 400);
END;
FUNCTION SPLIT_STRING(PLCY_NBR_LIST_IN PLCY_NBR_LIST_TYPE) RETURN HLDNG_SRCH_INFO_OBJ_TBL_TYPE IS
V_PLCY_TAB HLDNG_SRCH_INFO_OBJ_TBL_TYPE := HLDNG_SRCH_INFO_OBJ_TBL_TYPE();
V_PLCY_NBR_LIST LONG := PLCY_NBR_LIST_IN || ';';
V_IDX NUMBER;
V_PLCY_NBR_CO_CD VARCHAR2(25);
BEGIN
LOOP
V_IDX := INSTR(V_PLCY_NBR_LIST, ';');
EXIT WHEN NVL(V_IDX, 0) = 0;
V_PLCY_NBR_CO_CD := TRIM(SUBSTR(V_PLCY_NBR_LIST, 1, V_IDX - 1));
V_PLCY_TAB.EXTEND;
V_PLCY_TAB(V_PLCY_TAB.LAST) := HLDNG_SRCH_INFO_OBJ_TYPE(SUBSTR(V_PLCY_NBR_CO_CD,
1,
INSTR(V_PLCY_NBR_CO_CD, ',') - 1),
SUBSTR(V_PLCY_NBR_CO_CD,
INSTR(V_PLCY_NBR_CO_CD, ',') + 1),
NULL,
NULL);
V_PLCY_NBR_LIST := SUBSTR(V_PLCY_NBR_LIST, V_IDX + 1);
END LOOP;
RETURN V_PLCY_TAB;
END;
END HLDNG_SRCH_PKG111 ;
[Updated on: Mon, 04 February 2008 05:48] by Moderator Report message to a moderator
|
|
|
|
Re: what datatype is used to handle a very large string [message #297964 is a reply to message #297900] |
Mon, 04 February 2008 06:02 |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Sorry, Even After knowing all the rules still voilated.
AS you said earlier, I create a type PLCY_NBR_LIST_TYPE which is table of varchar2(32767).
Step 2: I am a passing this variable to Procedure P1(HLDNG_SRCH_PROC) Procedure.
Step 3: P1 internally calls a procedure P2(GET_PARTY_INFO_PROC).
for P2 Procedure this newly created type is an Inpput parameter.
Earlier I was using this type as Long.Now i want to use the newly created table type.
Step 4: From P2 (GET_PARTY_INFO_PROC)Procedure have a function f1 which accepts the long string and it will splits accordingly
p1,a1 and it type cast it to into pl/sql table.
And then I use this pl/sql table in my where clause.
My Problem: I am Unsure how to pass this Newly created type(which we are passing to P1 procedure) to f1 function .
|
|
|
|
Re: what datatype is used to handle a very large string [message #297989 is a reply to message #297900] |
Mon, 04 February 2008 07:48 |
ramanajv1968
Messages: 168 Registered: December 2005 Location: HYDERABAD
|
Senior Member |
|
|
Created a type and passing that to a Procedure p2(f1(newly_created_type));
FUNCTION SPLIT_STRING(PLCY_NBR_LIST_IN IN long) RETURN HLDNG_SRCH_INFO_OBJ_TBL_TYPE IS
V_PLCY_TAB HLDNG_SRCH_INFO_OBJ_TBL_TYPE := HLDNG_SRCH_INFO_OBJ_TBL_TYPE();
V_PLCY_NBR_LIST PLCY_NBR_LIST_TYPE := PLCY_NBR_LIST_IN || ';';
V_IDX NUMBER;
V_PLCY_NBR_CO_CD VARCHAR2(25);
BEGIN
LOOP
V_IDX := INSTR(V_PLCY_NBR_LIST, ';');
EXIT WHEN NVL(V_IDX, 0) = 0;
V_PLCY_NBR_CO_CD := TRIM(SUBSTR(V_PLCY_NBR_LIST, 1, V_IDX - 1));
V_PLCY_TAB.EXTEND;
V_PLCY_TAB(V_PLCY_TAB.LAST) := HLDNG_SRCH_INFO_OBJ_TYPE(SUBSTR(V_PLCY_NBR_CO_CD,
1,
INSTR(V_PLCY_NBR_CO_CD, ',') - 1),
SUBSTR(V_PLCY_NBR_CO_CD,
INSTR(V_PLCY_NBR_CO_CD, ',') + 1),
NULL,
NULL);
V_PLCY_NBR_LIST := SUBSTR(V_PLCY_NBR_LIST, V_IDX + 1);
END LOOP;
RETURN V_PLCY_TAB;
END;
Earlier I was able to do because it is Long data type.
Please help me what data type is to used instead of Long data type in the function
Thanks
|
|
|
Goto Forum:
Current Time: Sun Nov 03 06:43:27 CST 2024
|