Home » SQL & PL/SQL » SQL & PL/SQL » Help in Procedure (Oracle 9i)
Help in Procedure [message #420909] Thu, 03 September 2009 05:01 Go to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
I have the below procedure for which i m providing the in parameter value and it should return me 3 rows

Proc1
create or replace procedure proc1( x1 in varchar2)
as
declare
    cursor c1 is select ename from emp where job=x1;
    i ename.emp%type;      
begin
  dbms_output.put_line('Enter value is' || ' ' || x1);
  for i in c1
  LOOP
  dbms_output.put_line( i.ename);
  END LOOP;
end;



the procedure is showing the follwoing errors

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor external language
The symbol "begin" was substituted for "DECLARE" to continue.

12/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe

Can anyone tell me wht seems to be the problem ?
Re: Help in Procedure [message #420910 is a reply to message #420909] Thu, 03 September 2009 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Remove the declare.

In procedures/functions the section between the AS and BEGIN is a declare section implicitly.


And don't declare record types for FOR LOOPS.
Re: Help in Procedure [message #420911 is a reply to message #420909] Thu, 03 September 2009 05:10 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
create or replace procedure proc1( x1 in varchar2)
as
declare
    cursor c1 is select ename from emp where job=x1;
    ---i ename.emp%type;      
begin
  dbms_output.put_line('Enter value is' || ' ' || x1);
  for i in c1
  LOOP
  dbms_output.put_line(i.ename);
  END LOOP;
end;


Try this it will help you i think

Re: Help in Procedure [message #420912 is a reply to message #420911] Thu, 03 September 2009 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
sen_sam86 wrote on Thu, 03 September 2009 11:10
create or replace procedure proc1( x1 in varchar2)
as
declare
    cursor c1 is select ename from emp where job=x1;
    ---i ename.emp%type;      
begin
  dbms_output.put_line('Enter value is' || ' ' || x1);
  for i in c1
  LOOP
  dbms_output.put_line(i.ename);
  END LOOP;
end;


Try this it will help you i think




Not really, since it won't compile - did you read my reply?
Re: Help in Procedure [message #420913 is a reply to message #420911] Thu, 03 September 2009 05:16 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Thanks i got the 3 rows. Can anyone tell me how shall i use the out parameter here so that the 3 rows returned will be out to the calling anonymous block

It should be something like this

create or replace procedure proc1 ( i out varchar2)
as
    cursor c1 is select ename from emp where job='CLERK';
begin
   	for i in c1
  		LOOP
  			
  		END LOOP;
end;




[Updated on: Thu, 03 September 2009 05:17]

Report message to a moderator

Re: Help in Procedure [message #420914 is a reply to message #420913] Thu, 03 September 2009 05:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your out variable is a scalar. It cannot contain 3 rows.
Re: Help in Procedure [message #420916 is a reply to message #420909] Thu, 03 September 2009 05:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can use PL/SQL table with BULK COLLECT to do so.

regards,
Delna
Re: Help in Procedure [message #420919 is a reply to message #420916] Thu, 03 September 2009 05:29 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Can you show me how to use bulk collect here
Re: Help in Procedure [message #420920 is a reply to message #420919] Thu, 03 September 2009 05:34 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
See example.

regards,
Delna
Re: Help in Procedure [message #420923 is a reply to message #420920] Thu, 03 September 2009 05:49 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
i have written the procedure as follws but still it is showing errors

create or replace procedure proc1( x1 out varchar2)
as
declare
    TYPE NameTab IS TABLE OF emp.ename%TYPE;
    x1 NameTab;
begin
  select ename bulk collect into x1 from emp where job='CLERK'
end;


LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor external language
The symbol "begin" was substituted for "DECLARE" to continue.

8/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
. ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
group having intersect minus order start union where connect
Re: Help in Procedure [message #420925 is a reply to message #420923] Thu, 03 September 2009 05:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
DECLARE is not part of CREATE PROCEDURE syntax.
Remove it, as said so far.

regards,
Delna
Re: Help in Procedure [message #420926 is a reply to message #420909] Thu, 03 September 2009 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you write that on auto-pilot?
You've already fixed that error once.
Re: Help in Procedure [message #420927 is a reply to message #420909] Thu, 03 September 2009 05:55 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
i have removed declare part also but still getytting the belwo error

create or replace procedure proc1( x1 out varchar2)
as
    TYPE NameTab IS TABLE OF emp.ename%TYPE;
    x1 NameTab;
begin
  select ename bulk collect into x1 from emp where job='CLERK';
end;


LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted
Re: Help in Procedure [message #420928 is a reply to message #420927] Thu, 03 September 2009 06:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You have declared TYPE in the called PROCEDURE which is going to referred outside procedure. Which is not in the scope of that TYPE.
So you can't use that type variable outside PROCEDURE.
That TYPE must be global object in your schema.

regards,
Delna
Re: Help in Procedure [message #420929 is a reply to message #420927] Thu, 03 September 2009 06:02 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
x1  NameTab := NameTab();


Declare like this
Re: Help in Procedure [message #420932 is a reply to message #420909] Thu, 03 September 2009 06:06 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
same error


create or replace procedure proc1( x1 out varchar2)
as
TYPE NameTab IS TABLE OF emp.ename%TYPE;
x1  NameTab := NameTab();
begin
  select ename bulk collect into x1 from emp where job='CLERK';
end;
/



LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted
Re: Help in Procedure [message #420933 is a reply to message #420932] Thu, 03 September 2009 06:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
One more thing,
You are passing one argument with name X1.
And creating one variable in the procedure with same name, which makes Oracle confusing.
So you have to change variable name of TYPE.

regards,
Delna
Re: Help in Procedure [message #420936 is a reply to message #420909] Thu, 03 September 2009 06:45 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Now i m facing a new problem . The below procedure is complied successfully but when i want to check the values using bind variable it is showing null results

create or replace procedure proc1( x1 out varchar2)
as
TYPE NameTab IS TABLE OF emp.ename%TYPE;
x2  NameTab := NameTab();
begin
  select ename bulk collect into x2 from emp where job='CLERK';
end;
/


SQL> variable p2 varchar2(30);
SQL> exec proc1(:p2);

PL/SQL procedure successfully completed.

SQL> print p2;

P2
--------------------------------





but it has data when i query using

SQL> select ename from emp where job='CLERK';

ENAME
----------
SMITH
ADAMS
JAMES
MILLER

[Updated on: Thu, 03 September 2009 06:48]

Report message to a moderator

Re: Help in Procedure [message #420939 is a reply to message #420936] Thu, 03 September 2009 07:11 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You are fetching rows in your local associative array/plsql table where as your output parameter to the procedure is a scalar variable for which is nothing is assigned by your code, therefore it is showing as null when you are using print.

The other thing you should note that as Frank mentioned is that your output parameter is a scalar variable it cannot store 3 rows returned by your cursor.

[Updated on: Thu, 03 September 2009 07:16]

Report message to a moderator

Re: Help in Procedure [message #420940 is a reply to message #420939] Thu, 03 September 2009 07:14 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Yes i understood. Somebody pointed out that using bulk collect i can get the multiple rows from the stored procedure

So,Basically what i wanted how shall i get the multiple rows or data from a stored procedure to the calling annonymous block or bind variable

Can someone show me with an simple example

[Updated on: Thu, 03 September 2009 07:16]

Report message to a moderator

Re: Help in Procedure [message #420941 is a reply to message #420940] Thu, 03 September 2009 07:18 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member

create or replace procedure proc1( x1 out varchar2)
as
TYPE NameTab IS TABLE OF emp.ename%TYPE;
x2  NameTab := NameTab();
begin
  select ename bulk collect into x2 from emp where job='CLERK';

   for i in 1..x2.count
   loop
 
          dbms_output.put_line(x2(i));

   end loop;
end;


Re: Help in Procedure [message #420942 is a reply to message #420940] Thu, 03 September 2009 07:20 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
PL/SQL tables with BULK COLLECT into is the correct way to do so.

regards,
Delna
Re: Help in Procedure [message #420943 is a reply to message #420941] Thu, 03 September 2009 07:21 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
But what i asked when u call this procedure from another annoymous block or using bind variable how do you get the o/p using the out paramater
Re: Help in Procedure [message #420944 is a reply to message #420943] Thu, 03 September 2009 07:27 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL>select * from t1;

COL1
---------
01-AUG-09
03-AUG-09
16-AUG-09
01-SEP-09
22-SEP-09
10-OCT-09
20-OCT-09

7 rows selected.

SQL>create type typ as table of date;
  2  /

Type created.

SQL>create or replace procedure prc(p_typ out typ)
  2  as
  3  begin
  4   select col1 bulk collect into p_typ from t1;
  5  end;
  6  /

Procedure created.

SQL>declare
  2  v_typ typ := typ();
  3  begin
  4   prc(v_typ);
  5   for i in 1..v_typ.count
  6   loop
  7    dbms_output.put_line(v_typ(i));
  8   end loop;
  9  end;
 10  /
01-AUG-09
03-AUG-09
16-AUG-09
01-SEP-09
22-SEP-09
10-OCT-09
20-OCT-09

PL/SQL procedure successfully completed.


regards,
Delna
Re: Help in Procedure [message #420946 is a reply to message #420943] Thu, 03 September 2009 07:44 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
There are 2 ways of doing this. You can use that suits your needs


XE@SQL> create or replace type str_table as table of varchar2(30)
  2  /

Type created.

XE@SQL>  create or replace procedure proc1( x1 out str_table)
  2   as
  3   begin
  4     select dbms_random.string('U',10) bulk collect into x1
  5      from dual connect by level <= 10;
  6   end;
  7  /

Procedure created.

XE@SQL>  declare
  2   mytable str_table;
  3   begin
  4   proc1(mytable);
  5   for i in 1..mytable.count
  6   loop
  7      dbms_output.put_line(mytable(i));
  8   end loop;
  9   end;
 10  /
ALVPBODKMF
TJZRDNKFFL
XFVXGMADXX
FJKUXAAXTZ
HAIMLAGYAU
RVBMZGXWTZ
LJTXAQJAPX
NRGNXJFBWG
HSYYKFPHRQ
AMGNWHRSMB

PL/SQL procedure successfully completed.

XE@SQL>



or

XE@SQL>  create or replace package demo_pkg
  2   as
  3   type gv_str_table is table of varchar2(30);
  4   procedure proc1 (x out gv_str_table);
  5   end demo_pkg;
  6  /

Package created.

XE@SQL>   create or replace package body demo_pkg
  2    as
  3   procedure proc1 (x out gv_str_table)
  4    is
  5     begin
  6      select dbms_random.string('U',10) bulk collect into x
  7            from dual connect by level <= 10;
  8   end proc1;
  9   end demo_pkg;
 10  /
Package body created.

XE@SQL>  declare
  2   mytable demo_pkg.gv_str_table;
  3   begin
  4   demo_pkg.proc1(mytable);
  5   for i in 1..mytable.count
  6   loop
  7     dbms_output.put_line(mytable(i));
  8   end loop;
  9  end;
 10  /
BDKITYNLTQ
OQPAYIYULM
SAENRFAPYD
SJXCPFXJUC
YZRSSEPQIW
XWHJRSIKYR
CGVRSEWMJT
KXUDELETDA
BXMGMKCGNO
XZPPTREOEB

PL/SQL procedure successfully completed.

XE@SQL>




Re: Help in Procedure [message #420949 is a reply to message #420946] Thu, 03 September 2009 08:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A third (and in my opinion more logical) way would be to return a ref cursor.
Re: Help in Procedure [message #420951 is a reply to message #420949] Thu, 03 September 2009 08:10 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
4th way could be using pipelined function

[Updated on: Thu, 03 September 2009 08:11]

Report message to a moderator

Re: Help in Procedure [message #420953 is a reply to message #420909] Thu, 03 September 2009 08:16 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
4th way could be using pipelined function


More correctly Laughing ,
4th way could be using [pipelined] function.
I mean PIPELINED is optional.
Just kidding. Smile

regards,
Delna
Previous Topic: SQL Query to get a time index
Next Topic: A Tricky Left-Outer Join :-
Goto Forum:
  


Current Time: Sun Dec 04 04:51:38 CST 2016

Total time taken to generate the page: 0.09924 seconds