Execute Immediate: ORA-01008: not all variables bound 4

[kvang]

Anyone know what is wrong with this code?

DECLARE 
	v_deptexclude VARCHAR2(30) := 'Direct Retail Lending%';
	v_queuetype VARCHAR2(10) := 'new';
	v_catgnew VARCHAR2(10) := 'NEW';
	v_catgbacklog VARCHAR2(10) := 'BACKLOG';
	v_stepname1 VARCHAR2(10) := 'Complete';
	v_stepname2 VARCHAR2(10) := '%QA%';
	sql_stmt VARCHAR2(2000);
BEGIN
	EXECUTE IMMEDIATE 'TRUNCATE TABLE LSAPP_WORKFLOWSTATUS';
	-- Summarize the data.
	sql_stmt := 'INSERT INTO LSAPP_WORKFLOWSTATUS SELECT DEPARTMENT, DEPTINITIAL, SUM(CASE WHEN CATG = :4 THEN 1 ELSE 0 END) AS WOB_BACKLOG, SUM(CASE WHEN CATG = :4 AND CREATETIME < TRUNC(SYSDATE-7) THEN 1 ELSE 0 END) AS OLD_BACKLOG, ROUND(CASE WHEN SUM(CASE WHEN CATG = :4 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN CATG = :4 AND CREATETIME < TRUNC(SYSDATE-7) THEN 1 ELSE 0 END) / SUM(CASE WHEN CATG = :4 THEN 1 ELSE 0 END) END, 6) AS OLD_BACKLOG_PCT, SUM(CASE WHEN CATG = :4 AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND (C.STEPNAME = :5 OR C.STEPNAME LIKE :6)) THEN 1 ELSE 0 END) AS WOB_BACKLOG_COMPLETE,SUM(CASE WHEN CATG = :3 THEN 1 ELSE 0 END) AS WOB_NEW, SUM(CASE WHEN CATG = :3 AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND (C.STEPNAME = :5 OR C.STEPNAME LIKE :6)) THEN 1 ELSE 0 END) AS WOB_NEW_COMPLETE, ROUND(CASE WHEN SUM(CASE WHEN CATG = :3 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN CATG = :3 AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND (C.STEPNAME = :5 OR C.STEPNAME LIKE :6)) THEN 1 ELSE 0 END) / SUM(CASE WHEN CATG = :3 THEN 1 ELSE 0 END) END, 6) AS NEW_COMPLETE_PCT, SYSDATE AS LASTUPDATE FROM (SELECT WOBNUM AS F_WOBNUM, DEPARTMENT, DEPTINITIAL, :4 AS CATG, CREATETIME FROM LS_WOBBACKLOG A, REPORTLS.WORKTYPES B WHERE A.WORKTYPE = B.WORKTYPE AND ENQUEUETIME < TRUNC(SYSDATE-1)+17/24 UNION ALL SELECT WOBNUM AS F_WOBNUM, DEPARTMENT, DEPTINITIAL, :3 AS CATG, STARTTIME FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B WHERE A.WORKTYPE = B.WORKTYPE AND ENQUEUETIME >= TRUNC(SYSDATE-1)+17/24 AND ENQUEUETIME < TRUNC(SYSDATE)+17/24 AND DEPARTMENT NOT LIKE :1 AND USERID NOT IN (SELECT P8USERID FROM REPORTLS.BGPERFORMERS) AND STEPNAME IN (SELECT STEPNAME FROM REPORTLS.LSAPP_STEPNAME WHERE QUEUETYPE = :2)) GROUP BY DEPARTMENT, DEPTINITIAL';
	EXECUTE IMMEDIATE sql_stmt USING v_deptexclude, v_queuetype, v_catgnew, v_catgbacklog, v_stepname1, v_stepname2;
	-- Save the changes.
	COMMIT;
	EXCEPTION
	WHEN OTHERS THEN
	ROLLBACK; -- rollback the transaction
END;

I cannot figure why Oracle 8i is giving me this error: "ORA-01008: not all variables bound". Help please.

 

[BJCooperIT]

An excellent example of the need for indented code for readability.

