Sql developer oracle error

Viewing PLSQL Compilation Errors in Oracle SQL Developer

A question that comes up fairly frequently revolves around how to see your errors when working with PL/SQL in SQL Developer.

Most folks are probably working in the worksheet – this is the default editor for your connection.

Let’s take a look at this sample program

If we were to create this procedure, we would probably expect some errors. So let’s run this in the Worksheet. I’m using Ctrl+Enter to execute this single statement.

I have warnings, oh no!

Ok, but how do I see the errors?

This is the worksheet. The commands run here will run very similar to how they would run in SQL*Plus. So knowing this, if we change-up the process a little bit, we can start to get better feedback from SQL Developer.

Add ‘show errors’ after the create or replace, and use F5 instead of Ctrl+Enter. This will run the entire script, and ask Oracle to show us any errors for the session.

A little better, but not as good as it gets

You might be wondering why the line number is off. Oracle reports back a problem on line #4. If you’ll notice our program starts on SQL Developer worksheet line #2, but Oracle database only receives the actual statement, so you can do the math here to figure out the actual problem lies on line #5.

Viewing Errors in the Explorer

The current errors for the objects can also be viewed in the database explorer. Navigate to the object and open the ‘Errors’ panel.
Viewing Errors in the Explorer

Working in the Procedure Editor

As the name implies, the Procedure Editor is for developing and debugging your PL/SQL code. The full power of the IDE is realized here. Ok, so how do you get started with a Procedure Editor instead of a Worksheet?

In the object explorer, right-click on ‘Procedures’ and select ‘New Procedure.’ This will open a new Procedure Editor with the default procedure template code. Now when you compile, you’ll get much better error display support.
Just in case you’ve never seen a PL/SQL Error, Here’s One

If you have a large program with several errors you can use the right gutter scroll bar area to scan for errors (denoted in red), then mouse-over to get the error message text. If you are looking at the feedback in the ‘Compiler’ panel below, you can double-click on an error message. This will move your cursor to the linenumber, curpos.

If you are working on a large script with multiple PL/SQL objects, I recommend using the Procedure Editor to perfect your procedure code, then copying that code back to your script and running that in the worksheet. Just don’t forget to add the ‘SHOW ERRORS’ line at the end.

Disclaimer: This post was written with Oracle SQL Developer v3.1.

I’m a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Источник

SQL-Developer error:ORA-12505. TNS:listner does not currently know of SID given in connect desc

Hello guys, i know this question has been posted many times in the past but my issue includes sql oracle developer only. i can use the database and all features through sql*plus even execute sql queries. but when it comes to sql developer it will always return this error. (error:ORA-12505.)

windows 8 -64 oracle 11g r2

Welcome to LSNRCTL, type «help» for information.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 — Production

Start Date 11-AUG-2014 13:08:08

Uptime 0 days 0 hr. 14 min. 55 sec

Trace Level off

Security ON: Local OS Authentication

Listener Parameter File C:apporacleproduct11.2.0dbhome_1networkadminlistener.ora

Listener Log File c:apporaclediagtnslsnrNeedo-pclistener1alertlog.xml

Listening Endpoints Summary.

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 3 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.

The command completed successfully

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 3 handler(s) for this service.

«DEDICATED» established:0 refused:0

«ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

«ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

Service «orcl» has 1 instance(s).

Instance «orcl», status READY, has 1 handler(s) for this service.

«DEDICATED» established:22 refused:0 state:ready

Service «orclXDB» has 1 instance(s).

Instance «orcl», status READY, has 1 handler(s) for this service.

«D000» established:0 refused:0 current:0 max:1022 state:ready

The command completed successfully

Best Answer

Try it with sid ORCL instead of XE.

If that doesn’t help, then choose SERVICE_NAME orcl instead of SID in sql developer.

Your listener seems to have an entry for that.

You can test it also in SQl*Plus. But what you did so far in SQl*plus was a «server connect» without providing the database.

Since in your case the client and the server is on the same machine, this works with sql*plus.

If you try a client coonnect by specifying the database too, then you would do the same as the Oracle SQL Developer is trying.

For testing purposes something like

sqlplus user/[email protected]:port/ORCL

