If you could expound on it a little it would help! Thank you by the way for your help!
On Jul 22, 2:58 pm, Michael Moore <[email protected]> wrote: > let me know if that information was sufficient. I dont want to spend time > explaining something that you already understand. :-)... > > read more » > > On Wed, Jul 22, 2009 at 8:15 AM, Michael Moore <[email protected]>wrote: > > > > > I'm not at work now so I'l give a quick answer and a more detailed answer > > later. > > > PL/SQL has a DATE datatype. > > > define > > mydate DATE; > > myotherdate DATE; > > thrddate DATE; > > > mystring varchar2(100); > > begin > > mydate := sysdate; > > mystring = '20090423'; > > myotherdate := to_date(mystring,'yyyymmdd'); > > thrddate := sysdate -12; > > > If mydate > myother date then > > null; > > end if; > > > if myother date between mydate and thrddate then > > null; > > end if; > > > end; > > > this may give you some ideas. > > > On Wed, Jul 22, 2009 at 4:11 AM, jmoore <[email protected]> wrote: > > >> I created a package and in that package will be 3 procedures. I need > >> some help with the first one, if anyone has a few minutes. I am > >> passing as input member number, option, fromdate(20090701), thrudate > >> (20090720) If the option = 1 I compare billdate, if 2 readdate. > > >> I need to format billdate from 090701 to CCYYMMDD so I can see if they > >> are between from-thru. I am trying to send back output of location, > >> KWH to my Cobol program. How do I format? Also, can I just do a > >> regular select in the procedure? I am only passing back 1 record. > >> Thanks for everyone's help. I am taking baby steps and trying to just > >> do a litlle i-o with a few procedures and pass back to cobol. The > >> second problem is we don't have anything setup to create the reports > >> (page breaks) etc in the procedure. If anyone has some Pseudocode to > >> get me over the hump it would be greatly appreciated. Below is what > >> Cobol is passing and what the procedure is defined as: > > >> MOVE D-MEMBERDETL-MBRSEP-D(SS1)TO P-MBRSEP > >> MOVE WW-SELECT TO P-OPTION > >> MOVE Y2K-FROM-DATE TO P-FROMDATE > >> MOVE Y2K-THRU-DATE TO P-THRUDATE > >> EXEC SQL > >> EXECUTE > > >> BEGIN > >> subrport.getmbrhist > >> ( > >> :P-MBRSEP > >> ,:P-OPTION > >> ,:P-FROMDATE > >> ,:P-THRUDATE > >> ,:O-KWH > >> ,:O-SLKWH > >> ,:O-LOCATION > >> ,:S-STATUS > >> ); > > >> END; > >> END- > >> EXEC. > >> Procedure: > > >> PROCEDURE Getmbrhist(P_MBRSEP MBRHISTDETL.MBRSEP%TYPE,P_OPTION > >> VARCHAR2,P_FROMDATE DATE,P_THRUDATE DATE, > >> P_LOCATION OUT NOCOPY > >> VARCHAR2, P_KWH OUT NOCOPY VARCHAR2,P_SLKWH OUT NOCOPY VARCHAR2, > >> P_STATUS OUT NUMBER) > > >> On Jul 20, 7:58 pm, "Rob Wolfe" <[email protected]> wrote: > >> > I really like the APEX idea, a lovely tool. > > >> > My motto is always ... if it aint broke, don't try to port it to another > >> > platform. But that is just me. > > >> > I will be interested in hearing how painful (or not) the exercise was > >> when > >> > you are completed. > > >> > Rob > > >> > On Mon, July 20, 2009 11:32, Michael Moore wrote: > >> > > By "The schedule ..." I meant "Then schedule ..." , but I'd like to > >> > > elaborate. DBMS_SCHEDULER can be set up to run jobs (plsql/processes) > >> on a > >> > > time interval basis, or on-demand" An application such as APEX, can > >> accept > >> > > the user input and then trigger the appropriate DBMS_SCHEDULER job to > >> run. > >> > > Mike > > >> > > On Mon, Jul 20, 2009 at 7:54 AM, jmoore <[email protected]> wrote: > > >> > >> Thanks for your help!! I will try your approach! > > >> > >> On Jul 18, 2:53 pm, Michael Moore <[email protected]> wrote: > >> > >> > Start with a complete understanding of the types of structures that > >> > >> PL/SQL > >> > >> > does: > >> > >> > Package spec / Package body/ Package Global declarations / blocks > >> > >> /scope > >> > >> etc > >> > >> > etc. The take a look at the structure of your COBOL programs. I.E > >> do > >> > >> you > >> > >> use > >> > >> > 'common-coupling' or is everything passed through parameters. I > >> would > >> > >> not > >> > >> > try for a direct conversion, but go with a complete re-write once > >> you > >> > >> > understand the business logic. > > >> > >> > As for user input, consider using something like APEX to collect > >> the > >> > >> input > >> > >> > data into a holding table. The schedule your PL/SQL process to run > >> by > >> > >> using > >> > >> > the DBMS_SCHEDULER package. You batch jobs would then read from the > >> > >> holding > >> > >> > table. > > >> > >> > If I were a COBOL coder (which I was for about 10 years) I'd just > >> be > >> > >> happy > >> > >> > for the opportunity to learn PL/SQL. > > >> > >> > Mike > > >> > >> > On Fri, Jul 17, 2009 at 6:36 AM, jmoore <[email protected]> > >> wrote: > > >> > >> > > On Jul 17, 8:49 am, Rob Wolfe <[email protected]> wrote: > >> > >> > > > On Jul 17, 6:35 am, jmoore <[email protected]> wrote: > > >> > >> > > > > It certianly wasn't my idea to convert programs to pl/sql. We > >> > >> have > >> > >> > > > > plenty of Cobol programmers here. It was somebody's bright > >> idea > >> > >> way > >> > >> > > > > above us that we are going to convert everything to pl/sql > >> > >> procedures > >> > >> > > > > and packages. I guess they do not want to have to pay for > >> > >> micr-focus > >> > >> > > > > anymore. A few of us Cobol programmers have voiced our > >> concerns, > >> > >> but > >> > >> > > > > to no avail. They have not set any kind of standards yet, its > >> > >> more > >> > >> of > >> > >> > > > > the owner says do it. We have pl/sql programmers in India > >> that > >> > >> work > >> > >> > > > > for this company, but there isn't much standardization in > >> what I > >> > >> have > >> > >> > > > > seen. Also, I do not believe it will increase performance > >> when > >> > >> Oracle > >> > >> > > > > is having to load so many porcedures at once. I am just > >> looking > >> > >> for > >> > >> > > > > some good examples of how to use for while loops etc. Being a > >> > >> cobol > >> > >> > > > > guy I am used to structure and from what I have seen these > >> are > >> > >> not. > >> > >> I > >> > >> > > > > need some good logic where 1 table is read and it has to pass > >> by > >> > >> some > >> > >> > > > > exceptions (if not go back read next record) then takes the > >> key > >> > >> and > >> > >> > > > > gets info from table 2, passes some exceptions maybe table 3 > >> and > >> > >> than > >> > >> > > > > it would write out to a sort file. The return would be to > >> create > >> > >> a > >> > >> > > > > file or printfile. The batch programs we have ask the users > >> > >> things > >> > >> > > > > like > >> > >> > > > > Enter from date > >> > >> > > > > Enter thru date > > >> > >> > > > > Enter dept > >> > >> > > > > 1. all > >> > >> > > > > 2. specific > >> > >> > > > > Uses these variables to read the tables to create the sort > >> file. > > >> > >> > > > > On Jul 16, 11:51 pm, Rob Wolfe <[email protected]> wrote: > > >> > >> > > > > > On Jul 16, 2:10 pm, jmoore <[email protected]> wrote: > > >> > >> > > > > > > Does anyone have any example code of Cobol programs that > >> > >> were > >> > >> > > > > > > converted to PL/sql procedures/packages? Our company is > >> > >> trying > >> > >> to > >> > >> > > > > > > convert programs to pl/sql and they really haven't > >> addressed > >> > >> many > >> > >> > > of > >> > >> > > > > > > the challenges. First being batch programs that > >> > >> display/accept > >> > >> data > >> > >> > > > > > > from the user. Any help would be greatly appreciated. I > >> > >> certianly > >> > >> > > hope > >> > >> > > > > > > there is at least one dinosaur like me in this forum. > > >> > >> > > > > > Big Dino-roar from here. Can I ask why you would want to > >> do > >> > >> such > >> > >> a > >> > >> > > > > > horrible thing to yourself? Seriously, why would you > >> convert > >> > >> cobol to > >> > >> > > > > > PL/SQL of all things? There are lots of perfectly good > >> cobol > >> > >> > > compilers > >> > >> > > > > > out there and Oracle plays quite nicely with them. > >> > >> > > > > > I know that if someone came up to me with the idea of > >> > >> rewriting a > >> > >> > > > > > bunch of cobol programs in pl/sql I would want a very > >> > >> convincing > >> > >> > > > > > business case for the project. > >> > >> > > > > > Even if you are rehosting from a mainframe to (for > >> instance) a > >> > >> linux > >> > >> > > > > > VM there is still no good reason to do what you are > >> thinking > >> > >> about > >> > >> > > > > > unless someone has a religious aversion to cobol. If you > >> are > >> > >> short on > >> > >> > > > > > cobol programmers then I would suggest that training one of > >> > >> your > >> > >> > > > > > existing ones would be cheaper than converting anything but > >> > >> the > >> > >> most > >> > >> > > > > > trivial program to pl/sql. > > >> > >> > > > > > I would like to hear more about the thinking behind this > >> > >> project > >> > >> > > > > > because you have bitten off some nastiness. I do have to > >> say > >> > >> that > >> > >> I > >> > >> > > > > > don't understand one thing ... in my experience users don't > >> > >> interact > >> > >> > > > > > with batch programs, that is kind of the point of them. Or > >> am > >> > >> I > >> > >> > > > > > missing something? > > >> > >> > > > You will find as much or more structure in PL/SQL than you will > >> in > >> > >> > > > cobol. Honestly, you will be best off working through a good > >> > >> pl/sql > >> > >> > > > tutorial/cookbook (there are a bunch on amazon.com). I do have > >> to > >> > >> > > > reiterate that this is simply a dumb idea (I know it isnt > >> yours) > >> > >> and > >> > >> > > > someone has to grow a pair and tell management that they are > >> going > >> > >> to > >> > >> > > > spend more rewriting (this isnt a conversion- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