INSERT INTO LSAPP_WORKFLOWSTATUS 
    SELECT  DEPARTMENT, DEPTINITIAL, 
            SUM(CASE WHEN   CATG = :4 
                     THEN 1 
                     ELSE 0 
                     END) AS WOB_BACKLOG, 
            SUM(CASE WHEN   CATG = :4 
                     AND    CREATETIME < TRUNC(SYSDATE-7) 
                     THEN 1 
                     ELSE 0 
                     END) AS OLD_BACKLOG, 
            ROUND(CASE WHEN SUM(CASE WHEN CATG = :4 
                                     THEN 1 
                                     ELSE 0 
                                     END) = 0 
                  THEN 0 
                  ELSE SUM(CASE WHEN CATG = :4 
                                AND CREATETIME < TRUNC(SYSDATE-7) 
                                THEN 1 
                                ELSE 0 
                                END)
                       / SUM(CASE WHEN CATG = :4 
                             THEN 1 
                             ELSE 0 
                             END) 
                  END, [COLOR=red]6[/color]) AS OLD_BACKLOG_PCT, 
            SUM(CASE WHEN CATG = :4 
                     AND EXISTS (SELECT  * 
                                 FROM    REPORTLS.RETAIL_PROCESSLOG C 
                                 WHERE   C.WOBNUM = F_WOBNUM 
                                 AND     (C.STEPNAME = :5 OR 
                                          C.STEPNAME LIKE :6)
                                ) 
                THEN 1 
                ELSE 0 
                END) AS WOB_BACKLOG_COMPLETE,
            SUM(CASE WHEN CATG = :3 
                THEN 1 
                ELSE 0 
                END) AS WOB_NEW, 
            SUM(CASE WHEN CATG = :3 
                     AND EXISTS (SELECT * 
                                 FROM REPORTLS.RETAIL_PROCESSLOG C 
                                 WHERE C.WOBNUM = F_WOBNUM 
                                 AND  (C.STEPNAME = :5 OR 
                                       C.STEPNAME LIKE :6)) 
                THEN 1 
                ELSE 0 
                END) AS WOB_NEW_COMPLETE, 
            ROUND(CASE WHEN SUM(CASE WHEN CATG = :3 
                                THEN 1 
                                ELSE 0 
                                END) = 0 
                  THEN 0 
                  ELSE SUM(CASE WHEN CATG = :3 
                                AND EXISTS (SELECT * 
                                            FROM   REPORTLS.RETAIL_PROCESSLOG C 
                                            WHERE  C.WOBNUM = F_WOBNUM 
                                            AND (C.STEPNAME = :5 OR 
                                                 C.STEPNAME LIKE :6)
                                           ) THEN 1 
                                             ELSE 0 
                                             END) 
                       / SUM(CASE WHEN CATG = :3 
                                  THEN 1 
                                  ELSE 0 END) 
                  END, [COLOR=red]6[/color]) AS NEW_COMPLETE_PCT, 
            SYSDATE AS LASTUPDATE 
    FROM  (SELECT WOBNUM AS F_WOBNUM, 
                  DEPARTMENT, 
                  DEPTINITIAL, 
                  :4 AS CATG, 
                  CREATETIME 
           FROM   LS_WOBBACKLOG A, REPORTLS.WORKTYPES B 
           WHERE  A.WORKTYPE = B.WORKTYPE 
           AND ENQUEUETIME < TRUNC(SYSDATE-1)+17/24 
           UNION ALL 
           SELECT WOBNUM AS F_WOBNUM, 
                  DEPARTMENT, 
                  DEPTINITIAL, 
                  :3 AS CATG, 
                  STARTTIME 
           FROM   REPORTLS.RETAIL_PROCESSLOG A, 
                  REPORTLS.WORKTYPES B 
           WHERE  A.WORKTYPE = B.WORKTYPE 
           AND    ENQUEUETIME >= TRUNC(SYSDATE-1)+17/24 
           AND    ENQUEUETIME < TRUNC(SYSDATE)+17/24 
           AND    DEPARTMENT NOT LIKE :1 
           AND    USERID NOT IN (  SELECT  P8USERID 
                                   FROM    REPORTLS.BGPERFORMERS) 
           AND    STEPNAME IN (    SELECT  STEPNAME 
                                   FROM    REPORTLS.LSAPP_STEPNAME 
                                   WHERE   QUEUETYPE = :2)
          ) 
GROUP BY DEPARTMENT, DEPTINITIAL

Notice the use of 6 instead of :6 in two places.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[kvang]

That is because I am trying to round a percentage to 6 decimal places. I removed the ROUND(..., 6) but it didn't help. Oh, thanks for indenting.

 

[BJCooperIT]

You are so right. I give up today, my eyes are not working.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Don't give up, Barb...Your indenting was magnificent...I wouldn't even have looked at the code without your visual redesign.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[kvang]

I wasn't able to find much help from my google searches. Any help is appreciated.

 

[SantaMufasa]

Bkou,

Since none of us has a data set that supports your code, and since you have not posted any "CREATE TABLE..." and "INSERT INTO..." statements for us to simulate your data set(s), it makes it very difficult for us to troubleshoot/isolate problems for you without transforming ourselves into "human desk-checking machines": going over every clause in your very-tedious code.

Therefore, I propose that you apply these time-honored steps in debugging:

Step 1: Go back to what works. Test individual components (subsections/subqueries) to ensure that they work.

Step 2: Re-assemble and test working components. One at a time, add back in the working components that you successfully test in Step 1. Don't go on to the next component until this subassembly works. Repeat this step until everything works together.

It's not that we don't want to help you...it's just that helping makes it extremely difficult without the tools/helps that I mentioned, above.


Either post code that creates populated tables so we can help you, or post your findings from the troubleshooting steps that I posted, above.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[kvang]

Actually, I did those steps before I posted. Running the SQL in an editor works fine, it bugs out when I try to set it up using PL/SQL. I took pieces of it out in the PL/SQL code, but that didn't help either. Here's is my table schema that I am trying to fill:

