Please Help - How to pass Table objects as input and output parameters using VC++ - OO4O and Wizards(Archived)

Please help me by giving a sample code to call the procedure
GetClassInstanceProperties from a VC++ application using
Oracle Objects for OLE
CREATE OR REPLACE TYPE TY_INST_ID_PROP_PAIR AS OBJECT
(INST_ID CHAR(38), PROP_ID CHAR(38), VALUE XMLTYPE);
CREATE OR REPLACE PACKAGE DBMS_PROPERTYSTORE
AUTHID CURRENT_USER
AS
     --Used to return the ids from the functions
     TYPE tt_UUIDs IS TABLE OF char(38) index by binary_integer;
     TYPE tt_inst_id_prop_pairs is table of ty_inst_id_prop_pair index by binary_integer;
     PROCEDURE GetClassInstanceProperties(ClassId IN char, Prop_Ids in TT_UUIDs,
Inst_Id_Prop_Pairs out TT_Inst_Id_Prop_Pair);
END DBMS_PROPERTYSTORE;
CREATE OR REPLACE PACKAGE BODY DBMS_PROPERTYSTORE AS
     PROCEDURE GetClassInstanceProperties
     (
          ClassId IN char,
          Prop_Ids in TT_UUIDs,
          Inst_Id_Prop_Pairs out TT_INST_ID_PROP_PAIR
     )
     AS
     BEGIN
          --Return the instanceid,propertyid and values for a certain class
          --only return those properties that in the user supplied Prop_ids table
     
          FOR I in Prop_Ids.FIRST..Prop_Ids.LAST loop
               select TY_Inst_Id_Prop_Pair(i.Inst_Id,p.Prop_Id,p.Value) into Inst_Id_Prop_Pairs(I)
                    from TBL_Instance i, table(Prop_Pairs) p
                    where I.Cls_Id.Cls_Id=ClassID
                    and p.Prop_Id = Prop_Ids(I);
          END LOOP;
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
               raise_application_error(-20100, 'No data found');
          WHEN OTHERS THEN
               raise_application_error(-20200, SQLERRM);
     END GetClassInstanceProperties;
END DBMS_PROPERTYSTORE;

Related

Workflow HTML document

1) I am attempting to build a message body with below appearance (with proper spacing between the old and the new assignment data. I used dashes to indicate the presence of spaces).
Old_retirement_code--------------------New_retirement_code
4------------------------------------------------2
Old_appointment_end_date-----------New_appointment_end_date
10-AUG-2004-------------------------------29-MAY-2005
Old_assignment_status-----------------New_assignment_status
Active Assignment------------------------Temp Acting Assignment
….etc…
2) I created a Table which captures the HR column headings as well as the old and new HR data using the syntax"
CREATE TABLE ZZZL_HR_WF_JUNKX1
(SEQ CHAR(2 BYTE),
OLD VARCHAR2(50 BYTE),
NEW VARCHAR2(50 BYTE)
);
Then, programatically inserted data into the table. When selected them by SEQ, I can build message body in the desired order as shown in
SELECT * FROM hr_junkx1 order by 1;
SEQ-OLD------------------------------------NEW-----------------
A1--Old_retirement_code --------------New_retirement_code
A2—-4-----------------------------------------2
B1--Old_appointment_end_date------New_appointment_end_date
B2--10-AUG-2004-------------------------29-MAY-2005
C1--Old_assignment_status-----------New_assignment_status
C2--Active Assignment------------------Temp Acting
..etc…
3) I then build a package spec and body to build the HTML message body for workflow. The package procedure will be called by an WF item attribute (type=document). Below is a simplified version of coding for testing (not yet using document_id here. I used a unique key for document_id in the real coding).
I got the procedure from wf demo.But my code kept bombing out in the cursor for loop
(for assign_rec in csr_chgAssignx loop                                                                  
          ...
     end loop;)
Using the 'text/html' display_type, the program exception occurs after
writing the 'Old_retirement_code New_retirement_code' column headings.
Using the 'text/plain' display_type, the program exception occurs when
writing 'Old_retirement_code New_retirement_code' column headings.
Initially, I thought the problem is attributed to the various varchar2 columns of diffrent length from 30 to 240 bytes), I then built the table 'ZZZL_HR_WF_JUNKX1' to force all old and new fields to the same length (50 bytes). Thought this will help HTML table row output, but, it did not.
4) QUESTION: What and where did I do wrong? Being new to PL/SQL HTML doc coding, I would appreciate any expert guidance.
Thanks in advance.
5) Below is a simplified version of the code used in testing.
CREATE OR REPLACE package wf_msg_pkg as
-- --------------------------------------------------------------------
-- procedure: test --
-- --------------------------------------------------------------------     
                              
