Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Help in understanding bind variables

    I am trying to learn how to use bind variables, and to the best of my knowledge I am following the instructions in O'Reilly's 'Oracle PL/SQL'.

    For the following, DBMS_OUTPUT is not showing me the string I expect

    Code:
    CREATE TABLE forbesc.rpt (reportname VARCHAR2(10));
    Code:
    CREATE OR REPLACE PROCEDURE forbesc.writerptline (
       tablename      IN   VARCHAR2,
       reportname     IN   VARCHAR2
    )
    
    IS
       INSERT_STATEMENT   VARCHAR2 (2000);
    BEGIN
    	  
       INSERT_STATEMENT := 'INSERT INTO '
          || tablename
          || ' (REPORTNAME) '
          || 'VALUES (:REPORTNAME)';
    	  
       DBMS_OUTPUT.PUT_LINE(insert_statement);
    
    END;
    Code:
    BEGIN
    	forbesc.writerptline('forbesc.rpt','my report');
    END;
    
    shows:
    INSERT INTO forbesc.rpt (REPORTNAME) VALUES (:REPORTNAME)
    If I change it slightly and then run it with EXECUTE IMMEDIATE I get a not all variables bound error:

    Code:
    CREATE OR REPLACE PROCEDURE forbesc.writerptline (
       tablename      IN   VARCHAR2,
       reportname     IN   VARCHAR2
    )
    
    IS
       INSERT_STATEMENT   VARCHAR2 (2000);
    BEGIN
    
       EXECUTE IMMEDIATE 'INSERT INTO '
          	   			 || tablename
          				 || ' (REPORTNAME) '
          				 || 'VALUES (:REPORTNAME)';
    
    END;

    I know my error must be simple, but everytime I rewrite this, I just get a different error. Help would be much appreciated.

    Thanks,
    Chuck

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    The reason the first one simply prints out is that you are printing a string. So in you example 1, :REPORTNAME is just a string, and does not represent a variable. However, when you go to run it through execute immediate, it tries to treat it as a variable, to which it cannot find a match.

    Take out the colon part, and just put reportname as it appears in your parameter declaration list. If you are trying to run this outside of Forms or Reports, you are going to get that error, b/c you need to "define" the bind variable :REPORTNAME. In SQL*PLUS i think you use the variable command or var for short.

    But to summarize, take out the colon and it should work fine.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So, I noticed that I had 'REPORTNAME' twice in the code, and I wasn't sure what would happen once I removed the colon, since both strings looked identical at that point, so I exchanged the parameter for RPTNAME, and I still get an error of one type

    Code:
    SQL> CREATE OR REPLACE PROCEDURE forbesc.writerptline (
      2     tablename      IN   VARCHAR2,
      3     rptname     IN   VARCHAR2
      4  )
      5  IS
      6  BEGIN
      7     EXECUTE IMMEDIATE 'INSERT INTO '
      8                || tablename
      9             || ' (REPORTNAME) '
     10             || 'VALUES (rptname)';
     11  END;
     12  /
    
    Procedure created.
    
    SQL> BEGIN
      2   forbesc.writerptline('forbesc.rpt','my report');
      3  END;
      4  /
    BEGIN
    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    ORA-06512: at "FORBESC.WRITERPTLINE", line 7
    ORA-06512: at line 2
    despite the fact that the insert should work:

    Code:
    SQL> insert into forbesc.rpt (reportname) values ('my report');
    
    1 row created.
    You know how it feels when you're really close to solving something at the end of the day, but you just can't get it to work correctly. Man this is killing me. Thanks for the help.

    -Chuck

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Code:
    EXECUTE IMMEDIATE 'INSERT INTO ' || tablename || ' (REPORTNAME) VALUES (:b1)'
    USING rptname;
    Obviously this particular example is just a test case. Passing object names around would normally indicate a design weakness. It is not generally the calling procedure's business which database objects are used.
    Last edited by WilliamR; 01-31-05 at 21:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •