Windows / .NET :: Call External Dll From Stored Procedure
Jun 6, 2013
I have function in stored procedure which call function from another .net dll, is a first step. And this dll call function from unmanaged dll, is a second step.I do not have any problem in the first step, but when i do second step i have error:
ORA-20100: System.Security.SecurityException
System.Security.Permission.SecurityPermission
at ...
at ...
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: ...
ORA-06512: ...
when i am deploying proceduse i set security levels "external".
We have a stored procedure named proc_java_test . From this stored proc, we need to call a java class named java_test.java
This class posts messages to a queue hosted on an app server running on a separate box.
create or replace PROCEDURE PROC_JAVA_TEST ( num IN NUMBER ) AS LANGUAGE JAVA NAME 'java_test.post(int) ';
Is it mandatory to place java files in oracle_home/bin ? I tried placing the file on some other location and then calling it from stored proc. It did not work. Can we point oracle to read java file from location other than oracle_home/bin?
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8) as begin Execute sp1 param1...param6 rollback if any error Execute sp2 param1...param8 rollback if any error Execute sp3 param1...param4 rollback if any error end;
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.
The Store procedure definition looks like :
CREATE OR REPLACE FUNCTION SP( srq_id integer , unid IN SPkg.arr_parmid, parm_typ IN SPkg.arr_parm_typ,
[code].....
Type definitions
TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER; TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER; TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER; TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER; TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;
Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF") a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?
[code]...
Now, i want to convert this into oracle,plsql? Is it possible or not?
I am trying to return a value of an oracle stored procedure using Excel VBA.
OS: Win XP SP3 Excel 2003 Ora Client: 11g
By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:
'----------------------- Sub Extract_Data() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim db_name, UserName, Password As String cn.Open db_name, USerName, Password
[code]....
When debugging the connection string, I find the Provider=MSDASQL5.1.
when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14
PROCEDURE SP_SELCT_EMPLOYEE_DATA ( -- A_EMPLOYEE_ID IN VARCHAR2, --A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE, P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE, P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE, P_EMAIL IN EMPLOYEES.EMAIL%TYPE,
I am trying to invoke program from my local Windows machine (exactly java executor, which is in my environment on c:javajdkinjava) using database package with Java stored procedure.
Everything is fired by SQLPLUS script, this script invokes package with Java stored procedure (jar loaded into Oracle) and that procedure is trying to invoke runtime process (syntax:
c:javajdkinjava -cp
and here ara my classes and main jar) using my local java executor. I added all permissions using dbms_java.grant_permisions (execution forced me to give permissions java.io.FilePermission to <<ALL_FILES>> for execute), moreover I added to my database user JAVASYSPRIV and JAVAUSERPRIV role. Unfortunately, beside this I am still getting error:
C:javajdkinjava not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)
I cannot run a Scheduler remote external job on Windows. Here's what I'm doing:
--Check that XDB isinstalled: desc resource_view --Check that MTS is running sho parameters dispatcher sho parameters shared_Server --Setup an http listening port: exec dbms_xdb.sethttpport(8080)
[code].....
This is the error:
orcl> select job_name, status, error#, actual_start_date, additional_info 2 from user_scheduler_job_run_details where job_name='TRYIT'; TRYIT FAILED -1.074E+09 15-JUN-11 02.16.54.583000 EUROPE/LISBON
[code].....
I am certain that the communications and the credentials are correct, I've tried variants and get different errors. I think the problem is the job action. I've tried running batch files as well as OS commands, same result. THere is nothing useful in the core dump. Is there perhaps a Windows specific technique for running external jobs? Some way of nominating the batch file, or specifying a command interpreter?
I am trying to call a shell script through external procedure. To summarize the steps that I followed:
Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.
Then I logged into the sqlplus. Created one library pointing this .so file.
create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';
After this created the external procedure. This got compiled without any errors.
SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2) as external library ext_proc NAME "execute_cmd" parameters (in_script string); / Procedure created.
I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.
However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
I have a procedure(used to delete the data from all tables) which taking two parameters as input. like EXCS_kiwldate(1,'keepitems')[/color][/size] but it taking only one parameter at a time if i want to delete all data using that procedure by calling it in a cursor
how to write a cursor by giving loop function to delete the data once.
I install Oracle instant client 11g in my Windows 7(32bit), and use MSVC6++ to test program occidml.cpp, compile is no problem, but when I run it, it crash on create Connection. I have tried to link with different version of OCCI library ( v8,v9), but all crash on create Connection.
I am trying to call procedure inside trigger.. but i get error ora-04098 ..
create table emp_hstry as select * from emp where 1= 2 ;
create or replace procedure emp_del_hstry(v_empno NUMBER , v_ename VARCHAR2, v_job VARCHAR2) is insert into emp_hstry (empno,ename,job) values (v_empno,v_ename,v_job); COMMIT; end;
create or replace trigger emp_del_hstry1 after insert or delete on emp for each row begin if deleting then emp_del_hstry(:old.empno,:old.ename,:old.job); end if; end;
delete from emp where empno = '7369'
AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..
I work with oracle 10g, on windows 7. sometime when i try to start my sqlplus in prompt command this message display.
C:UsersSony>sqlplus SP2-1503: Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly
But the solution people have proposed into the forum are:
1 - check the environment variable Oracle_home and modify if it ending by ""
2 - or try tho run my promt command as Administrator.
I try the both, but only the second works. while now i don't understand why it works when i start the promt command as Administrator and why it no works when i don't run it like administrator. Another thing is i am the only user of my computer, i have only one session (administor session).
When My C# program executes the cmd.ExecuteNonQuery(), the following exception is thrown:
[System.Data.OracleClient.OracleException] = {"ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'VALIDATE_PATIENT_NEW' ORA-06550: line 1, column 7: PL/SQL: Statement ignored "}
Here is my Stored Procedure:
CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW ( P_LAST_NAME IN ogen.gen_m_patient_mast.last_name%TYPE, P_FIRST_NAME IN ogen.gen_m_patient_mast.first_name%TYPE, P_DOB IN OGEN.GEN_M_PATIENT_MAST.BIRTH_DATE%TYPE, -- timestamp DEFAULT NULL, c_dbuser OUT SYS_REFCURSOR
