Did you check out the Dynamic VDBs? they can be used now to define the views, where you provide the virtual procedure tranformation and its will be compiled by the engine when you deploy.
Otherwise can you explain what you are trying to do, I will try to offer a solution based on your usecase.
Thank you for your response Ramesh.
I understood one way of defining procedure DDL is when VDB definition is built at startup.
To better explain why I asked the question I can give an example. I am looking for an easy way to add virtual procedure utilizing temp table if the last one defined and created after VDB is already started.
You have dynamic SQL so looks like there is a way to bind parameters.
From current code samples that I work with temp. table DDLs are built within Java code with SQL code snippets and variables concatenations. I was wondering if there is a way to provide DDL as a separate my_virtual_procedure.sql file and parameter binding with ? for example so in my Java code when I know temp table name I can pass it as binded parameter, something like
CREATE VIRTUAL PROCEDURE
SELECT A FROM ?;
... do some other staff
and then execute it something like:
dbExecutor.execute(SELECT * FROM (EXEC my_virtual_procedure(curr_temp_table_name)...) AS x);
Appreciate the reply, is there any working code example I can look at?
There is an example in that link I provided. There may be test cases in source code too.
Dynamic sql executes withint the scope of a procedure. So the temp table would have to be defined with-in the procedure to be referenced. We don't yet support cursor/table type references so you have to do fairly messy workarounds if you want to pass a resultset/table reference into a procedure as a parameter. So while you can do something like:
EXECUTE IMMEDIATE 'select * from ' || tblName AS x string, y integer INTO #TEMP;
fom within a procedure and then operate over #temp, tblName would have to reference a table created in the procedure or be a non-session table.