Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: inconsistency sql and dbms_sql?

Re: inconsistency sql and dbms_sql?

From: Brian Price <bprice2000_at_sprintmail.com>
Date: 1998/01/06
Message-ID: <68v3oo$6je$1@newsfep3.sprintmail.com>#1/1

Hi,

When using DBMS_SQL and constructing your sql_string, you have to treat pl/sql variables differently. You either have to expand then during the construction or you have to bind them.

For example, to expand your variables during construction:

     sql_string := 'insert into Z ( X1) values ('||q1||')';

pl/sql variables are not automatically expanded, they have to be bound.

Or you can bind then by

    sql_string := 'insert into z (x1) values (:q1)';     dbms_sql.parse(cursor_handle, sql_string, dbms_sql.native);     dbms_sql.bind_variable(cursor_handle, 'q1', :q1);     ...

The ":q1" in the string does not have to have the same name as to the variable to which you will be binding the variable to; it is merely a place holder: it could be :xxx, as long as :xxx is bound to your local pl/sql variable q1. However, it is cleaner and more easily understood if your bind variable name matches your local pl/sql variable (as in the example above).

I think binding them is easier then trying to expand, especially when working with dates and numbers. Also, if you have the book Oracle PL/SQL from O'Reilly written by Steven Feuerstein, you can refer to page 512.

Brian Received on Tue Jan 06 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US