Re: PL/SQL Error with stored procedure

First of all, why are you doing this work as SYS. That is never a good idea. You should never use SYS or SYSTEM except for database maintenance.

Secondly when confronted with the MAXDATA message, use the command

SET ARRAYS 1 and that may help.

Thirdly, you are missing semicolons after your declarations of f_txt and f_bez.

Fourthly, if you call dbms_output.enable() then it would probably be a good idea to pass it a value. Since 1000000 is the maximum value, go ahead and use that.

Fifthly, if you open a cursor then make sure you close it rather than allowing an implicit close when the block goes out of scope.

Sixthly, if you get compilation errors then don't try to execute it - it is already invalid.

Seventhly, if you are just learning pl/sql then get the code to run as a stand-alone program first and once you have that working then convert it into a stored procedure.

That should at least get you started.

Ken

Fred wrote:
>
> Trying to write and execute this simple procedure, I get the following
> error-messages:
> (I'm looged on as User SYS, the table_name is Test)
>
> With the Partitioning and Objects options
> PL/SQL Release 8.0.3.0.0 - Production
>
> SQL> create or replace procedure Get_data Is
> 2 f_txt test.txt%type
> 3 f_bez test.bez%type
> 4 cursor c_Curs is
> 5 select txt, bez from test;
> 6 begin
> 7 dbms_output.enable;
> 8 open c_Curs;
> 9 loop
> 10 fetch c_Curs
> 11 into f_txt, f_bez;
> 12 exit when
> c_Curs%notfound;
> 13 dbms_output.put_line (f_txt
> || ' ' ||, to_char(f_bez));
> 14 end loop;
> 15 end;
> 16 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> SET SERVEROUTPUT ON
> SQL> execute get_data;
> begin get_data; end;
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00905: object SYS.GET_DATA is invalid
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> ""(column 7 in Error ORA-06550 doesn't say anything to me!!!!!!!!!)""
>
> Trying to track down, I get the following error messages as
> well............
>
> SQL> show errors
> buffer overflow. Use SET command to reduce ARRAYSIZE or increase
> MAXDATA.
> No errors.
> SQL> select object_name, object_type, status
> 2 from user_objects where object_name = 'Get_data';
>
> no rows selected
>
> SQL> select text from user_source where name = 'Get_data' order by line;
>
> buffer overflow. Use SET command to reduce ARRAYSIZE or increase
> MAXDATA.
> SQL>
>
> Your support will be highly appreciated.
> Thanks in advance
>
> Fred
>
> mailto:fredgrasser_at_netway.at
Received on Tue Jun 22 1999 - 15:35:43 CDT