procedure test (
     document_id in varchar2,
     display_type in varchar2,
     document in out varchar2,
     document_type in out varchar2);
          
end wf_msg_pkg;
/
CREATE OR REPLACE package body wf_msg_pkg as
-- --------------------------------------------------------------------
-- procedure: test --
-- --------------------------------------------------------------------
-- Build Assignment Change Message
--
procedure test (
     document_id in varchar2,
     display_type in varchar2,
     document in out varchar2,
     document_type in out varchar2)
is
     CURSOR csr_chgAssignx is
     select seq, old, new from zzzl_hr_wf_junkx1;
begin
if display_type = 'text/html' then
     document_type := 'text/html';
     document :=
          '<BR><BR><LEFT><TABLE BORDER CELLPADDING=5 BGCOLOR=#FFFFFF>'||
          '<TR BGCOLOR=#83C1C1>'||
          '<TH>Old Assignment </TH>'||
          '<TH>New Assigment</TH>'||
          '</TR>';
     else
     document_type := 'text/plain';
     document :=
          chr(10)||
          rpad('Old Assignment',50)||
          rpad('New Assigment',50)||
          chr(10);
     end if;
     -- build table body with data
     for assign_rec in csr_chgAssignx loop
     if display_type = 'text/html' then
     document := document||
          '<TR>'||
          '<TD>'||assign_rec.old||'</TD>'||
          '<TD>'||assign_rec.new||'</TD>'||
          '</TR>';
     else
     document := document||
          rpad(assign_rec.old,50)||
          rpad(assign_rec.new,50)||
          chr(10);
     end if;
     end loop;
     -- close the table
     if display_type = 'text/html' then
     document := document||
          '</TABLE></LEFT><BR>';
     end if;
return;
exception
when others then
-- The line below records this procedure call in the error system
-- in the case of an exception.
wf_core.context('xxxl_wf_event_HRMS_pkg', 'chgAssign_MSG',
document_id, display_type);     
raise;
end test;
END wf_msg_pkg;
/

VB: Returning PL Tables with Multiple Coulumns form Stored Procs

I have a stored procedure as below
CREATE OR REPLACE PACKAGE PK_EMPLOYEE AS
TYPE ERec IS RECORD
(eno EMPLOYEE.EMPNO%TYPE,
ename EMPLOYEE.ENAME%TYPE
);
TYPE TTypeTab IS TABLE OF ERec index by BINARY_INTEGER;
TYPE RefCurType IS REF CURSOR RETURN EMPLOYEE%ROWTYPE;
PROCEDURE GetEmpPL(ret OUT TTypeTab);
END PK_EMPLOYEE;
CREATE OR REPLACE PACKAGE BODY PK_EMPLOYEE AS
PROCEDURE GetEmpPL(ret OUT TTypeTab) IS
cCur RefCurType;
cR EMPLOYEE%ROWTYPE;
nCount NUMBER:=1;
BEGIN
OPEN cCur FOR SELECT * FROM EMPLOYEE;
LOOP
FETCH cCur INTO cR;
     EXIT WHEN cCur%NOTFOUND OR cCur%NOTFOUND IS NULL;
ret(nCount).ename := cR.ename;
ret(nCount).eno := cR.empno;
nCount:=nCount+1;
END LOOP;
CLOSE cCur;
END GetEmpPL;
END PK_EMPLOYEE;
can any one help me to get TTypeTab to VB from this? 
You can't pass a table of records outside the PL/SQL engine. You should consider passing back a ref cursor instead. You can build the ref cursor on a temporary table if needed.

Multiple return function for Report 6i

