Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My requirement is to trigger PL/SQL Stored Procedure on Oracle DB. On successful completion of this Stored Procedure I need to reload my Application. Please post your suggestions and approach for resolving this.
Thanks,
Nag
Hi Nagendra,
Check this thread
Example:
sql exec ('[ServerName].[SchemaName].[dbo].[storedProcedureName] $(ParameterName) ' ) ; -- With one parameter
or
sql exec ('[dbo].[storedProcedureName] $(ParameterName) ' ) ;-- With one parameter
or
sql exec ('[dbo].[storedProcedureName]') -- without parameters
Regards,
Jagan.
Hi Jagan,
Thanks for the reply.
When I use the below script :
ODBC CONNECT32 TO [C2P;DBQ=c2p] (XUserId is XXXXXXXXXX, XPassword is XXXXXXXXXX);
sql exec ('c3tool.c3_inserts_prd');
And try to reload my Qlikview document. It's throwing the below error:
[Oracle][ODBC]Syntax error or access violation.
Thanks,
Nagendra
these 2 works on my oracle (only with odbc, not with oledb)
ODBC CONNECT32 TO [DEV11ORACLE;DBQ=DEV11] (XUserId is xxxxxxx, XPassword is xxxxxxxxxxxxx); |
set vTable='%E%';
Table:
LOAD *;
SQL {? = CALL f_get_tables() } ;
//SQL {? = CALL pkg1.f_get_tables2('$(vTable)') } ;
this is the oracle function
CREATE OR REPLACE function TEST.f_get_tables return sys_refcursor
AS
c1 sys_refcursor;
BEGIN
OPEN C1 FOR
SELECT TABLE_NAME FROM user_tables;
RETURN C1;
END;
/
and this is the package
CREATE OR REPLACE PACKAGE TEST.pkg1 AS
TYPE t_cursor IS REF CURSOR;
function f_get_tables2 (p_table varchar2) return t_cursor;
END pkg1;
/
CREATE OR REPLACE PACKAGE BODY TEST.pkg1 AS
function f_get_tables2 (p_table varchar2) return t_cursor
AS
c1 t_cursor;
BEGIN
OPEN C1 FOR
SELECT TABLE_NAME FROM user_tables where table_name like p_table;
RETURN C1;
END;
END pkg1;
/
Hi Nagendra
I solved this issue using webservices to trigger procedures. I call the webservice and wait the return. The webservice does all the job. Look an example for a help at http://community.qlik.com/docs/DOC-3105
Hi Marcio, can you post an example about refcursors and webservices?
Hi.
Here is an application I used to test webservices.
To sse how I did it, see the code under "Macros" text object.
Márcio
Hello,
I'm facing the same issue. I'm trying to execute a stored procedure without Parameters (this proc will insert a entry to table)
Error message:
SQL##f - SqlState: 37000, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Syntax error or access violation.
my script :
ODBC CONNECT TO [DB]:
SQL EXEC Schemaname.Procname;
If you could help me with any suggestions, that would be really great.
Thanks.
Hi Suresh,
This is the syntax for SQL Server, you convert this to Oracle.
My be try
SQL EXECUTE Schemaname.Procname;
Hope this helps you.
Regards,
jagan.
Hi Jagan,
I tried that aswell, but continues to throw the same error.
Thanks