This discussion is archived
11 Replies Latest reply: Jul 26, 2010 6:09 AM by BluShadow RSS

Oracle LONG String

722885 Newbie
Currently Being Moderated
I M Using a Long variable in which i build a string according to For Loop Conditions .
This works Fine.

When i open a cursor foe that String.it throws an error Identifier is too long.

Regards,
  • 1. Re: Oracle LONG String
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle does not recommend the use of the LONG datatype any more...

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3806
  • 2. Re: Oracle LONG String
    722885 Newbie
    Currently Being Moderated
    Can you Please Suggest me a better way where i can handle large strings and execute and return cursor

    Please Find Below the query..

    csql := ' SELECT m.inq_No, m.inq_desc Inq_Desc, dept.dept_desc Department, dept.dept_code Dept_Code, cat.cat_description CATEGORY, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''01'' THEN act.ACT_CODE ELSE ''0'' END) Inq_Activity, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''02'' THEN act.ACT_CODE ELSE ''0'' END) Bids_Eval_Activity, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code IN (''03'', ''04'') THEN act.ACT_CODE ELSE ''0'' END) PO_Activity, ';
    csql := csql || ' ROUND(SUM(CASE WHEN sub.mlstn_id = 1 THEN PTR_EARNED_WF ELSE 0 END) / DECODE(SUM(CASE WHEN sub.mlstn_id = 1 THEN PTR_ESTIMATED_WF ELSE 0 END), 0, 1, SUM(CASE WHEN sub.mlstn_id = 1 THEN PTR_ESTIMATED_WF ELSE 0 END))*100, 2) PreOrd_Progress, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''04'' THEN a.PTR_ACTUAL_DT ELSE NULL END) AS Po_Actual_Date, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''04'' THEN a.PTR_FORECAST_DT ELSE NULL END) AS Po_Forecastt_Date, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 2 THEN act.ACT_CODE ELSE ''0'' END) Mfg_Activity, ';
    csql := csql || ' ROUND(SUM(CASE WHEN sub.mlstn_id = 2 THEN PTR_EARNED_WF ELSE 0 END) / DECODE(SUM(CASE WHEN sub.mlstn_id = 2 THEN PTR_ESTIMATED_WF ELSE 0 END), 0, 1, SUM(CASE WHEN sub.mlstn_id = 2 THEN PTR_ESTIMATED_WF ELSE 0 END))*100, 2) Mfg_Progress, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 3 THEN act.ACT_CODE ELSE ''0'' END) Ship_Activity, ';
    csql := csql || ' ROUND(SUM(CASE WHEN sub.mlstn_id = 3 THEN PTR_EARNED_WF ELSE 0 END) / DECODE(SUM(CASE WHEN sub.mlstn_id = 3 THEN PTR_ESTIMATED_WF ELSE 0 END), 0, 1, SUM(CASE WHEN sub.mlstn_id = 3 THEN PTR_ESTIMATED_WF ELSE 0 END))*100, 2) Ship_Progress ';

    for j in c2 loop

    FOR i in C1 loop
    if i.mlstn_id = j.element then
    csql := csql || ',MAX(CASE WHEN sub.mlstn_id = '|| i.mlstn_id || ' AND sub.sub_milstn_code = '|| i.sub_milstn_code || ' THEN a.PTR_PLANNED_DT ELSE NULL END) as ' || '"' || i.sub_mlstn_name || '_Plan' || '"' ;
    csql := csql || ',MAX(CASE WHEN sub.mlstn_id = '|| i.mlstn_id || ' AND sub.sub_milstn_code = '|| i.sub_milstn_code || ' THEN a.PTR_ACTUAL_DT ELSE NULL END) as ' || '"' || i.sub_mlstn_name || '_Act' || '"' ;
    csql := csql || ',MAX(CASE WHEN sub.mlstn_id = '|| i.mlstn_id || ' AND sub.sub_milstn_code = '|| i.sub_milstn_code || ' THEN a.PTR_FORECAST_DT ELSE NULL END) as ' || '"' || i.sub_mlstn_name || '_Fcast' || '"';
    end if;
    End loop;

    end loop;

    --select SUBSTR(csql,1,length(csql)-1) into csql from dual;



    csql := csql || ' FROM PTR_DETAILS a, PTR_M_SUB_MILESTONES sub, PTR_INQUIRY_DETL i, PTR_M_INQUIRY m, PTR_M_ACTIVITY act, ptr_m_dept dept, ptr_m_category cat ';
    csql := csql || ' WHERE a.sub_mlstn_id = sub.sub_mlstn_id AND A.JOB_CODE = SUB.JOB_CODE ';
    csql := csql || ' AND a.INQD_LINE_ID =i.INQD_LINE_ID AND i.inq_id = m.inq_id AND A.JOB_CODE = M.JOB_CODE ';
    csql := csql ||' AND dept.dept_id = m.dept_id AND dept.job_code = m.job_code AND cat.cat_id = m.cat_id AND cat.job_code = m.job_code ';
    csql := csql || ' AND a.act_id = act.act_id(+) AND A.JOB_CODE = ACT.JOB_CODE(+) ';
    csql := csql || ' AND a.job_code =''' || p_job_code || '''' ;
    csql := csql || ' and a.method_id= ' || p_method_id ;
    csql := csql || ' GROUP BY DEPT_SORT_ID, m.inq_No, m.inq_desc, dept.dept_desc, dept.dept_code, cat.cat_description,a.method_id ';
    csql := csql || ' ORDER BY DEPT_SORT_ID, m.inq_No, m.inq_desc, dept.dept_desc, dept.dept_code, cat.cat_description,a.method_id ';



    OPEN CUR_OUT FOR
    csql;
  • 3. Re: Oracle LONG String
    BluShadow Guru Moderator
    Currently Being Moderated
    a) do you really need to execute dynamic SQL for your query?
    b) In 11g EXECUTE IMMEDIATE handles CLOBs
    c) If you're not on 11g you can use the DBMS_SQL package to execute statements larger than 32K...
    SQL> ed
    Wrote file afiedt.buf
     
      1  declare
      2    v_large_sql  CLOB;
      3    v_num        NUMBER := 0;
      4    v_upperbound NUMBER;
      5    v_sql        DBMS_SQL.VARCHAR2S;
      6    v_cur        INTEGER;
      7    v_ret        NUMBER;
      8  begin
      9    -- Build a very large SQL statement in the CLOB
     10    LOOP
     11      IF v_num = 0 THEN
     12        v_large_sql := 'CREATE VIEW vw_tmp AS SELECT ''The number of this row is : '||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL';
     13      ELSE
     14        v_large_sql := v_large_sql || ' UNION ALL SELECT ''The number of this row is : '||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL';
     15      END IF;
     16      v_num := v_num + 1;
     17      EXIT WHEN DBMS_LOB.GETLENGTH(v_large_sql) > 40000 OR v_num > 800;
     18    END LOOP;
     19    DBMS_OUTPUT.PUT_LINE('Length:'||DBMS_LOB.GETLENGTH(v_large_sql));
     20    DBMS_OUTPUT.PUT_LINE('Num:'||v_num);
     21    --
     22    -- Now split that large SQL statement into chunks of 256 characters and put in VARCHAR2S array
     23    v_upperbound := CEIL(DBMS_LOB.GETLENGTH(v_large_sql)/256);
     24    FOR i IN 1..v_upperbound
     25    LOOP
     26      v_sql(i) := DBMS_LOB.SUBSTR(v_large_sql
     27                                 ,256 -- amount
     28                                 ,((i-1)*256)+1 -- offset
     29                                 );
     30    END LOOP;
     31    --
     32    -- Now parse and execute the SQL statement
     33    v_cur := DBMS_SQL.OPEN_CURSOR;
     34    DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
     35    v_ret := DBMS_SQL.EXECUTE(v_cur);
     36    DBMS_OUTPUT.PUT_LINE('View Created');
     37* end;
    SQL> / 
    Length:40015
    Num:548
    View Created
     
    PL/SQL procedure successfully completed.
     
    SQL> select count(*) from vw_tmp;
     
      COUNT(*)
    ----------
           548
     
    SQL> select * from vw_tmp where rownum <= 10;
     
    COL1
    -----------------------------------
    The number of this row is : 0000000
    The number of this row is : 0000001
    The number of this row is : 0000002
    The number of this row is : 0000003
    The number of this row is : 0000004
    The number of this row is : 0000005
    The number of this row is : 0000006
    The number of this row is : 0000007
    The number of this row is : 0000008
    The number of this row is : 0000009
     
    10 rows selected.
     
    SQL>
  • 4. Re: Oracle LONG String
    722885 Newbie
    Currently Being Moderated
    Thanks for the reply.i am returning a CUR_OUT as SYS_REFCURSOR ..can i proceed with your method..

    CREATE OR REPLACE PROCEDURE Dbp_prog_test
    (P_JOB_CODE VARCHAR2,
    p_method_id NUMBER,
    P_MILSTN_ID VARCHAR2,
    CUR_OUT OUT SYS_REFCURSOR) AS

    CURSOR C1 is
    select SUB_MLSTN_DISP_NAME as sub_mlstn_name,SUB_MILSTN_CODE,mlstn_id, max(sort_order) sort_order
    from ptr_m_sub_milestones
    where mlstn_id
    in (Select element from table(Get_CSV_to_Tab(P_MILSTN_ID))) group by SUB_MLSTN_DISP_NAME,SUB_MILSTN_CODE,mlstn_id order by sort_order;

    cursor C2 is
    Select element from table(Get_CSV_to_Tab(P_MILSTN_ID));

    csql LONG;
    p_err_no NUMBER;
    p_err_message VARCHAR2 (200);

    BEGIN

    csql := ' SELECT m.inq_No, m.inq_desc Inq_Desc, dept.dept_desc Department, dept.dept_code Dept_Code, cat.cat_description CATEGORY, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''01'' THEN act.ACT_CODE ELSE ''0'' END) Inq_Activity, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''02'' THEN act.ACT_CODE ELSE ''0'' END) Bids_Eval_Activity, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code IN (''03'', ''04'') THEN act.ACT_CODE ELSE ''0'' END) PO_Activity, ';
    csql := csql || ' ROUND(SUM(CASE WHEN sub.mlstn_id = 1 THEN PTR_EARNED_WF ELSE 0 END) / DECODE(SUM(CASE WHEN sub.mlstn_id = 1 THEN PTR_ESTIMATED_WF ELSE 0 END), 0, 1, SUM(CASE WHEN sub.mlstn_id = 1 THEN PTR_ESTIMATED_WF ELSE 0 END))*100, 2) PreOrd_Progress, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''04'' THEN a.PTR_ACTUAL_DT ELSE NULL END) AS Po_Actual_Date, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 1 AND sub.sub_milstn_code = ''04'' THEN a.PTR_FORECAST_DT ELSE NULL END) AS Po_Forecastt_Date, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 2 THEN act.ACT_CODE ELSE ''0'' END) Mfg_Activity, ';
    csql := csql || ' ROUND(SUM(CASE WHEN sub.mlstn_id = 2 THEN PTR_EARNED_WF ELSE 0 END) / DECODE(SUM(CASE WHEN sub.mlstn_id = 2 THEN PTR_ESTIMATED_WF ELSE 0 END), 0, 1, SUM(CASE WHEN sub.mlstn_id = 2 THEN PTR_ESTIMATED_WF ELSE 0 END))*100, 2) Mfg_Progress, ';
    csql := csql || ' MAX(CASE WHEN sub.mlstn_id = 3 THEN act.ACT_CODE ELSE ''0'' END) Ship_Activity, ';
    csql := csql || ' ROUND(SUM(CASE WHEN sub.mlstn_id = 3 THEN PTR_EARNED_WF ELSE 0 END) / DECODE(SUM(CASE WHEN sub.mlstn_id = 3 THEN PTR_ESTIMATED_WF ELSE 0 END), 0, 1, SUM(CASE WHEN sub.mlstn_id = 3 THEN PTR_ESTIMATED_WF ELSE 0 END))*100, 2) Ship_Progress ';

    for j in c2 loop

    FOR i in C1 loop
    if i.mlstn_id = j.element then
    csql := csql || ',MAX(CASE WHEN sub.mlstn_id = '|| i.mlstn_id || ' AND sub.sub_milstn_code = '|| i.sub_milstn_code || ' THEN a.PTR_PLANNED_DT ELSE NULL END) as ' || '"' || i.sub_mlstn_name || '_Plan' || '"' ;
    csql := csql || ',MAX(CASE WHEN sub.mlstn_id = '|| i.mlstn_id || ' AND sub.sub_milstn_code = '|| i.sub_milstn_code || ' THEN a.PTR_ACTUAL_DT ELSE NULL END) as ' || '"' || i.sub_mlstn_name || '_Act' || '"' ;
    csql := csql || ',MAX(CASE WHEN sub.mlstn_id = '|| i.mlstn_id || ' AND sub.sub_milstn_code = '|| i.sub_milstn_code || ' THEN a.PTR_FORECAST_DT ELSE NULL END) as ' || '"' || i.sub_mlstn_name || '_Fcast' || '"';
    end if;
    End loop;

    end loop;

    --select SUBSTR(csql,1,length(csql)-1) into csql from dual;



    csql := csql || ' FROM PTR_DETAILS a, PTR_M_SUB_MILESTONES sub, PTR_INQUIRY_DETL i, PTR_M_INQUIRY m, PTR_M_ACTIVITY act, ptr_m_dept dept, ptr_m_category cat ';
    csql := csql || ' WHERE a.sub_mlstn_id = sub.sub_mlstn_id AND A.JOB_CODE = SUB.JOB_CODE ';
    csql := csql || ' AND a.INQD_LINE_ID =i.INQD_LINE_ID AND i.inq_id = m.inq_id AND A.JOB_CODE = M.JOB_CODE ';
    csql := csql ||' AND dept.dept_id = m.dept_id AND dept.job_code = m.job_code AND cat.cat_id = m.cat_id AND cat.job_code = m.job_code ';
    csql := csql || ' AND a.act_id = act.act_id(+) AND A.JOB_CODE = ACT.JOB_CODE(+) ';
    csql := csql || ' AND a.job_code =''' || p_job_code || '''' ;
    csql := csql || ' and a.method_id= ' || p_method_id ;
    csql := csql || ' GROUP BY DEPT_SORT_ID, m.inq_No, m.inq_desc, dept.dept_desc, dept.dept_code, cat.cat_description,a.method_id ';
    csql := csql || ' ORDER BY DEPT_SORT_ID, m.inq_No, m.inq_desc, dept.dept_desc, dept.dept_code, cat.cat_description,a.method_id ';



    OPEN CUR_OUT FOR
    csql;



    EXCEPTION
    WHEN OTHERS
    THEN
    -- ROLLBACK;
    p_err_no := SQLCODE;
    p_err_message := SQLERRM;



    END;
  • 5. Re: Oracle LONG String
    BluShadow Guru Moderator
    Currently Being Moderated
    user8838379 wrote:
    Thanks for the reply.i am returning a CUR_OUT as SYS_REFCURSOR ..can i proceed with your method..
    In 11g, yes you could do something like that.
    You'd build up you SQL in a CLOB and then I think (I don't have 11g to test) you can just open the sys_refcursor based on the CLOB. If you can't open the ref cursor directly on the CLOB you can open it using DBMS_SQL as in my example above and then the DBMS_SQL package in 11g allows you to convert a DBMS_SQL cursor to a REF CURSOR.

    Another alternative would be to use some sort of pre-built views on your database so you're not having to create such large SQL statements, or use some subquery factoring if possible (the WITH clause you see people using on these forums quite a lot).
  • 6. Re: Oracle LONG String
    722885 Newbie
    Currently Being Moderated
    Thanks i used your method..but the last part is how will i convert DBMS_SQl to rsysrefcursor..
    it is a builtin in Oracle11g but im using 10g..

    DBMS_SQL.TO_REFCURSOR not available in 10g.

    Please Help
  • 7. Re: Oracle LONG String
    BluShadow Guru Moderator
    Currently Being Moderated
    user8838379 wrote:
    Thanks i used your method..but the last part is how will i convert DBMS_SQl to rsysrefcursor..
    it is a builtin in Oracle11g but im using 10g..

    DBMS_SQL.TO_REFCURSOR not available in 10g.

    Please Help
    If you're not using 11g, you can't. Hence why it's a new feature of 11g.
  • 8. Re: Oracle LONG String
    722885 Newbie
    Currently Being Moderated
    is there anything equivalent that we can try about..it almost fulfills my requirement except for that function.
    Please Suggest.
  • 9. Re: Oracle LONG String
    BluShadow Guru Moderator
    Currently Being Moderated
    What do you need a ref cursor for? Are you passing it back to Java or .NET etc.?

    If you really need a ref cursor then you're going to be stuck, unless you can reduce the size of your SQL statement (using views, subquery factoring etc.).
  • 10. Re: Oracle LONG String
    722885 Newbie
    Currently Being Moderated
    Im Calling the Procedure from .NET and should return refcursor back to .NET Page
  • 11. Re: Oracle LONG String
    BluShadow Guru Moderator
    Currently Being Moderated
    user8838379 wrote:
    Im Calling the Procedure from .NET and should return refcursor back to .NET Page
    In that case, you've got to limit your SQL to <= 32767 bytes. You won't be able to use the DBMS_SQL package.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points