ORA-1008 Not all variables bound

This is a discussion on ORA-1008 Not all variables bound within the C Programming forums, part of the General Programming Boards category; Trying something new and not able to find what the error is. Variables are in the SQL statment but it ...

  1. #1
    Registered User
    Join Date
    May 2010
    Posts
    6

    ORA-1008 Not all variables bound

    Trying something new and not able to find what the error is.
    Variables are in the SQL statment but it errors out when trying to open the cursor

    Code:
    #define TRM4_SQL                                                                  \
    "WITH                                                                             \
    TMP_CLM_TOTALS as                                                                 \
    (SELECT D.SAK_DRUG, COUNT(x.sak_claim) as clm_count                               \
     FROM                                                                             \
       t_drug d                                                                       \
      ,aim01.t_pd_pharm_dtl x                                                         \
      ,aim01.t_pd_pharm_hdr q                                                         \
      ,aim01.T_PHRM_HDR_KEYS w                                                        \
     WHERE                                                                            \
      NOT EXISTS( select null from tmp_cmstape505 t                                   \
                    where t.cde_ndc = d.cde_ndc )                                     \
      AND x.sak_drug = d.sak_drug                                                     \
      AND q.sak_claim = x.sak_claim                                                   \
      AND q.dte_dispense between :qrtDtebegin AND :qrtDtelast                         \
      AND w.sak_claim = x.sak_claim                                                   \
      AND w.IND_ENCOUNTER IN(' ','N')                                                 \
      GROUP BY d.sak_drug                                                             \
    )                                                                                 \
       SELECT                                                                         \
           d.cde_thera_cls_spec,                                                      \
           d.dsc_50,                                                                  \
           case when exists (select 'x'                                               \
                               from t_drug_specl_ind                                  \
                              where ind_spec = 'DME'                                  \
                                and id_group = '2'                                    \
                                and sak_drug = a.sak_drug                             \
                                and EFF_DATE >= :qrtDtebegin                          \
                                and END_DATE <= :qrtDtelast) then 'Y'                 \
                else 'N'                                                              \
           end ind_dme,                                                               \
           min(t.clm_count),                                                          \
           a.cde_ndc,                                                                 \
           MIN(a.dsc_ndc)                    AS dsc_ndc ,                             \
           '0'                               AS cms_begin ,                           \
           TO_CHAR(MAX(a.dte_term_hcfa))     AS cms_end ,                             \
           TO_CHAR(MAX(a.dte_drug_obsolete)) AS cms_obsolete ,                        \
           TO_CHAR(MIN(b.dte_effective))     AS dte_effective ,                       \
           TO_CHAR(MAX(b.dte_end))           AS dte_end                               \
      FROM t_drug            a,                                                       \
           t_covered_benefit b,                                                       \
           t_pub_hlth_pgm    p,                                                       \
           t_generic_drug    c,                                                       \
           t_therapeutic     d,                                                       \
           tmp_clm_totals    t                                                        \
     WHERE a.sak_drug         = b.sak_benefit                                         \
       AND b.cde_benefit_type = 'N'                                                   \
       AND :dteRun BETWEEN b.dte_effective AND b.dte_end                              \
       AND (a.DTE_TERM_HCFA     IS NULL OR a.DTE_TERM_HCFA     = 0                    \
            OR :dteRun < a.DTE_TERM_HCFA)                                             \
       AND (a.DTE_TERM_CMS      IS NULL OR a.DTE_TERM_CMS      = 0                    \
            OR :dteRun < a.DTE_TERM_CMS)                                              \
       AND (a.dte_drug_obsolete IS NULL OR a.dte_drug_obsolete = 0 OR                 \
            to_date(:dteRun, 'YYYYMMDD') < add_months(to_date(DECODE(                 \
                a.dte_drug_obsolete,0, 19800101                                       \
                ,a.dte_drug_obsolete), 'YYYYMMDD'),12))                               \
       AND p.SAK_PUB_HLTH     = b.SAK_PUB_HLTH                                        \
       AND p.CDE_PGM_HEALTH   IN ('HCB01','HCB02','HCB03','HCB04','HCB05'             \
            ,'HCB06','HCB07' ,'HCB08','HCB10','HCB11','HCB13','HCB14','HCB15' )       \
       AND NOT EXISTS (select 0                                                       \
                from tmp_cmstape505 t where t.cde_ndc = a.cde_ndc)                    \
       AND c.sak_drug_gen       = a.sak_drug_gen                                      \
       AND d.sak_drug_thera_cls = c.sak_drug_thera_cls                                \
       AND t.sak_drug (+) = a.sak_drug                                                \
      GROUP BY d.cde_thera_cls_spec, d.dsc_50, a.sak_drug, a.cde_ndc                  \
     Order By d.cde_thera_cls_spec                                                    "

    Code:
    sprintf( sqlStmt, TRM4_SQL);
        EXEC SQL PREPARE SQL_STMT from :sqlStmt;
        EXEC SQL DECLARE TRM4_CUR CURSOR FOR SQL_STMT;
        
        EXEC SQL OPEN TRM4_CUR USING :qrtDtebegin, :qrtDtelast, :dteRun;
    Any Help would be greatly appreciated.
    Thanks
    Last edited by oboeman; 05-21-2010 at 07:08 AM. Reason: Put code tags on

  2. #2
    Registered User
    Join Date
    Sep 2006
    Posts
    8,868
    Oboeman, welcome to the forum. PLEASE put your code between "code tags". Highlight your code, and click on the # icon at the top of the edit window (advanced only).

    That makes your code, look like code, instead of squished forum cannon-fodder.

  3. #3
    Registered User
    Join Date
    May 2010
    Posts
    6
    Thank you, I wasn't sure how to do the tags.

  4. #4
    WDT
    WDT is offline
    Tha 1 Sick RAT
    Join Date
    Dec 2003
    Posts
    271
    Any chance you could post the error and/or where or what you suspect might be causing it? I ask because some of us don't know SQL (Yes shocking I know), but we can still help in some way if we have some pointers.
    A hundred Elephants can knock down the walls of a fortress... One diseased rat can kill everyone inside

  5. #5
    Registered User
    Join Date
    May 2010
    Posts
    6
    This is the error that is returned

    ORA-1008 Not all variables bound

    It happens when the cursor is trying to open. It must have something to do with the three variables used in the OPEN CURSUR statement.

  6. #6
    Registered User
    Join Date
    Apr 2006
    Posts
    58
    Quote Originally Posted by oboeman View Post
    This is the error that is returned

    ORA-1008 Not all variables bound

    It happens when the cursor is trying to open. It must have something to do with the three variables used in the OPEN CURSUR statement.
    I think because you only give three variables as bind parameters when you actually use 8 in your OPEN statement.

    Code:
    EXEC SQL OPEN TRM4_CUR USING :qrtDtebegin, :qrtDtelast, :dteRun;
    In Pro*C, bind variables in the SQL statement do not correspond to the C variable names you provide on the OPEN command. Those variables on the OPEN command are positional, not keyword.

    Try this:
    Code:
    EXEC SQL OPEN TRM4_CUR USING :qrtDtebegin, :qrtDtelast, :qrtDtebegin,  :qrtDtelast, :dteRun, :dteRun, :dteRun, :dteRun;
    Remember, your C variables are just address to Pro*C of the data you wish to bind.
    Sam

  7. #7
    Registered User
    Join Date
    May 2010
    Posts
    6
    Thank you, that worked perfectly. I didn't think about repeating the same variable, I just used each one once. Thanks again.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Problem with Accessing Variables
    By vileoxidation in forum C++ Programming
    Replies: 10
    Last Post: 10-05-2009, 08:58 AM
  2. Protected / Private Variables accessable.
    By +Azazel+ in forum C++ Programming
    Replies: 19
    Last Post: 09-08-2009, 08:39 PM
  3. Remotely Creating Variables
    By Rajin in forum C++ Programming
    Replies: 1
    Last Post: 04-27-2005, 12:20 AM
  4. Declaring an variable number of variables
    By Decrypt in forum C++ Programming
    Replies: 8
    Last Post: 02-27-2005, 04:46 PM
  5. functions to return 2 variables?
    By tim in forum C Programming
    Replies: 5
    Last Post: 02-18-2002, 02:39 PM

Tags for this Thread


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21