PL/SQL :: Calling Oracle Procedure With Two OUT Parameters
Jun 20, 2012
I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.
proc_Test (p_resultset=> My_cursor)
How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 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,
My Oracle procedure works on two parameters, file type and file name.
exec xml_trans.load_xml('OMG','Sample.xml');
First parameter is xml file type and second parameter is xml file name. XML file is generated by by a web service and keep in a particular location.
Now the requirement is my procedure should pick up the in parameters, once the xml file is generated and my procedure should be running as per schedule. I can execute the procedure in schedule but how can i pick the file name and pass to procedure as soon the xml file is generated?
I am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.
Some thing like following:
I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.
My problem is how can I issue the
SELECT details FROM CONNECTION_DETAILS for USER2
so that i can get the details..?
Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.
I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?
I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'
Here is my procedure:
create or replace PROCEDURE INSERTINTOPHYSLOG AS prec_num long; srec_num long;
The application I am working on uses Oracle HTP procedures to generate HTML pages for our application. I am trying to perform some cleanup when a user closes a web application by clicking the 'X' button in there browser. I am thinking of using the onUnload trigger to call a javascript function, which in turn would call a procedure in my PL/SQL pacakge for cleaning up logical locks on records.
The body tag delaration looks like this code fragment:
Note that clear_locks is a pl/sql stored procedure.So far I have gotten it to pass in the user ID correctly.Is there a way I can call my clear_locks procedure?
I have created this procedure for printing ename,sal,job as output using empno as input:-
CREATE OR REPLACE PROCEDURE p_get ( p_empno NUMBER, p_name OUT VARCHAR2, p_sal OUT NUMBER, p_job OUT VARCHAR2 ) IS BEGIN SELECT ename, sal, job INTO p_name, p_sal, p_job FROM emp WHERE empno = p_empno; END;
Now My Requirement I want to call this procedure using Positional,Named and Mix Methods...I am calling this procedure using Positional Method:-
I am calling a procedure with the following parameters
DECLARE PROFIT_CENTER NUMBER; BEGIN PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132, 1122,3211, 1111
one by one by passing values manually, it is taking almost a minute for each profit center
)Prc_test_calc ( PROFIT_CENTER); COMMIT; END; For each profit_center proc is taking 1 mins (Approx) time.
But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center. Anything wrong with the below loop
DECLARE PROFIT_CENTER NUMBER; cursor c_profit is select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); --
Plng_pc is a table from where profit_center information is getting fetched
BEGIN For pc_rec in c_profit loop Prc_test_calc ( pc_rec .PROFIT_CENTER ); COMMIT; End loop; END;
I searched, found this one hit and according to mateoc15, you cannot commit within a procedure that is called from a trigger. He must be right, because mine is not committing either, nor are any errors given.
Trigger
Create or replace trigger owner_name.table1_trg2 after update on table1 for each row call owner_name.procedure1;
procedure Code (psuedo):
Create or replace procedure1 as begin update table1 set col1 = 'whatever'; commit; exception when others then rollback;
Executing the procedure as owner_name on SQLPlus works fine, but when I update a column of the table via the PL/SQL package (on the web form), the column does not update, telling me that the procedure never fired from the trigger.
I am trying to call a Web service using the SOAP API and I get the following error.
ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side ORA-06512: at "SYS.XMLTYPE", line 48 ORA-06512: at "MyConnection.SOAP_API", line 121 ORA-06512: at "MyConnection.MyFunction", line 28 31020. 00000 - "The operation is not allowed, Reason: %s" *Cause: The operation attempted is not allowed *Action: See reason and change to a valid operation.
However we do have another procedure that is able to make an HTTP call successfully within the same domain. Hence the ACLs that are required are for making an HTTP call are in place. Is there any other security setting to be tweaked to make the to enable me to make a Webservice call using the SOAP package?
I have a procedure that checks in the table if the daily refresh was successful or not for the previous day. If it failed I would like to receive an email, which I accomplish with calling my EMAIL() procedure. Otherwise call my program_scheduler that will run a shell script.
Below is my procedure:
create or replace PROCEDURE RD_ODS_REFRESH_LOG IS BEGIN FOR i IN ( SELECT RESULT FROM RD_REFRESH_LOG
[code].....
And here is CALL_SHELL job that my procedure calls:
I am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL
It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.
Scenario is like below:
create user a identified by a; grant connect,resource to a; create user b identified by b; grant connect,resource to b; create user c identified by c; grant connect,resource to c;
[code]....
Not getting why the schema is not changing... and how to resolve the error.
I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:
CREATE OR REPLACE PROCEDURE sp_all_attribute_difference ( i_product_id_new IN NUMBER, o_product_attr_diff_list OUT product_attr_diff_list, o_usage_attr_list OUT usage_attr_diff_list ) IS [code]....
I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?
Examples: a) WebApp->Call("select * from employees where department_id = ?", 10) ;
b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);
create package mypackage is function get_emp(dep_id in number) return sys_refcursor is begin open cur for select * from employees where department_id = dep_id; end; end;========================================== Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.
In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are
"UpdateRelatedOrders" and "SetGeoFraudScore"
with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures
"UpdateRelatedOrders" and "SetGeoFraudScore".
I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk IS TYPE rec_setscores IS RECORD ( pBUID score.buid%TYPE, OrderNum score.order_num%TYPE, ScoreType VARCHAR2(100), p_Score score.velocity_score%TYPE ); [code]....
In my application, we are getting the following exception on calling the Java procedure.
java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError ORA-04030: out of process memory when trying to allocate 10804020 bytes (joxp heap,f:OldSpace)
I have a trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure. I'm not positive of this since the ability to log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION?
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
I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example: in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2) . .... .... ... SELECT COLA, COLB, COLC FROM TABLE_TEMP WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.
I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.
The code for the procedure is
CREATE OR REPLACE PROCEDURE add_new_classes (p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number p_course_id classes.course_id%TYPE, p_period classes.period%TYPE, p_frequency classes.frequency%TYPE,
[code]....
I then use this to test it
BEGIN add_new_classes(1002,'first','daily',3002); END;
and the error I get is
Quote:ORA-06550: line 2, column 4: PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES' ORA-06550: line 2, column 4: PL/SQL: Statement ignored 1. BEGIN 2. add_new_classes(1002,'first','daily',3002); 3. END;