SQL Developer Locks up after telling executing ddlscript

1.  I really want to find out want when wrong in SQL Developer so I know what to avoid next time.  Surely there must be some kind of log file on what happened

2.  I have found that many sripts will run in SQL Plus that do not run in various other guis like EM's SQL worksheet or the other SQL Developer.   I have also found that some scripts will run in some gui's but not others.  So running the script in SQL Plus will not resolve the issue of what happened to lock up SQL Developer and why.  (If there was a problem with the script than it should have run those parts of the script it could and thrown errors in the output window like it did on the previous run.)

I do not want to run this script with SQL Plus until it is the last alternative for getting the job done .

Did you check the contents in the "Output" tab at the bottom of the window?

The output tab was not visible as it had been scrolled down.  Upon locking it was not possibe to scroll back up to view it.

Joeller,

my dutty is to warn you that tools like SQLDeveloper, PLSQLDeveloper and even TOAD are not working good with SQLs. They have excellent GUIs, many integrated functions, many excellent featrues  ... but in SQL and PL/SQL execution tasks often the result is confusing.

The SQL*Plus due the simplay functionality has not much bugs and strange features and is the most reliable tool to test SQL code.

I am sorry to say this, but this is my honest understanding. I hope in the future the things will change ....

maybe so but it is my inclination to use the command line as a last resort, since I would rather chew off my right arm than to use it.  This is not limited to just Oracle, but also applications like ArcSDE and XCopy deployments of .Net apps.  

However, the point of the question is not to get the script to work which I have already done, but to find out what happened to SQL developer and why, so I can keep it from happening in the future.  The current script ran fine when I ran the sql one statement at a time

Point of Information.  The database and SQL developer reside on Virtual Machines, which I reached via a remote desktop connection.  Could this be an issue?  I remember reading about a couple of years ago that Oracle is not supported to run on VM's.  Or maybe it is another Java version issue like the one I had with database console earlier. The question is surely there must be some kind of log find that records any issues discovered with SQL Developer.

Really Oracle doesn't provide assistance if it failed to reproduce on his virtual machine installations the scenario of the customer complain.
I am curious if the script runs on SQL Developer on virtual machine.
The second point is to check if the JVM is the needed and certified for usage.
The best solution will be to install SQLDeveloper with the JVM provided with the
installation kit.

But I will also warn you - do not waste your time fighting with wind mills.

The story: i was very interested in Data Pump introduced with 1og Release 1.
But all experiments failed - due bugs. I stoped the experiments because without
Oracle Service Requests it was not possible to use Data Pump and the production
environment doesn't give me chance for risky steps. Two years later most of the
bugs (10g Release 2) were fixed, new bugs were included. The Data Pump
works now fine, but it fails to transform the characters when the character sets are different
(in some cases, not in all cases).....