Hey All
I have a little situation that has been driving my crazy for the last few days and I have turned to the last resort.....ask someone for help :o)
My problem is, I need a pl/sql function to return more than one row to my report. I have been researching like mad to find a solution and thought I had found it in the form of REF CURSORS. First I tried to test it with a simple SELECT * FROM {table_name}; And it worked perfectly, so then I tried with a very simple WHERE clause....
PACKAGE BODY report_dynamic IS
FUNCTION func_dyn RETURN type_ref_cur_dyn IS
ref_cur_dyn type_ref_cur_dyn;
BEGIN
OPEN ref_cur_dyn FOR
SELECT * FROM ppa_est WHERE ppa_est <= 1 ;
RETURN ref_cur_dyn;
END;
END;
...and this work fine.
I then went for it and tried my own statement which has a WHERE clause and is very complex (it uses more than one table for reference)....
PACKAGE BODY report_static IS
FUNCTION func_sta RETURN type_ref_cur_sta IS
ref_cur_sta type_ref_cur_sta;
BEGIN
OPEN ref_cur_sta FOR
select ppo_ppm, ppo_object, ppa_est, evt_due, evt_status, EVT_METERDUE, ppm_freq, ppm_meter from r5ppmobjects, r5ppmacts, r5events,r5ppms
where ppo_ppm = ppm_code
and ppo_object like 'OCR-01%'
and ppo_deactive is null
and (ppo_ppm = ppa_ppm and ppa_trade = 'TEK')
and (ppo_ppm = evt_ppm and (evt_object like 'OCR-01%' and EVT_PPM IS NOT NULL AND EVT_STATUS NOT IN ('C', 'X'))AND (EVT_DUE BETWEEN '01-jan-2001' and '01-jan-2003'));
RETURN ref_cur_sta;
END;
END;
....but it would not accept this, I got a wrong type message when I tried to compile it. I have also tried using a weak REF CURSOR, but this give no results (however I do not get any wrong type errors, the code compiles fine)
I would be very grateful if someone with a great knowledge of Reports , PL/SQL and all things would take the time to help me out of this ....
Yours Gratefully
David Hollinsworth
You'd better use a pl/sql table as your argument, e.g.:
package my_emp_pck is
  type   t_emp is table of emp%rowtype index by binary_integer;
  function get_emp return t_emp;
end;
package body my_emp_pck is
  function get_emp return t_emp is
    cursor lc_emp is
     select * from emp;
    lt_emp t_emp;
  begin
    for i in lc_emp loop
      lt_emp(lc_emp%rowcount) := i;
    end loop;
  end;
end;
-- And in the (local) package:
declare
  lt_emp my_emp_pck.t_emp;
begin
  lt_emp := my_emp_pck.get_emp;
  ...
end;
Hi David,
I guess this might help.
I am here with enclosing a sample code.
================================================================================
CREATE OR REPLACE PACKAGE emp_pkg AS
TYPE emp_rec IS RECORD
(
deptno EMP.deptno%TYPE,
empno EMP.empno%TYPE,
ename EMP.ename%TYPE,
sal EMP.sal%TYPE
);
TYPE emp_s_refcur IS REF CURSOR RETURN emp_rec;
TYPE emp_w_refcur IS REF CURSOR;
PROCEDURE emp_pro
(
p_deptno NUMBER,
ret_emp IN OUT emp_w_refcur
);
END emp_pkg;
/
================================================================================
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE emp_pro
(
p_deptno NUMBER,
     ret_emp IN OUT emp_w_refcur
) IS
v_query VARCHAR2(2000);
BEGIN
v_query := 'SELECT deptno,
     empno,
                              ename,
                              sal
                    FROM emp
                    WHERE deptno = ' || p_deptno || '
                    ORDER BY sal desc ';
     
Open ret_emp for v_query;
END emp_pro;
END emp_pkg;
/
================================================================================
After Creating the above Package
Create a Report with "Ref Cursor Query" with the following script
------------------------
function QR_1RefCurDS return emp_pkg.emp_s_refcur is
temp1 emp_pkg.emp_s_refcur;
begin
emp_pkg.emp_pro ( :p_deptno, temp1 );
return temp1;
end;
--------------------------
Note:
Make sure that the return datatypes of the
"Select statement" is in sink with the Record datatype;
Regards,
Diwakar
--------
Yenduri

Store Procedure w/ Arrays

