locked
Assign dynamic query output to a variable RRS feed

  • Question

  • User-1685971342 posted

    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.

    Thursday, April 5, 2012 5:29 AM

Answers

  • User-1685971342 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 6:46 AM

All replies

  • User-1681926258 posted

    Use executescalar metod for count return.

    it returns integer value.

    Thursday, April 5, 2012 5:39 AM
  • User-1685971342 posted

    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.

    Thursday, April 5, 2012 5:45 AM
  • User269602965 posted

    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};
    /

     

    Thursday, April 5, 2012 11:22 AM
  • User-1685971342 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 6:46 AM