Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql variables

Re: pl/sql variables

From: tony <ttigger4321_at_hotmail.com>
Date: Fri, 07 Jun 2002 13:05:51 GMT
Message-ID: <3d00af67.350177161@news.dal.ca>


This looks like it should help, but, I can't compile it & have no idea why.

SQL> start aPackage
Package created.
Warning: Package Body created with compilation errors. SQL> On Thu, 6 Jun 2002 18:58:26 +0200, "Richard Spee" <rhpspee_at_wxs.nl> wrote:

>This is a multi-part message in MIME format.
>
>------=_NextPart_000_0030_01C20D8C.23BF0920
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: quoted-printable
>
>This examples makes use of package variables.
>Just run the script and see what happens.
>
>--
>set serveroutput on size 1000000
>--
>CREATE OR REPLACE PACKAGE aPackage
>IS
>--
>-- public procedures
>--
>PROCEDURE aPublicProcedure
>(anInputVariable IN VARCHAR2
>);
>--
>-- public package variables
>--
>aPublicVariable varchar2(20);
>--
>END aPackage;
>/
>
>CREATE OR REPLACE PACKAGE BODY aPackage
>IS
>--
>PROCEDURE aLocalProcedure
>(anInputVariable IN VARCHAR2
>)
>IS
>BEGIN
> dbms_output.put_line(anInputVariable);
>END;
>--
>--
>PROCEDURE aPublicProcedure
>(anInputVariable IN VARCHAR2
>)
>IS
>--
>aLocalVariable VARCHAR2(20);
>--
>BEGIN
> aLocalProcedure('aPublicVariable: '||aPublicVariable);
> aLocalProcedure('aLocalVariable: '||aLocalVariable);
> --
> if aPublicVariable is null
> then
> aPublicVariable :=3D anInputVariable;
> end if;
> --
> aLocalVariable :=3D aPublicVariable;
> --
>END;
>--
>END aPackage;
>/
>execute aPackage.aPublicProcedure('Testing.. 1.2.3');
>execute aPackage.aPublicProcedure('Testing.. 1.2.3');
>
>
>"tony" <ttigger4321_at_hotmail.com> wrote in message =
>news:3cff7bdd.271435646_at_news.dal.ca...
>> I would like to learn more about variables in pl/sql. =20
>>=20
>> What I am tryng to do is write dynamic sql like this:
>>=20
>> 'select part_no, part_desc from parts
>> order by ' || v_orderby || ' ' || v_asc_desc;
>>=20
>> I would like to be able to do something like:
>>=20
>> IF v_orderby =3D part_no AND v_asc_desc =3D asc THEN
>> v_asc_desc =3D desc
>> END IF
>>=20
>> Every time I call the procedure the variables get initialized. How
>> can I store their previous values in memory?
>>=20
>> thanks
>>=20
>> cheers,
>> tony
>> 902.494.1021 w
>> 902.461.4550 h
>
>------=_NextPart_000_0030_01C20D8C.23BF0920
>Content-Type: text/html;
> charset="iso-8859-1"
>Content-Transfer-Encoding: quoted-printable
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
><HTML><HEAD>
><META content=3D"text/html; charset=3Diso-8859-1" =
>http-equiv=3DContent-Type>
><META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
><STYLE></STYLE>
></HEAD>
><BODY>
><DIV><FONT face=3DCourier size=3D2>This examples makes use of package=20
>variables.</FONT></DIV>
><DIV><FONT face=3DCourier size=3D2>Just run the script and see what=20
>happens.</FONT></DIV>
><DIV><FONT size=3D2><FONT face=3DCourier></FONT></FONT>&nbsp;</DIV>
><DIV><FONT size=3D2><FONT face=3DCourier>--<BR>set serveroutput on size=20
>1000000<BR>--<BR>CREATE OR REPLACE PACKAGE aPackage<BR>IS<BR>--<BR>-- =
>public=20
>procedures<BR>--<BR>PROCEDURE aPublicProcedure<BR>(anInputVariable&nbsp; =
>IN=20
>VARCHAR2<BR>);<BR>--<BR>-- public package =
>variables<BR>--<BR>aPublicVariable=20
>varchar2(20);<BR>--<BR>END aPackage;<BR>/</FONT></FONT></DIV>
><DIV>&nbsp;</DIV>
><DIV><FONT size=3D2><FONT face=3DCourier>CREATE OR REPLACE PACKAGE BODY=20
>aPackage<BR>IS<BR>--<BR>PROCEDURE =
>aLocalProcedure<BR>(anInputVariable&nbsp; IN=20
>VARCHAR2<BR>)<BR>IS<BR>BEGIN<BR>&nbsp;&nbsp;=20
>dbms_output.put_line(anInputVariable);<BR>END;<BR>--<BR>--<BR>PROCEDURE=20
>aPublicProcedure<BR>(anInputVariable&nbsp; IN=20
>VARCHAR2<BR>)<BR>IS<BR>--<BR>aLocalVariable&nbsp;=20
>VARCHAR2(20);<BR>--<BR>BEGIN<BR>&nbsp;&nbsp; =
>aLocalProcedure('aPublicVariable:=20
>'||aPublicVariable);<BR>&nbsp;&nbsp; aLocalProcedure('aLocalVariable:=20
>'||aLocalVariable);<BR>&nbsp;&nbsp; --<BR>&nbsp;&nbsp; if =
>aPublicVariable is=20
>null<BR>&nbsp;&nbsp; then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
>aPublicVariable :=3D=20
>anInputVariable;<BR>&nbsp;&nbsp; end if;<BR>&nbsp;&nbsp; =
>--<BR>&nbsp;&nbsp;=20
>aLocalVariable :=3D aPublicVariable;<BR>&nbsp;&nbsp; =
>--<BR>END;<BR>--<BR>END=20
>aPackage;<BR>/<BR>execute aPackage.aPublicProcedure('Testing..=20
>1.2.3');<BR>execute aPackage.aPublicProcedure('Testing..=20
>1.2.3');<BR></FONT></DIV></FONT>
><DIV>&nbsp;</DIV>
><DIV><FONT face=3DCourier size=3D2>"tony" &lt;</FONT><A=20
>href=3D"mailto:ttigger4321_at_hotmail.com"><FONT face=3DCourier=20
>size=3D2>ttigger4321_at_hotmail.com</FONT></A><FONT face=3DCourier =
>size=3D2>&gt; wrote in=20
>message </FONT><A href=3D"news:3cff7bdd.271435646_at_news.dal.ca"><FONT =
>face=3DCourier=20
>size=3D2>news:3cff7bdd.271435646_at_news.dal.ca</FONT></A><FONT =
>face=3DCourier=20
>size=3D2>...</FONT></DIV><FONT face=3DCourier size=3D2>&gt; I would like =
>to learn more=20
>about variables in pl/sql.&nbsp; <BR>&gt; <BR>&gt; What I am tryng to do =
>is=20
>write dynamic sql like this:<BR>&gt; <BR>&gt; 'select part_no, part_desc =
>from=20
>parts<BR>&gt; order by ' || v_orderby || ' ' || v_asc_desc;<BR>&gt; =
><BR>&gt; I=20
>would like to be able to do something like:<BR>&gt; <BR>&gt; IF =
>v_orderby =3D=20
>part_no AND v_asc_desc =3D asc THEN<BR>&gt; &nbsp;&nbsp; v_asc_desc =3D =
>desc<BR>&gt;=20
>END IF<BR>&gt; <BR>&gt; Every time I call the procedure the variables =
>get=20
>initialized.&nbsp; How<BR>&gt; can I store their previous values in=20
>memory?<BR>&gt; <BR>&gt; thanks<BR>&gt; <BR>&gt; cheers,<BR>&gt; =
>tony<BR>&gt;=20
>902.494.1021 w<BR>&gt; 902.461.4550 h</FONT></BODY></HTML>
>
>------=_NextPart_000_0030_01C20D8C.23BF0920--
>

cheers,
tony
902.494.1021 w
902.461.4550 h Received on Fri Jun 07 2002 - 08:05:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US