×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Dynamic SQL help
2

Dynamic SQL help

Dynamic SQL help

(OP)
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.

CODE --> plsql

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!!

RE: Dynamic SQL help

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

Thanks!

RE: Dynamic SQL help

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/

RE: Dynamic SQL help

(OP)
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!!!

RE: Dynamic SQL help

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...:

CODE

set serveroutput on format wrap
CREATE OR REPLACE PROCEDURE ...IS
   l_str   VARCHAR2 (4000);
begin
    ...AND pos_sum.external_ref IS NULL';
   DBMS_OUTPUT.PUT_LINE(l_str);
   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.

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

RE: Dynamic SQL help

(OP)
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!!!

RE: Dynamic SQL help

Quote (northw)

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.

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

RE: Dynamic SQL help


Like this:

CODE

. . .   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;
/ 
3eyes

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

RE: Dynamic SQL help

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

Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close