Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: About the Stored Procedure, need help

    I have to replace the Query below in to the Oracle Stored procedure, I tried to write the procedure like this but this is not a complete stored procedure. Can somebody suggest me to make a right procedure? This is very important for me in my Job. Any help will be appreciated.

    CREATE OR REPLACE PROCEDURE strProc_StoredProcedure
    IS
    BEGAIN
    "SELECT SSBSECT_TERM_CODE, SSBSECT_CRN, SSBSECT_SUBJ_CODE, SSBSECT_CRSE_NUMB, SSBSECT_SEQ_NUMB, SSBSECT_CAMP_CODE,SSBSECT_PTRM_START_DATE,
    SSBSECT_PTRM_END_DATE, START_DATE, END_DATE, SCBCRSE_TITLE As Title
    FROM SSBSECT
    inner join SCBCRSE on SCBCRSE_CRSE_NUMB = SSBSECT_CRSE_NUMB
    AND SCBCRSE_SUBJ_CODE=SSBSECT_SUBJ_CODE
    left outer join (select ssrmeet_term_code, ssrmeet_crn, min(ssrmeet_start_date) as start_date, max(ssrmeet_end_date) as end_date from ssrmeet group by ssrmeet_term_code, ssrmeet_crn
    ) ssrmeet on ssrmeet_term_code = ssbsect_term_code
    and ssrmeet_crn = ssbsect_crn
    WHERE ({0})
    AND SSBSECT_SSTS_CODE >= 'A'
    AND SCBCRSE_EFF_TERM=(SELECT Max(SCBCRSE_EFF_TERM) FROM SCBCRSE
    WHERE SCBCRSE_SUBJ_CODE = SSBSECT_SUBJ_CODE
    AND SCBCRSE_CRSE_NUMB=SSBSECT_CRSE_NUMB)";

    END;


    -----Here is the main query in the C# function , which I have to replace with oracle stored procedure-------

    private void FillTable()
    {
    string usersCommandString = @"SELECT SSBSECT_TERM_CODE,
    SSBSECT_CRN,
    SSBSECT_SUBJ_CODE,
    SSBSECT_CRSE_NUMB,
    SSBSECT_SEQ_NUMB,
    SSBSECT_CAMP_CODE,
    SSBSECT_PTRM_START_DATE,
    SSBSECT_PTRM_END_DATE,
    START_DATE,
    END_DATE,
    SCBCRSE_TITLE As Title
    FROM SSBSECT
    inner join SCBCRSE on SCBCRSE_CRSE_NUMB = SSBSECT_CRSE_NUMB
    AND SCBCRSE_SUBJ_CODE=SSBSECT_SUBJ_CODE
    left outer join (
    select ssrmeet_term_code, ssrmeet_crn, min(ssrmeet_start_date) as start_date, max(ssrmeet_end_date) as end_date from ssrmeet group by ssrmeet_term_code, ssrmeet_crn
    ) ssrmeet on ssrmeet_term_code = ssbsect_term_code
    and ssrmeet_crn = ssbsect_crn
    WHERE ({0})
    AND SSBSECT_SSTS_CODE >= 'A'
    AND SCBCRSE_EFF_TERM=(SELECT Max(SCBCRSE_EFF_TERM) FROM SCBCRSE
    WHERE SCBCRSE_SUBJ_CODE = SSBSECT_SUBJ_CODE
    AND SCBCRSE_CRSE_NUMB=SSBSECT_CRSE_NUMB)";
    string whereClause = GetWhereFromTermCodes("SSBSECT_TERM_CODE", TermCode.GetValidCourseTermCodes(false));
    usersCommandString = string.Format(usersCommandString, whereClause);

    {


    I don’t know what WHERE ({0}) does and how to include “” < string whereClause = GetWhereFromTermCodes("SSBSECT_TERM_CODE", TermCode.GetValidCourseTermCodes(false)); > ””

    Into the where statement.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I don’t know what WHERE ({0}) does
    Then how will you know if/when the new stored procedure is complete & correct?
    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
    Mar 2008
    Posts
    2
    That query runs perfectly somebody wrote it, my part is just make a store procedure to replace that query in the application.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I'm not a C# expert, but {0} must mean the 2nd argument of the string.Format method, which means that string.Format replaces "{0}" with the whereClause variable. Now, what is in this variable, that is :
    Code:
    string whereClause = GetWhereFromTermCodes("SSBSECT_TERM_CODE", TermCode.GetValidCourseTermCodes(false));
    I don't know at all .

    Can't you see the code of the GetWhereFromTermCodes function ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by RBARAER
    I'm not a C# expert, but {0} must mean the 2nd argument of the string.
    I would think {0} is the first argument.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    I would think {0} is the first argument.
    I don't think so
    Code:
    usersCommandString = string.Format(usersCommandString, whereClause);
    And usersCommandString contains the whole string containing "{0}"...

    So I guess string.Format takes as first argument the string to modify and as second argument what to put in place of {0} in the string, and then maybe as third argument what to put in place of {1}...

    Anyway, it cannot be the first argument, agreed ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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