11 Replies Latest reply: Jul 26, 2010 8:09 AM by BluShadow RSS

    Oracle LONG String

    722885
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Im Calling the Procedure from .NET and should return refcursor back to .NET Page
                            • 11. Re: Oracle LONG String
                              BluShadow
                              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.