also in the Sqlnet.ora file there needs to be an etry for local connect method EZCONNECT. I’m not sure if this is set during the default net installation.

You can try if there is a TNS-Alias name (configured in tnsnames.ora)

sqlplus user/[email protected]

If that works with sql*plus then you still need to tell the sql developer where the tnsnames.ora file is located on your computer.

The setting is in menu: extras/tools/database/enhanced

The tnsnames.ora file is usually in some folder like this:

C:Oracleproduct11.2.0client_1networkadmin

Источник

Oracle SQL Developer 101: PLSQL Compiler Warnings, Errors and the Right Gutter

Ever wonder what all the red, yellow, and purple marks in your procedure editor are supposed to tell you in the Oracle SQL Developer Procedure Editor? No idea what I’m talking about?

Ohh, pretty colors!

This is the editor trying to help direct you to where you might have problems, instead of you having to to scroll the text looking for the squiggles.

What squiggles, you ask?

See the yellow-dotted lines?

Mousing over the line shows you the Compiler Warning. You won’t see compiler warnings unless you turn them on in the preferences.

You can also configure the color and style of the PL/SQL Warning and Error squiggles and lines.

Change the type of line, color, etc.

But Back to the Right Gutter Space

Red – errors.
Yellow – warnings.
Purple – lines with active editor search results.

Mousing over the little block will give you a preview of the text. Clicking on it will take you to that text.

Hover to preview, click to go to

And just in case you haven’t seen the purple ones before, here’s what a search and it’s set of results look like:

See what ‘found’ text is before going there

I’m a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Источник

8 Error Handling and Diagnostics

An application program must anticipate runtime errors and attempt to recover from them. This chapter provides an in-depth discussion of error reporting and recovery. You learn how to handle warnings and errors using the ANSI status variables SQLCODE and SQLSTATE, or the Oracle SQLCA (SQL Communications Area) structure. You also learn how to use the WHENEVER statement and how to diagnose problems using the Oracle ORACA (Oracle Communications Area) structure.

The following topics are discussed:

Why Error Handling is Needed

A significant part of every application program must be devoted to error handling. The main benefit of error handling is that it enables your program to continue operating in the presence of errors. Errors arise from design faults, coding mistakes, hardware failures, invalid user input, and many other sources

You cannot anticipate all possible errors, but you can plan to handle certain kinds of errors meaningful to your program. For Pro*COBOL, error handling means detecting and recovering from SQL statement execution errors. You must trap errors because the precompiler will continue regardless of the errors encountered unless you halt processing.

You can also prepare to handle warnings such as «value truncated» and status changes such as «end of data.» It is especially important to check for error and warning conditions after every data manipulation statement because an INSERT, UPDATE, or DELETE statement might fail before processing all eligible rows in a table.

Error Handling Alternatives

Pro*COBOL supports two general methods of error handling:

The Oracle-specific method with SQLCA and optional ORACA.

The SQL standard method with SQLSTATE status variable.

The precompiler MODE option governs compliance with the SQL standard. When MODE=, you declare the SQLSTATE status variable as PIC X(5). Additionally, the ANSI SQL-89 SQLCODE status variable is still supported, but it is not recommended for new programs because it has been removed from the SQL standard. When MODE=, you must include the SQLCA through an EXEC SQL INCLUDE statement. It is possible to use both methods in one program but usually not necessary.

For detailed information on mixing methods see «Status Variable Combinations».

SQLCA

The SQLCA is a record-like, host-language data structure which includes Oracle warnings, error numbers and error text. Oracle updates the SQLCA after every executable SQL or PL/SQL statement. (SQLCA values are undefined after a declarative statement.) By checking return codes stored in the SQLCA, your program can determine the outcome of a SQL statement. This can be done in two ways:

Implicit checking with the WHENEVER statement

Explicit checking of SQLCA variables

When you use the WHENEVER statement to implicitly check the status of your SQL statements, Pro*COBOL automatically inserts error checking code after each executable statement. Alternatively, you can explicitly write your own code to test the value of the SQLCODE member of the SQLCA structure. Include SQLCA by using the embedded SQL INCLUDE statement:

ORACA

