This discussion is archived
1 Reply Latest reply: Oct 29, 2010 10:57 AM by gdarling - oracle RSS

How to call a stored function

805197 Newbie
Currently Being Moderated
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.



SET SERVEROUTPUT ON
SET VERIFY OFF


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


AdvQueryCurs tablename%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE stmt INTO AdvQueryCurs;

RETURN AdvQueryCurs;

END;
/



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)

Try

ResultDataAdapt.Fill(ResultDS)
AdvancedResultsListBox.Text = ResultDataAdapt.ToString

Any help appreciated!

Catch fails As Exception

MessageBox.Show(fails.Message.ToString)

End Try
  • 1. Re: How to call a stored function
    gdarling - oracle Expert
    Currently Being Moderated
    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,
    Greg
    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
    
    SYSDATE
    ---------
    29-OCT-10
    
    SQL>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points