[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

ORA-01008: not all variables bound

Hello all;
the select stm work but when we try to use it an insert we get
ORA-01008: not all variables bound


variable vCA_MONTH    VARCHAR2(20 BYTE);
variable vCA_YEAR     VARCHAR2(20 BYTE);
variable vCreateby   VARCHAR2(20 BYTE);
variable vmodby      VARCHAR2(20 BYTE);
variable vBeginDate   varchar2(11);
variable vEndDate     varchar2(11);



EXEC :vCA_MONTH    :='January';
EXEC :vCA_YEAR     :='2010';
EXEC :vCreateby    :='aschema';
EXEC :vmodby       :='aschema';
EXEC :vBeginDate   :='01/01/2010';
EXEC :vEndDate     :='01/31/2010';

--'MM/DD/YYYY'

INSERT /*+ append*/
      INTO aschema.CA_REPORTING (dealer_NUMBER,
                               review_NUMBER,
                               provname,
                               kh_Total,
                               kh_start,
                               ca_month,
                               ca_year,
                               Z_CREATE_DATE,
                               Z_CREATED_BY,
                               Z_MODIFIED_BY,
                               Z_MODIFIED_DATE)
   SELECT   DISTINCT tm.provid AS dealer_NUMBER,
                     CB.review_NUMBER,
                     tm.provname,
                     qq.kh_Total AS kh_Total,
                     CB.kh_start AS kh_start,
                     :vCA_MONTH AS ca_month,
                     :vCA_YEAR AS ca_year,
                     SYSDATE,
                     :vCreateby,
                     :vmodby,
                     SYSDATE
     FROM   cschema.p01_tblmaster tm,
            bschema.pacs_review pp,
            bschema_reports.CA_start CB,
            (  SELECT   provid,
                        TO_CHAR (SUM (total), '99999999999.00') AS kh_Total
                 FROM   cschema.ordereview
                WHERE   billing_date BETWEEN TO_DATE (:vBeginDate,
                                                      'MM/DD/YYYY')
                                         AND  TO_DATE (:vEndDate, 'MM/DD/YYYY')
             GROUP BY   provid) qq
    WHERE       tm.provid = pp.dealer_number
            AND tm.provid = qq.provid(+)
            AND CB.dealer_NUMBER = PP.dealer_NUMBER
            AND CB.dda_start IS NULL
            AND CB.kh_start IS NOT NULL
0
it-rex
Asked:
it-rex
  • 2
1 Solution
 
it-rexAuthor Commented:
looks like a TOAD issue sqlplus is running fine!
0
 
slightwv (䄆 Netminder) Commented:
>>looks like a TOAD issue sqlplus is running fine!

I'm glad to hear that.  I've been trying to find where the error is since you asked.

I'm not a Toad person but I believe there is a section that will 'execute as script' that should allow this to run.
0
 
it-rexAuthor Commented:
slightwv it is a bug with ,my TOAD,
even if you run as a script!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now