Much to my dismay I have been forced into using SQLplus because while attempting to create a table in a new schema.  My "sys" user was informed that it di not have enough priviledges in that tablespace.  (This is despite the fact that it is reported that sys has unlimited tablespace priviledges.  So I went to SQLPPLUS on my local machine and SQLPLUS locked up.  After several reboots and attempts to create the table in em andsql developer and sqlplus, I managed to save a script.  (previous attempts resulted in losing the desired command before getting the opportunity to save it to a file), (After 4 hours I went to bed and got up this morning came to work and started trying again in SQLPLUS using the file I had managed to save.  Hurray.  Now I tried to export that file to the development server.  attempts to login resulted in TNS: protocol adaptor error.  google searches report that this is due to attempting to log into a client (??? I am on the server.)  so I then tried to connect to the instance in sqlplus using sys/password@arcgis as sysdba.  At least this time sqlplus open up befroe delivering the followiing error "TNS:could not resolve the connect identifier specified" (!????!!!!)  entry on internet says the following

ORA-12154: TNS:could not resolve the connect identifier specified
Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
 
Action: - If you are using local naming (TNSNAMES.ORA file):

- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

 
I carried out all of these actions.  everything was as it should be.  So I am in a position where sys is reported as having no rights on the new tablespace by the GUI's and SQLPlus will not allow sys to login.
Is there anyway to resolve this?  Whoever does will get all the points even though this is not the original question.  (I would still like to find out the reason for SQL Developer locking up like that because I feel it must be something I did and I need to know what it is so I don't do it again.)  But if you can resolve this, I will shut up about it and sweeten the pot with a few more points of my limited supply.

On which OS is the database?
If on Unix/Linux, it seems you have an environment issue. Logoff/Login to the server using the OS oracle account and execute the attached commands.
Post any errors and the following files:
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/network/admin/sqlnet.ora
The alert_arcgis.log.
PS: DO NOT create objects using the SYS account but rather login to the database using the schema owner account.

ORAENV_ASK=NO
export ORACLE_SID=arcgis
. /usr/local/bin/oraenv
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus '/as sysdba'
                                              

1:
2:
3:
4:
5:

No its is windows server 2003.  I don't see any oracle users, but I don't have rights to see any user in "computer management"  You saw the errors here are the tnsnames.ora and the sqlnet.ora.
TNSNAMES
# tnsnames.ora Network Configuration File: C:\app\administrator\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ARCGIS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = egis.cflhd.zekiah.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = arcgis)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = egis.cflhd.zekiah.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

# sqlnet.ora Network Configuration File: C:\app\administrator\product\11.1.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Both of which I verify as per the instruction from the website cited above.

Can not connect using the schema owner despite its having connect role reports says insufficient priviledges on tablespace.

Eventually completed making table on development server using Oracle em signed in as sys as sysdba.  However was never able to log into sqlplus as sys as sysdba.  The table creation shows up in the alert_arcgis.log, but not the errors.  Do you want to see that entry?

here is the TNSNames and SQLNet.ora on the local machine which has never given an TNS:protocol adapter error.  This machine is Windows XP SP3

# tnsnames.ora Network Configuration File: C:\Ora11G\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ZTLatitude)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

and the SQLNet.ora
# sqlnet.ora Network Configuration File: C:\Ora11G\product\11.1.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


And here is the p[ertinent part of the local machine's alert_orcl.log file

Mon Nov 09 15:17:34 2009
CREATE  SMALLFILE  TABLESPACE "ERFOAPPLICANT" DATAFILE 'C:\ORA11G\ORADATA\ORCL\ERFOAPPLICANT.dbf' SIZE 100M AUTOEXTEND ON NEXT 100K MAXSIZE UNLIMITED LOGGING  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO  DEFAULT NOCOMPRESS
Mon Nov 09 15:17:53 2009
Completed: CREATE  SMALLFILE  TABLESPACE "ERFOAPPLICANT" DATAFILE 'C:\ORA11G\ORADATA\ORCL\ERFOAPPLICANT.dbf' SIZE 100M AUTOEXTEND ON NEXT 100K MAXSIZE UNLIMITED LOGGING  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO  DEFAULT NOCOMPRESS
Mon Nov 09 16:31:21 2009


***********************************************************************

Fatal NI connect error 12638, connecting to:
 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

  VERSION INFORMATION:
      TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
      Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.1.0.6.0 - Production
  Time: 09-NOV-2009 16:31:21
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12638
   
TNS-12638: Credential retrieval failed
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
Mon Nov 09 19:39:58 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as C:\Ora11G\product\11.1.0\db_1\RDBMS
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\ORA11G\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEORCL.ORA
System parameters with non-default values:
  processes                = 150
  memory_target            = 816M
  control_files            = "C:\ORA11G\ORADATA\ORCL\CONTROL01.CTL"
  control_files            = "C:\ORA11G\ORADATA\ORCL\CONTROL02.CTL"
  control_files            = "C:\ORA11G\ORADATA\ORCL\CONTROL03.CTL"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "C:\Ora11G\flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
Mon Nov 09 19:40:09 2009
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  audit_file_dest          = "C:\ORA11G\ADMIN\ORCL\ADUMP"
  audit_trail              = "DB"
  db_name                  = "orcl"
  open_cursors             = 300
  diagnostic_dest          = "C:\ORA11G"
Mon Nov 09 19:40:11 2009
PMON started with pid=2, OS id=5380
Mon Nov 09 19:40:11 2009
VKTM started with pid=3, OS id=5384 at elevated priority
VKTM running at (20)ms precision
Mon Nov 09 19:40:11 2009
DIAG started with pid=4, OS id=5388
Mon Nov 09 19:40:11 2009
DBRM started with pid=5, OS id=5392
Mon Nov 09 19:40:11 2009
PSP0 started with pid=6, OS id=5396
Mon Nov 09 19:40:11 2009
DSKM started with pid=7, OS id=5400
Mon Nov 09 19:40:11 2009
DIA0 started with pid=8, OS id=5404
Mon Nov 09 19:40:11 2009
MMAN started with pid=9, OS id=5408
Mon Nov 09 19:40:11 2009
DBW0 started with pid=10, OS id=5412
Mon Nov 09 19:40:11 2009
LGWR started with pid=11, OS id=5416
Mon Nov 09 19:40:11 2009
CKPT started with pid=12, OS id=5420
Mon Nov 09 19:40:11 2009
SMON started with pid=13, OS id=5424
Mon Nov 09 19:40:12 2009
MMON started with pid=15, OS id=5432
Mon Nov 09 19:40:12 2009
RECO started with pid=14, OS id=5428
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Nov 09 19:40:12 2009
MMNL started with pid=16, OS id=5440
starting up 1 shared server(s) ...
ORACLE_BASE from environment = C:\Ora11G
Mon Nov 09 19:40:15 2009
alter database mount exclusive
Setting recovery target incarnation to 2
Mon Nov 09 19:40:23 2009
Successful mount of redo thread 1, with mount id 1230281762
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Mon Nov 09 19:40:27 2009
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 316 redo blocks read, 60 data blocks need recovery
Started redo application at
 Thread 1: logseq 16, block 31866
Recovery of Online Redo Log: Thread 1 Group 1 Seq 16 Reading mem 0
  Mem# 0: C:\ORA11G\ORADATA\ORCL\REDO01.LOG
Completed redo application
Completed crash recovery at
 Thread 1: logseq 16, block 32182, scn 1369175
 60 data blocks read, 60 data blocks written, 316 redo blocks read
Mon Nov 09 19:40:35 2009
Thread 1 advanced to log sequence 17
Thread 1 opened at log sequence 17
  Current log# 2 seq# 17 mem# 0: C:\ORA11G\ORADATA\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Nov 09 19:40:35 2009
SMON: enabling cache recovery
Mon Nov 09 19:40:40 2009
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Opening with internal Resource Manager plan
Starting background process FBDA
Starting background process SMCO
Mon Nov 09 19:40:44 2009
FBDA started with pid=21, OS id=2864
Mon Nov 09 19:40:44 2009
SMCO started with pid=22, OS id=3600
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Nov 09 19:40:50 2009
Starting background process QMNC
Mon Nov 09 19:40:51 2009
QMNC started with pid=24, OS id=2556
Mon Nov 09 19:41:15 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Nov 09 19:41:31 2009
Completed: alter database open
Mon Nov 09 19:45:18 2009
Starting background process CJQ0
Mon Nov 09 19:45:19 2009
CJQ0 started with pid=35, OS id=5836
Mon Nov 09 22:14:11 2009
Setting Resource Manager plan SCHEDULER[0x2C09]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Nov 09 22:14:34 2009
Mon Nov 09 22:14:32 2009
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is   UnwindToSCN (LockdownSCN) is 1383859
Mon Nov 09 22:14:34 2009
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Nov 09 22:27:50 2009
Thread 1 advanced to log sequence 18
  Current log# 3 seq# 18 mem# 0: C:\ORA11G\ORADATA\ORCL\REDO03.LOG
Dump file c:\ora11g\diag\rdbms\orcl\orcl\trace\alert_orcl.log
Mon Nov 09 23:07:01 2009
ORACLE V11.1.0.6.0 - Production vsnsta=0
vsnsql=15 vsnxtr=3
Windows XP Version V5.1 Service Pack 3
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:294M/2037M, Ph+PgF:2252M/3930M, VA:1187M/2047M
Mon Nov 09 23:07:01 2009
Errors in file
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C911689]
Mon Nov 09 23:08:18 2009
Errors in file c:\ora11g\diag\rdbms\orcl\orcl\cdump\orclcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C910B2C]
Mon Nov 09 23:08:18 2009
Errors in file c:\ora11g\diag\rdbms\orcl\orcl\cdump\orclcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [{empty}] [0x7C910B2C]
Tue Nov 10 08:48:02 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as C:\Ora11G\product\11.1.0\db_1\RDBMS
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\ORA11G\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEORCL.ORA
System parameters with non-default values:
  processes                = 150
  memory_target            = 816M
  control_files            = "C:\ORA11G\ORADATA\ORCL\CONTROL01.CTL"
  control_files            = "C:\ORA11G\ORADATA\ORCL\CONTROL02.CTL"
  control_files            = "C:\ORA11G\ORADATA\ORCL\CONTROL03.CTL"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "C:\Ora11G\flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
Tue Nov 10 08:48:12 2009
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  audit_file_dest          = "C:\ORA11G\ADMIN\ORCL\ADUMP"
  audit_trail              = "DB"
  db_name                  = "orcl"
  open_cursors             = 300
  diagnostic_dest          = "C:\ORA11G"
Tue Nov 10 08:48:14 2009
PMON started with pid=2, OS id=5496
Tue Nov 10 08:48:14 2009
VKTM started with pid=3, OS id=1504 at elevated priority
Tue Nov 10 08:48:14 2009
DIAG started with pid=4, OS id=5508
VKTM running at (20)ms precision
Tue Nov 10 08:48:15 2009
DBRM started with pid=5, OS id=5516
Tue Nov 10 08:48:15 2009
PSP0 started with pid=6, OS id=5520
Tue Nov 10 08:48:15 2009
DSKM started with pid=7, OS id=5524
Tue Nov 10 08:48:15 2009
DIA0 started with pid=8, OS id=5528
Tue Nov 10 08:48:15 2009
MMAN started with pid=9, OS id=5532
Tue Nov 10 08:48:15 2009
DBW0 started with pid=10, OS id=5536
Tue Nov 10 08:48:15 2009
LGWR started with pid=11, OS id=5540
Tue Nov 10 08:48:15 2009
CKPT started with pid=12, OS id=5544
Tue Nov 10 08:48:15 2009
SMON started with pid=13, OS id=4100
Tue Nov 10 08:48:15 2009
RECO started with pid=14, OS id=5552
Tue Nov 10 08:48:15 2009
MMON started with pid=15, OS id=3064
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Nov 10 08:48:15 2009
MMNL started with pid=16, OS id=5556
starting up 1 shared server(s) ...
ORACLE_BASE from environment = C:\Ora11G
Tue Nov 10 08:48:18 2009
alter database mount exclusive
Setting recovery target incarnation to 2
Tue Nov 10 08:48:27 2009
Successful mount of redo thread 1, with mount id 1230365654
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Tue Nov 10 08:48:29 2009
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 671 redo blocks read, 71 data blocks need recovery
Started redo application at
 Thread 1: logseq 18, block 29516
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0
  Mem# 0: C:\ORA11G\ORADATA\ORCL\REDO03.LOG
Completed redo application
Completed crash recovery at
 Thread 1: logseq 18, block 30187, scn 1424037
 71 data blocks read, 71 data blocks written, 671 redo blocks read
Tue Nov 10 08:48:39 2009
Thread 1 advanced to log sequence 19
Thread 1 opened at log sequence 19
  Current log# 1 seq# 19 mem# 0: C:\ORA11G\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Tue Nov 10 08:48:40 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Nov 10 08:48:40 2009
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Opening with internal Resource Manager plan
Starting background process FBDA
Tue Nov 10 08:48:49 2009
FBDA started with pid=21, OS id=5592
Starting background process SMCO
Tue Nov 10 08:48:49 2009
SMCO started with pid=22, OS id=5600
Tue Nov 10 08:48:51 2009
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Nov 10 08:48:54 2009
QMNC started with pid=24, OS id=4716
Tue Nov 10 08:49:18 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Nov 10 08:49:37 2009
Completed: alter database open
Tue Nov 10 08:53:19 2009
Starting background process CJQ0
Tue Nov 10 08:53:19 2009
CJQ0 started with pid=30, OS id=4108

Ooops, I don't do WinDoze.
But, if you do not have rights to see any user in "computer management" it may be a "permissions" error that is preventing you from doing whatever you are trying to do.
PS: Next time just "attach" the logs as files.
Thanks.

Which is odd as I am a member of the Administrators group.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

should be

# SQLNET.AUTHENTICATION_SERVICES= (NTS)

Now post LISTENER.ORA file or do static registration:

For STATIC registration of the Oracle service to the Listener:

Go to the remote 10g installation where the Oracle server resides.

Backup LISTENER.ORA file!!!!!!!

In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.


In Oracle 11g you have to create from scratch in the listener.ora file the section

SID_LIST_LISTENER =
&..

mentioned above.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start

Thats all.

Questions
1. Why are we commenting out "SQLNET.AUTHENTICATION_SERVICES= (NTS)" when that is what is present in the listener files on the machines that are working and when that is what was generated by the Oracle install?
By Post Listener file do you mean post it on this thread?  If so see below.

The first file is the one from my local machine.  

# listener.ora Network Configuration File: C:\Ora11G\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ZTLatitude)(PORT = 1521))
    )
  )

