Ora-01008 from PL/SQL

From: Greg Akins <insomnia_at_cvzoom.net>
Date: Mon, 21 Oct 2002 06:55:07 -0400
Message-ID: <3db3d668$1@post.usenet.com>

• Post for FREE via your newsreader at post.usenet.com ****

Hi,

I've been getting an ORA-01008: Not all variables bound error while running a PL/SQL block. It happens whether I run from a stored procedure or from an anonymous block.

I get the error on a 7.3.4 database, but not an 8.1.7.

The code is follows below.

Within the testcur cursor, I use strDatasource to pass a value. The cursor is where the error is occurring. If I repalce strDatasource with a literal string, the the error doesn't occur. Any idea why this might be happening? I looked through Metalink and the only references I could find were for unrelated bugs and issues with bind variables.

Any help would be greatly appreciated.

-greg

---

CREATE or replace PROCEDURE testproc IS
--Leveraged Facts Table

 v_bu_id DASHBD_DBA.LEVERAGED_FACTS.BU_ID%TYPE ;  v_act_percentage DASHBD_DBA.LEVERAGED_FACTS.ACT_PERCENTAGE%TYPE;

 strDatasource varchar2(20) := 'LEV';
 strErrorType varchar2(50) := 'Load From Leveraged Staging Table';  counter number ;

cursor testcur is SELECT v_bu_id, YEAR, QUARTER, sum(ACT_PERCENTAGE) as v_act_percentage

   FROM (SELECT f_retrieve_bu(trim(BU_ID),strDatasource) v_bu_id,    YEAR, QUARTER, ACT_PERCENTAGE
          FROM LEVERAGED_STAGING)
  group by v_bu_id, YEAR, QUARTER
  order by v_bu_id, YEAR, QUARTEr ;

BEGIN
--Delete initial errors from the error table before executing
 delete from DASHBD_DBA.ERROR_LOG
 where datasource_name = strDatasource
 and UPPER(error_type) = UPPER(strErrorType);

    counter := 0 ;
 For LEV_FACTS in testcur loop

     BEGIN
     counter := counter + 1 ;

   if LEV_FACTS.v_bu_id <> 0 then
   dbms_output.put_line('Test '||v_bu_id) ;     --INSERT INTO DASHBD_DBA.LEVERAGED_FACTS

• (BU_ID, YEAR, QUARTER, ACT_PERCENTAGE) --VALUES (LEV_FACTS.v_bu_id, LEV_FACTS.YEAR, LEV_FACTS.QUARTER, LEV_FACTS.v_act_percentage); end if; END; end loop; end testproc ;

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Mon Oct 21 2002 - 05:55:07 CDT