When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA, which contains cursor statistics, SQL statement text, certain option settings and system statistics. Include ORACA by using the embedded SQL INCLUDE statement:

The ORACA is optional and can be declared regardless of the MODE setting. For more information about the ORACA status variable, see «Using the Oracle Communications Area».

ANSI SQLSTATE Variable

When MODE=ANSI, you can declare the ANSI SQLSTATE variable inside the Declare Section for implicit or explicit error checking. If the option DECLARE_SECTION is set to NO, then you can also declare it outside of the Declare Section.

When MODE=ANSI, you can also declare the SQLCODE variable with a picture S9(9) COMP. While it can be used instead of or with the SQLSTATE variable, this is not recommended for new programs. You can also use the SQLCA with the SQLSTATE variable. When MODE=ANSI14, then SQLSTATE is not supported and you must declare either SQLCODE or include SQLCA. You cannot declare both SQLCODE and SQLCA for any setting of mode.

Declaring SQLSTATE

This section describes how to declare SQLSTATE. SQLSTATE must be declared as a five-character alphanumeric string as in the following example:

SQLSTATE Values

SQLSTATE status codes consist of a two-character class code followed by a three-character subclass code . Aside from class code 00 (successful completion), the class code denotes a category of exceptions. Aside from subclass code 000 (not applicable), the subclass code denotes a specific exception within that category. For example, the SQLSTATE value ‘22012’ consists of class code 22 (data exception) and subclass code 012 (division by zero).

Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in the SQL standard). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined sub-conditions. All other subclass codes are reserved for implementation-defined sub-conditions. Figure 8-1 shows the coding scheme:

Figure 8-1 SQLSTATE Coding Scheme

Description of «Figure 8-1 SQLSTATE Coding Scheme»

Table 8-1 shows the classes predefined by the SQL standard.

Table 8-1 Predefined Classes

dynamic SQL error

triggered action exception

feature not supported

feature not supported

invalid target type specification

invalid schema name list specification

invalid SQL-invoked procedure reference

invalid role specification

invalid transform group name specification

target table disagrees with cursor specification

attempt to assign to non-updatable column

attempt to assign to ordering column

prohibited statement encountered during trigger execution

integrity constraint violation

invalid cursor state

invalid transaction state

invalid SQL statement name

triggered data change violation

invalid authorization specification

direct SQL syntax error or access rule violation

dependent privilege descriptors still exist

invalid character set name

invalid transaction termination

invalid connection name

SQL routine exception

invalid collation name

invalid SQL statement identifier

invalid SQL descriptor name

invalid cursor name

invalid condition number

cursor sensitivity exception

dynamic SQL syntax error or access rule violation

external routine exception

external routine invocation exception

ambiguous cursor name

invalid catalog name

invalid schema name

syntax error or access rule violation

with check option violation

remote database access

The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access .

Table 8-4, «SQLSTATE Codes» shows how errors map to SQLSTATE status codes. In some cases, several errors map to the status code. In other cases, no error maps to the status code (so the last column is empty). Status codes in the range 60000..99999 are implementation-defined.

Using the SQL Communications Area

Oracle uses the SQL Communications Area (SQLCA) to store status information passed to your program at run time. The SQLCA is a record-like, COBOL data structure that is a updated after each executable SQL statement, so it always reflects the outcome of the most recent SQL operation. Its fields contain error, warning, and status information updated by Oracle whenever a SQL statement is executed. To determine that outcome, you can check variables in the SQLCA explicitly with your own COBOL code or implicitly with the WHENEVER statement.

When MODE=, the SQLCA is required; if the SQLCA is not declared, compile-time errors will occur. The SQLCA is optional when MODE=, but if you want to use the WHENEVER SQLWARNING statement, you must declare the SQLCA. The SQLCA must also be included when using multibyte NCHAR host variables.

When your application uses Oracle Net to access a combination of local and remote databases concurrently, all the databases write to one SQLCA. There is not a different SQLCA for each database. For more information, see «Concurrent Logons».

Contents of the SQLCA

The SQLCA contains runtime information about the execution of SQL statements, such as error codes, warning flags, event information, rows-processed count, and diagnostics.

Figure 8-2 shows all the variables in the SQLCA.

