DevOps, CI/CD and Automation

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