Read from insert? - Data Service Integrator

Is it possible to query the database from a create statement? For instance, when the create service is called, an SQL statement is executed and those results are included in the insert. If this is possible, what is the general approach?
Thanks!
Dave 

You can write an update procedure to do whatever you want. Yours would read some data from the database, construct an element using that data, and then call the physical create procedure.
Or perhaps you can use the ODSI "AutoNumber" feature to do this for you, if you are populating a column from a sequence. In Workshop, go to the Design View for the physical dataservice, click on the column to be populated, then Window -> Show View -> Properties, under General -> AutoNumber, select either identity or sequence from the drop down. If you select sequence, you also need to specify a Sequence Object. 

Excellent. Are you aware of any examples or documentation I could reference? 

You can write a create procedure in XQSE that first reads and then calls another create procedure. Any procedure can be designated a create procedure, but in order to be a primary create procedure, it has to adhere to the parameter and return type convention.
Here is an example that only adds an address if none exists for that customer, you can add it in the RetailDataspace to Normalized/Customer/Address.ds:
(::pragma  function <f:function kind="create" visibility="public" isPrimary="false" xmlns:f="urn:annotations.ld.bea.com"/>::)
declare procedure tns:addAddressCheckFirst($arg0 as element(ns7:ADDRESS)) as element(ns7:Address_KEY)?{
    declare $sameCustomerAddresses := tns:getAddress()[CustomerID = $arg0/CustomerID];
    fn-bea:probe($sameCustomerAddresses);
    if (not(fn:exists($sameCustomerAddresses))) then {
        return value tns:createADDRESS($arg0);
    } else {
        return value ();
    };
};Recommended reading:
More about procedure types:
[http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Data%20Service%20Types%20and%20Functions.html]
[http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/xquery/xqse.html]
[http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/odsi10gr3/How%20To%20Develop%20Good%20XQSEs.html] 

Yes. The 'documentation' link in the announcement for ODSI 10.3 in this forum. That said, the documenation on AutoNumber is spars, that's why I told you exactly where to click etc.
As for writing your own update - please refer to the documentation referenced above. You can search it by adding site:download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3 to a Google search, or you can use the (almost identical) searchable doc at http://edocs.bea.com/aldsp/docs32/. Keep in mind that I cannot search/read the documentation for 160,000 customers :)

Related

Adding a member function

