1 Reply Latest reply: Oct 29, 2010 12:57 PM by gdarling - oracle RSS

    How to call a stored function

      Hi guys,

      I am trying to build a stored function that receives 2 parameters from VB.Net. What I want to do is pass in a whole SQL statement and a table name and have the function run the sql in the code block as dynamic SQL. Then pass the DATA into a cursor which is passed back to the VB program as results.

      I am having trouble with the stored function compiling because I am having the cursor built on tablename as a %ROWTYPE.

      The function will not compile this way since tablename is not the name of a table but rather an argument that is received.

      Please feel free to move this to where it belongs if not here.

      Here is the SQL part of the code.


      CREATE FUNCTION ADV_query(stmt VARCHAR2, tablename varchar2)
      RETURN tablename%Rowtype IS

      AdvQueryCurs tablename%ROWTYPE;


      EXECUTE IMMEDIATE stmt INTO AdvQueryCurs;

      RETURN AdvQueryCurs;


      Here is the VB:
      Dim cmd As New OracleCommand
      cmd.Connection = conn
      cmd.CommandText = "ADV_query"
      cmd.CommandType = CommandType.StoredProcedure
      cmd.Parameters.Add(QueryTextBox.Text, OracleType.VarChar)
      cmd.Parameters.Add(TableNameStr, OracleType.VarChar)

      Dim ResultDataAdapt As New OracleDataAdapter(cmd)


      AdvancedResultsListBox.Text = ResultDataAdapt.ToString

      Any help appreciated!

      Catch fails As Exception


      End Try
        • 1. Re: How to call a stored function
          gdarling - oracle
          The PLSQL forum is where you'd want to post PLSQL questions.

          However, if you plan on calling it from a client side app, you'll want to stop right now. TABLE%ROWTYPE is a PLSQL-only type, and cant be passed to/from client side applications, which I presume you want to do since you've also provided .NET code. You probably want to look into using a REF CURSOR instead.

          Hope it helps,
          SQL> ed
          Wrote file afiedt.buf
            1  create or replace function getcurs (stmt in varchar2, tbl in varchar2) return sys_refcursor as
            2  retcur sys_refcursor;
            3  begin
            4    open retcur for stmt||' ' ||tbl;
            5    return retcur;
            6* end;
          SQL> /
          Function created.
          SQL> var abc refcursor;
          SQL> exec :abc := getcurs ('select sysdate from ','dual');
          PL/SQL procedure successfully completed.
          SQL> print abc