Re: A question to Martin.

[Quoted] 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:50:30 CET