Dear All,
Let's say we have created a type in Oracle as below (I am copying the example from psoug.org):
CREATE OR REPLACE TYPE ssn AS OBJECT (
n_ CHAR(11),
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2) RETURN self AS result,
MEMBER FUNCTION get_ssn RETURN CHAR);
/
CREATE OR REPLACE TYPE BODY ssn IS
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)
RETURN self AS RESULT IS
BEGIN
IF validate_ssn(ssn_in) THEN
n_ := ssn_in;
RETURN;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'INVALID SSN');
END IF;
END;
MEMBER FUNCTION get_ssn RETURN CHAR IS
BEGIN
RETURN n_;
END;
END;
/
Now let's say I add an attribute ssn12 to that type with alter type add attribute and I want to add a getter method for that attribute. Do I have to rewrite the whole create or replace type body statement or is there an equivalent of alter type ssn add member function (could not find one)? It would be cumbersome if you have objects with multiple functions to not been able to add a method on the fly.
Regards,
George 
>
is there an equivalent of alter type ssn add member function (could not find one)?
>
And yet I search for 'ORACLE 11g ALTER TYPE' and this is the very first item returned
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/alter_type.htm
>
The ALTER TYPE statement adds or drops member attributes or methods. You can change the existing properties (FINAL or INSTANTIABLE) of an object type, and you can modify the scalar attributes of the type.
>
You may want to replace your search tool. 
Hi,
I did see that but my understanding is as follows: I can add an attribute by doing
alter type ssn add attribute my_attr char(12);
and then I can add a getter by doing
alter type ssn add member function get_my_attr return char cascade;
All fine - and now the question: I want to write the code for get_my_attr without re-creating the package body (just like I did for alter type ssn add member function) - is that possible? Otherwise, if you have 500 functions you would have to repeat all of them just for adding another one (not to mention that you might not have access to the code in the first place).
Regards,
George 
Could anyone please help? Does the entire body have to be recreated or can I just instruct Oracle to add that member function to the existing ones?
Regards,
George 
I don't believe that it is possible to add an implementation for a single member function without doing a CREATE OR REPLACE on the entire type body.
I am curious, though, about why you'd want to do that? It would seem to really complicate your ability to version control your code-- it would seem orders of magnitude easier to have a single CREATE OR REPLACE TYPE BODY statement in source control than to have to keep track of the original CREATE TYPE BODY and dozens of subsequent ALTER statements. You'd need to query the data dictionary at that point to be able to view the entire type body rather than just looking at what's in source control.
Your comment that you might not have access to the source of the type body is also confusing. How is that possible? Are you saying that you somehow have the ability to change the type body but you don't even have access to the data dictionary to see the original source?
Justin 
>
I did see that
>
Well first you said you searched and couldn't find anything and I found it on the very first page when I did a search.
Now you say you saw the reference I cited. But apparently you didn't read it because it has an example
>
Examples
Adding a Member Function: Example
>
And that example will answer you question. 
When I said I saw the reference I meant that it did not contain anything that answers my question. I do appreciate the version control concerns that were raised and I believe they are valid but the question remains: is it possible to use the alter type statement to add the body of a new function or not?
George 
>
is it possible to use the alter type statement to add the body of a new function or not?
>
There is no remaining question. It is not possible as the example for 'Adding a Member Function' and an entire reading of the doc shows.
Aside from compiling, the ALTER statment primarily allows changing the specification. It is the 'spec' that is the public face (the interface) of the type and it is changes to the 'spec' that affect other objects and code of the database.
As the example shows you can ALTER the spec to add a member function but you must then recompile the body which must now include code for the new function. You cannot have a spec that defines a function without the corresponding function existing in the body or the body will be invalid.

Shared settings implementation question

Peace be with you all,
I have an process that consists of a number of procedures and
functions (all started from a single procedure) that has around
20 settings that change each run and are accessed at various
levels of the call hierarchy. A couple of the settings should
properly be considered variables because they count things
such as errors and warnings. (Also, this process is guaranteed to
to be singleton, ie: non-concurrent).
My question is which is the best way (shading towards
performance) to implement such a structure; I am considering
three choices:
1) As a 'globalVars' table with name and value fields, where there
is a setVar procedure and a getVar function (always returning
varchar2). This is the current implementation, which is a bit
ugly, IMO.
2) As a Record type that is passed (with nocopy) down the
hierarchy. I've already tested this option and know that called
routines can modify the elements of the record, as long as it
is declared as an 'in out' parameter.
3) As a Package that declares variables global to it and has
an initialization function that is called once at the beginning
of each run.
I'm leaning towards #3, but #2 has a certain niceness to it,
though I'm concerned about performance.
Thanks in advance,
Robert 
3) As a Package that declares variables global to it
and has
an initialization function that is called once at the
beginning
of each run.I'd took this variant. Passing unncessary parameters to my mind is:
1) more code
2) more choices for bugs, for example forget to pass them around
Also package variables is more felxible i.e. you an easily add them, delete them and set/get only in necessary places. This variant would definitely be faster than updating a globalvars table, to be sure you of course can test it.
Table of course is necessary if you want to store some info between sesions and/or share it among many sessions.
Gints Plivna
http://www.gplivna.eu 
A fourth option would be to create a context and allow the application to get & set context variables as they are needed.
From a performance standpoint, I wouldn't expect there to be any significant difference between any of the options. Passing around records to every procedure seems like a pain, so I'd tend to focus on #3 or #4.
Justin 
Actually, I meant for all my global vars/settings to be stored
within a single Record type as such:
create type PROC_VARS is Record
   (  var1 number := 0,
      var2 varchar2 := '',
      ...
   );Then, each routine (proc or fct) would have, as its first parameter:
