Question

SQL Developer Locks up after telling executing ddlscript

Asked by: Joeller

I know I am piggy backing on another question, but the guys have been very good at providing answers.  I am running an export script generated by SQL Developer in SQL developer on a different database.  I first I kept getting a lot of table not found errors until I realized that SQL Developer had generated the script to create each table without placing them in order so that the tables to which the foreign keys were related were created first.  (i.e. it made a script that tries to make table B which has a foreign key relationship to Table a before making table A.)  So I went into the script and moved the create table statements so that depedencies were created first.  Then when I ran it SQL developer locked up.  I can see what is wrong because the output window is scrolled down and not visible.  I can't find the log file to see if anything was logged. (For all I know nothing was logged due to the lockup of SQL Server.) Does anyhone know how I can find out what went wrong.

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-11-04 at 11:52:42ID24872205
Topic

Oracle 11.x Database

Participating Experts
2
Points
100
Comments
39

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Foreign Keys, Primary Keys, and relationships?!?!
    Hi, Im having a problem creating a relationship between two entities: tblAddrBookContacts and tblObjectShareContacts. The schema for the entities is: *tblAddrBookContacts(UserID, ContactUserID) the primary key is both of these together (they point to different rows on the...
  2. To find foreign keys which hase "enforce relationship for r…
    Sql Server 2000 I want to know all foreign key names in one of database where this option is checked. Enforce relationship for REPLICATION Result format Relationship_name PrimaryKeyTable ForeignKeyTable --------------------- ------------------- -----------------...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: MikeOM_DBAPosted on 2009-11-04 at 12:37:53ID: 25743635

Login to the server and run the script using sqlplus.
-- or --
From your client run the script using sqlplus.

PS: Remember to set "SPOOL ..." command to capture errors.

 

by: JoellerPosted on 2009-11-05 at 06:13:09ID: 25749599

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 .

 

by: MikeOM_DBAPosted on 2009-11-05 at 06:40:00ID: 25749870

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

 

by: JoellerPosted on 2009-11-05 at 06:55:19ID: 25750067

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.

 

by: schwertnerPosted on 2009-11-05 at 08:47:28ID: 25751344

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 ....

 

by: JoellerPosted on 2009-11-05 at 10:38:54ID: 25752465

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.

 

by: schwertnerPosted on 2009-11-06 at 00:39:12ID: 25757559

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).....

 

by: JoellerPosted on 2009-11-10 at 07:40:17ID: 25786403

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.

 

by: MikeOM_DBAPosted on 2009-11-10 at 08:45:38ID: 25787167

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:

Select allOpen in new window

 

by: JoellerPosted on 2009-11-10 at 09:32:13ID: 25787652

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

 

by: MikeOM_DBAPosted on 2009-11-10 at 11:17:28ID: 25788692

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.

 

by: JoellerPosted on 2009-11-10 at 14:46:54ID: 25790764

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

 

by: schwertnerPosted on 2009-11-11 at 00:44:50ID: 25793228

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.

 

by: JoellerPosted on 2009-11-13 at 07:53:08ID: 25814934

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.

 

by: schwertnerPosted on 2009-11-13 at 08:23:48ID: 25815204

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

 

by: JoellerPosted on 2009-11-13 at 08:39:00ID: 25815320

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.

 

by: schwertnerPosted on 2009-11-16 at 00:30:07ID: 25828574

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 ...

 

by: JoellerPosted on 2009-11-16 at 13:15:56ID: 25834208

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:

Select allOpen in new window

 

by: JoellerPosted on 2009-11-19 at 12:02:15ID: 25864346

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.

 

by: JoellerPosted on 2009-11-19 at 12:06:32ID: 25864390

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"

 

by: JoellerPosted on 2009-11-19 at 12:14:07ID: 25864452

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

 

by: JoellerPosted on 2009-11-24 at 12:17:33ID: 25900993

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:

Select allOpen in new window

 

by: schwertnerPosted on 2009-11-25 at 01:18:41ID: 25905422

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:

Select allOpen in new window

 

by: JoellerPosted on 2009-11-25 at 06:31:30ID: 25907357

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.

 

by: schwertnerPosted on 2009-11-25 at 06:47:48ID: 25907512

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.

 

by: JoellerPosted on 2009-11-25 at 08:30:27ID: 25908608

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:
:= . ( @ % ;

 

by: JoellerPosted on 2009-11-25 at 08:33:51ID: 25908650

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:

Select allOpen in new window

 

by: schwertnerPosted on 2009-11-26 at 02:25:01ID: 25914472

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:

Select allOpen in new window

 

by: JoellerPosted on 2009-11-30 at 05:57:36ID: 25933806

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

 

by: JoellerPosted on 2009-11-30 at 07:07:25ID: 25934302

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.

 

by: JoellerPosted on 2009-11-30 at 07:13:43ID: 25934358

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.

 

by: schwertnerPosted on 2009-11-30 at 07:58:31ID: 25934720

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

 

by: JoellerPosted on 2009-12-03 at 12:19:22ID: 25966134

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.

 

by: JoellerPosted on 2009-12-09 at 10:41:51ID: 26010976

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:

Select allOpen in new window

 

by: schwertnerPosted on 2009-12-14 at 01:25:25ID: 26043013

What is true!
You have to define IRESULT also

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   IRESULT     some_type(some_length);
....................................................

Also try to mention IRESULT without colon and with colon

:iResult);
 

 

by: JoellerPosted on 2009-12-14 at 07:31:58ID: 26045261

Any thing about enterprise manager console?

 

by: JoellerPosted on 2009-12-14 at 07:46:38ID: 26045400

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:

Select allOpen in new window

 

by: schwertnerPosted on 2009-12-14 at 08:14:27ID: 26045634

:iResult);
should be
iResult);


(please delete to colon!!!!!)

print is not legal PL/SQL command

Use
 DBMS_OUTPUT.PUT_LINE(iresult);

 

by: JoellerPosted on 2009-12-28 at 11:45:25ID: 26133517

Both of the above are actually directly contrary to information provided from elsewhere that worked and solved the issue. was specifically instructed to insert colon.  Many provided examples used the command "print".

Experts exchange is nagging me to terminate this conversation.  so I will award  100 points to schwertner for sticking with it for all these weeks.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...