Results 1 to 2 of 2

Thread: REF Cursor

  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Post Unanswered: REF Cursor

    I want to attach bind variable that are generated dynamically,depending on the conditions.

    How can i append these bind variables to ref cursor By USING keyword.

    I tried with attaching a string variable which appends all the bind variables,but not worked.

    Can someone let me know how to go about this.

    IF gBindRSCol IS NOT NULL THEN
    strBindVAR:=''''||gBindRSCol||''',';
    END IF;

    IF gBindRPMin IS NOT NULL THEN
    strBindVAR:=strBindVAR||''''||gBindRPMin||''',';
    END IF;

    IF gBindRPMax IS NOT NULL THEN
    strBindVAR:=strBindVAR||''''||gBindRPMax||''','||' ''100''' ;
    END IF;


    --Open and Fetch Count--
    OPEN curEntityCount FOR strSQL USING strBindVAR;





    Iam trying to use REf cursor which will have multiple bind variables that are genearted dynamically.

    Like If the condition is statisfied Then add this bind variable to the query.

    In sql query there might be 1 bind variable at a time or multiple bind variables Which I have to attach using "USING" keyword.
    Last edited by jitubhatia; 11-26-03 at 13:08.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Two options, first with dynamic SQL

    PHP Code:
    BindVariable := 'value';
    OPEN curSomething for 
       
    'select * from table where column = :value' USING BindVariable
    Or with static,

    PHP Code:
    DECLARE
       
    CURSOR myCursor (BindVar TABLE.COL%TYPE IS
           SELECT 
    from table where column BindVar;
    BEGIN
       OPEN myCursor
    'value' );
    ... 
    Hth
    Bill

Posting Permissions

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