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 project, it is a
> >> rewrite)
> >> > > > the code than they are going to save on licenses (back it up with
> >> > > > numbers, MF Cobol isnt THAT expensive, if you can afford
> >> Oracle...).
> >> > > > As soon as they see a negative ROI for the project, if they are
> >> > > > competent, they will back off on it. Certainly as new
> >> functionality
> >> is
> >> > > > added you can use PL/SQL to implement it but you have the makings
> >> of
> >> a
> >> > > > disaster here.
>
> >> > > > Sorry I can't sound more positive about this but I have
> >> participated
> >> > > > in the aftermath of top down technical decisions and they are
> >> nearly
> >> > > > uniformly a result of someone being sold something or reading
> >> > > > something and half understanding it. It should be VERY easy to do
> >> a
> >> > > > quick ROI on this and I would be shocked if it comes out positive.
> >> I
> >> > > > think that it is the duty of professional development staff to
> >> push
> >> > > > back hard on really dumb ideas with as much business related
> >> > > > information as they can muster.
>
> >> > > > Rob- Hide quoted text -
>
> >> > > > - Show quoted text -
>
> >> > > Unfortunately, someone in product design took a pl/sql class or
> >> > > something and sold this bill of goods to the president. Everything
> >> > > here is needed yesterday w/o much forethought. So I just have to do
> >> > > what I have to do. The job market is quite bleak at the moment. But
> >> I
> >> > > whole-heartedly agree with everything you have written- Hide quoted
> >> text -
>
> >> > - Show quoted text -- 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
-~----------~----~----~----~------~----~------~--~---
