Home » SQL & PL/SQL » SQL & PL/SQL » Anonymous block in sql plus
Anonymous block in sql plus [message #656584] Tue, 11 October 2016 22:51 Go to next message
SamMen
Messages: 4
Registered: October 2016
Junior Member
Hi,

I am pretty new to oracle.
I was trying to execute an anonymous block in sql plus. But sqlplus just stops..mean..it does nothing..
The same script runs in sql developer fine. Can anyone point what the issue might be?
Thanks!!

DECLARE 
  VAR_CLIENT_ID  RAW(16);

BEGIN
--STEP 1 - INSERT INTO CLIENT
  VAR_CLIENT_ID := '3C7E80603573136BE053421F080AB134';
  INSERT  INTO CLIENT (CLIENT_ID,CLIENT_NBR) 
SELECT VAR_CLIENT_ID,'123' FROM DUAL;
END;
/


[mod-edit: code tags added by bb]

[Updated on: Tue, 11 October 2016 23:17] by Moderator

Report message to a moderator

Re: Anonymous block in sql plus [message #656585 is a reply to message #656584] Tue, 11 October 2016 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Anonymous block in sql plus [message #656591 is a reply to message #656584] Wed, 12 October 2016 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It "works" for me:
SQL> DECLARE
  2    VAR_CLIENT_ID  RAW(16);
  3
  4  BEGIN
  5  --STEP 1 - INSERT INTO CLIENT
  6    VAR_CLIENT_ID := '3C7E80603573136BE053421F080AB134';
  7    INSERT  INTO CLIENT (CLIENT_ID,CLIENT_NBR)
  8  SELECT VAR_CLIENT_ID,'123' FROM DUAL;
  9  END;
 10  /
  INSERT  INTO CLIENT (CLIENT_ID,CLIENT_NBR)
               *
ERROR at line 7:
ORA-06550: line 7, column 16:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored
You must post your client and server Oracle version with 4 decimals like 11.2.0.4.
These values are displayed when you start SQL*Plus and connect to the database:
E:\Oracle\Scripts>sqlplus michel/michel@mika

SQL*Plus: Release 11.2.0.4.0 Production on Mer. Oct. 12 07:59:07 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

Also post the CREATE TABLE and all other related statements you used to create and modify the table.

[Updated on: Wed, 12 October 2016 01:00]

Report message to a moderator

Re: Anonymous block in sql plus [message #656592 is a reply to message #656584] Wed, 12 October 2016 01:18 Go to previous messageGo to next message
flyboy
Messages: 1850
Registered: November 2006
Senior Member
Just a guess from my crystal ball as you did not provide the table definition:

There is a primary key/unique constraint on some column (I would bet on CLIENT_ID) and the sqlplus session is waiting on the sql developer session (which run exactly the same PL/SQL block) to end the transaction (COMMIT or ROLLBACK it) whether it should fail on ORA-00001 unique constraint violated or just proceed further ... and block another session trying to run exactly the same piece of code until the end of its transaction.
Re: Anonymous block in sql plus [message #656593 is a reply to message #656592] Wed, 12 October 2016 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This was also my guess then I asked for table creation statement. Wink

Re: Anonymous block in sql plus [message #656597 is a reply to message #656593] Wed, 12 October 2016 07:01 Go to previous messageGo to next message
SamMen
Messages: 4
Registered: October 2016
Junior Member
Hi All,
Sorry I slept and couldn't reply earlier. Smile
And yes..it was a transaction that was causing the issue..I had an uncommitted transaction in sql developer which was causing the issue.
I rolled back the sql developer transaction and the script ran successfully on sql plus.
Going forward, I shall keep in mind to provide the DDL's too for any questions..
Thank you very much!!
Re: Anonymous block in sql plus [message #656599 is a reply to message #656597] Wed, 12 October 2016 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

.. and Oracle versions. Smile

Re: Anonymous block in sql plus [message #656609 is a reply to message #656599] Wed, 12 October 2016 11:38 Go to previous message
Bill B
Messages: 1709
Registered: December 2004
Senior Member
Unless your are testing an insert using a select then it would be written as follows

DECLARE 
  VAR_CLIENT_ID  RAW(16);
BEGIN
--STEP 1 - INSERT INTO CLIENT
  VAR_CLIENT_ID := '3C7E80603573136BE053421F080AB134';
  INSERT  INTO CLIENT (CLIENT_ID,CLIENT_NBR) VALUES(VAR_CLIENT_ID,'123');
END;
/

[Updated on: Wed, 12 October 2016 11:38]

Report message to a moderator

Previous Topic: Create Table error
Next Topic: pl/sql table
Goto Forum:
  


Current Time: Fri Jan 19 16:37:46 CST 2018

Total time taken to generate the page: 0.03397 seconds