| Anonymous block in sql plus [message #656584] |
Tue, 11 October 2016 22:51  |
 |
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 #656591 is a reply to message #656584] |
Wed, 12 October 2016 01:00  |
 |
Michel Cadot
Messages: 68651
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
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 |
flyboy
Messages: 1903
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 #656609 is a reply to message #656599] |
Wed, 12 October 2016 11:38 |
Bill B
Messages: 1971
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