# listener.ora Network Configuration File: C:\app\administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = egis.cflhd.zekiah.com)(PORT = 1521))
    )
  )

Could not upload using file button due to ora not being in the list of acceptableextensions.

There other is the one from the server.

1. You have to delete this line because it ask to do OS authentification of the user,
    not Oracle authentification.

2. I need the server listener.ora.
Apparantly you have not registered instance by the Listener.
Do following on the server listener.ora:

Go to the remote 10g installation where the Oracle server resides.

Backup LISTENER.ORA file!!!!!!!

In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.


In Oracle 11g you have to create from scratch in the listener.ora file the section

SID_LIST_LISTENER =
&..

mentioned above.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start

The second part is the server listerner file here it is again.
# listener.ora Network Configuration File: C:\app\administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = egis.cflhd.zekiah.com)(PORT = 1521))
    )
  )

But re: authentication,  we want to do OS authentication.

Check on the server if the instance is registered:

lsnrctl  services

If it is up

lsnrctl status

If not - start it and register the instance as described over ...

listener is up and running see attached code  I need to ask another question.  I will enter as "NVL function not consistently working."  That answer is more imperative than this one.  Could you guys take a look same number of points.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
 
C:\Documents and Settings\ejoell>lsnrctl  services
 
LSNRCTL for 64-bit Windows: Version 11.1.0.6.0 - Production on 16-NOV-2009 16:07
:59
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "arcgis" has 1 instance(s).
  Instance "arcgis", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:25659 refused:0 state:ready
         LOCAL SERVER
Service "arcgisXDB" has 1 instance(s).
  Instance "arcgis", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: EGIS, pid: 2616>
         (ADDRESS=(PROTOCOL=tcp)(HOST=egis.cflhd.zekiah.com)(PORT=1107))
Service "arcgis_XPT" has 1 instance(s).
  Instance "arcgis", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:25659 refused:0 state:ready
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:164 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: EGIS, pid: 2640>
         (ADDRESS=(PROTOCOL=tcp)(HOST=egis.cflhd.zekiah.com)(PORT=1104))
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:164 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
 
C:\Documents and Settings\ejoell>lsnrctl status
 
LSNRCTL for 64-bit Windows: Version 11.1.0.6.0 - Production on 16-NOV-2009 16:09
:09
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date                30-SEP-2009 22:17:50
Uptime                    46 days 18 hr. 51 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\administrator\product\11.1.0\db_1\network\admin
\listener.ora
Listener Log File         c:\app\administrator\diag\tnslsnr\egis\listener\alert\
log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=egis.cflhd.zekiah.com)(PORT=1521)))
Services Summary...
Service "arcgis" has 1 instance(s).
  Instance "arcgis", status READY, has 1 handler(s) for this service...
Service "arcgisXDB" has 1 instance(s).
  Instance "arcgis", status READY, has 1 handler(s) for this service...
Service "arcgis_XPT" has 1 instance(s).
  Instance "arcgis", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:

OK back here again.  I need to run a stored procedure in SQLPlus on my local instance.  But I can't get into SQLPlus.  same error as I was getting on server.  Same TNS Protocol adaptor error.  ( I am truly tired of seeing those three words.)  see attached listener.ora and tnsnames.ora.  mean while I will try to stop and start the service.

P.S. I can successfully connect to the server described in the tnsnames.ora as "arcgis" by entering "SQLPlus gis@Arcgis/gis".  But I can't connect to local instance even if I enter "SQLPlus user@ERFO/password"

Never mind. that SQLPLUs opened fine after stopping and starting the service.  (head shake in wonderment.)  

You guys have been trying to get me to run stuff in sQLPlus. So I have been trying I would particularly like to run this stored procedure in SQLPlus, so I can find out while it is failing.  I enter a variable to hold the contents of the out parameter (both using Declare iResult in; and var iResult. )  I then try to enter the call to the stored prcedure as
var iResult
EXEC ERFOAPPLICANT.INSERT_INOI_SP
(
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      222,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      546,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      65 ,
      175,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      :iResult
);

