Re: Declare in SQL

A copy of this was sent to gmburns_at_my-deja.com (if that email address didn't require changing) On Thu, 03 Feb 2000 11:46:29 GMT, you wrote:

> When I attempt to define a variable in SQL*Plus using the "declare"
>statement, I receive the following error:
>
>SQL> declare
>invalid command:
>declare
>SQL>
>
>However, when I log in the same Oracle instance as another user,
>declare is recognized as a valid command. Why is the first user's
>account unable to see "declare" as a valid command?
>
>Thanks,
>Greg
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

someones been playing with the product_user_profile table... See the sqlplus guide. Here is how it works to make declare an invalid command:

ops$tkyte_at_8i> declare x number; begin null; end;   2 /
PL/SQL procedure successfully completed.

REM No problem yet -- declare is OK...

ops$tkyte_at_8i> insert into product_user_profile   2 ( product, userid, attribute, char_value )   3 values
  4 ( 'SQL*Plus', user, 'DECLARE', 'DISABLED' );

1 row created.

ops$tkyte_at_8i> commit;

Commit complete.

ops$tkyte_at_8i> declare x number; begin null; end;   2 /

PL/SQL procedure successfully completed.

REM Still OK until......

ops$tkyte_at_8i> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
ops$tkyte_at_8i> connect /
Connected.
ops$tkyte_at_8i> declare x number; begin null; end; SP2-0544: invalid command: declare

REM Now declare is an invalid command. plus reads the product user profile table when you login

ops$tkyte_at_8i> delete from product_user_profile;

1 row deleted.

ops$tkyte_at_8i> commit;

Commit complete.

ops$tkyte_at_8i> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
ops$tkyte_at_8i> connect /
Connected.
ops$tkyte_at_8i> declare x number; begin null; end;   2 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>

Now, declare is a command again....

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Feb 03 2000 - 07:47:15 CST