Figure 8-2 SQLCA Variable Declarations for Pro*COBOL

Description of «Figure 8-2 SQLCA Variable Declarations for Pro*COBOL»

Declaring the SQLCA

To declare the SQLCA, simply include it (using an EXEC SQL INCLUDE statement) in your Pro*COBOL source file outside the Declare Section as follows:

The SQLCA must be declared outside the Declare Section.

Do not declare SQLCODE if SQLCA is declared. Likewise, do not declare SQLCA if SQLCODE is declared. The status variable declared by the SQLCA structure is also called SQLCODE, so errors will occur if both error-reporting mechanisms are used.

When you precompile your program, the INCLUDE SQLCA statement is replaced by several variable declarations that allow Oracle to communicate with the program.

Key Components of Error Reporting

The key components of Pro*COBOL error reporting depend on several fields in the SQLCA.

Status Codes

Every executable SQL statement returns a status code in the SQLCA variable SQLCODE, which you can check implicitly with WHENEVER SQLERROR or explicitly with your own COBOL code.

Warning Flags

Warning flags are returned in the SQLCA variables SQLWARN0 through SQLWARN7, which you can check with WHENEVER SQLWARNING or with your own COBOL code. These warning flags are useful for detecting runtime conditions that are not considered errors.

Rows-Processed Count

The number of rows processed by the most recently executed SQL statement is returned in the SQLCA variable SQLERRD(3). For repeated FETCHes on an OPEN cursor, SQLERRD(3) keeps a running total of the number of rows fetched.

Parse Error Offset

Before executing a SQL statement, Oracle must parse it; that is, examine it to make sure it follows syntax rules and refers to valid database objects. If Oracle finds an error, an offset is stored in the SQLCA variable SQLERRD(5), which you can check explicitly. The offset specifies the character position in the SQL statement at which the parse error begins. The first character occupies position zero. For example, if the offset is 9, the parse error begins at the tenth character.

If your SQL statement does not cause a parse error, Oracle sets SQLERRD(5) to zero. Oracle also sets SQLERRD(5) to zero if a parse error begins at the first character (which occupies position zero). So, check SQLERRD(5) only if SQLCODE is negative, which means that an error has occurred.

Error Message Text

The error code and message for errors are available in the SQLCA variable SQLERRMC. For example, you might place the following statements in an error-handling routine:

At most, the first 70 characters of message text are stored. For messages longer than 70 characters, you must call the SQLGLM subroutine, which is discussed in «Getting the Full Text of Error Messages».

SQLCA Structure

This section describes the structure of the SQLCA, its fields, and the values they can store.

SQLCAID

This string field is initialized to «SQLCA» to identify the SQL Communications Area.

SQLCABC

This integer field holds the length, in bytes, of the SQLCA structure.

SQLCODE

This integer field holds the status code of the most recently executed SQL statement. The status code, which indicates the outcome of the SQL operation, can be any of the following numbers:

SQLERRM

This sub-record contains the following two fields:

Verify SQLCODE is negative before you reference SQLERRMC. If you reference SQLERRMC when SQLCODE is zero, you get the message text associated with a prior SQL statement.

This string field is reserved for future use.

SQLERRD

This table of binary integers has six elements. Descriptions of the fields in SQLERRD follow:

The rows-processed count is zeroed after an OPEN statement and incremented after a FETCH statement. For the EXECUTE, INSERT, UPDATE, DELETE, and SELECT INTO statements, the count reflects the number of rows processed successfully. The count does not include rows processed by an update or delete cascade. For example, if 20 rows are deleted because they meet WHERE-clause criteria, and 5 more rows are deleted because they now (after the primary delete) violate column constraints, the count is 20 not 25.

SQLERRD(4) This field is reserved for future use. SQLERRD(5) This field holds an offset that specifies the character position at which a parse error begins in the most recently executed SQL statement. The first character occupies position zero. SQLERRD(6) This field is reserved for future use.

SQLWARN

This table of single characters has eight elements. They are used as warning flags. Oracle sets a flag by assigning it a ‘W’ (for warning) character value. The flags warn of exceptional conditions.

For example, a warning flag is set when Oracle assigns a truncated column value to an output host character variable.

