Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: Passing parameter to Oracle Procedure

    Hi,

    I have a stored procedure in oracle, which retrieve a set of records based on a URL(where condition) and will update the URL with another URL.

    Now I'm trying to pass these two URLs as a parameter, instead of including in the stored procedure.

    I've modified the procedure by giving the parameter variables in the procedure and updated it whereever necessary.

    There is no error in while run the proedure. But the URLs are not updated. I kept a count in the procedure which is showing 0 after execution, which means no records are updated.

    Can anybody please help me on this?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can't get any help, sorry.

    However, if you post the procedure (if you do so, format it and enclose into the CODE tags to preserve formatting), create a simple test case (CREATE TABLE and INSERT INTO several sample records), show us how you call the procedure (using which parameters), we would be able to assist.

    Otherwise, all I can say is: you did something wrong and the procedure doesn't do what you wanted it to.

  3. #3
    Join Date
    Feb 2012
    Posts
    4
    Thanks for the reply. I've attached the procedure.

    I'm passing the parameter like below:

    EXECUTE WIKI_FORM_PARAM ('URL1', 'URL2');
    Attached Files Attached Files
    Last edited by praveen_dk; 02-07-12 at 03:25.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This:
    Code:
    v_rec_count := v_rec_count + 1
    would be executed regardless UPDATE actually updates a record or not. It means that FOR loop exited without even entering the loop. Furthermore, it means that (probably) WHERE clause caused no records to be retrieved by a cursor.

    What is the result of cursor's SELECT statement when you run it alone, in SQL*Plus, providing values you pass as parameters?

    I'd try it myself, but - where's that test case I asked for? Why didn't you properly format the code?

  5. #5
    Join Date
    Feb 2012
    Posts
    4
    Sorry for not formatting the code.

    The result of cursor's SELECT statement in SQL*Plus is a set of records which contains the URL1 in the table defobjectdatahtml. It means that there are some records (66 records) available for this select query.

    There are only three columns which contains the URL1 in this table defobjectdatahtml, but need to join with so many tables to get the expected report. You might be noticed in the FOR loop of procedure.

    I can able to run the procedure successfully without passing the parameter, ie giving the URL1 & URL2 values directly in the procedure. But now I need to modify the procedure inorder to pass the URLs as a parameter.

    If you still need a test case, I will provide it.
    Last edited by praveen_dk; 02-07-12 at 03:47.

  6. #6
    Join Date
    Feb 2012
    Posts
    4
    Thanks for looking into this.

    This issue is resolved by a small modification in the procedure.

Posting Permissions

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