Re: Help! Simple Oracle Question!

From: Oracleguru, Suresh Bhat <oracleguru_at_mailcity.com>
Date: Wed, 24 Feb 1999 20:05:01 GMT
Message-ID: <01be6041$00f74b60$a504fa80@mndnet>

Hi !

This is for UNIX.

Since you say you are new to Oracle, I don't think you want to put the create cpmmand in PL/SQL procedure. What you want to do is put your command in a script, let us say x.sql.

Then in SQL*Plus, you would simply enter

@x
or
@x.sql

which should create the table.

---

To clarify what Joel wrote, Boolean is a valid data type in PL/SQL. I use it to debug my PL/SQL procedures. Here is how I use it.

pkg_general used in the code fragment below is another PL/SQL package that I wrote in-house.

Code fragment:

create or replace
package pkg_meter_data
is

    global_debug                 boolean;
    global_update_user_id    varchar2 (31);
    global_update_date         date;
    temp_varchar21             varchar2 (80);

--

   procedure update_meter_id

               (debug                   in      boolean default FALSE );

.
.
.
.

END; -- of specification for package pkg_meter_data /
create or replace
package body pkg_meter_data
is   

/************************************************************************
   *                                                                       
	        *
   *                   PROCEDURE    UPDATE_METER_ID                        
*
   *                                                                       
	        *
  
************************************************************************/
procedure       update_meter_id
               (debug                   in      boolean default FALSE )

is
BEGIN
   /*

      Assign DEBUG to GLOBAL_DEBUG, so that it will be used in all the
      Procedures and Functions called by this Procedure.

   */
   --
   global_debug := debug;
   --
   if global_debug then

      dbms_output.put_line('Entering Procedure UPDATE_METER_ID');    end if;
.
.
.

Other dbms_output.put_line statements with debug info.
.
.
.

EXCEPTION
   when others then

      /*
         Do not anticipate an EXCEPTION in this procedure. Safety feature.
      */
      rollback;
      dbms_output.put_line
         ('********** PROBLEM **********');
      pkg_general.show_sql_error_message
         ( 'UPDATE_METER_ID: ',
           sqlerrm(sqlcode)
         );
END;    -- of procedure                 update_meter_id

.
.

other procedures and functions
.
.

BEGIN -- Initialization of Package Body

   /*

      The statements here are executed only once whenever any of
      the objects in this package are accessed the first time.

   */
   pkg_general.get_session_info

      ( global_update_user_id,
        global_update_date,
        temp_varchar21
      );
END;    -- of body for package          pkg_meter_data

/

When I call the procedure, say in SQL*Plus:

If I want to debug and print lines on screen, then I enter

   set serveroutput on size 1000000
   execute pkg_meter_data.update_meter_id(TRUE)

If I do not want screen printout, then I enter

   execute pkg_meter_data.update_meter_id

hth

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Joel R. Kallman <jkallman_at_us.oracle.com> wrote in article <36d44d32.21249655_at_newshost.us.oracle.com>...

> On Wed, 24 Feb 1999 12:33:33 -0800, Vipul Desai <desai65_at_ixnetcom.com>
> wrote:
> 
> >I am a new user to Oracle 8.05 coming from a Visual Basic/SQL Server 
> >background. I am trying to create a template for a table within SQL Plus

and
> >I run the following command from my script. I am have trouble a table > >whereby my script is crashing with the following code. The compiler is not
> >liking the datatype Boolean, which to my understanding is a valid data type
> >for the Oracle server. Can someone please help? Also, can someone tell me
> >whether or not I can put this into a PL/SQL procedure. I am having trouble

> >with this as well. Thanks, Vipul
> >
> >
> >CREATE TABLE COLLAPSED (
> >   STUDY_NUM_DATE VARCHAR2(15),
> >   STUDY_NUM_DATE VARCHAR2(10),
> >   LOGISTIC_NUM VARCHAR2(15),
> >   SUBMIT_NAME VARCHAR2(25),
> >   STUDY_DATE DATE,
> >   LOT_NUM VARCHAR2(25),
> >   AUDIT_REQUESTED BOOLEAN,
> >   CHEMIST_NAME VARCHAR(25),
> >   DATE_GIVEN_TO_CHEMIST DATE,
> >   DATE_REPORTED_TO_CHEMIST DATE,
> >   FINISH_DATE DATE,
> >   PULL_DATE DATE,
> >   AUDIT_DATE DATE) ;
> 
> BOOLEAN is not a valid datatype.  You could implement this multiple
> ways (NUMBER of 0 or 1, VARCHAR2) with your own semantics.
> 
> Chapter 2 of the Oracle8 SQL Reference lists all of the valid Oracle
> datatypes.
> 
> To put this in a PL/SQL procedure, you will have to use dynamic SQL
> (check out the Dynamic SQL chapter of the Oracle8 Application
> Developer's Guide).  BTW, this becomes a lot easier in Oracle8i.
> 
> Thanks!
> 
> Joel
> 
> Joel R. Kallman                          
> Oracle Service Industries
> Columbus, OH            
> jkallman@us.oracle.com                   http://www.oracle.com

> 
> ----
> The statements and opinions expressed here are my own 
> and do not necessarily represent those of Oracle Corporation.
> 

Received on Wed Feb 24 1999 - 14:05:01 CST