Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    6

    Unanswered: stored procedure

    ......................................
    Last edited by kots0s; 10-01-07 at 15:12.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please read & follow posting guidelines found here:
    http://www.dbforums.com/showthread.php?t=1031644

    >where test parameter will be passed dynamically.

    EXACTLY what in your posted SQL corresponds to "test parameter" above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2007
    Posts
    6
    test params will be values that the system that will run the stored procedure will pass them

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please read & follow posting guidelines found here:
    http://www.dbforums.com/showthread.php?t=1031644

    >where test parameter will be passed dynamically.

    EXACTLY what in your posted SQL corresponds to "test parameter" above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2007
    Posts
    6
    test_asset will be an Id
    test_date a date
    and
    testA1 : Credit
    testA2,A3,A6,A9 : Dunning
    testA4,A5,A7,A8 : Lost
    testA10 :Active
    testB1 : Dep
    testB2 : Pay
    testB3 : Cancel
    testB4 : Susp D
    testB5 : Susp E
    testB6 : Susp I
    testB7 : Susp F
    testB8 : Delete
    testB9 : Bill
    testB10 : Bar

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We obviously are not communicating.
    I give up.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2007
    Posts
    6
    when i say test parameters I mean test_asset, test_date testAs and testBs used in my query..
    i think this is want you want to know. in my previous post I gave you the datatypes for each of the above

    I would appreciate it if you could help me
    Last edited by kots0s; 09-26-07 at 16:44.

  8. #8
    Join Date
    Sep 2007
    Posts
    6
    What i have done till now is:

    CREATE OR REPLACE PACKAGE myTypes

    AS

    TYPE my_ref_cursor IS REF CURSOR;

    END;

    CREATE OR REPLACE PROCEDURE TESTBEAGETCUSTOMERREQUESTS (param1 IN

    STRING,

    param2 IN STRING,
    param3 IN STRING,
    param4 IN DATE,

    p_recordset1 OUT

    myTypes.my_ref_cursor)

    AS


    BEGIN

    OPEN p_recordset1 for

    'SELECT * FROM ('||
    'SELECT a.asset_num ContractId,'||
    'sr.sr_type_cd Type_,'||
    'sr.sr_subtype_cd Operation,||
    'sr.sr_area Category_,||
    'sr.sr_sub_area Sub_Category,||
    'sr.sr_stat_id Status,||
    'sr.sr_sub_stat_id Sub_Status,||
    'sr.act_open_dt Dt_Opened,||
    'sr.act_close_dt Dt_Closed,||
    'sr.sr_title Summary,||
    'sr.sr_desc_text Description,||
    'sr.comm_method_cd Source_,||
    'sr.resolution_cd Resolution,||
    'sr.prio_cd Priority,||
    'sr.sr_sev_cd Severity,||
    'ROW_NUMBER() OVER (ORDER_BY ACT_OPEN_DT DESC) IDD'||
    'FROM siebel.s_asset a, siebel.s_srv_req sr'||
    'WHERE sr.asset_id = a.row_id'||
    'AND a.asset_num = : param1'||
    'AND sr.sr_stat_id <> 'Cancelled'||
    'AND sr.created > to_date (param4,'dd/mm/yyyy')'||
    'AND sr.sr_area IN param2'||
    'AND sr.sr_sub_area IN param3'||
    ')'||
    'WHERE IDD >0 ' USING param1, param2, param3, param4;


    Could you give me some corrections/ hints

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    First, I see no reason to use dynamic SQL, issuing simple SELECT query should work. Also no needto issue TO_DATE on parameter with DATE type.
    For the correct way, how to dynamically pass string into IN condition, use the solution given in following threads on AskTom
    How can I do a variable "in list"
    Varying elements in IN list

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •