The Symbol an Identifier Was Substituted for to Continue

Help in Procedure [message #420909]	Thu, 03 September 2009 05:01
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 #420911 is a reply to message #420909]	Thu, 03 September 2009 05:10
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
cookiemonster Messages: 13904 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
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 #420923 is a reply to message #420920]	Thu, 03 September 2009 05:49
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 #420927 is a reply to message #420909]	Thu, 03 September 2009 05:55
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
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 #420932 is a reply to message #420909]	Thu, 03 September 2009 06:06
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 #420936 is a reply to message #420909]	Thu, 03 September 2009 06:45
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
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
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
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 #420944 is a reply to message #420943]	Thu, 03 September 2009 07:27
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
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>

tanganch1937.blogspot.com

Source: https://www.orafaq.com/forum/t/149669