Re: A question to Martin.

Here's a related tip: always use CREATE OR REPLACE PROCEDURE (instead of CREATE PROCEDURE) so you can easily modify your source code and re-run the script without getting an error message that an object of this name already exists.

Martin Doherty wrote:

> That's OK Peter, you should always address your questions to the group
> anyway instead of relying on any one individual.
>
> It sounds like you'd really benefit from reading some documentation to
> understand the basic concepts around using SQL*Plus. If you go to
> http://tahiti.oracle.com you can browse or download the SQL*Plus
> User's Guide and Reference (click on your version of Oracle, then
> click on List Of Books, then scroll down to SQL*Plus User's Guide and
> Reference).
>
> Here's an explanation of running a script to create a PL/SQL procedure:
>
> You can run a script within SQL*Plus to automate the typing of
> commands at the SQL*Plus command prompt. It's equivalent to running a
> shell script in Unix, or a .bat file in MS-DOS or Windows. In your
> case, you have created a script called 'raise_salary.sql' that
> contains a single CREATE PROCEDURE command. To create the procedure,
> run the script by typing 'START raise_salary.sql' at the SQL> command
> prompt. This is what happens as a result:
> 1) SQL*Plus will try to find and open the file called raise_salary.sql
> 2) Because the first line starts with CREATE, SQL*Plus realizes that
> this is a DDL statement that needs to be submitted to the database
> server for processing.
> 3) It will read successive lines from the file into the SQL buffer
> (which is basically just a holding area for the lines of text that
> make up a single DML or DDL command), until it sees the slash (/)
> character on a line by itself. This signals SQL*Plus to stop reading
> lines into the buffer, and transmit the buffer to the database server
> for processing.
> 4) The database server receives the CREATE PROCEDURE command, creates
> the code object in the database with the name RAISE_SALARY, and runs
> it through the PL/SQL compiler. Note, this means you can create code
> objects even if they don't compile. It will be defined in the
> database, but its status will be INVALID and you won't be able to
> execute the code.
> 5) If the code compiles cleanly, the database will signal this to
> SQL*Plus, which will print the message 'Procedure Created'.
> 6) If the code has errors, the database will likewise signal this to
> SQL*Plus, which will print 'Procedure created with errors'. I expect
> your code example to give an error because your UPDATE has the keyword
> "set" misspelled as "st".
>
> To see the compiler error messages, type SHOW ERROR (or is it SHOW
> ERRORS? I forget).
>
> To see your code objects, type
> SELECT * FROM USER_OBJECTS ORDER BY OBJECT_TYPE, OBJECT_NAME;
> (Look for PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY. If your code
> isn't listed then something went wrong in the creation step).
>
> To see the source code directly from the database, type
> SELECT * FROM USER_SOURCE WHERE OBJECT_NAME = 'RAISE_SALARY';
>
> To execute your procedure, type EXEC RAISE_SALARY(25000); at the SQL>
> prompt.
>
> If you want, you can put the EXEC command into your raise_salary.sql
> script, so it will first create (or recreate) the procedure, then
> immediately try to execute it. A script can contain any number of
> different commands to be run in sequence.
>
> *Important concept: you cannot execute code that is stored in a text
> file!* You can only execute code that is stored in the database. You
> get your code into the database by 'START'ing your file as a script in
> SQL*Plus. The START command tells SQL*Plus to look for a file with the
> .sql ending and process all the commands contained within. The EXEC
> command tells SQL*Plus to ask the database to execute a procedure that
> was created as a code object in the database.
>
> By the way, '_at_' is just an abbreviation for START, so these are all
> equivalent:
> START raise_salary.sql
> START raise_salary
> _at_raise_salary.sql
> _at_raise_salary
>
> To avoid any confusion, you might want to give your .sql file a
> different name from your procedure, e.g. create_raise_salary.sql
> Better?
>
> Martin Doherty
>
> peter wang wrote:
>
>> Hi Martin,
>>
>> Thank you for your reply, I can't reply, I don't know why, O have to
>> repost
>> my new questions here.
>>
>> how to compile a stored procedure under sqlplus? _at_? start?
>> and how to run a stored procedure under sqlplus?
>>
>> thanks.
>>
>>
>>
>>
>>
>>
>
Received on Wed Nov 06 2002 - 09:56:23 CET