Can't select from ALL_TABLES in a stored procedure

From: Ivan Petrovski <ivan_pet_at_hotmail.com>
Date: Sat, 25 Oct 2003 22:06:55 +0200
Message-ID: <bneklc$pou$1@ls219.htnet.hr>

I am logged in my schema. Plain SQL Plus:

-SELECT COUNT(*) FROM ALL_TABLES

 461
-I have my own table LNK_TABLES:

        TRUNCATE TABLE LNK_TABLES;
        table truncated

        INSERT INTO LNK_TABLES
        SELECT fields...
        FROM ALL_TABLES;
        COMMIT;

        SELECT COUNT(*) FROM LNK_TABLES;
        461 <= this is OK

• BUT, the same thing in a procedure:

    BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE LNK_TABLES';

     INSERT INTO LNK_TABLES
        SELECT fields...
        FROM ALL_TABLES;

    COMMIT;
    END: After execution fills LNK_TABLES with only 64 records!!?? What am I doing wrong?
Do I need some special grants, privileges or something for doing in procedure something I can obviously do in a simple SQL statement? It's a mistery to me! Received on Sat Oct 25 2003 - 15:06:55 CDT