Re: Full PL/SQL Programs

From: Bruce Pihlamae <pihlab_at_cbr.hhcs.gov.au>
Date: 1995/03/29
Message-ID: <1995Mar29.081112.1_at_cbr.hhcs.gov.au>#1/1

In article <3l6scs$fcq_at_dcsun4.us.oracle.com>, surman_at_oracle.com (Scott Urman) writes:
> You need to create procedures in the database with a 'create or replace procedure' command. Check out the Application Developer's Guide.
>
> In article <1995Mar24.004046.22795_at_biosym.com>, ahr_at_biosym.com (Alan H. Rosenthal ) writes:
> |> Hi,
> |>
> |> I have rtfm'ed and I can not find an example of a full PL/SQL
> |> 2.0 program that runs in the SQL*PLUS environment. Specifically,
> |> I want to be able to write a program that consists of a single
> |> main routine and a few procedures and functions.
> |>
> |> For example:
> |>
> |> procedure xyz is
> |> n number ;
> |> begin
> |> n := 1 ;
> |> end ;
> |> begin
> |> xyz ;
> |> end ;
> |>
> |> when I try to run this I get a syntax error on the first line.
> |>
> |> Can you write such a program?????

You can either use the CREATE OR REPLACE PROCEDURE xyz command and build your procedure inside the database and call it from sqlplus

 OR

I think you can do it inside sqlplus by enclosing your program inside a BEGIN...END;/ block. SQLPLUS passes everything in the BEGIN...END block to the database for execution so you can define multiple procedures and functions in there and execute a main routine that calls them.

It is more efficient to use PACKAGES inside the database however because of parsing and network transmission of your 'program' from sqlplus.

Bruce... pihlab_at_cbr.hhcs.gov.au

> |>
> |>
> |> Thanks,
> |>
> |> alan
> |>
> |>
> |>
> |> --
> |>
> |> +=============================================================+
> |> | Alan Rosenthal :O) email ahr_at_biosym.com |
> |> | Management Information Systems vmail 619-597-9732 |
> |> | Biosym Technologies fax 619-458-0136 |

 +-------------------------------------------------------------+


> |> |        The thinking that got us where we are today          |

> |> |       will not get us where we want to go tommorrow         |

> |> +-------------------------------------------------------------+

> |> 

> |> -- 

> |> *******************************************************************************

> |> * DISCLAIMER: Unless indicated otherwise, everything in this note is          *

> |> * personal opinion, not an official statement of Biosym Technologies, Inc. *
> |> *******************************************************************************

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras

Received on Wed Mar 29 1995 - 00:00:00 CEST