Figure 8-2, «SQLCA Variable Declarations for Pro*COBOL» illustrates SQLWARN implementation in Pro*COBOL as a group item with elementary PIC X items named SQLWARN0 through SQLWARN7.

Descriptions of the fields in SQLWARN follow:

To find out if a column value was truncated and by how much, check the indicator variable associated with the output host variable. The (positive) integer returned by an indicator variable is the original length of the column value. You can increase the length of the host variable accordingly.

SQLWARN2 This flag is set if one or more NULLs were ignored in the evaluation of a SQL group function such as AVG, COUNT, or MAX. This behavior is expected because, except for COUNT(*), all group functions ignore NULLs. If necessary, you can use the SQL function NVL to temporarily assign values (zeros, for example) to the NULL column entries. SQLWARN3 This flag is set if the number of columns in a query select list does not equal the number of host variables in the INTO clause of the SELECT or FETCH statement. The number of items returned is the lesser of the two. SQLWARN4 This flag is no longer in use. SQLWARN5 This flag is set when an EXEC SQL CREATE

statement fails because of a PL/SQL compilation error. SQLWARN6 This flag is no longer in use. SQLWARN7 This flag is no longer in use.

SQLEXT

This string field is reserved for future use.

PL/SQL Considerations

When your Pro*COBOL program executes an embedded PL/SQL block, not all fields in the SQLCA are set. For example, if the block fetches several rows, the rows-processed count, SQLERRD(3), is set to 1, not the actual number of rows fetched. So, you should rely only on the SQLCODE and SQLERRM fields in the SQLCA after executing a PL/SQL block.

Getting the Full Text of Error Messages

Regardless of the setting of MODE, you can use SQLGLM to get the full text of error messages if you have explicitly declared SQLCODE and not included SQLCA. The SQLCA can accommodate error messages up to 70 characters long. To get the full text of longer (or nested) error messages, you need the SQLGLM subroutine.

If connected to a database, you can call SQLGLM using the syntax

where the parameters are:

All parameters must be passed by reference. This is usually the default parameter passing convention; you need not take special action.

The maximum length of an error message is 512 characters including the error code, nested messages, and message inserts such as table and column names. The maximum length of an error message returned by SQLGLM depends on the value specified for MAX-SIZE.

The following example uses SQLGLM to get an error message of up to 200 characters in length:

In the example, SQLGLM is called only when a SQL error has occurred. Always make sure SQLCODE is negative before calling SQLGLM. If you call SQLGLM when SQLCODE is zero, you get the message text associated with a prior SQL statement.

If your application calls SQLGLM to get message text, the message length must be passed. Do not use the SQLCA variable SQLERRML. SQLERRML is a PIC S9(4) COMP integer while SQLGLM and SQLIEM expect a PIC S9(9) COMP integer. Instead, use another variable declared as PIC S9(9) COMP.

DSNTIAR

DB2 provides an assembler routine called DSNTIAR to obtain a form of the SQLCA that can be displayed. For users migrating to Oracle from DB2, Pro*COBOL provides DSNTIAR. The DSNTIAR implementation is a wrapper around SQLGLM. The DSNTIAR interface is as follows

where MESSAGE is the output message area, in VARCHAR form of size greater than or equal to 240, and LRECL is a full word containing the length of the output messages, between 72 and 240. The first half-word of the MESSAGE argument contains the length of the remaining area. The possible error codes returned by DSNTIAR are listed in the following table.

Table 8-2 DSNTIAR Error Codes and Their Meanings

More data was available than could fit into the provided message

The logical record length (LRECL) was not between 72 and 240

The message area was not large enough (greater than 240)

WHENEVER Directive

By default, Pro*COBOL ignores error and warning conditions and continues processing, if possible. To do automatic condition checking and error handling, you need the WHENEVER statement.

With the WHENEVER statement you can specify actions to be taken when Oracle detects an error, warning condition, or «not found» condition. These actions include continuing with the next statement, PERFORMing a paragraph, branching to a paragraph, or stopping.

Conditions

You can have Oracle automatically check the SQLCA for any of the following conditions.

SQLWARNING