All,
A very simple problem, I need to store a "select" query into a stored proc. and get the results back.
I am currently using an array... but for some reason I can't execute this stored procedure from another stored proc.
This is what I have been doing...
CREATE OR REPLACE PACKAGE FC AS
TYPE char_array IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE num_array IS TABLE OF FLOAT INDEX BY BINARY_INTEGER;
PROCEDURE getList( user_id OUT char_array, first_nm OUT char_array, last_nm OUT char_array);
END FC;
/
CREATE OR REPLACE PACKAGE BODY FC AS
PROCEDURE getList( user_id OUT char_array, first_nm OUT char_array, last_nm OUT char_array) IS
done_fetch INTEGER;
found INTEGER;
batch_size INTEGER;
CURSOR crGetBuyerList IS
SELECT u.user_id, u.first_nm, u.last_nm
FROM users u
WHERE u.codeType = 'A'
AND DECODE(u.deleted, NULL, 'N', u.deleted) = 'N'
ORDER BY u.last_nm, u.first_nm;
BEGIN
IF NOT crGetBuyerList%ISOPEN THEN
OPEN crGetBuyerList(); END IF;
done_fetch := 0;
found := 0;
FOR i IN 1..batch_size LOOP
FETCH crGetBuyerList INTO user_id(i), first_nm(i), last_nm(i);
IF crGetBuyerList%NOTFOUND
THEN
CLOSE crGetBuyerList;
done_fetch := 1;
EXIT;
ELSE
found := found + 1;
END IF;
END LOOP;
END getList;
END FC;
/
null

How to pass table from one procedure to another procedure ?

How to send the table from one procedure to another procedure which are in different schemas?
i.e.
In one log in i would like to create a package in which i will fetch the info from two tables into two plsql tables. These two plsql tables should be returned as an out parameter from this procedure to another procedure which residing in another user
What i am trying to do is....
Suppose i created the following package in user2 Schema
---------------------------------------------------------------------
CREATE OR REPLACE PACKAGE test_actions AS
--Record Type
TYPE REC_TYPE IS RECORD(EMP_ID VARCHAR2(30),
EMP_NAME VARCHAR2(100));
--Table
TYPE ARRAY IS TABLE OF REC_TYPE;
TYPE REC_TYPE1 IS RECORD(DEPT_ID VARCHAR2(30),
DEPT_NAME VARCHAR2(30));
TYPE ARRAY1 IS TABLE OF REC_TYPE1;
PROCEDURE fast_proc(l_data out ARRAY,r_data out ARRAY1);
end ;
/
CREATE OR REPLACE PACKAGE BODY test_actions AS
PROCEDURE fast_proc(l_data out ARRAY,r_data out ARRAY1) IS
CURSOR c IS
SELECT ENO,ENAME
FROM EMPL WHERE ROWNUM < 5;
CURSOR c1 IS
SELECT Dno,DNAME
FROM DEPT WHERE ROWNUM < 5;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO l_data ;
CLOSE c;
OPEN c1;
FETCH c1 BULK COLLECT INTO r_data;
CLOSE c1;
end;
END;
The following package is trying to create in user1
CREATE OR REPLACE PACKAGE receive_actions AS
--Record Type
TYPE REC_TYPE IS RECORD(EMP_ID VARCHAR2(30),
EMP_NAME VARCHAR2(100));
--Table
TYPE ARRAY IS TABLE OF REC_TYPE;
TYPE REC_TYPE1 IS RECORD(DEPT_ID VARCHAR2(30),
DEPT_NAME VARCHAR2(30));
TYPE ARRAY1 IS TABLE OF REC_TYPE1;
PROCEDURE get_info;
end ;
/
CREATE OR REPLACE PACKAGE BODY receive_actions AS
PROCEDURE get_info IS
l_data ARRAY;
r_data ARRAY1;
BEGIN
user2.test_actions.fast_proc(l_data,r_data);
FOR i IN 1..l_data.COUNT
loop
dbms_output.put_line('The EMP_ID IS :: ' || l_data(i).EMP_ID);
dbms_output.put_line('The ENAME IS :: ' || l_data(i).EMP_NAME);
end loop;
FOR i IN 1..r_data.COUNT
loop
dbms_output.put_line('The DEPT_ID IS :: ' || r_data(i).DEPT_ID);
dbms_output.put_line('The DEPTNAME IS :: ' || r_data(i).DEPT_NAME);
end loop;
exception
when others then
dbms_output.put_line('Exception occured'||sqlerrm(sqlcode));
end;
END;
/
If i am trying to create this procedure its giving the following error...
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/8 PLS-00306: wrong number or types of arguments in call to
'FAST_PROC'
7/8 PLS-00306: wrong number or types of arguments in call to
'FAST_PROC'
7/8 PL/SQL: Statement ignored
Here my objective is i have the information in one schema (i.e.) fetched into table type. This table i would like to send to another procedure which is there another schema. How can i achieve this.
If at all any alternative solution please let me know.

Categories

Resources