Print iResult;

The result is always var iResult
EXEC ERFOAPPLICANT.INSERT_INOI_SP
(
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      222,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      546,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      65 ,
      175,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      'sss' ,
      :iResult
);

Print iResult;  
I try to print the result.  When I copy and paste the Stored procedure call it runs after the first line in it runs and create an error  as below

SQL> var iResult
SP2-0552: Bind variable "iresult"
SQL> EXEC ERFOAPPLICANT.INSERT_IN
BEGIN ERFOAPPLICANT.INSERT_INOI_S

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> (
  2     'sss' ,
  3     'sss' ,
  4     'sss' ,
  5     'sss' ,
  6     'sss' ,
  7     'sss' ,
  8     'sss' ,
  9     222,
 10     'sss' ,
 11     'sss' ,
 12     'sss' ,
 13     'sss' ,
 14     'sss' ,
 15     546,
 16     'sss' ,
 17     'sss' ,
 18     'sss' ,
 19     'sss' ,
 20     'sss' ,
 21     65 ,
 22     175,
 23     'sss' ,
 24     'sss' ,
 25     'sss' ,
 26     'sss' ,
 27     'sss' ,
 28     'sss' ,
 29     'sss' ,
 30     'sss' ,
 31     :iResult
 32  );
        'sss' ,
        *
ERROR at line 2:
ORA-00928: missing SELECT keyword


SQL>
SQL> Print iResult;
SQL> Print iResult;
SP2-0552: Bind variable "IRESULT" not declared.
SQL>
I want to run the stored procedure from sqlplus inorder to debugging it but I can't even get it to start.
See code for SP.

create or replace PROCEDURE INSERT_INOI_SP
(
	VARCOORDINATORNAME IN VARCHAR,
	VARCOORDINATORPHONE IN VARCHAR,
	VARDIVISIONNAME IN VARCHAR,
	VARADDRESS IN VARCHAR,
	VARCITY IN VARCHAR,
	VARSTATE IN VARCHAR,
	VARZIP IN VARCHAR,
	VARCONTACTID IN number,
	VARCONTACTFNAME IN VARCHAR,
	VARCONTACTMNAME IN VARCHAR,
	VARCONTACTLNAME IN VARCHAR,
	VARCONTACTPHONE IN VARCHAR,
	VARCONTACTEMAIL IN VARCHAR,
	VARCONTACTORGID IN number,
	VARCONTACTORGNAME IN VARCHAR,
	VARCONTACTADDRESS IN VARCHAR,
	VARCONTACTCITY IN VARCHAR,
	VARCONTACTSTATE IN VARCHAR,
	VARCONTACTZIP IN VARCHAR,
	VARLAT IN Number,
	VARLON IN Number,
	VARLOCATIONDETAILS IN VARCHAR,
	VARPRELIMCOSTEST IN VARCHAR,
	VARSTARTDATE IN VARCHAR,
	VARENDDATE IN VARCHAR,
	VARSTORMEVENTID IN VARCHAR,
	VARSTORMEVENTNAME IN VARCHAR,
	VAREVENTDETAILS IN VARCHAR,
	VARCONTACTPINNUM IN VARCHAR,
	VARRESULT OUT number
)
AS
 
BEGIN
	DECLARE VARINOIID number;
	BEGIN
	SELECT MAX(INOIID) into VARINOIID FROM ERFOAPPLICANT.INOI;
	VARINOIID := VARINOIID + 1;
 
	Insert into ERFOAPPLICANT.INOI
		(INOIID
		,	COORDINATORNAME
		,	COORDINATORPHONE
		,	DIVISIONNAME
		,	ADDRESS
		,	CITY
		,	STATE
		,	ZIP
		,	CONTACTID
		,	CONTACTFNAME
		,	CONTACTMNAME
		,	CONTACTLNAME
		,	CONTACTPHONE
		,	CONTACTEMAIL
		,	CONTACTORGID
		,	CONTACTORGNAME
		,	CONTACTADDRESS
		,	CONTACTCITY
		,	CONTACTSTATE
		,	CONTACTZIP
		,	LAT
		,	LON
		,	LOCATIONDETAILS
		,	PRELIMCOSTEST
		,	STARTDATE
		,	ENDDATE
		,	STORMEVENTID
		,	STORMEVENTNAME
		,	EVENTDETAILS
		,	CONTACTPINNUM)
	VALUES
		(VARINOIID
		,	VARCOORDINATORNAME
		,	VARCOORDINATORPHONE
		,	VARDIVISIONNAME
		,	VARADDRESS
		,	VARCITY
		,	VARSTATE
		,	VARZIP
		,	VARCONTACTID
		,	VARCONTACTFNAME
		,	VARCONTACTMNAME
		,	VARCONTACTLNAME
		,	VARCONTACTPHONE
		,	VARCONTACTEMAIL
		,	VARCONTACTORGID
		,	VARCONTACTORGNAME
		,	VARCONTACTADDRESS
		,	VARCONTACTCITY
		,	VARCONTACTSTATE
		,	VARCONTACTZIP
		,	VARLAT
		,	VARLON
		,	VARLOCATIONDETAILS
		,	VARPRELIMCOSTEST
		,	VARSTARTDATE
		,	VARENDDATE
		,	VARSTORMEVENTID
		,	VARSTORMEVENTNAME
		,	VAREVENTDETAILS
		,	VARCONTACTPINNUM);
		END;
		VARRESULT := 1;
		EXCEPTION
		WHEN OTHERS THEN
		VARRESULT := 0;
END;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:

Some rectifications:

set serveroutput on
 
create or replace PROCEDURE INSERT_INOI_SP
(
	VARCOORDINATORNAME IN VARCHAR,
	VARCOORDINATORPHONE IN VARCHAR,
	VARDIVISIONNAME IN VARCHAR,
	VARADDRESS IN VARCHAR,
	VARCITY IN VARCHAR,
	VARSTATE IN VARCHAR,
	VARZIP IN VARCHAR,
	VARCONTACTID IN number,
	VARCONTACTFNAME IN VARCHAR,
	VARCONTACTMNAME IN VARCHAR,
	VARCONTACTLNAME IN VARCHAR,
	VARCONTACTPHONE IN VARCHAR,
	VARCONTACTEMAIL IN VARCHAR,
	VARCONTACTORGID IN number,
	VARCONTACTORGNAME IN VARCHAR,
	VARCONTACTADDRESS IN VARCHAR,
	VARCONTACTCITY IN VARCHAR,
	VARCONTACTSTATE IN VARCHAR,
	VARCONTACTZIP IN VARCHAR,
	VARLAT IN Number,
	VARLON IN Number,
	VARLOCATIONDETAILS IN VARCHAR,
	VARPRELIMCOSTEST IN VARCHAR,
	VARSTARTDATE IN VARCHAR,
	VARENDDATE IN VARCHAR,
	VARSTORMEVENTID IN VARCHAR,
	VARSTORMEVENTNAME IN VARCHAR,
	VAREVENTDETAILS IN VARCHAR,
	VARCONTACTPINNUM IN VARCHAR,
	VARRESULT OUT number
)
AS
 
