RE: Problems with PL/SQL

From: Janardhana Babu <jbdonga_at_ucdavis.edu>
Date: Tue, 18 Jul 2000 15:17:15 -0700
Message-Id: <10562.112338@fatcity.com>

Jorge,

In addition to what I told you in my earliar E-Mail, You may still use the same procedure stmt, but with little change in syntax:

Create or replace procedure ... in the 1st line, then compile, and execute. Exec "set serveroutput on" stmt. before executing your procedure.

Eitherway, it should work.

Jorge,

It worked OK for me, I did only a small modification: Removed "procedure stmt", and replaced with "declare". Owner, column_name values have been changed to reflect my tables and columns.

Janardhana Babu

-----Original Message-----
From: Jorge Luiz Alves [mailto:jalves_at_mdic.gov.br] Sent: Tuesday, July 18, 2000 2:48 PM
To: Multiple recipients of list ORACLE-L Subject: Problems with PL/SQL - URGENT

Hi all,

I have a pl/sql block that declare a cursor for read a view ALL_TAB_COLUMNS. The select statement that form cursor, runs at sqlplus and doesn´t works in the storage procedure, that is my problem. I´m running the procedure with owner.

Please give a look in my procedure:

procedure pr_charge_data as

     cursor find_tabs_c is
            select table_name, column_name
              from all_tab_columns
             where owner = 'TEST' and
                   table_name  like 'AGREG%' and
                   column_name like 'ID%'   and
                   column_name not in ('ID_TITLE','ID_ECONOMIC_BLOCK')
           order by column_id; 
     wtable varchar2(30);
     wcol   varchar2(30);
begin
     open find_tabs_c;
     loop
        fetch find_tabs_c into wtable,wcol;
        if find_tabs_c%NOTFOUND then
           dbms_output.put_line('*** end ***');
           exit;
        end if;
        dbms_output.put_line(wtable);
     end loop;
     close find_tabs_c;

end;

THE RESULT IS: *** end ***, when i execute SP.

and when i run de sql statement it brings all the record that attempt the conditions in where clause;

Thank you in advance,

Jorge Alves.

--

Author: Jorge Luiz Alves
INET: jalves_at_mdic.gov.br

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Tue Jul 18 2000 - 17:17:15 CDT

RE: Problems with PL/SQL - URGENT