Jump to content


- - - - -

Oracle Stored Procedures


5 replies to this topic

#1 Guest_Chuck Levene_*

Guest_Chuck Levene_*
  • Guests

Posted 04 November 2002 - 03:20 PM

I am looking for the best way to call an Oracle Stored Procedure from ProIV. I understand that the Procedure will populate an Oracle table which will then be read from ProIV, I just can't seem to get the procedure to execute.
Any help, code samples, etc. will be appreciated.

#2 Gregg Barr

Gregg Barr

    Member

  • Members
  • PipPip
  • 18 posts
  • Gender:Male
  • Location:Kansas City, United States

Posted 07 November 2002 - 08:12 PM

I use SL so I am not sure if this will help but here is an example from a Glovia SL function:

1 PLSQLIN()
2 $EFFDATE = CDATE(@DATE,'MMDDYYYY')
3 #QTY = 1
4 $ASSEM_QTY = CONV(#QTY)
5 SQL
6 BEGIN
7 U_MLBOM.MAIN(:$PROCESS_ID,:CCN,:ITEM,:REVISION,
8 'CUR', ' ',:#QTY,' ','Y',
9 TO_DATE(:$EFFDATE,'MMDDYYYY'),' ','S');
10 END;
11 ENDSQL
12 PLSQLOUT()

In oracle the package is defined like this:
CREATE OR REPLACE PACKAGE BODY 'GLOVIA_PROD'.'U_MLBOM' AS

S_CURRENT NUMBER;
S_TAIL NUMBER;
S_NEXT_LEVEL NUMBER;
**** More defs ****
.
.
.


PROCEDURE MAIN(P_PROCESS_ID S_MLBOM.PROCESS_ID%TYPE,
P_CCN CCN.CCN%TYPE,
P_ITEM BOM.ITEM%TYPE,
P_REVISION BOM.REVISION%TYPE,
P_BCR_TYPE BOM.BCR_TYPE%TYPE,
P_BCR_ALT BOM.BCR_ALT%TYPE,
P_ASSEM_QTY VARCHAR2,
P_INCL_COST VARCHAR2,
P_INCL_ALT VARCHAR2,
P_EFFDATE DATE,
P_SUMMARY VARCHAR2,
P_SORT VARCHAR2) IS

**** CODE GOES HERE ****
.
.
.


END MAIN;

END U_MLBOM;

Hope this helps.

#3 Dan Shannon

Dan Shannon

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 374 posts
  • Gender:Male
  • Location:Australia

Posted 07 November 2002 - 09:10 PM

I'd do something similar but use SYS-SQL() to do it - SYS-SQL will execute immediately and is pretty reliable, whereas the SQL...ENDSQL method has a number of problems/issues on various platforms and versions, and doesn't actually appear to execute immediately. You can't (still, AFAIAA) use PL/SQL OUT parameters to pass values to PRO-IV variables properly as binding doesn't work with numbers properly.

Cheers

Dan Shannon

#4 Guest_Chuck Levene_*

Guest_Chuck Levene_*
  • Guests

Posted 08 November 2002 - 02:59 PM

thanks for all replies.

Gregg,
Are PLSQLIN() and PLSQLOUT() Global Logics that you
have created or are they part of ProIV? We are running version 5 and they do not exist for us(yet). What is
required in these Global Logics or where can I get them?

thanks again
chuckl

#5 Gregg Barr

Gregg Barr

    Member

  • Members
  • PipPip
  • 18 posts
  • Gender:Male
  • Location:Kansas City, United States

Posted 11 November 2002 - 01:16 PM

Chuck,

PLSQLIN() clears some return variable.

003 @MSG = 0
004 @MSGTEXT = ''
005 @MSGARGS = ''
006 @SYSERR = ''
007 @SYSERRTEXT = ''

PLSQLOUT() does the following.

003 IF @SYSERRTEXT # '' THEN
004 MSG(99)
005 @LFUNCT = @RFUNCT
006 #STAT = ROLLBACK()
007 FNEXIT
008 ENDIF

#6 Guest_Chuck Levene_*

Guest_Chuck Levene_*
  • Guests

Posted 13 November 2002 - 02:37 PM

Works great, thanks for all replies.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users