OraFAQ Forum: SQL & PL/SQL » Basic Pl/sql ?

Home » SQL & PL/SQL » SQL & PL/SQL » Basic Pl/sql ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Basic Pl/sql ? [message #1312]

Fri, 19 April 2002 12:45

Jack Brown
Messages: 4
Registered: April 2002

Junior Member

How do I fix this. I get an error saying you can only use a select statement with an INTO if not it can appear in the subquery. But I want the answer of this PL/SQL block to be the result of a select statement.

create or replace PROCEDURE SP_TableInfo
AS
BEGIN
SELECT x,y FROM emp;
END SP_TableInfo;

Report message to a moderator

Re: Basic Pl/sql ? [message #1315 is a reply to message #1312]

Fri, 19 April 2002 14:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

create or replace procedure sp_tableinfo
as
cursor c1 is select x,y from emp;
begin
for var in c1 loop
exit when c1%notfound;
dbms_output.put_line(mag.x||','||mag.y);
end loop;
end;
------
dont forget to issue SET SERVEROUTPUT ON before executing the procedure.

---
since the select statement is returning more than one row, u need make use of looping.
else
use this.

1 create or replace PROCEDURE SP_TableInfo
2 AS
3 a emp.ename%type;
4 b emp.sal%type;
5 BEGIN
6 SELECT ename,sal into a,b from emp where empno=7934;
7 dbms_output.put_line(a||','||b);
8* end;
9 /

Procedure created.

SQL> exec sp_tableinfo

MILLER,1300

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Basic Pl/sql ? [message #1332 is a reply to message #1312]

Mon, 22 April 2002 07:15

Oracle Begineer
Messages: 2
Registered: April 2002

Junior Member

I am going to be calling this procedure from a Visual Basic application. I want the
store procedure to execute and give me the result of the query. Based on your response
it looks like you are writing to the output with a comma. Or is this the only way to do so ...

Report message to a moderator

Previous Topic:	The query behind the DESCRIBE command?
Next Topic:	Describe Command - The query behind it?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 21:27:11 CDT 2024