Re: Can't select from ALL_TABLES in a stored procedure

"Ivan Petrovski" <ivan_pet_at_hotmail.com> a écrit dans le message de news:bneklc$pou$1_at_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!
>
>
>

Roles are not enabled in a procedure.
Execute "set role none" and then your statements in SQL*Plus and I bet you'll get only 64 records.

Hope this helps
Michel Cadot Received on Sat Oct 25 2003 - 15:42:17 CDT