SQLWARN(0) is set because Oracle returned a warning (one of the warning flags, SQLWARN(1) through SQLWARN(7), is also set) or SQLCODE has a positive value other than +1403. For example, SQLWARN(1) is set when Oracle assigns a truncated column value to an output host variable.

Declaring the SQLCA is optional when MODE=. To use WHENEVER SQLWARNING, however, you must declare the SQLCA.

You have to have included SQLCA for this to work.

SQLERROR

SQLCODE has a negative value if Oracle returns an error.

NOT FOUND or NOTFOUND

SQLCODE has a value of +1403 (or +100 when MODE= or when END_OF_FETCH=100) when the end of fetch has been reached. This can happen when all the rows that meet the search criteria have been fetched or no rows meet that criteria.

You may use the END_OF_FETCH option to override the value use by the MODE macro option.

For more details, see «END_OF_FETCH».

Actions

You can use the WHENEVER statement to specify the following actions.

CONTINUE

Your program continues to run with the next statement if possible. This is the default action, equivalent to not using the WHENEVER statement. You can use it to «turn off» condition checking.

DO CALL

Your program calls a nested subprogram. When the end of the subprogram is reached, control transfers to the statement that follows the failed SQL statement.

DO PERFORM

Your program transfers control to a COBOL section or paragraph. When the end of the section is reached, control transfers to the statement that follows the failed SQL statement.

GOTO or GO TO

Your program branches to the specified paragraph or section.

Your program stops running and uncommitted work is rolled back.

Be careful. The STOP action displays no messages before logging off.

Though in the generated code EXEC SQL WHENEVER SQLERROR STOP is converted to IF SQLCODE IN SQLCA IS EQUAL TO 1403 THEN STOP RUN END-IF, Oracle server will take care of rolling back uncommitted data.

Coding the WHENEVER Statement

Code the WHENEVER statement using the following syntax:

DO PERFORM

When using the WHENEVER . DO PERFORM statement, the usual rules for PERFORMing a paragraph or section apply. However, you cannot use the THRU, TIMES, UNTIL, or VARYING clauses.

For example, the following WHENEVER . DO statement is invalid :

In the following example, WHENEVER SQLERROR DO PERFORM statements are used to handle specific errors:

Notice how the paragraphs check variables in the SQLCA to determine a course of action.

DO CALL

This clause calls an action subprogram. Here is the syntax of this clause:

The following restrictions or rules apply:

You cannot use the RETURNING, ON_EXCEPTION, or OVER_FLOW phrases in the USING clause.

You may have to enter the subprogram name followed by the keyword COMMON in the PROGRAM-ID statement of your COBOL source code.

You must use a WHENEVER CONTINUE statement in the action subprogram.

The action subprogram name may have to be in double quotes in the DO CALL clause of the WHENEVER directive.

Here is an example of a program that can call the error subprogram SQL-ERROR from inside the subprogram LOGON, or inside the MAIN program, without having to repeat code in two places, as when using the DO PERFORM clause:

Scope

Because WHENEVER is a declarative statement, its scope is positional, not logical. It tests all executable SQL statements that follow it in the source file, not in the flow of program logic. So, code the WHENEVER statement before the first executable SQL statement you want to test.

A WHENEVER statement stays in effect until superseded by another WHENEVER statement checking for the same condition.

Suggestion : You can place WHENEVER statements at the beginning of each program unit that contains SQL statements. That way, SQL statements in one program unit will not reference WHENEVER actions in another program unit, causing errors at compile or run time.

Careless Usage: Examples

Careless use of the WHENEVER statement can cause problems. For example, the following code enters an infinite loop if the DELETE statement sets the NOT FOUND condition, because no rows meet the search condition:

In the next example, the NOT FOUND condition is properly handled by resetting the GOTO target:

Getting the Text of SQL Statements

In many Pro*COBOL applications, it is convenient to know the text of the statement being processed, its length, and the SQL command (such as INSERT or SELECT) that it contains. This is especially true for applications that use dynamic SQL.

The routine SQLGLS, which is part of the SQLLIB runtime library, returns the following information:

The text of the most recently parsed SQL statement

The length of the statement

A function code

You can call SQLGLS after issuing a static SQL statement. With dynamic SQL Method 1, you can call SQLGLS after the SQL statement is executed. With dynamic SQL Method 2, 3, or 4, you can call SQLGLS after the statement is prepared.

