Dynamic SQL help 2

[northw]

Hi All,

I Have created stored proc where I am passing a parameter value which needs to be appended to the end of the table and executed, this is for a crystal reports application. Another value is passed as a parameter for the where clause.

It compiled fine, but it throws error when trying to access the data. Please suggest me any changes to fix it.

CREATE OR REPLACE PROCEDURE gp_realloc (p_eod_date   IN     VARCHAR2,
                                        p_link       IN     NUMBER,
                                        c_rec        IN OUT SYS_REFCURSOR)
IS
   l_str   VARCHAR2 (4000);
BEGIN
   l_str :=
         'SELECT DISTINCT r_t_s.trade_link_num trade_link_num,
         pos_sum.trade_num trade_num,
         ROUND (pos_sum.market_price / NVL (mass_conv_factor, 1.0), 8)
            market_price,
         ROUND (pos_sum.trade_price / NVL (mass_conv_factor, 1.0), 8)
            trade_price,
         m_c_d.price_curve_type_cd
    FROM pos_sum_m_'
      || p_eod_date
      || 'pos_sum
         LEFT OUTER JOIN
         r_t_s
            ON     pos_sum.trade_num = r_t_s.trade_num
               AND pos_sum.term_num = r_t_s.term_num
               AND r_t_s.record_type_ind = 0
               AND (   pos_sum.data_source_cd = ''SYSTEM-STORAGE''
                    OR r_t_s.internal_ind = 0
                    OR (r_t_s.internal_ind = 1
                        AND (pos_sum.buy_sell_ind = r_t_s.buy_sell_ind)))
         LEFT OUTER JOIN m_c_d
            ON pos_sum.mtm_quote_def_num = m_c_d.curve_num
         JOIN org_strategy ON pos_sum.strategy_num = org_strategy.strategy_num
   WHERE     pos_sum.mkt_snapshot_cd = ''Official''
         AND r_t_s.trade_link_num :p_link
         AND pos_sum.bifurcation_ind IN (0, 2)
         --AND pos_sum.trade_num = 148094
         AND pos_sum.time_period_type_ind = 4
         AND pos_sum.risk_start_dt >= TO_DATE (''20110501000000'', ''YYYYMMDDHH24MISS'')
         AND pos_sum.external_ref IS NULL';

   OPEN c_rec FOR l_str;
END gp_realloc;
/

Thanks in advance!!

 

[northw]

Getting error, when trying to access the stored proc from crystal reports.
ORA 00972: Identifier too long.

Thanks!

 

[ggriffit]

have you tried running the report from SQL Plus or similar ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[northw]

Thanks for the reply!
Yes, tried and getting the same error as in crystal reports application.

DECLARE
l_ref_cur SYS_REFCURSOR;
begin
gp_realloc('20130319', l_ref_cur );
commit;
end;
/
Thanks!!!

 

[SantaMufasa]

Northw,

Whenever I have issues with dynamic SQL, I display the generated code just prior to its execution, using the following technique. Using your code variable names...:

set serveroutput on format wrap
CREATE OR REPLACE PROCEDURE ...IS
   l_str   VARCHAR2 (4000);
begin
    ...AND pos_sum.external_ref IS NULL';
   [B][I]DBMS_OUTPUT.PUT_LINE(l_str);[/I][/B]
   OPEN c_rec FOR l_str;
END gp_realloc;
/

Even if the code results in a run-time error, you can see what is generated and debug the generated code by running it independently at a SQL*Plus prompt. I am guessing that your problem comes when SQL reaches the code, "FROM pos_sum_m_' || p_eod_date || 'pos_sum' and the contents of the incoming p_eod_date is exceeding 13 characters in length, which would result in an illegal table name of at least 31 characters.

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[northw]

Thanks alot Mufasa!!
As you said, the issue was with the table name, there is no space between table name and alais, that was causing the issue. Now how do I pass a parameter to the where clause in that query.

Thanks again!!!

 

[SantaMufasa]

Now how do I pass a parameter to the where clause in that query.

There are several ways to make that happen, so if you give us a "f'rinstance" of which piece of the WHERE clause you'd like to replace with a parameter, we can offer a specific suggestion or two.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

Like this:

. . .   E t c   . . .
WHERE     pos_sum.mkt_snapshot_cd = ''Official''
         AND r_t_s.trade_link_num :p_link
         AND pos_sum.bifurcation_ind IN (0, 2)
         --AND pos_sum.trade_num = 148094
         AND pos_sum.time_period_type_ind = 4
         AND pos_sum.risk_start_dt >= TO_DATE (:p_eod_date, ''YYYYMMDDHH24MISS'')
         AND pos_sum.external_ref IS NULL';

   OPEN c_rec FOR l_str USING p_link, p_eod_date
END gp_realloc;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[northw]

Thanks for the replies all,
For this field
r_t_s.trade_link_num = 12345 --It will take a number.

Thanks!