Accessing procedures that return tables - OO4O and Wizards(Archived)

We have an API (a number of packages) that is exposed for us to use, unfortunatly we can not make any changes to it. To complicate matters further we cannot access a server running this api to develop on, we must replicate the api locally leaving even more room for error :(
Here is the problem, The procedure all make use of tables as return parameters:
T_Snowtam_text_part_def VARCHAR2(2000);
SUBTYPE T_Snowtam_text_part IS T_Snowtam_text_part_def%TYPE;
TYPE T_Snowtam_text IS TABLE OF T_Snowtam_text_part;
T_abbr_heading_def VARCHAR2(28);
SUBTYPE T_abbr_heading IS T_abbr_heading_def%TYPE;
T_Airport_id_def VARCHAR2(4);
SUBTYPE T_Airport_id IS T_Airport_id_def%TYPE;
T_Aftn_address_def VARCHAR2(8);
SUBTYPE T_Aftn_address IS T_Aftn_address_def%TYPE;
T_Serial_number_def NUMBER(10);
SUBTYPE T_Serial_number IS T_Serial_number_def%TYPE;
T_Long_date_def VARCHAR2(14);
SUBTYPE T_Long_date IS T_Long_date_def%TYPE;
CREATE OR REPLACE PACKAGE BODY Snowtam AS
     Procedure Get ( aerodrome IN Std_types.T_Airport_id,
          originator OUT Std_types.T_Aftn_address,
          serial_num OUT Std_types.T_Serial_number,
          location OUT Std_types.T_Airport_id,
          observation_dt OUT Std_types.T_long_date,
          abbr_heading OUT T_abbr_heading,
          snowtam_text OUT T_Snowtam_text) IS
     BEGIN
          originator :='egnm';
          serial_num:= 123;
          location:='egnm';
          observation_dt:='10/12/2001';
          abbr_heading:='heading text';
          
     END Get;
END Snowtam;
in this example all of the OUT params are single items except the last which is a table.
If I edit the procedure so that it doesn't contain the last parameter then i can call the function via oledb and OO40, however i have not been able to call the function and return the table.
var ora_session = Server.CreateObject("OracleInProcServer.XOraSession");
var ora_db = ora_session.OpenDatabase("...","...",0);
ora_db.Parameters.Add("ORIGINATOR",0,2,1,8);
ora_db.Parameters.Add("SERIAL_NUM",0,2,2,4);
ora_db.Parameters.Add("LOCATION",0,2,1,4);
ora_db.Parameters.Add("OBSERVATION_DT",0,2,1,4);
ora_db.Parameters.Add("ABBR_HEADING",0,2,1,20);
ora_db.Parameters.AddTable("SNOWTAM_TEXT",2,1,10,200);
var ora_set = ora_db.ExecuteSQL("begin SNOWTAM.Get('egnm',:ORIGINATOR,:SERIAL_NUM,:LOCATION,:OBSERVATION_DT,:ABBR_HEADING,:SNOWTAM_TEXT); end;");
this works fine if the last param is not there but not if the table parameter is there.
can anyone advise on how i access these table parameters from either OO40 or ADO?
Regards,
Edward Longhurst

Related

How to access Packaged Type

I got a problem when accessing packaged type in java: I created a package like this: create or replace package TEST_PKG is TYPE TESTTYPE IS TABLE OF VARCHAR2 (128) INDEX BY BINARY_INTEGER; procedure TEST(data in testType, message out varchar2); end TEST_PKG; create or replace package body TEST_PKG is procedure TEST(data in TESTTYPE, message out varchar2) is begin --do nothing to the  in parameter "data", but just test to use the type in a package. message:='this is a message'; end test; end TEST_PKG; This procedure runs ok in sql plus like this: var message varchar2(100); declare data test_pkg.testtype; begin test_pkg.test(data => data, message => :message); end; But when I call it by Java, the problem occurs. To call this procedure, I have to create an input for "data in TESTTYPE". The "TESTTYPE" is table of varchar2(128), this type is corresponding to the oracle.sql.ARRAY class in java. And before creating the instance for oracle.sql.ARRAY, we have to create an instance of oracle.sql.ArrayDescriptor to get the oracle type for this ARRAY. The codes are like this: conn=java.sql.DriverManager.getConnection(sConnStr,"test","test");// the connection is ok. stmt=(OracleCallableStatement)conn.prepareCall("{call test_pkg.test(?,?)}"); ArrayDescriptor ad=ArrayDescriptor.createDescriptor("TEST_PKG.TESTTYPE", conn);//An error cought here String[] t_tags={"aaaaa","bbbbb"}; oracle.sql.ARRAY tagsArray=new ARRAY(ad,conn,t_tags); I use ArrayDescriptor.createDescriptor("TEST_PKG.TESTTYPE",conn) to get an ArrayDescription, but "invalid name pattern" error occurs. I think a problem may happen when using the type name "TEST_PKG.TESTTYPE", and I have tried to use "TESTTYPE", or "MANOR.TESTTYPE", or "MANOR.TEST_PKG.TESTTYPE", or "MANOR.TEST_PKG_TESTTYPE", but all of them have the same error when the codes run. I wonder if Oracle 10g do not allow us to use the types in a package in java?
Hello,
You cannot access a pl/sql type (declared in the package spec) from outside the pl/sql environment in Java.
You will need to create the type in sql, then use that in your package.
sql> create type testtype is table of ...Then reference schema.testtype in the call to createDescriptor.

Support for OBJECT TYPEs?

Does the current PHP/OCI API support the use of object types from Oracle?
i.e. I have an object defined as such, and wish to create an instance of discount_price_obj, set some properties on it, and pass it to other packages/objects/procedures in Oracle. Even call methods on it. It's unclear from the documentation I've seen whether or not this is even possible, let alone how to do it.
CREATE OR REPLACE TYPE discount_price_obj as OBJECT (
     discount_rate NUMBER(10,4),
price NUMBER(10,2),
MEMBER FUNCTION discount_price RETURN NUMBER
)
;
CREATE OR REPLACE TYPE BODY discount_price_obj IS
     MEMBER FUNCTION discount_price RETURN NUMBER IS
BEGIN
     return price * (1-discount_rate);
     END;
END;
/ 
Unfortunately, user-defined object types are not supported. I actually have asked the product manager and development manager for this feature. Let's hope it becomes a reality in a future release.
Regard,
Michael 
If the question is purely about using and passing instances of the type in PL/SQL code, perhaps the PL/SQL forum can help with specific questions.
If you are wondering about interacting with PHP, perhaps try create a mapping function like "myf":
drop function myf;
drop type discount_price_obj;
create or replace type discount_price_obj as object (
  discount_rate number(10,4),
  price number(10,2),
  member function discount_price return number
);
/
show errors
create or replace type body discount_price_obj is
  member function discount_price return number is
  begin
    return price * (1-discount_rate);
  end;
end;
/
show errors
create or replace function myf return number as
  v discount_price_obj;
begin
  v := new discount_price_obj(0.2, 100);
  return v.discount_price;
end;
/
show errorsYou can call "myf" from PHP in the standard way:
$c = oci_connect("hr", "hrpwd", "localhost/XE");
$s = oci_parse($c, "begin :v := myf; end;");
oci_bind_by_name($s, ":v", $v, 40);
oci_execute($s);
var_dump($v);The output is:
string(2) "80"

How to access types in package in oracle?

I got a problem when accessing packaged type in java: I created a package like this: create or replace package TEST_PKG is TYPE TESTTYPE IS TABLE OF VARCHAR2 (128) INDEX BY BINARY_INTEGER; procedure TEST(data in testType, message out varchar2); end TEST_PKG; create or replace package body TEST_PKG is procedure TEST(data in TESTTYPE, message out varchar2) is begin --do nothing to the in parameter "data", but just test to use the type in a package. message:='this is a message'; end test; end TEST_PKG; This procedure runs ok in sql plus like this: var message varchar2(100); declare data test_pkg.testtype; begin test_pkg.test(data => data, message => :message); end; But when I call it by Java, the problem occurs. To call this procedure, I have to create an input for "data in TESTTYPE". The "TESTTYPE" is table of varchar2(128), this type is corresponding to the oracle.sql.ARRAY class in java. And before creating the instance for oracle.sql.ARRAY, we have to create an instance of oracle.sql.ArrayDescriptor to get the oracle type for this ARRAY. The codes are like this: conn=java.sql.DriverManager.getConnection(sConnStr,"test","test");// the connection is ok. stmt=(OracleCallableStatement)conn.prepareCall("{call test_pkg.test(?,?)}"); ArrayDescriptor ad=ArrayDescriptor.createDescriptor("TEST_PKG.TESTTYPE", conn);//An error cought here String[] t_tags={"aaaaa","bbbbb"}; oracle.sql.ARRAY tagsArray=new ARRAY(ad,conn,t_tags); I use ArrayDescriptor.createDescriptor("TEST_PKG.TESTTYPE",conn) to get an ArrayDescription, but "invalid name pattern" error occurs. I think a problem may happen when using the type name "TEST_PKG.TESTTYPE", and I have tried to use "TESTTYPE", or "MANOR.TESTTYPE", or "MANOR.TEST_PKG.TESTTYPE", or "MANOR.TEST_PKG_TESTTYPE", but all of them have the same error when the codes run. I wonder if Oracle 10g do not allow us to use the types in a package in java?
Hi,
We are facing similar issue.
Can any one please help us?
Regards,
Gerin Jacob

olite data subsetting & VPD ?

I already have data subsetting throw VPD (Virtual Private Database)
How i can set session context from olite subset parameters?
For now I see only one solution:
create publication item with subset
select * from dual mypackage.setcontext(:userid) = 0
with minimum width = W_SET
and set width > W_SET to other items;
not tested yet..
Only one problemma, synchronization works in one session?
Someone do this already, and knows a better solution?
---
One week passed and no one said that the weight is taken into account only in insert/update/delete :(
probably all been celebrating the сhristmas, new year....
now i trying to use dml callbacks
Edited by: lunicon on 28.12.2011 21:13 
I try to create package CUSTOMIZE as in documentation, restart server, but it never been called. What's wrong?
CREATE OR REPLACE PACKAGE MOBILEADMIN.CUSTOMIZE AS
    PROCEDURE NullSync (clientid varchar2, isNullSync boolean);
    PROCEDURE BeforeProcessApply;
    PROCEDURE AfterProcessApply;
    PROCEDURE BeforeProcessCompose;
    PROCEDURE AfterProcessCompose;
    PROCEDURE BeforeProcessLogs;
    PROCEDURE AfterProcessLogs;
    PROCEDURE BeforeClientCompose(clientid varchar2);
    PROCEDURE AfterClientCompose(clientid varchar2);
END; dont know howto make hidden text on forum..
---
Got it:
clog: MGP.callBoundCallBack: FINEST: GENERAL: CUSTOMIZE package does not exist or is invalid.
MISSING OR INVALID ::     CUSTOMIZE.BeforeSyncMapCleanup();
Bad example in documentation 2.7.1.12
Edited by: lunicon on 29.12.2011 21:56 
VPD works!
For example, i use external authentication using my table users and update context from mobile server username
CREATE OR REPLACE PACKAGE BODY CUSTOMIZE
AS
     PROCEDURE NullSync(clientid varchar2, isNullSync boolean)
     IS
     usrid NUMBER;
     BEGIN
          BEGIN
               SELECT userid INTO usrid FROM MYUSER.appuser WHERE UPPER(login) = UPPER(clientid);
          EXCEPTION
               WHEN NO_DATA_FOUND
          THEN
               usrid := NULL;
          END;
          MYUSER.B2B_CONTEXT.set_context(clientid, usrid);
     END;
....turned a great monologue :)
happy holidays!

Ref Cursor Calling Database Package/Function

I'm using 9i reports and the ref cursor functionality. I'm able to get everthing to work the way that I want except on thing. Whenever I run the report it seems to put a lock on my db package. When I try to complie it it just hangs...
Here is my package:
create or replace package oracle_test as
-- plant
type plant_rec is record ( plant_id iis_plant.plant_id%type
,plant_name iis_plant.basin_name%type
,plant_desc iis_plant.plant_desc%type);
type plant_ref is ref cursor return plant_rec;
function get_plant_data return plant_ref;
end oracle_test;
/
create or replace package body oracle_test as
function get_plant_data return plant_ref is
plant_rec iis_reporting_api.plant_ref;
begin
open plant_rec for select plant_id, plant_name, plant_desc from iis_plant;
return plant_rec;
exception when others then
null;
end get_plant_data;
end oracle_test;
/
And here is my report ref cursor:
function QR_1RefCurDS return oracle_test.plant_ref is
begin
return oracle_test.get_plant_data();
end;
Any suggestions as to why this is happening would be greatly appriciated....
Thanks,,,

Categories

Resources