VARINOIID number;
BEGIN
	SELECT MAX(INOIID) into VARINOIID FROM ERFOAPPLICANT.INOI;
	VARINOIID := VARINOIID + 1;
 
	Insert into ERFOAPPLICANT.INOI
		(INOIID
		,	COORDINATORNAME
		,	COORDINATORPHONE
		,	DIVISIONNAME
		,	ADDRESS
		,	CITY
		,	STATE
		,	ZIP
		,	CONTACTID
		,	CONTACTFNAME
		,	CONTACTMNAME
		,	CONTACTLNAME
		,	CONTACTPHONE
		,	CONTACTEMAIL
		,	CONTACTORGID
		,	CONTACTORGNAME
		,	CONTACTADDRESS
		,	CONTACTCITY
		,	CONTACTSTATE
		,	CONTACTZIP
		,	LAT
		,	LON
		,	LOCATIONDETAILS
		,	PRELIMCOSTEST
		,	STARTDATE
		,	ENDDATE
		,	STORMEVENTID
		,	STORMEVENTNAME
		,	EVENTDETAILS
		,	CONTACTPINNUM)
	VALUES
		(VARINOIID
		,	VARCOORDINATORNAME
		,	VARCOORDINATORPHONE
		,	VARDIVISIONNAME
		,	VARADDRESS
		,	VARCITY
		,	VARSTATE
		,	VARZIP
		,	VARCONTACTID
		,	VARCONTACTFNAME
		,	VARCONTACTMNAME
		,	VARCONTACTLNAME
		,	VARCONTACTPHONE
		,	VARCONTACTEMAIL
		,	VARCONTACTORGID
		,	VARCONTACTORGNAME
		,	VARCONTACTADDRESS
		,	VARCONTACTCITY
		,	VARCONTACTSTATE
		,	VARCONTACTZIP
		,	VARLAT
		,	VARLON
		,	VARLOCATIONDETAILS
		,	VARPRELIMCOSTEST
		,	VARSTARTDATE
		,	VARENDDATE
		,	VARSTORMEVENTID
		,	VARSTORMEVENTNAME
		,	VAREVENTDETAILS
		,	VARCONTACTPINNUM);
		END;
		VARRESULT := 1;
		EXCEPTION
		WHEN OTHERS THEN
		VARRESULT := 0;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(substr(sqlerrm,1,254));
END;
/
SHOW ERR
 
execute INSERT_INOI_SP
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:

The purpose of this stored priocedure is to be run from an application so I don't want server output i need the output deposited into the output parameter.
You cannot execute without any as the dbengine will error saying insufficient parameters provided.  When I set the variable for the output parameter as specified in the documentation I get the error SP2-0552: Bind variable "iresult"  when I try to run it without specifying variable for ouput parameter I get SP2-0552: Bind variable "iresult"  When I run it from inside visual studio I get the ouput parameter back set to 0 after it is set by an exception call in the stored procedure.

Sofar you return only one value.
You can use FUNCTION that returns this value and shows if

SELECT function_call_here  FROM dual;

You will see the result without using bind variable.

I know the result it is 0. I want to know why it is zero which is why I am trying to run it sqlplus.  but it won't run in sqlplus without a bind variable and it won't let me set a bind variable.  I am also trying to determine if there is something wrong with the insert so I tried to run that strictly from sqlplus.  with Listing 1 I get  
ERROR at line 3:
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the
following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "DECLARE" to continue.
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the
following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "b
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the
following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "b
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the
following:
begin function pragma procedure subtype type <an ident