create or replace procedure NAME( pContext in out nocopy PROC_VARS, ... ) is ...That way they are all encapsulated into one data structure, making the
'housekeeping' of remembering to pass the vars around very easy, and,
hopefully, quick, with the nocopy option.
That said, it looks like I need to investigate the notion of a context, as
Justin mentioned, although I am dealing with 9i, which may not have contexts (I
haven't looked them up yet).
Thanks,
Robert 
Contexts should be available in 9i
Not verified, so there may be a couple of syntax errors
CREATE CONTEXT my_ctx
  USING pkg_my_ctx;
CREATE PACKAGE pkg_my_ctx
AS
  PROCEDURE set_variable1( p_var1 IN NUMBER );
END;
CREATE PACKAGE BODY pkg_my_ctx
AS
  PROCEDURE set_variable1( p_var1 IN NUMBER )
  AS
  BEGIN
    dbms_session.set_context( 'MY_CTX', 'Variable1', p_var1 )
  END;
END;You can then refer to context variables as SYS_CONTEXT( 'MY_CTX', 'Variable1' ) in SQL or PL/SQL
Justin 
Hi All,
I have a question about using set_context and sys_context. If I have 100 clients and before each client can execute a query their user_id is set using pkg_my_ctx.set_variable1. Does the variable that is supplied apply only to the current connection or can other clients also read this value?
I am looking for a function that will bind a variable to a value for only the current connection.
I want to do this so that views that look like this:
CREATE OR REPLACE VIEW "V_My_View" AS SELECT * FROM "DB"."MyTable"
WHERE "User_Id" = SYS_CONTEXT('userIdParameter', 'userId')
/
can be used to filter client information before the query is executed. The client will only have access to the view. This is to prevent clients viewing each others information.
Thanks in advance,
Martyn

Dynamic sql from Forms 6 with return value

Hi,
My goal is to run pl/sql code (functions) which return values and use the returned value in forms6.
To be more precise:
I put some little business logic into a table's column e.g some_function(in_parameter_1 number);
My app starts a cursor, reads the records and wants to execute some_function and catch it's return value.
Because the function's name is not the same every time I need to do it in dynamic way somehow.
I used recently form's built in exec_sql to run dml statements without returning values or fetching anything.
Please let me know how can I achieve this in simplest way.
thanks., Sandor 
Hello,
The easyest way is to create a stored function in the DB that uses the EXECUTE IMMEDIATE feature, and set the function return value.
So that, all you have to do within Forms is to call that stored function.
Francois 
Yes, Francois is correct. Exec_SQL is extremely cumbersome and slow, requiring lots of extra calls to the database to get any data back. A single call to a stored function (or a function in a database package), passing it the name of the function you want to run would be far easier. 
I put some little business logic into a table's column e.g some_function(in_parameter_1 number);
My app starts a cursor, reads the records and wants to execute some_function and catch it's return value.
Because the function's name is not the same every time I need to do it in dynamic way somehow.Just to be annoying, but exactly how often do those functions change or are some other functions added to this table? If the answer isn't "every day" then I wouldn't go down the dynamic SQL route. I'll tell you why:
1.) As your functions most certainly will have different parameters you'll have to implement a dynamic bind algorithm with dbms_sql as otherwise you'll be SQL Injection prone AND most likely will fill up the shared pool of the database with somewhat similar statements making the whole database suffer of a trashed shared pool. This will result in a bunch of hard-to-read code.
2.) If someone chooses to change the signature of some_function (add a parameter) and forgets to change it in your table as well or analyze your whole code and remove it as it seems to be dead code you won't detect that until it is too late.
3.) Have fun debugging your procedures
4.) All in all to me this sounds like an unperformant, hard to maintain new invention of stored procedures
If you are lazy like me keep your table with your procedure calls, but instead of executing your procedures dynamically everytime write them out in a textfile once, paste the code in a stored procedure and call that stored procedure instead of the dynamic thingie.
If you are using dynamic SQL just because you are to lazy to write down all the somewhat similar code then write a code-generating procedure which does that for you. Trust me,maintenance of static code is a lot easier as it is dynamic code :)
cheers 
christian erlinger wrote:
I put some little business logic into a table's column e.g some_function(in_parameter_1 number);
My app starts a cursor, reads the records and wants to execute some_function and catch it's return value.
Because the function's name is not the same every time I need to do it in dynamic way somehow.Just to be annoying, but exactly how often do those functions change or are some other functions added to this table? If the answer isn't "every day" then I wouldn't go down the dynamic SQL route. I'll tell you why: This is not an everyday task.
>
1.) As your functions most certainly will have different parameters you'll have to implement a dynamic bind algorithm with dbms_sql as otherwise you'll be SQL Injection prone AND most likely will fill up the shared pool of the database with somewhat similar statements making the whole database suffer of a trashed shared pool. This will result in a bunch of hard-to-read code.
2.) If someone chooses to change the signature of some_function (add a parameter) and forgets to change it in your table as well or analyze your whole code and remove it as it seems to be dead code you won't detect that until it is too late. This won't be happened.
3.) Have fun debugging your proceduresIt's not impossible but debugging stored procedures called forms would be a nightmare.
4.) All in all to me this sounds like an unperformant, hard to maintain new invention of stored procedures
If you are lazy like me keep your table with your procedure calls, but instead of executing your procedures dynamically everytime write them out in a textfile once, paste the code in a stored procedure and call that stored procedure instead of the dynamic thingie.
If you are using dynamic SQL just because you are to lazy to write down all the somewhat similar code then write a code-generating procedure which does that for you. Trust me,maintenance of static code is a lot easier as it is dynamic code :)I figured out the solution which perfectly suits to my needs.
1. I created a stored func like this:
FUNCTION process(in_text varchar2) RETURN NUMBER IS
result NUMBER;
BEGIN
EXECUTE IMMEDIATE in_text USING OUT result;
RETURN result;
END;
2. I call this from Forms with this little sql block.
declare
w_sum_ora number;
w_osszeg number:=0;
begin
begin
select sum(napora) into w_sum_ora from bbdetail1 where bbjog_ervev=:ERVEV and feldho=:FELDHO and bbst_torzsszam=:TORZSSZAM
and bbjog_jogcim in (50,51);
exception when no_data_found then w_sum_ora:=0;
end;
if nvl(w_sum_ora,0)=:GL201*:PV240 then
:return:=:FT_OSSZEG;
else
w_osszeg:= ROUND((:FT_OSSZEG/(:GL201*:PV240))*NVL(W_SUM_ORA,0),0);
end if;
:return:=w_osszeg;
end;
This gives back a number as result which is perfect for me.
The bottom line the code will be very small more sql block rather than complex procedures. So I'm happy now :).
And a big thank you to everyone!
Regards., Sandor
>
cheers 
I am glad you are happy.
But I do not see any calls to your stored function from your block of sql code.
So I am puzzled. 
Steve Cosner wrote:
I am glad you are happy.
But I do not see any calls to your stored function from your block of sql code.
So I am puzzled.1. [ param table with sql block]
2. forms procedure processing param table, gets formula column, makes some variable binding (
eg. replace(:PV240,something) then
     calling stored function with w_sql_text has only :return variable in it.
     
     declare
     cursor c1 is select * from myparamtable;
     w_ret_val number;
     w_sql_text varchar2(4000);
     
     begin
     for r1 in c1 loop
w_sql_text:=replace(r1.formula,':PV240','8');
               w_sql_text:=replace(r1.formula,':...','XXX');
          w_ret_val:=process(w_sql_text);     
     insert into some_table values (w_ret_val);          
          end loop;
     end;
I hope it helps clarify the solution.
thnx., Sandor 
Hi,
you can try a little [dynamic sql demo |http://friedhold-matz.blogspot.de/2012/03/forms11g-procedure-block-mit.html]
defined in a table also running in Forms 6i.
Hope it helps
Friedhold

26.4 Basing an entity Object on a PL/SQL Package API - Ref Cursor, no View

I am hoping that I could get some help in the details of a problem. I am trying to follow the directions in the Oracle Application Development Framework Developer's Guide for Forms/4GL Developers, Section 26.4 - Basing an Entity Object on a PL/SQL Package API.
There is example code in the downloadable AdvancedEntityExamples - EntityWrappingPL/SQLPackage
The question is, how will the implementation change if the entity is based entirely on PL/SQL - simply stated - no view is available, just ref cursors and insert,update,delete procedures.
In the example code, there are two procedures, lock_product and select_product. This is where things get more complicated. I can create a function to return a single record ref cursor, instead of the list of OUT variables defined in both functions (select_product and lock_product). It makes sense that I just return one cursor and get all of the columns from that instead of lots of OUT variables.
So what's stopping me you may ask... There is one difference between select_product and select_lock. Select_lock has a select that includes "FOR UPDATE NOWAIT". I don't have that as an option when creating my ref cursor. I am not sure what the impact of "FOR UPDATE NOWAIT" is? Can I ignore it?
In the problem I am working with, (getting data from Oracle Portal 10.1.4) I return the following:
function getRefCursor return ref_cursor is
v_tab wwsbr_all_items_object_type := wwsbr_all_items_object_type();
p_recordset wwsbr_types.cursor_type;
l_results wwsrc_api.items_result_array_type;
begin
wwctx_api.set_context(<username>,<password>);
l_results := wwsrc_api.item_search(.. parameters..);
<snip>
... Loop through the objects and populate v_tab
<snip>
open p_recordset for
select * from table(cast(v_tab as wwsbr_all_items_object_type));
return p_recordset;
end getRefCursor;
With this sample, it would be easy to return a single row by passing the masterid as a parameter.
So I am still left with, how should the implementation of callLockProcedureAndCheckForRowInconsistency() and callSelectProcedure() be changed in order to use a ref cursor instead of a view? The user guide was missing that extra section <bg>.
What would be REALLY helpful, is an example, say 26.4A that demonstrates creating an entity object from a ref cursor and procedures from PL/SQL only without a view.
Thank you, Ken 
The lock procedure is expected to obtain a row-level lock on the row, given its key.
Depending on the setting of jbo.locking.mode, the entity object's lock() method will be invoked either as soon as the first persistent attribute is successfully modified by the user (in the case of jbo.locking.mode=pessimistic), or it will be called during commit processing just before the row is updated in the database (with jbo.locking.mode=optimistic).
Usually 2-tier Swing applications use pessimistic mode, while web applications use optimistic mode.
The FOR UPDATE NOWAIT is the Oracle clause that can be appened to a SELECT statement to acquire a row-level lock on the selected rows. The NOWAIT modifier means that rather than hanging, waiting for a row locked by another user to free up, it will raise an exception if any of the rows being selected-and-locked are not available to lock.
If you're not able to work the FOR UPDATE NOWAIT into the syntax of the ref cursor, perhaps you can initially perform the lock using a different cursor inside the stored procedure, then return your ref cursor. 
Okay, I think I understand. Now for a best practice question.
When creating an entity object based on a PL/SQL Ref Cursor, what is the "best" way for creating the object?
In section 26.4.1, following the Create Entity Object Wizard, selecting a view works if you have one, so what are the steps when you have only a ref cursor?
1. Create a table that matches structure of ref cursor, and create entity object based on that. Drop table once object created.
2. Follow Create table, but don't select a table, just add each field manually with New button.
3. Something else?
Thank you, Ken 
Option 1 would save you time if you can type out the CREATE TABLE statement matching the structure of your REF CURSOR faster than you can click around in the wizard defining the same properties by hand that our New Entity Object wizard would reverse-engineer for you.

Forms 11gR1 with Database Package

Hi everyone, I'm asking this here, i need to know what i'm doing wrong.I have a database package that contains pipiled record. I want to use it in a form with forms builder. if i do  
select * from table(mypackage.myfunction());
  in PL/SQL Developper, i get all what i except (list of record) if i try to use this select in a CURSOR inside my form, i get error about SQL type not authorisedwith my from table(); but if i use it with a populate_group_with_query, everything works perfectly. So, there's a way i can use my package inside a form program unit? Thanks for your help.
The PL/SQL interpretor built into Oracle Forms is not at the same level as the database.  Consequently, depending on where SQL or PL/SQL is parsed - your code will pass or fail.  In the case where you get an error in a CURSOR, the Forms PL/SQL interpretor is validating the SQL statement and it fails because it doesn't support the pipelined construct.  However, because the POPULATE_GROUP_WITH_QUERY send the SQL statement to the database to be parsed/validate, it succeeds because the database does support pipelined construct and Forms is simply receiving the record set returned by the database. 
Neimad wrote:
So, there's a way i can use my package inside a form program unit?
Yes, have your database package populate and return a collection; which Forms can handle.  Then, in your Forms trigger just LOOP through the collection to process/display the data.  Another option would be to use the pipelined query in your package. Craig...
Thanks for your answer,Have you an example to populate the database package to return a collection? Is it like POPULATE_GROUP_WITH_QUERY? Thanks again for your help, i'm lost about this. Have a nice day!
No, working with collection is not like the POPLUATE_GROUP_WITH_QUERY built-in because you have to write the code to process the rows and columns of the collection.  A collection, in Oracle, is essentially an Array.To work with a collection, you have to create an object in your trigger PL/SQL to store the collection.  To do this you create a user defined TYPE and then create an object based on this type.  Take a look at the article, https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-procedure-based-block for an example of how to write a database package the uses a collection as the source of data for a Forms data block.Base on this article, in your Forms trigger you would then create an object typed off of the package type T_EMPLOYEE.  Next, you call your database package and assign the collection to your trigger object.  Finally, you loop through the collection to perform the needed action on each row.  Here is an example of the basic code using the article above as an example:/* sample code - for demonstration purposes *//* code is untested */DECLARE  rec_employees   EMPLOYEE.T_EMPLOYEE;BEGIN  EMPLOYEE.EMP_SELECT(rec_employees);    FOR idx in 1 .. rec_employees.COUNT LOOP    -- now do something with each column in the row...    rec_employees(idx).empno ...    rec_employees(idx).ename ...    rec_employees(idx).job ...    etc...  END LOOP;END;Hope this helps.Craig...
Thanks a lot for your help.I will read the article and try it. I hope this will be ok! Thanks!
Well, if your pipelined function doesn't have arguments the easiest way would be to wrap a view around it and select from the view. If you have arguments there is another ugly workaround for that; just make getter/setters for the parameters and call them accordingly: 
create or replace package body get_set as
  val1 number;
  --
procedure set_val1(inval in number) is
begin
val1 := inval;
end;
--
function get_val1 return number is
begin
return val1;
end;
end;
/
 and create the view like 
create or replace force view my_view as select * from mypackage.myfunction(get_set.get_val1);
 Then you'd simply need to call get_set.set_val1 in forms before you open a cursor on my_view. It is a ugly workaround, but IMHO better then using dynamic SQL. cheers

Categories

Resources