Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


How to call a procedure from another procedure with IN and OUT parameters


Hi Here is the my procedure with parameters  create or replace PROCEDURE AUTOALLOTSHIFTS_PROC ( P_SHIFTPATTERNCODE IN VARCHAR2, P_EMPNO IN VARCHAR2, P_STARTDATE IN DATE, P_CREATEDBY IN VARCHAR2, P_INSORUPD IN NUMBER, CUR_OUT OUT SYS_REFCURSOR )  how can call this procedure with my parameters, I want to call the procedure with 5 IN parameters and 1 cursor out parameters how can I get the out variable result.

Check  http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_pc.htm http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_app_dbms_aw026.htm

 Hi Here is the my procedure with parameters  create or replace PROCEDURE AUTOALLOTSHIFTS_PROC ( P_SHIFTPATTERNCODE IN VARCHAR2, P_EMPNO IN VARCHAR2, P_STARTDATE IN DATE, P_CREATEDBY IN VARCHAR2, P_INSORUPD IN NUMBER, CUR_OUT OUT SYS_REFCURSOR )  how can call this procedure with my parameters, I want to call the procedure with 5 IN parameters and 1 cursor out parameters how can I get the out variable result.   Just define the variables locally, call the procedure and loop thru the results. SET SERVEROUTPUT ON SIZE 1000000 DECLARE   v_SHIFTPATTERNCODE VARCHAR2(4000);   v_EMPNO VARCHAR2(4000);   v_STARTDATE DATE;   v_CREATEDBY VARCHAR2(4000);   v_INSORUPD NUMBER;   vCUR_OUT SYS_REFCURSOR; BEGIN    AUTOALLOTSHIFTS_PROC (v_SHIFTPATTERNCODE, v_EMPNO IN VARCHAR2,                                           v_STARTDATE IN DATE, v_CREATEDBY IN VARCHAR2,                                           v_INSORUPD IN NUMBER, vCUR_OUT OUT SYS_REFCURSOR )                LOOP      FETCH l_cursor     EXIT WHEN l_cursor%NOTFOUND;     -- process cursor here   END LOOP;   CLOSE l_cursor; END; /  

Hi,  Below code is working in HR schema. You can try this one.  create or replace procedure test_a (   p_dept_id      in  varchar2,   p_man_id       in  varchar2,   p_job_id       in  varchar2,   p_sal          in  varchar2,   p_hire_date    in  varchar2,   p_cur_ptr      out sys_refcursor ) as   begin        open p_cur_ptr for select last_name , job_id , salary                         from   employees                        where  department_id = p_dept_id                        and    manager_id    = p_man_id                        and    job_id        = p_job_id                        and    p_sal         > 1000                        and    to_date(p_hire_date,'dd-mon-yyyy')   < sysdate; end; /  show errors     create or replace procedure test_b as   v_last_name     employees.last_name%type;   v_job_id        employees.job_id%type;   v_sal           employees.salary%type;      cur_ptr         sys_refcursor;   begin     test_a('50','103','SA_REP','30000','01-JAN-1982',cur_ptr);      loop   fetch cur_ptr into v_last_name , v_job_id , v_sal;   exit when cur_ptr%notfound;          dbms_output.put_line(v_last_name ||'     '|| v_job_id ||'       '|| v_sal);      end loop;    end test_b; /  show errors 

thank you it is correct


Related Links

extended audit trail for XML
Analyzing Load Profile[Tuning]
Shell script question
Copy DB to a new server
Tablespace
DBA_REGISTRY inconsistent
hi
unable to login through isqlplus
Ora-27300
Analytic Function
Full Database Exp &#38; Imp
Explain the use of setting GLOBAL_NAMES equal to TRUE
Delicate issue with locked user
What role/roles require to administer OEM DB control jobs?
problem connecting from Oracle to SQL DB
Create database