CREATE TABLE LSAPP_WORKFLOWSTATUS
(
  DEPARTMENT            VARCHAR2(50),
  DEPTINITIAL           VARCHAR2(20),
  WOB_BACKLOG           NUMBER,
  OLD_BACKLOG           NUMBER,
  OLD_BACKLOG_PCT       NUMBER,
  WOB_BACKLOG_COMPLETE  NUMBER,
  WOB_NEW               NUMBER,
  WOB_NEW_COMPLETE      NUMBER,
  NEW_COMPLETE_PCT      NUMBER,
  LASTUPDATE            DATE
)

Again, thanks for taking the time to help.

 

[SantaMufasa]

it bugs out when I try to set it up using PL/SQL.

This I suggest you starting from:

DECLARE
    sql_stmt VARCHAR2(2000);
BEGIN
    sql_stmt := 'insert into LSAPP_WORKFLOWSTATUS values ('
                ||'NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)';
    execute immediate sql_stmt;
    commit;
end;
/

Once that works, then start adding back in your tested components.


Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[karluk]

It appears that the execute immediate treats variable substitution differently depending on whether you are executing a SQL statement or a pl/sql block. SQL does not allow you to repeatedly reuse the same substitution variables the way pl/sql does.

To simplify your complex sql, I created a table called emp_table

create table emp_tab (emp_id integer,
                      monthly_salary number (9,2),
                      bonus number(9,2));

I then attempted to insert an employee with a monthly salary of $6,000 and a bonus equal to the monthly salary. That reproduced your ORA-01008 error:

  1  DECLARE
  2     sql_stmt    VARCHAR2(200);
  3     v_emp_id    integer := 1001;
  4     v_salary    NUMBER(4) := 6000;
  5  BEGIN
  6     sql_stmt := 'INSERT INTO emp_tab VALUES (:1, :2, :2)';
  7     EXECUTE IMMEDIATE sql_stmt USING v_emp_id, v_salary;
  8* end;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 7

However, I can fix the error by wrapping the insert statement between a "begin" and an "end", thus converting it into a pl/sql block.

  1  DECLARE
  2     sql_stmt    VARCHAR2(200);
  3     v_emp_id    integer := 1001;
  4     v_salary    NUMBER(4) := 6000;
  5  BEGIN
  6     sql_stmt := 'BEGIN INSERT INTO emp_tab VALUES (:1, :2, :2); END;';
  7     EXECUTE IMMEDIATE sql_stmt USING v_emp_id, v_salary;
  8* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from emp_tab;

    EMP_ID MONTHLY_SALARY      BONUS
---------- -------------- ----------
      1001           6000       6000

So, I suggest that you try the same thing with your execute immediate - wrapping the sql into a begin ... end block. There is a good chance that will fix the error.

 

[jimirvine]

Maybe this'll help:

DROP TABLE foo;
CREATE TABLE foo (eg NUMBER, x NUMBER, y NUMBER, z NUMBER);
--Example 1
DECLARE
  l_x NUMBER := 1;
  l_y NUMBER := 2;
  l_z NUMBER := 3;
  l_str VARCHAR2(32767);
   err_line EXCEPTION;
  PRAGMA exception_init(err_line, -01008);
BEGIN
  BEGIN 
  EXECUTE IMMEDIATE 'INSERT INTO foo
                    VALUES(1, :1, :1, :2)' USING l_x, l_z;
  EXCEPTION
    WHEN err_line THEN
     dbms_output.put_line('error on insert 1');
  END;
  BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO foo
                    VALUES(2, :1, :1, :2)' USING l_x, l_x, l_y;
    EXCEPTION
    WHEN err_line THEN
     dbms_output.put_line('error on insert 2');
  END;
  BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO foo
                    VALUES(3, :1, :1, :2)' USING l_x, l_y, l_z;
  EXCEPTION
    WHEN err_line THEN
     dbms_output.put_line('error on insert 3');
  END;
END;
/
SELECT * FROM foo;

 

[kvang]

Thanks karluk for trying to help. Unfortunately, because I using Oracle 8i, CASE statement is not supported in PL/SQL. That is the reason why I am trying to use the execute immediate method.

 

[BJCooperIT]

I went through and replaced each bind variable with the name of your v_something variable and the ORA-01008 error went away. Karluk is correct.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[karluk]

If you need to run this as sql rather than pl/sql, then the obvious solution is to adopt BJCooper's suggestion of expanding the USING clause of your Execute Immediate to include one variable for every substitution. I hesitated to suggest this solution, since I count 21 substitutions in your code which would make the code a little messy. But this approach seems to work. At least it worked for BJ and I also tried it in one of my 9i databases. Unless there is some problem specific to 8i, it will probably work for you too.

For my code above, the SQL solution is

DECLARE
   sql_stmt    VARCHAR2(200);
   v_emp_id    integer := 1001;
   v_salary    NUMBER(4) := 6000;
BEGIN
   sql_stmt := 'INSERT INTO emp_tab VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING v_emp_id, v_salary, v_salary;
END;

 

[kvang]

Thank you. I recoded to reflect your last example and it worked. Thanks so much for everyone's help.