Now when I execute this script it is giving me an compilation error stating " an into clause is expected in the select statement" (strange isn’t it ?!).
Also in toad on the left hand side under the procedure, I can see the name of the SP that I am trying to create which also has a “Red crossmark” to its left. I am not able to figure out the problem and would appreciate if you can help me out.
Thanks in advance!
Hello All,
I am newbie to Taod for Oracle but have been using Oracle 9i for a long time. I am trying to writing a very basic stored procedure which will get all the data from the table that I have.
CREATE PROCEDURE sp_testfriday
AS
Begin
Select * from emp_test;
End;
Now when I execute this script it is giving me an compilation error
stating " an into clause is expected in the select statement" (strange
isn't it ?!).
In PL/SQL you must select into, as follows:
select
INTO
From
or, if you don't know how bit it will be, you could use a cursor loop:
for x in (select from
where ) loop
-- do something with each record here
end loop;
Also in toad on the left hand side under the procedure, I can see the
name of the SP that I am trying to create which also has a "Red
crossmark" to its left. I am not able to figure out the problem and
would appreciate if you can help me out.
The red cross says that the procedure is not compiled and has errors.
If you are going to query data in PL/SQL, you have to store that data somewhere.
When you right that in SQL – the output comes back to the client. When you
run that in PL/SQL – where does the data go?
You have a couple of options, but try looking up REF Cursors.
Our Knowledge Xpert has a lot of material on this subject, here’s an
example
You may have been working with Oracle for a long time but I’ll bet
you’ve been working with SQL Server longer. In SQL Server a procedure can
execute a query and return the results of that query. In Oracle that would more
typically be done with a view, not a procedure. (There are ways that a procedure
can return the results of a query, using Ref Cursors, but that would not be
considered a “very basic” stored procedure.) In Oracle a stored
procedure or function is more like a Java program or something like that: you
can declare variables, read data from the database into variables (that’s
the “into” clause that the Select statement is expecting),
manipulate the data, write it back to the database, or return one or more values
to the program that called the function or procedure (if the function/procedure
declaration says it will return values). Since the procedure or function lives
inside the database, it doesn’t really have a user interface; it can write
some text out to dbms_output, but that’s about it.
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
You may have been working with Oracle for a long time but I’ll bet
you’ve been working with SQL Server longer. In SQL Server a procedure
can execute a query and return the results of that query.
I can do that too, in Firebird.
I initially thought: there's some client that can reference an Oracle database called Firefox which can create a stored procedure like the one outlined that will actually compile?
Then I googled Firefox and found it is a relational database itself
Pritish:
If you had attempted to compile the procedure through SQLPlus, you would have run into the same error. Delete, Insert and Update SQL statements can be done in a PLSQL block without any additional variables. However, as another has already said, a select statement must return the value into a local variable of some type for use in the PLSQL block (the stored procedure).
Additionally, if you modify your procedure call to be:
CREATE OR REPLACE PROCEDURE
instead of just
CREATE PROCEDURE
you'll be able to avoid the compile error that tells you the object already exists.
Roger S.
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.
I can do that too, in Firebird.
You had me going there for a moment Norm.
I initially thought: there's some client that can reference an Oracle
database called Firefox which can create a stored procedure like the one
outlined that will actually compile?
Then I googled Firefox and found it is a relational database itself
Well, Firebird is a relational database system! Firefox is a browser.
However, before Firefox was Firefox they called it Firebird and caused
no end of trouble at the time.
However, they were advised of the problem, and changed the name.
Firebird is good, like Oracle, because it has read consistency. It does
it differently from Oracle, but it still works.
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.
