Assign dynamic query output to a variable RSS

• • Reply
  

  sargamlucy

  Member

  559 Points

  164 Posts

  Assign dynamic query output to a variable

  Apr 05, 2012 09:29 AM|LINK

  Hi All,

  I have a dynamic query like below.

  v_count_query := ' select count(feed_temp.*) FROM ' || v_feed_type_table || ' feed_temp';
  v_count_query := v_count_query || ' WHERE ' || v_filters;

  Now the filters and the table name are user inputs, so this query has to be a dynamic query. Does anyone knows how can I get that count(feed_temp.*) to a local variable in the package?

  Please help.

  
  Regards,
  Snigdha
  Please Mark as Answer if my reply helped you.
• • Reply
  

  mikeprince.a...

  Member

  278 Points

  54 Posts

  Re: Assign dynamic query output to a variable

  Apr 05, 2012 09:39 AM|LINK

  Use executescalar metod for count return.

  it returns integer value.

• • Reply
  

  sargamlucy

  Member

  559 Points

  164 Posts

  Re: Assign dynamic query output to a variable

  Apr 05, 2012 09:45 AM|LINK

  It's all happening inside the package and I don't think we have a ExecuteScalar method in database.

  I am trying to make something like execute immediate v_count_query into v_count;

  I dunno, if it is gonna work, I am just gonna try it out.

  
  Regards,
  Snigdha
  Please Mark as Answer if my reply helped you.
  • Edited by sargamlucy on Apr 12, 2012 10:41 AM
  • Edited by sargamlucy on Apr 12, 2012 10:47 AM
• • Reply
  

  Lannie

  Contributor

  3724 Points

  726 Posts

  Re: Assign dynamic query output to a variable

  Apr 05, 2012 03:22 PM|LINK

  Return the SELECT parameters as a REFCURSOR.

  ' Get data from stored procedure '
      Try
        Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
        Using conn As New OracleConnection(connstr)
          Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectCountContracts", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Clear()
            cmd.Parameters.Add("CountContracts", OracleDbType.RefCursor, ParameterDirection.Output)
            conn.Open()
            Using oda As New OracleDataAdapter(cmd)
              Dim ds As New DataSet()
              oda.Fill(ds)
              '  then code to get info from your dataset, you can also populate a DataReader
            End Using
          End Using
        End Using
      Catch ex As Exception
      End Try
  
  Oracle PL/SQL code
      
  CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
    PROCEDURE rcSelectCountContracts(CountContracts OUT refCursor);
  END {PACKAGENAME};
  /
  
  CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
  
  PROCEDURE rcSelectCountContracts(CountContracts OUT refCursor)
  IS
  BEGIN
    OPEN CountContracts FOR
      SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE FROM {SCHEMANAME}.VW_COUNT_CONTRACTS;
  END;
  
  END {PACKAGENAME};
  /

   

• • Reply
  

  sargamlucy

  Member

  559 Points

  164 Posts

  Re: Assign dynamic query output to a variable

  Apr 12, 2012 10:46 AM|LINK

  Thanks for the reply Lannie, but I want all this to happen inside oracle package and not in the dot net code. So, I can not use this.

  Anyway, I have found out the soluion.

  Make a dynamic query like : v_query = 'select count(*) from table_name where conditions';

  Execute immediate v_query into v_count;

  The v_count variable will be assigned the result. I am sure this will work for all those queries where we return only one value. When we return more than one value, we might have to go for curso or any other alternative.

  
  Regards,
  Snigdha
  Please Mark as Answer if my reply helped you.
  • Marked as answer by sargamlucy on Apr 12, 2012 10:46 AM