Execute SP in Oracle

Jesper_Wiken_(IDNordicUser2_(Delete)_2300285)

Is it possible to execute a stored procedure that updates the database in oracle?Some info indicates it is possible the "normal" way:https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=2655015&objAction=viewBut some info indicates it is not:https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=2662327&objAction=view&show=2The select from dual does not work when the procedure updates the db.I cant get the stmt="execute my_procedure" to work.Any help is greatly appreciated!/Jesper

Find more posts tagged with

Comments

Mohsin_Jessa_(mjessa_(Delete)_2724055)

Hi LL Developpers:Thanks to Jesper for pointing me in the right direction and providing me with the base code to work with.Apparently this can be done at least via LiveReports.See the zip file attached. It contains screen shots of a livereport (the sql in the LR, the input parameters), the input screen generated when the LR is run and the confirmation of message output by the stored procedure, the FOO table and it's contents in the Livelink schema that is updated by the stored procedure, and the personal workspace in my Livelink instance where the LR is stored. The LR is called Run_a_SP_from_LL.I have tested this on Oracle 9iRelease2 (currently not supported for LiveLink) and LL91SP3. I'd appreciate being informed if this does not work in Oracle 8i.Below is the code for the Function, Stored Procedure (called from the function) and the results from running the function and the stored procedure from a SQL+ session.01:02:28 SQL> select ll91sp3.runupd ('555','James') as mmj from dual;MMJ-----------------------------------------------------------------------------------------it works1 row selected.01:02:45 SQL> select * from ll91sp3.foo; ID NAME---------- ---------------------------------------- 44 Tjosan 44 Tjosan 44 Tjosan 44 Tjosan 44 Tjosan 999 Mohsin 7777 Jessa 666 Mohsin Jessa 666 Mohsin Jessa 555 James10 rows selected.01:03:07 SQL> The function:CREATE OR REPLACE function runupd ( LL_id number, LL_name char ) return varchar2 isbegin ll91sp3.updtest (LL_id,LL_name); return 'it works';end;/The stored Procedure:CREATE OR REPLACE procedure updtest (my_id number, my_name char) is pragma autonomous_transaction;begin insert into ll91sp3.foo (id,name) values (my_id, my_name); commit;end;/01:54:00 SQL> desc ll91sp3.foo Name Null? Type ----------------------------------------------------------------- -------- ------------------------ ID NUMBER(10) NAME VARCHAR2(40)01:54:05 SQL> Hope this helps.Mohsin JessaOracle Technical Specialist.Escalation Group.

Jesper_Wiken_(IDNordicUser2_(Delete)_2300285)

To execute a SP the syntax is:sql = Str.Format("BEGIN user.pkg.proc('%1','%2'); END;",\ var1,\ var2)res = CAPI.Exec(connection, sql)A function can also be executed from within a block.But there is no way to catch the return value from the function.

Magnus_S_(novnoruser_-_(deleted))

There's a new feature in oracle 9i that is called pipelined tables which enables stored procedures to return recordsets to livelink.

Pfizer_Developers

Can you please give an example livereport or oscript of how you achieved this?ThanksMohsin Jessa

Arun_Vijayakumar

Still I am not able to acheive this. Can anybody post some example that includes LiveReport side and the oracle procedure that brings some records?

Thanks in advance!

Greg_Griffiths

see Moshin Jessa's response at https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=3045092&objAction=viewincontainer does that provide what you are looking for ?