With Listing2 I get:
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "VARCOORDINATORNAME" when expecting one of
the following:
:= . ( @ % ;

I don't know what happend to my code block but here is is again

Listing 1
BEGIN
Declare VARCOORDINATORNAME VARCHAR;
Declare 	VARCOORDINATORPHONE VARCHAR ;
Declare 	VARDIVISIONNAME VARCHAR ;
Declare 	VARADDRESS VARCHAR ;
Declare 	VARCITY VARCHAR ;
Declare 	VARSTATE VARCHAR ;
Declare 	VARZIP VARCHAR ;
Declare 	VARCONTACTID number ;
Declare 	VARCONTACTFNAME VARCHAR ;
Declare 	VARCONTACTMNAME VARCHAR ;
Declare 	VARCONTACTLNAME VARCHAR ;
Declare 	VARCONTACTPHONE VARCHAR ;
Declare 	VARCONTACTEMAIL VARCHAR ;
Declare 	VARCONTACTORGID number ;
Declare 	VARCONTACTORGNAME VARCHAR ;
Declare 	VARCONTACTADDRESS VARCHAR ;
Declare 	VARCONTACTCITY VARCHAR ;
Declare 	VARCONTACTSTATE VARCHAR ;
Declare 	VARCONTACTZIP VARCHAR ;
Declare 	VARLAT Number ;
Declare 	VARLON Number ;
Declare 	VARLOCATIONDETAILS VARCHAR ;
Declare 	VARPRELIMCOSTEST Decimal ;
Declare 	VARSTARTDATE date ;
Declare 	VARENDDATE date ;
Declare 	VARSTORMEVENTID number ;
Declare 	VARSTORMEVENTNAME VARCHAR ;
Declare 	VAREVENTDETAILS VARCHAR ;
Declare 	VARCONTACTPINNUM VARCHAR;
 
VARCOORDINATORNAME := 'name';
VARCOORDINATORPHONE  := 'phone';
VARDIVISIONNAME  := 'name';
VARADDRESS := 'addr';
VARCITY  := 'city';
VARSTATE := 'st' ;
VARZIP  := 'eeee';
VARCONTACTID :=  1;
VARCONTACTFNAME  := 'Fname';
VARCONTACTMNAME  := 'MName';
VARCONTACTLNAME  := 'Lname';
VARCONTACTPHONE  := 'cph';
VARCONTACTEMAIL  := 'cem';
VARCONTACTORGID := 2;
VARCONTACTORGNAME := 'org' ;
VARCONTACTADDRESS  := 'caddr';
VARCONTACTCITY  := 'ccity' ;
VARCONTACTSTATE := 'md' ;
VARCONTACTZIP := 'czip' ;
VARLAT := 45.26;
VARLON := 75.55;
VARLOCATIONDETAILS  := 'Details';
VARPRELIMCOSTEST := 5000.21;
VARSTARTDATE := '22-01-2009';
VARENDDATE := '24-02-2009';
VARSTORMEVENTID := 2;
VARSTORMEVENTNAME  := 'storm' ;
VAREVENTDETAILS  := 'StDet';
VARCONTACTPINNUM := '6226';
 
Insert into ERFOAPPLICANT.INOI
		(INOIID
		,	COORDINATORNAME
		,	COORDINATORPHONE
		,	DIVISIONNAME
		,	ADDRESS
		,	CITY
		,	STATE
		,	ZIP
		,	CONTACTID
		,	CONTACTFNAME
		,	CONTACTMNAME
		,	CONTACTLNAME
		,	CONTACTPHONE
		,	CONTACTEMAIL
		,	CONTACTORGID
		,	CONTACTORGNAME
		,	CONTACTADDRESS
		,	CONTACTCITY
		,	CONTACTSTATE
		,	CONTACTZIP
		,	LAT
		,	LON
		,	LOCATIONDETAILS
		,	PRELIMCOSTEST
		,	STARTDATE
		,	ENDDATE
		,	STORMEVENTID
		,	STORMEVENTNAME
		,	EVENTDETAILS
		,	CONTACTPINNUM)
	VALUES
		(1
		,	VARCOORDINATORNAME
		,	VARCOORDINATORPHONE
		,	VARDIVISIONNAME
		,	VARADDRESS
		,	VARCITY
		,	VARSTATE
		,	VARZIP
		,	VARCONTACTID
		,	VARCONTACTFNAME
		,	VARCONTACTMNAME
		,	VARCONTACTLNAME
		,	VARCONTACTPHONE
		,	VARCONTACTEMAIL
		,	VARCONTACTORGID
		,	VARCONTACTORGNAME
		,	VARCONTACTADDRESS
		,	VARCONTACTCITY
		,	VARCONTACTSTATE
		,	VARCONTACTZIP
		,	VARLAT
		,	VARLON
		,	VARLOCATIONDETAILS
		,	VARPRELIMCOSTEST
		,	VARSTARTDATE
		,	VARENDDATE
		,	VARSTORMEVENTID
		,	VARSTORMEVENTNAME
		,	VAREVENTDETAILS
		,	VARCONTACTPINNUM);
END;
 
Listing2
BEGIN
Variable VARCOORDINATORNAME VARCHAR;
Variable 	VARCOORDINATORPHONE VARCHAR ;
Variable 	VARDIVISIONNAME VARCHAR ;
Variable 	VARADDRESS VARCHAR ;
Variable 	VARCITY VARCHAR ;
Variable 	VARSTATE VARCHAR ;
Variable 	VARZIP VARCHAR ;
Variable 	VARCONTACTID number ;
Variable 	VARCONTACTFNAME VARCHAR ;
Variable 	VARCONTACTMNAME VARCHAR ;
Variable 	VARCONTACTLNAME VARCHAR ;
Variable 	VARCONTACTPHONE VARCHAR ;
Variable 	VARCONTACTEMAIL VARCHAR ;
Variable 	VARCONTACTORGID number ;
Variable 	VARCONTACTORGNAME VARCHAR ;
Variable 	VARCONTACTADDRESS VARCHAR ;
Variable 	VARCONTACTCITY VARCHAR ;
Variable 	VARCONTACTSTATE VARCHAR ;
Variable 	VARCONTACTZIP VARCHAR ;
Variable 	VARLAT Number ;
Variable 	VARLON Number ;
Variable 	VARLOCATIONDETAILS VARCHAR ;
Variable 	VARPRELIMCOSTEST Decimal ;
Variable 	VARSTARTDATE date ;
Variable 	VARENDDATE date ;
Variable 	VARSTORMEVENTID number ;
Variable 	VARSTORMEVENTNAME VARCHAR ;
Variable 	VAREVENTDETAILS VARCHAR ;
Variable 	VARCONTACTPINNUM VARCHAR;
 
VARCOORDINATORNAME := 'name';
VARCOORDINATORPHONE  := 'phone';
VARDIVISIONNAME  := 'name';
VARADDRESS := 'addr';
VARCITY  := 'city';
VARSTATE := 'st' ;
VARZIP  := 'eeee';
VARCONTACTID :=  1;
VARCONTACTFNAME  := 'Fname';
VARCONTACTMNAME  := 'MName';
VARCONTACTLNAME  := 'Lname';
VARCONTACTPHONE  := 'cph';
VARCONTACTEMAIL  := 'cem';
VARCONTACTORGID := 2;
VARCONTACTORGNAME := 'org' ;
VARCONTACTADDRESS  := 'caddr';
VARCONTACTCITY  := 'ccity' ;
VARCONTACTSTATE := 'md' ;
VARCONTACTZIP := 'czip' ;
VARLAT := 45.26;
VARLON := 75.55;
VARLOCATIONDETAILS  := 'Details';
VARPRELIMCOSTEST := 5000.21;
VARSTARTDATE := '22-01-2009';
VARENDDATE := '24-02-2009';
VARSTORMEVENTID := 2;
VARSTORMEVENTNAME  := 'storm' ;
VAREVENTDETAILS  := 'StDet';
VARCONTACTPINNUM := '6226';
 
Insert into ERFOAPPLICANT.INOI
		(INOIID
		,	COORDINATORNAME
		,	COORDINATORPHONE
		,	DIVISIONNAME
		,	ADDRESS
		,	CITY
		,	STATE
		,	ZIP
		,	CONTACTID
		,	CONTACTFNAME
		,	CONTACTMNAME
		,	CONTACTLNAME
		,	CONTACTPHONE
		,	CONTACTEMAIL
		,	CONTACTORGID
		,	CONTACTORGNAME
		,	CONTACTADDRESS
		,	CONTACTCITY
		,	CONTACTSTATE
		,	CONTACTZIP
		,	LAT
		,	LON
		,	LOCATIONDETAILS
		,	PRELIMCOSTEST
		,	STARTDATE
		,	ENDDATE
		,	STORMEVENTID
		,	STORMEVENTNAME
		,	EVENTDETAILS
		,	CONTACTPINNUM)
	VALUES
		(1
		,	VARCOORDINATORNAME
		,	VARCOORDINATORPHONE
		,	VARDIVISIONNAME
		,	VARADDRESS
		,	VARCITY
		,	VARSTATE
		,	VARZIP
		,	VARCONTACTID
		,	VARCONTACTFNAME
		,	VARCONTACTMNAME
		,	VARCONTACTLNAME
		,	VARCONTACTPHONE
		,	VARCONTACTEMAIL
		,	VARCONTACTORGID
		,	VARCONTACTORGNAME
		,	VARCONTACTADDRESS
		,	VARCONTACTCITY
		,	VARCONTACTSTATE
		,	VARCONTACTZIP
		,	VARLAT
		,	VARLON
		,	VARLOCATIONDETAILS
		,	VARPRELIMCOSTEST
		,	VARSTARTDATE
		,	VARENDDATE
		,	VARSTORMEVENTID
		,	VARSTORMEVENTNAME
		,	VAREVENTDETAILS
		,	VARCONTACTPINNUM);
END;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:

I disagree!
 This what you are constructing will never be accepted by Oracle!
please try to use prepared samples:


An anonymous block containing declaration, execution, and exception sections:

DECLARE
   right_now DATE := SYSDATE;
   too_late EXCEPTION;
BEGIN
   IF :employee.hiredate < ADD_MONTHS (right_now, 6)
   THEN
      RAISE too_late;
   ELSE
      :employee.hiredate := right_now;
   END IF;
EXCEPTION
   WHEN too_late
   THEN
      DBMS_OUTPUT.PUT_LINE
         ('You no longer qualify for free air.');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE
         ('Error encountered: ' || SQLCODE);
END;

DECLARE
VARCOORDINATORNAME VARCHAR2(80)  := 'name';;
VARCOORDINATORPHONE VARCHAR2(65) :=  'phone';;
.......
 
Insert into ERFOAPPLICANT.INOI
		(INOIID
		,	COORDINATORNAME
		,	COORDINATORPHONE
		,	DIVISIONNAME
		,	ADDRESS
		,	CITY
		,	STATE
		,	ZIP
		,	CONTACTID
		,	CONTACTFNAME
		,	CONTACTMNAME
		,	CONTACTLNAME
		,	CONTACTPHONE
		,	CONTACTEMAIL
		,	CONTACTORGID
		,	CONTACTORGNAME
		,	CONTACTADDRESS
		,	CONTACTCITY
		,	CONTACTSTATE
		,	CONTACTZIP
		,	LAT
		,	LON
		,	LOCATIONDETAILS
		,	PRELIMCOSTEST
		,	STARTDATE
		,	ENDDATE
		,	STORMEVENTID
		,	STORMEVENTNAME
		,	EVENTDETAILS
		,	CONTACTPINNUM)
	VALUES
		(1
		,	VARCOORDINATORNAME
		,	VARCOORDINATORPHONE
		,	VARDIVISIONNAME
		,	VARADDRESS
		,	VARCITY
		,	VARSTATE
		,	VARZIP
		,	VARCONTACTID
		,	VARCONTACTFNAME
		,	VARCONTACTMNAME
		,	VARCONTACTLNAME
		,	VARCONTACTPHONE
		,	VARCONTACTEMAIL
		,	VARCONTACTORGID
		,	VARCONTACTORGNAME
		,	VARCONTACTADDRESS
		,	VARCONTACTCITY
		,	VARCONTACTSTATE
		,	VARCONTACTZIP
		,	VARLAT
		,	VARLON
		,	VARLOCATIONDETAILS
		,	VARPRELIMCOSTEST
		,	VARSTARTDATE
		,	VARENDDATE
		,	VARSTORMEVENTID
		,	VARSTORMEVENTNAME
		,	VAREVENTDETAILS
		,	VARCONTACTPINNUM);
END;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:

Hope you guys had a nice holiday.  
1. Why the double semi-colon?

Did not work.  supposedly the script ran without error.  However upon viewing the table in the database, the row was never inserted.  The same message was shown when I ran the exact same insert statment on a table with a primary key. of INOIID.
Here is screen shot of SQLPLUS
SQL> DECLARE
  2  VARCOORDINATORNAME VARCHAR2(75) :=  'name' ;
  3  VARCOORDINATORPHONE VARCHAR2(20) :=  'phone';
  4  VARDIVISIONNAME VARCHAR2(75) :=  'name';
  5  VARADDRESS VARCHAR2(75) :=  'addr' ;
  6  VARCITY VARCHAR2(75) :=  'city';
  7  VARSTATE VARCHAR2(2) :=  'st';
  8  VARZIP VARCHAR2(10) := 'eeee' ;
  9  VARCONTACTID number(38, 0) :=  1;
 10  VARCONTACTFNAME VARCHAR2(35) :=  'Fname';
 11  VARCONTACTMNAME VARCHAR2(35) :=  'MName';
 12  VARCONTACTLNAME VARCHAR2(35) :=  'Lname';
 13  VARCONTACTPHONE VARCHAR2(25) :=  'cph';
 14  VARCONTACTEMAIL VARCHAR2(75) :=  'cem';
 15  VARCONTACTORGID number(38, 0) :=  2;
 16  VARCONTACTORGNAME VARCHAR2(75) :=  'org';
 17  VARCONTACTADDRESS VARCHAR2(35) :=  'caddr';
 18  VARCONTACTCITY VARCHAR2(35) :=  'ccity';
 19  VARCONTACTSTATE VARCHAR2(25) := 'md';
 20  VARCONTACTZIP VARCHAR2(10) :=  'czip';
 21  VARLAT Number(22) :=  45.26;
 22  VARLON Number(22) := 75.55 ;
 23  VARLOCATIONDETAILS VARCHAR2(500) :=  'Details';
 24  VARPRELIMCOSTEST Number(22):=  5000.21;
 25  VARSTARTDATE date :=  '22-Jan-2009';
 26  VARENDDATE date :=  '24-Feb-2009';
 27  VARSTORMEVENTID number(38, 0) :=  2;
 28  VARSTORMEVENTNAME VARCHAR2(75) :=  'storm';
 29  VAREVENTDETAILS VARCHAR2(1000) :=  'StDet';
 30  VARCONTACTPINNUM VARCHAR2(8) :=  '6226';
 31  BEGIN
 32  Insert into ERFOAPPLICANT.INOI
 33             (INOIID
 34             ,       COORDINATORNAME
 35             ,       COORDINATORPHONE
 36             ,       DIVISIONNAME
 37             ,       ADDRESS
 38             ,       CITY
 39             ,       STATE
 40             ,       ZIP
 41             ,       CONTACTID
 42             ,       CONTACTFNAME
 43             ,       CONTACTMNAME
 44             ,       CONTACTLNAME
 45             ,       CONTACTPHONE
 46             ,       CONTACTEMAIL
 47             ,       CONTACTORGID
 48             ,       CONTACTORGNAME
 49             ,       CONTACTADDRESS
 50             ,       CONTACTCITY
 51             ,       CONTACTSTATE
 52             ,       CONTACTZIP
 53             ,       LAT
 54             ,       LON
 55             ,       LOCATIONDETAILS
 56             ,       PRELIMCOSTEST
 57             ,       STARTDATE
 58             ,       ENDDATE
 59             ,       STORMEVENTID
 60             ,       STORMEVENTNAME
 61             ,       EVENTDETAILS
 62             ,       CONTACTPINNUM)
 63     VALUES
 64             (3
 65             ,       VARCOORDINATORNAME
 66             ,       VARCOORDINATORPHONE
 67             ,       VARDIVISIONNAME
 68             ,       VARADDRESS
 69             ,       VARCITY
 70             ,       VARSTATE
 71             ,       VARZIP
 72             ,       VARCONTACTID
 73             ,       VARCONTACTFNAME
 74             ,       VARCONTACTMNAME
 75             ,       VARCONTACTLNAME
 76             ,       VARCONTACTPHONE
 77             ,       VARCONTACTEMAIL
 78             ,       VARCONTACTORGID
 79             ,       VARCONTACTORGNAME
 80             ,       VARCONTACTADDRESS
 81             ,       VARCONTACTCITY
 82             ,       VARCONTACTSTATE
 83             ,       VARCONTACTZIP
 84             ,       VARLAT
 85             ,       VARLON
 86             ,       VARLOCATIONDETAILS
 87             ,       VARPRELIMCOSTEST
 88             ,       VARSTARTDATE
 89             ,       VARENDDATE
 90             ,       VARSTORMEVENTID
 91             ,       VARSTORMEVENTNAME
 92             ,       VAREVENTDETAILS
 93             ,       VARCONTACTPINNUM);
 94  EXCEPTION
 95  WHEN OTHERS
 96  THEN
 97   DBMS_OUTPUT.PUT_LINE
 98   ('Error on insert: ' || SQLCODE);
 99  END;
100  /

PL/SQL procedure successfully completed.

Double semi-colon fails with                                             *
ERROR at line 2:
ORA-06550: line 2, column 45:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

Need to discontinue fwork on this stored procedure as there wsere 5 others I was supposed to have developed by now.

Double semi-colon is my mistake.
Sorry!

If it doesn't work it have to give an error message

To see the message before running the code issue in SQL*Plus

set serveroutput on

OK the above code ran once but its use in my stored procedure did not work.  The issue turned out to be the fact that the  code was converting the output of an html input box of the type text it a date rahter than the output of an asp textbox to a date and sending it to the stored procedure.  The stored procedure would not accept the date from the html text box but would from the asp textbox.  Go figure.  I am closing this thread now and awarding schwertner the points.

I know we've been down this road before.  but I can't get my stored procedure to execute in sqlplus.
The same old sp2-0552 error bind variable not declared being returned, yet as far as I can tell I am trying to execute this stored procedure the same way as the other.  Any assistance would be of benefit.

Also I have a server with two oracle instances.  The default orcl instance's enterprise manager is opening on http://url:1158/em while the second instance is trying to open on http://url:5500/em.  However the response to the second attempt to open is unable to display web site.
How can I tell what port to use to view em for second instance.  presumably using the command line or a parameter file.

SQL> DECLARE
  2  VARCOORDINATORID number(38, 0) :=  'name' ;
  3  VARCONTACTID number(38, 0) :=  1;
  4  VARCONTACTFNAME VARCHAR2(35) :=  'Fname';
  5  VARCONTACTMNAME VARCHAR2(35) :=  'MName';
  6  VARCONTACTLNAME VARCHAR2(35) :=  'Lname';
  7  VARCONTACTPHONE VARCHAR2(25) :=  'cph';
  8  VARCONTACTEMAIL VARCHAR2(75) :=  'cem';
  9  VARCONTACTORGID number(38, 0) :=  2;
 10  VARCONTACTORGNAME VARCHAR2(75) :=  'org';
 11  VARCONTACTADDRESS VARCHAR2(35) :=  'caddr';
 12  VARCONTACTCITY VARCHAR2(35) :=  'ccity';
 13  VARCONTACTSTATE VARCHAR2(25) := 'md';
 14  VARCONTACTZIP VARCHAR2(10) :=  'czip';
 15  VARLAT Number(22) :=  45.26;
 16  VARLON Number(22) := 75.55 ;
 17  VARLOCATIONDETAILS VARCHAR2(500) :=  'Details';
 18  VARPRELIMCOSTEST Number(22):=  5000.21;
 19  VARSTARTDATE VARCHAR2(25) :=  '22-Jan-2009';
 20  VARENDDATE VARCHAR2(25) :=  '24-Feb-2009';
 21  VARSTORMEVENTID number(38, 0) :=  2;
 22  VARSTORMEVENTNAME VARCHAR2(75) :=  'storm';
 23  VAREVENTDETAILS VARCHAR2(1000) :=  'StDet';
 24  VARCONTACTPINNUM VARCHAR2(8) :=  '6226';
 25
 26  BEGIN
 27  variable iResult NUMBER
 28  EXEC ERFOAPPLICANT.INSERT_FNOI_SP(VARCOORDINATORID, VARCONTACTID, VARCONTAC
TFNAME, VARCONTACTMNAME, VARCONTACTLNAME,  VARCONTACTPHONE, VARCONTACTEMAIL, VAR
CONTACTORGID, VARCONTACTORGNAME, VARCONTACTADDRESS, VARCONTACTCITY, VARCONTACTST
ATE, VARCONTACTZIP, VARLAT, VARLON, VARLOCATIONDETAILS, VARPRELIMCOSTEST, VARSTA
RTDATE, VARENDDATE, VARSTORMEVENTID, VARSTORMEVENTNAME, VAREVENTDETAILS, VARCONT
ACTPINNUM, :iResult);
 29  end;
 30  /
SP2-0552: Bind variable "IRESULT" not declared.

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

Any thing about enterprise manager console?

Made three attempts using your suggestions  see attached code for results  (Only copied pertinent areas of code for each attempt.)

25  iResult NUMBER(38,0);
 26  EXEC ERFOAPPLICANT.INSERT_FNOI_SP(VARCOORDINATORID, VARCONTACTID, VARCONTAC
TFNAME, VARCONTACTMNAME, VARCONTACTLNAME,  VARCONTACTPHONE, VARCONTACTEMAIL, VAR
CONTACTORGID, VARCONTACTORGNAME, VARCONTACTADDRESS, VARCONTACTCITY, VARCONTACTST
ATE, VARCONTACTZIP, VARLAT, VARLON, VARLOCATIONDETAILS, VARPRELIMCOSTEST, VARSTA
RTDATE, VARENDDATE, VARSTORMEVENTID, VARSTORMEVENTNAME, VAREVENTDETAILS, VARCONT
ACTPINNUM, :iResult);
 27  print iresult;
 28  EXCEPTION
 29  WHEN OTHERS THEN
 30     DBMS_OUTPUT.PUT_LINE('Error running sP: ' || SQLCODE);
 31  END;
 32  /
SP2-0552: Bind variable "IRESULT" not declared.
 
 25  iResult NUMBER(38,0) := -1;
 26  EXEC ERFOAPPLICANT.INSERT_FNOI_SP(VARCOORDINATORID, VARCONTACTID, VARCONTAC
TFNAME, VARCONTACTMNAME, VARCONTACTLNAME,  VARCONTACTPHONE, VARCONTACTEMAIL, VAR
CONTACTORGID, VARCONTACTORGNAME, VARCONTACTADDRESS, VARCONTACTCITY, VARCONTACTST
ATE, VARCONTACTZIP, VARLAT, VARLON, VARLOCATIONDETAILS, VARPRELIMCOSTEST, VARSTA
RTDATE, VARENDDATE, VARSTORMEVENTID, VARSTORMEVENTNAME, VAREVENTDETAILS, VARCONT
ACTPINNUM, :iResult);
 27  print iresult;
 28  EXCEPTION
 29  WHEN OTHERS THEN
 30     DBMS_OUTPUT.PUT_LINE('Error running sP: ' || SQLCODE);
 31  END;
 32  /
SP2-0552: Bind variable "IRESULT" not declared.
 
 25  iResult NUMBER(38,0) = -1;
 26  EXEC ERFOAPPLICANT.INSERT_FNOI_SP(VARCOORDINATORID, VARCONTACTID, VARCONTAC
TFNAME, VARCONTACTMNAME, VARCONTACTLNAME,  VARCONTACTPHONE, VARCONTACTEMAIL, VAR
CONTACTORGID, VARCONTACTORGNAME, VARCONTACTADDRESS, VARCONTACTCITY, VARCONTACTST
ATE, VARCONTACTZIP, VARLAT, VARLON, VARLOCATIONDETAILS, VARPRELIMCOSTEST, VARSTA
RTDATE, VARENDDATE, VARSTORMEVENTID, VARSTORMEVENTNAME, VAREVENTDETAILS, VARCONT
ACTPINNUM, :iResult);
 27  print iresult;
 28  EXCEPTION
 29  WHEN OTHERS THEN
 30     DBMS_OUTPUT.PUT_LINE('Error running sP: ' || SQLCODE);
 31  END;
 32  /
SP2-0552: Bind variable "IRESULT" not declared.

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:

:iResult);
should be
iResult);


(please delete to colon!!!!!)

print is not legal PL/SQL command

Use
 DBMS_OUTPUT.PUT_LINE(iresult);