Re: Variables in select statement?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Sep 1999 11:34:46 GMT
Message-ID: <37d260b1.55762141@newshost.us.oracle.com>

A copy of this was sent to "Nicky" <nickyterwel_at_hotmail.com> (if that email address didn't require changing) On Thu, 2 Sep 1999 09:08:23 +0200, you wrote:

>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:37d145a3.103942241_at_newshost.us.oracle.com...
>> A copy of this was sent to "Nicky" <nickyterwel_at_hotmail.com>
>> (if that email address didn't require changing)
>> On Wed, 1 Sep 1999 17:06:28 +0200, you wrote:
>>
>> >This is what we are trying to do, but it doesn't work.
>> >
>> >select strColumnName1 from strTableName where strColumnName2 =
>strValue;
>> >
>> >When we don't use the variables but the real values instead it works just
>> >fine.
>> >What am I doing wrong?
>> >
>> >Nicky Terwel
>> >
>> >
>>
>>
>> You need to use dynamic sql to do that (replace column and tablenames) in
>a
>> query. The query needs to be parsed before each and every execution.
>>
>> what language are you using?
>
>We are using PL\SQL in Oracle procedure builder.
>The function looks like this:
>
>function NameOfFunction (strColumnName1 varchar2, strTableName varchar2,
>strColumnName2 varchar2, strValue integer) return number is
>
>Declares
>....
>...
>
>Begin
>
>....
>....
>....
>
> select strColumnName1
> into numResult
> from strTableName
> where strColumnName2 = strValue;
>
>return numResult;
>
>End
>

The function would look like this then:

tkyte_at_8.0> create or replace function dynquery( strCname1 in varchar2,

  2                                         strTname  in varchar2,
  3                                         strCname2 in varchar2,
  4                                         strValue  in integer ) return number
  5  is
  6      l_theCursor     integer default dbms_sql.open_cursor;
  7      l_columnValue   number;
  8      l_status        integer;
  9      l_query         varchar2(1000) default 'select ' || strCname1 || '
 10                                                from ' || strTname || '
 11                                           where ' || strCname2 || ' = :x';
 12  begin
 13      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 14      dbms_sql.bind_variable( l_theCursor, ':x', strValue );
 15      dbms_sql.define_column( l_theCursor, 1, l_columnValue );
 16  
 16      l_status := dbms_sql.execute(l_theCursor);
 17  
 17      if ( dbms_sql.fetch_rows(l_theCursor) <= 0 ) then
 18          l_columnValue := NULL;
 19      else
 20          dbms_sql.column_value( l_theCursor, 1, l_columnValue );
 21      end if;
 22      dbms_sql.close_cursor(l_theCursor);
 23  
 23      return l_columnValue;
 24  exception
 25      when others then
 26          if ( dbms_sql.is_open( l_theCursor ) ) then
 27              dbms_sql.close_cursor( l_theCursor );
 28          end if;
 29          RAISE;

 30 end ;
 31 /

Function created.

tkyte_at_8.0>
tkyte_at_8.0> exec dbms_output.put_line( dynquery( 'sal', 'emp', 'empno', 7902 ) ); 3000

PL/SQL procedure successfully completed.

>> what environment are you in?
>>
>> then, we might be able to help you out.
>>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 06:34:46 CDT