Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10

    Question Unanswered: Not All Variables Bound

    I can't seem to figure out why I'm getting this not all variables bound error. I have 4 bind variables and I'm specifying one for each variable in the using clause. This is my procedure:

    Code:
    PROCEDURE STANDARD_PUBLIC_PROC_DYN
      (
      period_master_value NUMBER, proj_type_value varchar2,
                                         proj_associd Number, formid_value NUMBER
    
    		
    )
    
    
      as 
       v_Info 						  PRV_ARM_CORE.PROCESS_INFO;
       v_PROC_NAME 					  varchar2(65):='RLK_DEV.STANDARD_PUBLIC_PROC_DYN';
       v_sql                          varchar2(2000) :='';
    
    
     BEGIN
    
    
        v_sql := '  SELECT pd.period_detail_oid, pd.closing_date as period_closing_date, ';
        v_sql := v_sql || ' m.wr_measure_oid,m.measure_name,mv.pa_entered_value, ';
        v_sql := v_sql || ' i.wr_item_oid, i.title, i.description, fm.HORIZONTAL_DISPLAY_SEQUENCE ';
        v_sql := v_sql || ' FROM ';
        v_sql := v_sql || '    project p ';
        v_sql := v_sql || '    INNER JOIN  project_associate pa  ON p.project_oid = pa.project_oid ';
        v_sql := v_sql || '    INNER JOIN  wr_measure_value mv   ON pa.project_associate_oid = mv.project_associate_oid ';
        v_sql := v_sql || '    INNER JOIN  period_detail pd      ON pd.period_detail_oid = mv.period_oid ';
        v_sql := v_sql || '    INNER JOIN  wr_geo g              ON mv.wr_geo_oid = g.wr_geo_oid ';
        v_sql := v_sql || '    INNER JOIN  wr_item i             ON mv.wr_item_oid = i.wr_item_oid ';
        v_sql := v_sql || '    INNER JOIN  wr_measure m          ON mv.wr_measure_oid = m.wr_measure_oid ';
        v_sql := v_sql || '    INNER JOIN  wr_form_measure fm    ON m.wr_measure_oid = fm.wr_measure_oid ';
        v_sql := v_sql || '    INNER join  wr_form f             ON fm.wr_form_oid = f.wr_form_oid ';
        v_sql := v_sql || ' WHERE ';
        v_sql := v_sql || '    p.project_oid = pa.project_oid ';
        v_sql := v_sql || '    AND pd.period_detail_oid = mv.period_oid ';
        v_sql := v_sql || '    AND pa.project_associate_oid = mv.project_associate_oid ';
        v_sql := v_sql || '    AND g.wr_geo_oid = mv.wr_geo_oid ';
        v_sql := v_sql || '    AND i.wr_item_oid = mv.wr_item_oid ';
        v_sql := v_sql || '    AND m.wr_measure_oid = fm.wr_measure_oid ';
        v_sql := v_sql || '    AND f.wr_form_oid = fm.wr_form_oid ';
        v_sql := v_sql || '    AND pd.period_master_oid = :pmaster ';
        v_sql := v_sql || '    AND pd.closing_date BETWEEN SYSDATE - (30*24) AND SYSDATE ';
        v_sql := v_sql || '    AND p.project_type = :ptype ';
        v_sql := v_sql || '    AND mv.PROJECT_ASSOCIATE_OID = :passocid ';
        v_sql := v_sql || '    AND wr_form_oid = :formoid ';
        v_sql := v_sql || ' ORDER BY ';
        v_sql := v_sql || '     pd.period_detail_oid desc, fm.HORIZONTAL_DISPLAY_SEQUENCE ';
    
      
       OPEN p_role_ref for v_sql;
       EXECUTE IMMEDIATE v_sql 
       USING period_master_value,proj_type_value,proj_associd,formid_value;
       --USING 3, 'NCA', 460, 1;
       
     END;

    I tried hard coding values in the using clause too as you can see but I got the same error there. Do you see what I'm doing wrong?

    Thanks
    Last edited by Bozo34; 01-22-10 at 11:43.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How is it that argument list ends with a comma?

    >period_master_value NUMBER, proj_type_value varchar2, proj_associd Number, formid_value NUMBER,
    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
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10
    Quote Originally Posted by anacedent View Post
    How is it that argument list ends with a comma?

    >period_master_value NUMBER, proj_type_value varchar2, proj_associd Number, formid_value NUMBER,
    Oh...that was a mistake in copying and pasting. It doesn't really have a comma at the end.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Oh...that was a mistake in copying and pasting
    You need to file a bug report to get that fixed.
    CUT & PASTE should not be adding extra characters
    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
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10
    Quote Originally Posted by anacedent View Post
    >Oh...that was a mistake in copying and pasting
    You need to file a bug report to get that fixed.
    CUT & PASTE should not be adding extra characters
    Ok thanks for your help.


    If anyone knows the answer to my question (without irrelevant sarcasm), it would appreciated.
    Last edited by Bozo34; 01-22-10 at 11:59.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You don't provide bind variables in the OPEN statement. By the way, what is the point of using both OPEN and EXECUTE IMMEDIATE?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10
    Quote Originally Posted by n_i View Post
    You don't provide bind variables in the OPEN statement. By the way, what is the point of using both OPEN and EXECUTE IMMEDIATE?
    I wonder if I remove the OPEN statement if it would work. I'll give that a try. I'm a beginner with pl/sql but trying to jump into it in the middle I guess.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Bozo34 View Post
    I wonder if I remove the OPEN statement if it would work.
    You see, each statement is (or at least should be) there for a reason. If you are not sure what each statement does, I would advise against removing (or adding) them just to see what happens. The Book of Manual has plenty of examples that should help you.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10
    Quote Originally Posted by n_i View Post
    You see, each statement is (or at least should be) there for a reason. If you are not sure what each statement does, I would advise against removing (or adding) them just to see what happens. The Book of Manual has plenty of examples that should help you.

    Actually the problem was the execute immediate like you said. I didn't need that. I just needed this:

    OPEN p_role_ref for v_sql
    USING period_master_value,proj_type_value,proj_associd,f ormid_value;

Posting Permissions

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