7 Replies Latest reply: Oct 21, 2008 9:38 AM by alinux RSS

    ORA-01006: bind variable does not exist

    alinux
      Hi guys,
      1.               EXECUTE IMMEDIATE 'SELECT EXTRACT (XMLTYPE (:VC_POSTED_CHARGES),''/result//post[:VN_COUNT]/result'')                    
                        FROM DUAL' INTO VX_XML USING VC_POSTED_CHARGES, VN_COUNT;
      
      2./*                  SELECT EXTRACT (XMLTYPE (VC_POSTED_CHARGES),'/result//post['|| VN_COUNT ||']/result') 
                        INTO VX_XML 
                        FROM DUAl;*/
      What is wrong with the first query?

      the second one is working fine, but I wanted to 'share' it.

      BANNER
      ----------------------------------------------------------------
      Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
      PL/SQL Release 9.2.0.7.0 - Production
      CORE     9.2.0.7.0     Production
      TNS for Solaris: Version 9.2.0.7.0 - Production
      NLSRTL Version 9.2.0.7.0 - Production


      Thanks for your time
        • 1. Re: ORA-01006: bind variable does not exist
          Nicolas.Gasparotto
          Try to put your expression into a variable, and output it to your screen. However, I don't understand what you are meaning by "share".

          Nicolas.
          • 2. Re: ORA-01006: bind variable does not exist
            alinux
            I wanted to use bind variables so this sql to be shared between exectutions. no?
            • 3. Re: ORA-01006: bind variable does not exist
              Nicolas.Gasparotto
              Are you talking about PL/SQL ?
              No, that doesn't give more sharing.

              Here without explicit bind variable, the variable is translated internally as a bind variable, and query is shared anyway :
              SQL> declare
                2      v_oprid varchar2(30);
                3      v_res   number;
                4  begin
                5      v_oprid:='PS';
                6      select count(*) into v_res from psoprdefn where oprid = v_oprid;
                7      v_oprid:='NLGASN';
                8      select count(*) into v_res from psoprdefn where oprid = v_oprid;
                9  end;
               10  /
              
              PL/SQL procedure successfully completed.
              
              SQL> disc
              Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              SQL> quit
              oracle@:/appl/oracle/admin/cdev10/udump# ls
              cdev10_ora_839782.trc
              oracle@:/appl/oracle/admin/cdev10/udump# tkprof cdev10_ora_839782.trc cdev10_ora_839782.out sys=no
              
              TKPROF: Release 10.2.0.4.0 - Production on Tue Oct 21 14:09:40 2008
              
              Copyright (c) 1982, 2007, Oracle.  All rights reserved.
              
              
              oracle@:/appl/oracle/admin/cdev10/udump# more cdev10_ora_839782.out
              
              TKPROF: Release 10.2.0.4.0 - Production on Tue Oct 21 14:09:40 2008
              
              Copyright (c) 1982, 2007, Oracle.  All rights reserved.
              
              Trace file: cdev10_ora_839782.trc
              Sort options: default
              
              ********************************************************************************
              count    = number of times OCI procedure was executed
              cpu      = cpu time in seconds executing
              elapsed  = elapsed time in seconds executing
              disk     = number of physical reads of buffers from disk
              query    = number of buffers gotten for consistent read
              current  = number of buffers gotten in current mode (usually for update)
              rows     = number of rows processed by the fetch or execute call
              ********************************************************************************
              
              declare
                  v_oprid varchar2(30);
                  v_res   number;
              begin
                  v_oprid:='PS';
                  select count(*) into v_res from psoprdefn where oprid = v_oprid;
                  v_oprid:='NLGASN';
                  select count(*) into v_res from psoprdefn where oprid = v_oprid;
              end;
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.01       0.01          0          4          0           0
              Execute      1      0.00       0.10          0          4          0           1
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2      0.01       0.12          0          8          0           1
              
              Misses in library cache during parse: 1
              Optimizer mode: CHOOSE
              Parsing user id: 26
              ********************************************************************************
              
              SELECT COUNT(*)
              FROM
               PSOPRDEFN WHERE OPRID = :B1
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        2      0.00       0.00          0          0          0           0
              Execute      2      0.00       0.00          0          0          0           0
              Fetch        2      0.00       0.10          2          4          0           2
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        6      0.00       0.10          2          4          0           2
              Same example with explicit bind variable in the code :
              SQL> alter session set sql_trace=true;
              
              Session altered.
              
              SQL> declare
                2      v_oprid varchar2(30);
                3      v_res   number;
                4  begin
                5      v_oprid:='PS';
                6      execute immediate 'select count(*) from psoprdefn where oprid = :b1' into v_res using v_oprid;
                7      v_oprid:='NLGASN';
                8      execute immediate 'select count(*) from psoprdefn where oprid = :b1' into v_res using v_oprid;
                9  end;
               10  /
              
              PL/SQL procedure successfully completed.
              
              SQL> disc
              Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              SQL> quit
              oracle@:/appl/oracle/admin/cdev10/udump# ls
              cdev10_ora_839810.trc
              oracle@:/appl/oracle/admin/cdev10/udump# tkprof cdev10_ora_839810.trc cdev10_ora_839810.out sys=no
              
              TKPROF: Release 10.2.0.4.0 - Production on Tue Oct 21 14:12:04 2008
              
              Copyright (c) 1982, 2007, Oracle.  All rights reserved.
              
              
              oracle@:/appl/oracle/admin/cdev10/udump# more cdev10_ora_839810.out
              
              TKPROF: Release 10.2.0.4.0 - Production on Tue Oct 21 14:12:04 2008
              
              Copyright (c) 1982, 2007, Oracle.  All rights reserved.
              
              Trace file: cdev10_ora_839810.trc
              Sort options: default
              
              ********************************************************************************
              count    = number of times OCI procedure was executed
              cpu      = cpu time in seconds executing
              elapsed  = elapsed time in seconds executing
              disk     = number of physical reads of buffers from disk
              query    = number of buffers gotten for consistent read
              current  = number of buffers gotten in current mode (usually for update)
              rows     = number of rows processed by the fetch or execute call
              ********************************************************************************
              
              declare
                  v_oprid varchar2(30);
                  v_res   number;
              begin
                  v_oprid:='PS';
                  execute immediate 'select count(*) from psoprdefn where oprid = :b1' into v_res using v_oprid;
                  v_oprid:='NLGASN';
                  execute immediate 'select count(*) from psoprdefn where oprid = :b1' into v_res using v_oprid;
              end;
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.00          0          0          0           0
              Execute      1      0.00       0.00          0          0          0           1
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2      0.00       0.00          0          0          0           1
              
              Misses in library cache during parse: 1
              Optimizer mode: CHOOSE
              Parsing user id: 26
              ********************************************************************************
              
              select count(*)
              from
               psoprdefn where oprid = :b1
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        2      0.00       0.00          0          0          0           0
              Execute      2      0.00       0.00          0          0          0           0
              Fetch        2      0.00       0.00          0          4          0           2
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        6      0.00       0.00          0          4          0           2
              Is there any differences ? No.

              Nicolas.
              • 4. Re: ORA-01006: bind variable does not exist
                Sven W.
                alinux wrote:
                I wanted to use bind variables so this sql to be shared between exectutions. no?
                You don't need sql to do this task. You are already in pl/sql. Why not stay with pl/sql?

                You can simply use the methods already available with the xmltype. Solution may depend on the database version.
                   vx_xml :=xmltype(VC_POSTED_CHARGES).extract('/result//post['|| VN_COUNT ||']/result');
                • 5. Re: ORA-01006: bind variable does not exist
                  alinux
                  Ohh...thanks a lot...I did not know this sollution.

                  If I only want to extract one value?! extractvalue does not work

                  Thanks

                  Edited by: alinux on Oct 21, 2008 4:23 PM
                  • 6. Re: ORA-01006: bind variable does not exist
                    alinux
                    I can see that in your solutiion the variable is in the WHERE clause.

                    But in my case I litteraly attach vn_count to my select. You are saying that it will use binf variable also?

                    '/result//post['|| VN_COUNT ||']/result') .. I will give it a try.

                    Thanks
                    • 7. Re: ORA-01006: bind variable does not exist
                      alinux
                      I did not manage to find a list of all the methods that XMLTYPE has.

                      Can tou help me?

                      thanks

                      Edited by: alinux on Oct 21, 2008 5:38 PM