To call SQLGLS, you use the following syntax:

Источник

Adblock
detector

Hello guys, i know this question has been posted many times in the past but my issue includes sql oracle developer only. i can use the database and all features through sql*plus even execute sql queries. but when it comes to sql developer it will always return this error. (error:ORA-12505.)

windows 8 -64 oracle 11g r2

—————————————————————————————————————————————

Welcome to LSNRCTL, type «help» for information.

LSNRCTL> status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

————————

Alias                     LISTENER1

Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 — Production

Start Date                11-AUG-2014 13:08:08

Uptime                    0 days 0 hr. 14 min. 55 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:apporacleproduct11.2.0dbhome_1networkadminlistener.ora

Listener Log File         c:apporaclediagtnslsnrNeedo-pclistener1alertlog.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Needo-pc)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))

Services Summary…

Service «CLRExtProc» has 1 instance(s).

  Instance «CLRExtProc», status UNKNOWN, has 3 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…

The command completed successfully

——————————————————————————————————————

LSNRCTL> services

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary…

Service «CLRExtProc» has 1 instance(s).

  Instance «CLRExtProc», status UNKNOWN, has 3 handler(s) for this service…

    Handler(s):

      «DEDICATED» established:0 refused:0

         LOCAL SERVER

      «ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

         CLRExtProc

         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeNTN_122C_C6D0FCF3.ORA))

      «ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

         CLRExtProc

         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeNTN_122C_C6D0FCF3.ORA))

Service «orcl» has 1 instance(s).

  Instance «orcl», status READY, has 1 handler(s) for this service…

    Handler(s):

      «DEDICATED» established:22 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: NEEDO-PC, pid: 3172>

         (ADDRESS=(PROTOCOL=tcp)(HOST=Needo-pc)(PORT=52654))

The command completed successfully

——————————————————————————————

thanks!

A question that comes up fairly frequently revolves around how to see your errors when working with PL/SQL in SQL Developer.

Most folks are probably working in the worksheet – this is the default editor for your connection.

Let’s take a look at this sample program

CREATE OR REPLACE PROCEDURE do_nothing IS
BEGIN
 dbms_output.put(sysdate);
 this should probably error OUT, RIGHT?
 NULL;
END;
/

If we were to create this procedure, we would probably expect some errors. So let’s run this in the Worksheet. I’m using Ctrl+Enter to execute this single statement.

Ok, but how do I see the errors?

This is the worksheet. The commands run here will run very similar to how they would run in SQL*Plus. So knowing this, if we change-up the process a little bit, we can start to get better feedback from SQL Developer.

Add ‘show errors’ after the create or replace, and use F5 instead of Ctrl+Enter. This will run the entire script, and ask Oracle to show us any errors for the session.

You might be wondering why the line number is off. Oracle reports back a problem on line #4. If you’ll notice our program starts on SQL Developer worksheet line #2, but Oracle database only receives the actual statement, so you can do the math here to figure out the actual problem lies on line #5.

Viewing Errors in the Explorer

The current errors for the objects can also be viewed in the database explorer. Navigate to the object and open the ‘Errors’ panel.

Working in the Procedure Editor

As the name implies, the Procedure Editor is for developing and debugging your PL/SQL code. The full power of the IDE is realized here. Ok, so how do you get started with a Procedure Editor instead of a Worksheet?

In the object explorer, right-click on ‘Procedures’ and select ‘New Procedure.’ This will open a new Procedure Editor with the default procedure template code. Now when you compile, you’ll get much better error display support.

If you have a large program with several errors you can use the right gutter scroll bar area to scan for errors (denoted in red), then mouse-over to get the error message text. If you are looking at the feedback in the ‘Compiler’ panel below, you can double-click on an error message. This will move your cursor to the linenumber, curpos.

If you are working on a large script with multiple PL/SQL objects, I recommend using the Procedure Editor to perfect your procedure code, then copying that code back to your script and running that in the worksheet. Just don’t forget to add the ‘SHOW ERRORS’ line at the end.

Disclaimer: This post was written with Oracle SQL Developer v3.1.