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 Go to next message
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 #297902 is a reply to message #297900] Mon, 04 February 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For a lone string: VARCHAR2 up to 32767, otherwise CLOB.
Yes, you can use collections (varray is not the best one, use array/table) of VARCHAR2(32767) and then loop on the element of the table.

Regards
Michel
Re: what datatype is used to handle a very large string [message #297905 is a reply to message #297900] Mon, 04 February 2008 03:48 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Michel,

Can you please demonstrate how to use the arrays to achieve this?

Thanks,
Re: what datatype is used to handle a very large string [message #297914 is a reply to message #297905] Mon, 04 February 2008 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace type mytyp is table of varchar2(32767)
  2  /

Type created.

SQL> create or replace procedure p (p mytyp) is
  2  begin
  3    for i in 1..p.count loop
  4      dbms_output.put_line(p(i));
  5    end loop;
  6  end;
  7  /

Procedure created.

SQL> exec p(mytyp('string1','string2','string3'));
string1
string2
string3

PL/SQL procedure successfully completed.

Regards
Michel
Re: what datatype is used to handle a very large string [message #297932 is a reply to message #297900] Mon, 04 February 2008 05:06 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Michel,

For a lone string: VARCHAR2 up to 32767, otherwise CLOB


But,Michel if you observe my query's length is 10102 .
10102 < VARCHAR2 up to 32767.

Then why my procedure throughing error like

SP2-0027: Input is too long (> 2499 characters) - line ignored
Re: what datatype is used to handle a very large string [message #297933 is a reply to message #297932] Mon, 04 February 2008 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't have your procedure and how you call it.

This is a SQL*Plus message.
In SQL*Plus line must be less than 2500 characters.
Call your procedure in several lines.

Regards
Michel
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 Go to previous messageGo to next message
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 #297955 is a reply to message #297951] Mon, 04 February 2008 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Problem: I am facing a problem in the Function SPLIT_STRING.

You don't expect I analyze your code to guess what is the error, don't you?
YOU have to explain what is the problem.
And you have to keep your code in 80 character lines.

Regards
Michel
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 Go to previous messageGo to next message
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 #297967 is a reply to message #297964] Mon, 04 February 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave you the way how to use the table, now you have to work and adapt your procedure/function.
You have no more ONE string but a TABLE of strings, so handle them one by one.

Regards
Michel
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 Go to previous message
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
Previous Topic: Acess a remote database without database link please help
Next Topic: Problem with Copying data from a Remote Database to Local Database
Goto Forum:
  


Current Time: Sun Dec 04 08:59:03 CST 2016

Total time taken to generate the page: 0.08757 seconds