This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1ECFD.54CA0010
Content-Type: text/plain
also found the two loosely related doc's below,
************
Problem Description :
How to call an Oracle stored procedure to generate a sequence number in QTP
without losing numbers when no records are processed.
Solution Description :
The declare cursor syntax will call the procedure for every input record. To
avoid losing sequence numbers then the SQL CALL syntax needs to be used e.g.
:
run test
global temporary t_next_bat_no num*6
request test1
access customers
select if bal <> 0
sql at initial call sel_next_sl_bat_no(t_next_bat_no)
output postings add
item batch_no final t_next_bat_no
************
Problem Description
Are there any example of how to call an Oracle Stored Procedure from
Powerhouse?
Solution Description
Note, PowerHouse does not support Oracle Stored Procedures that return
multiple rows.
An example of how to call a stored procedure that returns a result is below:
Code to create the procedure in SQLPlus:
> CREATE OR REPLACE PROCEDURE GET_TOTAL(dept IN NUMBER, empcount out number)
IS
> BEGIN
> SELECT count(*) INTO EMPCOUNT from emp where DEPTNO=dept;
> END;
> /
Code to test the procedure in SQLPlus:
> VARIABLE tot NUMBER
> EXECUTE get_total(10,:tot);
> PRINT tot;
QDesign code to call the procedure:
> SCREEN orasptest
> TEMP tdept NUM INIT 10
> TEMP ttot NUM
> FIELD tdept LABEL "Dept"
> FIELD ttot DISPLAY
>
> PROCEDURE DESIGNER test
> BEGIN
> SQL IN oradb CALL get_total(tdept IN, ttot OUT)
> DISPLAY ttot
> END
Check the Oracle Documentation for further details on Oracle Stored
Procedures.
-----Original Message-----
From: Darren Reely [mailto:darren.reely@latticesemi.com]
Sent: 25 April 2002 19:30
To: 'powerh-l@lists.swau.edu'
Subject: Oracle Sequence Numbers
Hi all,
Is there anyway to implement the following cursor so I can have the
database return a sequence number.
sql in mfg declare myseq cursor for &
select trays_seq.nextval from dual
cursor myseq designer
Platform:
Using PH 8.13.D1
Solaris
Oracle 7.3.4.4.0
Darren,
Thank you.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
This message may contain privileged and/or confidential information. If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. Thank you.
------_=_NextPart_001_01C1ECFD.54CA0010
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
RE: Oracle Sequence Numbers
also found the two loosely related doc's =
below,
************
Problem Description :
How to call an Oracle stored procedure to generate a =
sequence number in QTP without losing numbers when no records are =
processed.
Solution Description :
The declare cursor syntax will call the procedure for =
every input record. To avoid losing sequence numbers then the SQL CALL =
syntax needs to be used e.g. :
run test
global temporary t_next_bat_no num*6
request test1
access customers
select if bal <> 0
sql at initial call =
sel_next_sl_bat_no(t_next_bat_no)
output postings add
item batch_no final t_next_bat_no
************
Problem Description
Are there any example of how to call an Oracle Stored =
Procedure from Powerhouse?
Solution Description
Note, PowerHouse does not support Oracle Stored =
Procedures that return multiple rows.
An example of how to call a stored procedure that =
returns a result is below:
Code to create the procedure in SQLPlus:
> CREATE OR REPLACE PROCEDURE GET_TOTAL(dept IN =
NUMBER, empcount out number) IS
> BEGIN
> SELECT count(*) INTO EMPCOUNT from =
emp where DEPTNO=3Ddept;
> END;
> /
Code to test the procedure in SQLPlus:
> VARIABLE tot NUMBER
> EXECUTE get_total(10,:tot);
> PRINT tot;
QDesign code to call the procedure:
> SCREEN orasptest
> TEMP tdept NUM INIT 10
> TEMP ttot NUM
> FIELD tdept LABEL "Dept"
> FIELD ttot DISPLAY
>
> PROCEDURE DESIGNER test
> BEGIN
> SQL IN oradb CALL get_total(tdept IN, =
ttot OUT)
> DISPLAY ttot
> END
Check the Oracle Documentation for further =
details on Oracle Stored Procedures.
-----Original Message-----
From: Darren Reely [mailto:darren.reely@lattice=
semi.com]
Sent: 25 April 2002 19:30
To: 'powerh-l@lists.swau.edu'
Subject: Oracle Sequence Numbers
Hi all,
Is there anyway to implement the following cursor so =
I can have the
database return a sequence number.
sql in mfg declare myseq cursor for &
select =
trays_seq.nextval from dual
cursor myseq designer
Platform:
Using PH 8.13.D1
Solaris
Oracle 7.3.4.4.0
Darren,
Thank you.
=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =
=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to =
powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body =
to powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you =
must be a subscriber.
This message may contain privileged and/or =
confidential information. If you have received this e-mail in =
error or are not the intended recipient, you may not use, copy, =
disseminate or distribute it; do not open any attachments, delete it =
immediately from your system and notify the sender promptly by e-mail =
that you have done so. Thank you.
------_=_NextPart_001_01C1ECFD.54CA0010--