Ora 24344 success with compilation error no errors

ORA-24344

Error ORA-24344 alerts the user that there is a problem of some kind within the code. The vague, non-specific nature of the message makes it tricky to immediately determine what the exact problem may be. However, by expanding the error and locating the mistake, the error itself is likely an easy fix.

The Problem

While not common, ORA-24344 may occur in a number of situations. For example, a line break char in the trigger code may not be well suited to be used in certain platforms, or a trigger defined in a table may be being used as a synonym for another user table. Still, the error may be occurring if you try to execute code that does not compile or if the block of code is very long. Users should pay special attention to longer lines and blocks of code as, of course, there is a greater chance of seeing ORA-24344 as well any other errors when the code becomes long.

When you open the oerr utility, you will see a similar message to the following regarding ORA-24344:

ORA-24344: success with compilation error

*Cause: A SQL/PLSQL compilation error occurred.

*Action: Return OCI_SUCCESS_WITH_INFO along with error code

The Solution

First, skim through your code to look for any obvious mistakes such as spelling and syntax errors. Catching such mistakes is made easier by using an integrated development environment (IDE) that includes a good source code editor. If you do not see any spelling or syntax errors, run the following query to show errors:

show errors procedure <procedure_name>;

You may also run the following query to see the compilation error:

select * from user_errors;

This query should show where the mistake is located and you should be able to correct the mistake. ORA-24344 simply points to a mistake in the code; it is not specific to any one error. However, it is likely that the problem may have to do with properly defining variables and dependencies among variables.

In the following example, error ORA-24344 is thrown.

create or replace force view test_view

as select a,b from dual5;

declare
procedure run(p_sql varchar5) as
begin
execute immediate p_sql;
end;
begin
run(‘rename “TEST_VIEW” to TEST_VIEW’);
run(‘alter view  TEST_VIEW compile’);
end;

ORA-24344: success with compilation error

*Cause: A SQL/PLSQL compilation error occurred.

*Action: Return OCI_SUCCESS_WITH_INFO along with error code

The TEST_VIEW was invalid because DUAL5 does not exist. The problem lies within the following 2 lines of code:

run(‘rename “TEST_VIEW” to TEST_VIEW’);

run(‘alter view  TEST_VIEW compile’);

The lines should read:

run(‘rename “TEST_VIEW” to TEST_VIEW5’);

run(‘alter view  TEST_VIEW5 compile’);

Moving Forward

Prior to running the “show errors procedure <procedure_name>;” or “select * from user errors” to locate any error, it is always good practice to check dependencies to verify that they exist. In addition, you should check that triggers, constraints, indices and columns for tables have been properly redefined.

If you continue to face problems with ORA-24344, you may consider contacting your database administrator for assistance if you yourself are not the DBA. Another viable option may be to contact an Oracle professional to resolve the issue. Remember to always check the consultant’s credentials and certification to ensure your Oracle needs are properly met.

Datamasking Scripts Reports ORA-24344: Success With Compilation Error (Doc ID 1260245.1)

Last updated on AUGUST 17, 2022

Applies to:

Symptoms

Running a Data Masking script in Grid Control 11G may fail with the error ORA-24344: «success with compilation error» when a package or another object needs to be recompiled and the dependencies can only be resolved using object privileges.

Changes

You are trying to mask a table that has references to objects in other schemas or is being referenced by objects in other schemas such as views or stored pl/sql objects.

A typical example of the sort of errors you may get are:

When you investigate the issue and try the failing statement manually:

SQL> ALTER PACKAGE » «.» » COMPILE BODY;

Warning: Package Body altered with compilation errors.

SQL> show err
Errors for PACKAGE BODY » «.» «:

In the reported case the second package . depended on a view that was also invalid because the owner of the view had no privileges on the base tables (with grant option).

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Error ORA-24344/ORA-06512 on PL/SQL Trigger creation

got a strange problem here.

The following script execute perfectly
CREATE OR REPLACE TRIGGER VersionInsertTrigger
BEFORE INSERT ON Version
FOR EACH ROW
BEGIN
SELECT VersionSequence.nextval INTO :new.Idx FROM dual;
SELECT UID INTO :new.UserIdent FROM dual;
SELECT UPPER(:new.Entity) INTO :new.Entity FROM dual;
END;

But when I try to do it with help procedure I am getting a wiered error message
Error ORA-24344/ORA-06512 on PL/SQL Trigger creation
————
BEGIN
EXECUTE IMMEDIATE ‘
CREATE OR REPLACE TRIGGER VersionInsertTrigger
BEFORE INSERT ON Version
FOR EACH ROW
BEGIN
SELECT VersionSequence.nextval INTO :new.Idx FROM dual;
SELECT UID INTO :new.UserIdent FROM dual;
SELECT UPPER(:new.Entity) INTO :new.Entity FROM dual;
END;’;
END;

After this error — when I re compile it is working fine.

Can someone help me how to get rid of the error.

Answers

The following script execute perfectly
CREATE OR REPLACE TRIGGER VersionInsertTrigger
BEFORE INSERT ON Version
FOR EACH ROW
BEGIN
SELECT VersionSequence.nextval INTO :new.Idx FROM dual;
SELECT UID INTO :new.UserIdent FROM dual;
SELECT UPPER(:new.Entity) INTO :new.Entity FROM dual;
END;

But when I try to do it with help procedure I am getting a wiered error message
Error ORA-24344/ORA-06512 on PL/SQL Trigger creation
————

you said the script execute perfectly but when you placed it on a procedure it throws the error. post also the code for the procedure that you mentioned has an error during compile.

or is it when you attempt to insert a row on version table that you get the error?

I am sharing the DDL’s

Create a test table
CREATE TABLE TEST1
( «COL_1» VARCHAR2(10 BYTE),
«COL_2» VARCHAR2(10 BYTE)
);

Create a trigger using dynamic sql as below
BEGIN
EXECUTE IMMEDIATE ‘CREATE OR REPLACE TRIGGER TEST1TRIGGER
BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
update test1 set col_2=NULL;
END;’;
END;

This works fine —-

Create a another temp table
CREATE TABLE «RECOVERY_OBJECT_DDL»
( «OBJECT_NAME» VARCHAR2(30 BYTE),
«OBJECT_DDL» CLOB NOT NULL ENABLE);

insert into RECOVERY_OBJECT_DDL values (‘TEST1TRIGGER’,’CREATE OR REPLACE TRIGGER TEST1TRIGGER
BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
update test1 set col_2=NULL;
END;’)
commit;

Create a procedure as shown below

create or replace procedure recovery_proc
is
cursor c_recovery_ddl is
select object_ddl from recovery_object_ddl;
BEGIN
FOR rec in c_recovery_ddl
LOOP
IF rec.object_name in (‘TEST1TRIGGER’) THEN
—dbms_output.put_line(REC.object_ddl);
EXECUTE IMMEDIATE TO_CHAR(REC.OBJECT_DDL);
END IF;
END LOOP;
end;

When I try to run the procedure

BEGIN
recovery_proc();
END;

I am getting error as
ORA-24344: success with compilation error
ora-06512
24244.00000- «success with compilation error»
Caues: A sql/plsql compilation error occured.
Action: Return OCI_SUCCESS_WITH_INFO along with the error code
Vendor code 24344Error at Line:1

After running the above procedure. I can see that Trigger is created but it is invalid
When I just try to compile the trigger with out doing any modification it gets compiled and status is changed as Valid.

Can you tell me why it is failing in the first step ?

Источник

«ORA-24344: success with compilation error» in beginDDL

I’m facing a really strange problem. When i try to use dbms_wm.beginDDL in one of my versioned tables, i get the error:

ORA-24344: success with compilation error
ORA-06512: «WMSYS.LT», line 12178
ORA-06512: line 2

I just get the error if i run the procedure from SQLPlus.
Using PLSQL Developer, for example, I get a normal execution but the LTS table it’s not created and the state of the table stays ‘VERSIONED’.

I used this procedure other times in the past and there were no problems.
Trying to identify the cause of the error, i’ve tried:

(1) — run dbms_wm.beginDDL for all the other tables — No Problem
(2) — verifiy if there were invalid objects — All valid
(3) — verify the error tables for more details (user_, dba_, user_wm_vt_) — No entries
(4) — generate a trace file, to search for abnormal executions — Apparently nothing really strange (but obviously I don’t know what was supposed to happen)

Any possible reasons for this to happen?
Any ideas?
Some help would be welcome.

Answers

. . . .Have you checked dependencies (for your table and all associated PL/SQL) and verified that they all exist?

. . . .Has the offending table been redefined (columns, indexes, triggers, constraints)?

I just found the problem.
I have a trigger defined on the table that uses a synonym for another user table.
That synomym was dropped, so the trigger was invalid.
I didn’t notice the problem because the trigger was disabled, so the WM$ procedure wasn’t being generated, and there were no «visible errors».

Recreating the synomyn, the problem was solved.
Thanks for your help!

Источник

success with Compilation Error with trigger Help needed in Apex.

CREATE OR REPLACE EDITIONABLE TRIGGER «BI_ITEM»

before insert on «ITEM»

if :NEW.»ITEM_ID» is null then

select «ITEM_SEQ».nextval into :NEW.»ITEM_ID» from sys.dual;

Answers

what kind of compilation error you getting?

I dunno about you, but I struggle to fix errors I can’t see. It would be helpful if you told us what the error was.

You really don’t need to double quote all the object names. That’s only required if the object was created with double quotes and contains non-uppercase names in the first place. Without double quotes, all objects are created in uppercase in the database anyway and any reference to objects is considered in uppercase, so it usually just works without you having to concern yourself.

Unless you’re using 10g database or below, you don’t need to obtain the sequence value by selecting from dual, and you certainly don’t need to refer to dual as sys.dual. There shouldn’t be any other dual object, and if you know you’re inserting and you know you want the item_id to be the primary key and assigned from the sequence, so some numpty can’t assign a value specifically, then you don’t want to test for it being null either.

So something like:

CREATE OR REPLACE EDITIONABLE TRIGGER BI_ITEM

before insert on ITEM

Would often do the job.

-A- Note that if you are using Oracle >= 12c you can use a new feature that does what you want: when you define the table, specify for example

item_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY

(look at the documentation of «CREATE TABLE» for more details / explanations)

-B- Welcome to the forums! Note that you can easily modify your display name to somethign more human-firendly. Please have a look at (it takes only a couple of minutes)

This is the error that is shown for all tables I have three in total. Market ,Item and Offer.

ORA-24344: success with compilation error

ORA-06512: at «SYS.WWV_DBMS_SQL_APEX_200100», line 581

ORA-06512: at «SYS.DBMS_SYS_SQL», line 1658

ORA-06512: at «SYS.WWV_DBMS_SQL_APEX_200100», line 567

ORA-06512: at «APEX_200100.WWV_FLOW_DYNAMIC_EXEC», line 2127

3. insert on «MARKET1»

4. for each row

5. begin

6. CREATE OR REPLACE EDITIONABLE TRIGGER «BI_MARKET1»

7. before insert on «MARKET1»

Источник

Ora 24344 success with compilation error

Cause: An undefined mode value was specified.

Action: Check that the correct mode is selected and that an allowed value for that mode is specified.

For more information about mode values, see the index entries on «mode, of a parameter in C,» «mode, of a parameter in COBOL,» «mode, of a parameter in FORTRAN» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24301: null host specified in thread-safe logon

Cause: An HDA was not specified in the logon call while running in a thread safe environment.

Action: Make sure that HDA is not NULL when calling the logon routine.

For more information about host data areas(HDA) and defining the OCI data structures, see the index entries on «host data area» and on «data structures, defining» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24302: host connection in use by another thread

Cause: An attempt was made to use the host connection while it was in use by another thread.

Action: Wait for another thread to finish before using this connection.

For more information about connecting to the Oracle Server, see the index entry on «connections, multiple» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24303: call not supported in non-deferred linkage

Cause: One of the calls that was supported in deferred mode linkage exclusively was invoked when the client was linked non-deferred.

Action: Use this call in deferred mode of linkage.

For more information about deferred mode linking, see the index entry on «deferred, mode linking» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24304: datatype not allowed for this call

Cause: Data of this datatype cannot be sent or fetched in pieces.

Action: Use other bind or define calls for this datatype.

For more information about bind and define calls, see the index entry on «developing an OCI program» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24305: bad bind or define context

Cause: The call was executed on a cursor for which this was invalid.

Action: Verify that this call is valid for this cursor. For example, GET PIECE INFORMATION and SET PIECE INFORMATION are valid on a cursor if appropriate binds and defines have been done on this cursor.

For more information about bind and define calls, see the index entries on «bind operation» and on «define operation» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24306: bad buffer for piece

Cause: A zero length buffer or a null buffer pointer was provided.

Action: Verify that the buffer pointing to this piece or its length is non-zero. The buffer pointer for the next piece or its length can be zero if it is the last piece to be inserted and there are no more data for the column.

For more information about fetching operations, see the index entry on «rows, fetching» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24307: invalid length for piece

Cause: The length of the piece exceeds the maximum possible size of the data.

Action: Verify that the length of this piece and the cumulative length of all the previous pieces are not more than the PROGVL parameter specified in the OBINDPS call.

For more information about fetching operations, see the index entry on «rows, fetching» in the Programmer’s Guide to the Oracle Call Interface, Volume II: OCI Reference .

ORA-24308: illegal named data type variable

Cause: A named data type define was issued for a non-existent variable.

Action: Verify that a define call is already issued for this variable.

ORA-24309: already connected to this server

Cause: The specified server handle is already attached to a server.

Action: Disconnect from the server and then retry the ociatch() call.

ORA-24310: length specified for null connect string

Cause: The connect string is NULL, but a length was specified for it.

Action: Set length to zero if connect string is NULL.

ORA-24311: memory initialization failed

Cause: Cannot initialize user memory.

Action: Contact customer support.

ORA-24312: illegal parameters specified for allocating user memory

Cause: An illegal size or NULL pointer was specified for user memory.

Action: Specify a legal size and a valid pointer for user memory.

ORA-24313: user is already authenticated

Cause: A user has already been authenticated on this service handle.

Action: Terminate the service context before using it for another user.

ORA-24314: need explicit attach before initialize service context

Cause: An ociatch() was not done before an ociisc() call.

Action: Issue ociatch() before calling ociatch().

ORA-24315: illegal attribute type

Cause: An illegal attribute type was specified for the handle.

Action: Consult user manual to specify an attribute valid for this handle.

ORA-24316: illegal handle type

Cause: An illegal handle type was specified.

Action: Consult user manual to specify a valid handle type.

ORA-24317: re-defining different position

Cause: A define was done again with a different position.

Action: Specify the same position as before on a re-define.

ORA-24318: named data type calls not allowed for scalar data types

Cause: A named data type bind or define call was issued for a scalar type.

Action: Verify that the data-type for this variable is a named data type.

ORA-24319: unable to allocate memory for Diagnostic Record

Cause: Process was unable to allocate memory.

Action: Terminate other processes in order to reclaim needed memory.

ORA-24320: unable to initialize a mutex

Cause: An attempt to initialize a mutex failed.

Action: Contact customer support.

ORA-24321: inconsistent parameters passed to ocipi

Cause: One of the three memory function pointers is null or non-null.

Action: Verify that either all the memory functions are null or non-null.

ORA-24322: unable to delete an initialized mutex

Cause: An attempt to delete an initialized mutex failed.

Action: Contact customer support.

ORA-24323: value not allowed

Cause: A null value or a bogus value was passed in for a mandatory parameter.

Action: Verify that all mandatory parameters are properly initialized.

ORA-24324: service handle not initialized

Cause: An attempt was made to use an improper service context handle.

Action: Verify that the service context handle has all the parameters initialized and set for this call.

ORA-24325: this OCI operation is not currently allowed

Cause: An attempt was made to use an improper context handle.

Action: Verify that the context handle is not a part of a service context that has been converted to a logon data area for other OCI calls. The logon data area must be converted back to a service context before its components can be used.

ORA-24326: handle passed in is already initialized

Cause: An attempt was made to pass an initialized handle.

Action: Verify that the parameter passed in to retrieve a handle does not already point to a handle.

ORA-24327: need explicit attach before authenticating a user

Cause: A server context must be initialized before creating a session.

Action: Create and initialize a server handle.

ORA-24328: illegal attribute value

Cause: The attribute value passed in is illegal.

Action: Consult the users manual and specify a legal attribute value for the handle.

ORA-24329: invalid character set identifier

Cause: The character set identifier specified is invalid.

Action: Specify a valid character set identifier in the OCI call.

ORA-24330: internal OCI error

Cause: An internal OCI error has occurred.

Action: Please contact Oracle customer support.

ORA-24331: user buffer too small

Cause: The user buffer to contain the output data is too small.

Action: Specify a larger buffer.

ORA-24332: invalid object type

Cause: An invalid object type is requested for the describe call.

Action: Specify a valid object type to the describe call.

ORA-24333: zero iteration count

Cause: An iteration count of zero was specified for a non-select statement.

Action: Specify the number of times this statement must be executed.

ORA-24334: no descriptor for this position

Cause: The application is trying to get a descriptor from a handle for an illegal position.

Action: Check the position number.

ORA-24335: cannot support more than 1000 columns

Cause: The number of columns exceeds the maximum number supported.

Action: Specify a number of columns less than 1000.

ORA-24336: invalid result set descriptor

Cause: The result set descriptor should have valid data fetched into it before it can be converted to a statement handle

Action: Fetch valid data into the descriptor before attempting to convert it into a statement handle.

ORA-24337: statement handle not prepared

Cause: A statement cannot be executed before making preparing a request.

Action: Prepare a statement before attempting to execute it.

ORA-24338: statement handle not executed

Cause: A fetch was attempted before executing a statement handle.

Action: Execute a statement and then fetch the data.

ORA-24340: cannot support more than 255 columns

Cause: The number of columns exceeds maximum supported by the server.

Action: Limit your operation to 255 columns.

ORA-24341: bad mode specified

Cause: OCI_ENV_NO_MUTEX mode was specified for a non-threaded client.

Action: OCI_ENV_NO_MUTEX may be specified when OCI_THREADED had been specified at process initialization.

ORA-24342: unable to destroy a mutex

Cause: An attempt to destroy a mutex failed.

Action: Contact Oracle Worldwide Support.

ORA-24343: user defined callback error

Cause: The only valid return value for a user defined callback function is OCI_CONTINUE. Any other value will cause this error.

Action: Please insure that OCI_CONTINUE is returned from the user defined callback function.

ORA-24344: success with compilation error

Cause: A SQL/PLSQL compilation error occurred.

Action: Return OCI_SUCCESS_WITH_INFO along with the error code.

ORA-24345: a truncation or NULL fetch error occurred

Cause: A truncation or a null fetch error occured.

Action: Please ensure that the buffer size is long enough to store the returned data. Then retry the operation.

ORA-24346: cannot execute without binding variables

Cause: None of the bind variables in the SQL statement are bound.

Action: Please bind all the variables before the execute is done.

ORA-24350: this OCI call is not allowed

Cause: This OCI callback is not permitted from trusted 3GL callouts.

Action: Do not use this call.

ORA-24351: invalid date passed into OCI call

Cause: An invalid date was passed into one of the OCI calls.

Action: Check your date bind values and correct them.

ORA-24352: invalid COBOL display type passed into OCI call

Cause: An invalid COBOL display type was passed into one of the OCI calls.

Action: Check your COBOL display type bind values and correct them.

ORA-24353: user buffer too small to accommodate COBOL display type

Cause: User supplied buffer for a COBOL display type was too small to accommodate fetched number.

Action: Increase the allocation for COBOL display type buffer.

ORA-24354: number fetched too large to fit in COBOL display type buffer.

Cause: The number fetched was beyond the range that can be displayed.

Action: Please check the number in the database.

ORA-24355: attempt to store a negative number in an Unsigned Display type.

Cause: An attempt was made to convert a negative number into an unsigned display type.

Action: Please check the number in the database or change the defined datatype.

ORA-24356: internal error while converting from to COBOL display type.

Cause: An internal error was encountered during conversion to COBOL display type.

Action: Contact customer support.

ORA-24357: internal error while converting from to OCIDate.

Cause: An internal error was encountered during conversion to OCIDate type.

Action: Contact customer support.

ORA-24358: OCIBindObject not invoked for a Object type or Reference

Cause: OCIBindObject was not invoked resulting in an incomplete bind specification for a Object Type or Reference.

Action: Please invoke the OCIBindObject call for all Object Types and References.

ORA-24359: OCIDefineObject not invoked for a Object type or Reference

Cause: OCIDefineObject was not invoked resulting in an incomplete bind specification for a Object Type or Reference.

Action: Please invoke the OCIDefineObject call for all Object Types and References.

ORA-24360: Type Descriptor Object not specified for Object Bind/Define

Cause: Type Descriptor Object is a mandatory parameter for Object Types Binds and Defines.

Action: Please invoke the OCIBindObject() or OCIDefineObject() call with a valid Type Descriptor Object.

ORA-24361: basic bind call not invoked before invoking advanced bind call

Cause: One of the basic bind calls was not invoked on this bind handle before performing an advanced bind call.

Action: Please invoke the advanced bind call on this bind handle only after performing a basic bind call.

ORA-24362: improper use of the character count flag

Cause: When the character count flag is set, then the maximum size of the buffer in the server should be specified as a non-zero value.

Action: Please use a non-zero value for the mamimum size of the buffer in the server.

ORA-24363: measurements in characters illegal here

Cause: Measurements in characters instead of bytes are illegal if either the server’s or client’s character set is varying width.

Action: If either the client’s or server’s character set is varying width then do not use the OCI_ATTR_CHAR_COUNT attribute for the bind handle. Use OCI_ATTR_MAXDATA_SIZE instead.

ORA-24364: internal error while padding blanks

Cause: An internal error has occurred while attempting to blank pad string data. This error should not occur normally.

Action: Contact customer support.

ORA-24365: error in character conversion

Cause: This usually occurs during conversion of a multibyte character data when the source data is abnormally terminated in the middle of a multibyte character.

Action: Make sure that all multibyte character data is properly terminated.

ORA-24366: migratable user handle is set in service handle

Cause: This occurs during user authentication: a migratable user handle has been set in the service handle.

Action: Service handle must not be set with migratable user handle when it is used to authenticate another user.

ORA-24367: user handle has not been set in service handle

Cause: This occurs during authentication of a migratable user. the service handle has not been set with non-migratable user handle.

Action: Service handle must be set with non-migratable user handle when it is used to authenticate a migratable user.

ORA-24368: OCI mutex counter non-zero when freeing a handle

Cause: This is an internal OCI error.

Action: Contact customer support.

ORA-24369: required callbacks not registered for one or more bind handles»

Cause: No callbacks have been registered for one or more of the bind handles which are part of the RETURNING clause.

Action: The bind handles which are to receive data in a DML statememt with a RETURNING clause must have their mode set as DATA_AT_EXEC and callback functions must be registered for these bind handles using OCIBindDynamic.

ORA-24370: illegal piecewise operation attempted

Cause: Data of a certain datatype that does not support piecewise operation is being sent or fetched in pieces.

Action: Always set the piece value to OCI_ONE_PIECE for datatypes that does not support piecewise operation.

ORA-24371: data would not fit in current prefetch buffer

Cause: An internal OCI error has occurred.

Action: Please contact Oracle customer support.

ORA-24372: invalid object for describe

Cause: The object to be described is not valid. It either has compilation or authorization errors.

Action: The object to be described must be valid.

ORA-24373: invalid length specified for statement

Cause: The length specified for the statement is either 0 or too large.

Action: Specify a valid length for the statement.

ORA-24374: define not done before fetch or execute and fetch

Cause: The application did not define output variables for data being fetched before issuing a fetch call or invoking a fetch by specifying a non-zero row count in an execute call.

Action: Issue OCI define calls for the columns to be fetched.

ORA-24375: cannot use V6 syntax when talking to a V8 server

Cause: V6 syntax is no longer supported in V8 server.

Action: Change syntax to V7 syntax or higher.

ORA-24750: incorrect size of attribute

Cause: Transaction ID attribute size is incorrect.

Action: Verify that the size parameter is correct.

ORA-24752: OCI_TRANS_NEW flag must be specified for local transactions

Cause: Application attempted to start a local transaction without using OCI_TRANS_NEW.

Action: Use OCI_TRANS_NEW when starting local transactions.

ORA-24753: local transactions cannot be detached

Cause: An attempt to detach a local transaction was made.

Action: Local transactions may only be committed or rolled back.

ORA-24754: cannot start new transaction with an active transaction

Cause: An attempt to start a new transaction was made when there was an active transaction.

Action: Commit, rollback or detach the existing transaction before starting a new transaction.

ORA-24755: OCI_TRANS_NOMIGRATE, OCI_TRANS_JOIN options are not supported

Cause: These flags are currently not supported.

Action: No action required.

ORA-24756: transaction does not exist

Cause: An invalid transaction identifier or context was used or the transaction has completed.

Action: Supply a valid identifier if the transaction has not completed and retry the call.

ORA-24757: duplicate transaction identifier

Cause: An attempt was made to start a new transaction with an identifier already in use by an existing transaction.

Action: Verify that the identifier is not in use.

ORA-24758: not attached to the requested transaction

Cause: An attempt was made to detach or complete a transaction that is not the current transaction.

Action: Verify that the transaction context refers to the current transaction.

ORA-24759: invalid transaction start flags

Cause: An invalid transaction start flag was passed.

Action: Verify that one of the following values — OCI_TRANS_NEW, OCI_TRANS_JOIN, OCI_TRANS_RESUME was specified.

ORA-24760: invalid isolation level flags

Cause: An invalid isolation level flag was passed.

Action: Verify that only one of following values — OCI_TRANS_READONLY, OCI_TRANS_READWRITE, OCI_TRANS_SERIALIZABLE is used.

ORA-24761: transaction rolled back

Cause: The application tried to commit a transaction and the server rolled it back.

Action: No action required.

ORA-24762: server failed due to unspecified error

Cause: An internal error has occurred in the server commit protocol.

Action: Contact customer support.

ORA-24763: transaction operation cannot be completed now

Cause: The commit or rollback cannot be performed now because the session cannot switch to the specified transaction.

Action: Retry the operation later.

ORA-24764: transaction branch has been heuristically committed

Cause: This is an information message.

Action: No action required.

ORA-24765: transaction branch has been heuristically rolled back

Cause: This is an information message.

Action: No action required.

ORA-24766: transaction branch has been partly committed and aborted

Cause: This is an information message.

Action: No action required.

ORA-24767: transaction was read-only and has been committed

Cause: This is an information message.

Action: No action required.

ORA-24768: commit protocol error occurred in the server

Cause: This is an internal error.

Action: Contact customer support

ORA-24769: cannot forget an active transaction

Cause: Transaction identifier refers to an active transaction.

Action: Verify that the identifier of an active transaction was not passed as an argument.

ORA-24770: cannot forget a prepared transaction

Cause: Transaction identifier refers to a prepared transaction.

Action: Verify that the identifier of a prepared transaction was not passed as an argument.

ORA-24771: cannot detach, prepare or forget a local transaction

Cause: Service handle contains a local transaction context.

Action: Verify that the transaction context does not refer to a local transaction.

ORA-24772: Cannot mix tightly-coupled and loosely-coupled branches

Cause: Application attempted to start a transaction with a global transaction identifier and a wrong option.

Action: Verify that all branches of a global transaction are started with either OCI_TRANS_TIGHT or OCI_TRANS_LOOSE option. If the application is correct and uses distributed updates, contact customer support.

ORA-24773: invalid transaction type flags

Cause: OCI_TRANS_TIGHT or OCI_TRANS_LOOSE mode was not specified.

Action: Verify that the right parameters are being used.

ORA-24774: cannot switch to specified transaction

Cause: The transaction specified in the call refers to a transaction created by a different user.

Action: Create transactions with the same authentication so that they can be switched.

ORA-24775: cannot prepare or commit transaction with non-zero lock value

Cause: An attempt was made to detach the transaction with a non-zero lock value.

Action: Detach the transaction with lock value set to zero and then try to prepare or commit the transaction.

ORA-24776: cannot start a new transaction

Cause: An attempt was made to start a new transaction when session was already attached to an existing transaction.

Action: End the current transaction before creating a new transaction.

ORA-24777: cannot create migratable transaction

Cause: The transaction tried to access a remote database from a non-multi threaded server instance.

Action: Perform the work in the local database or open a connection to the remote database from the client. If multi threaded server option is installed connect to the Oracle instance through the dispatcher.

ORA-24778: cannot open connections

Cause: The migratable transaction tried to access a remote database when the session itself had opened connections to remote database(s).

Action: Close the connection(s) in the session and then try to access the remote database from the migratable transaction. If the error still occurs, contact Oracle customer support.

Источник

#oracle #plsql

Вопрос:

Процедура занимает две даты, и найдите отчеты, выпущенные за этот период, обновите общую зарплату и распечатайте их.

 CREATE OR REPLACE PROCEDURE Salary_Update(p_Date1 DATE, p_Date2 DATE) AS 
CURSOR MYCR IS 
SELECT ReportID, TotalSale, Rate, TotalSalary, ReportDate
FROM WEEKLY_REPORT
WHERE ReportDate BETWEEN TO_DATE(p_Date1, 'DD-MM-YYYY') AND TO_DATE(p_Date2, 'DD-MM-YYYY'); 
BEGIN
FOR MYPOINTER IN MYCR LOOP
UPDATE WEEKLY_REPORT SET TotalSalary = ((TotalSale/100)*Rate);
DBMS_OUTPUT.PUT_LINE('The total salary for report ' || MYPOINTER.REPORTID || '  updated to ' || MYPOINTER.TotalSalary || 'dollars, which is ' || MYPOINTER.Rate || '% of the total sale of ' || MYPOINTER.TotalSale || 'dollars.');
WHERE ReportID = MYPOINTER.ReportID;
END LOOP;
END Salary_Update;
/
 

казнь

 BEGIN Salary_Update('02-04-2020','05-04-2020');
END;
BEGIN 
Salary_Update(to_date('02-04-2020','dd-mm-yyyy'), to_date('05-04-2020','dd-mm-yyyy'));
END;
 

и то, и другое не работает.

Комментарии:

1. Сделайте show errors это после компиляции или запроса user_errors , чтобы узнать , что не так. := = Для начала , в вашем обновлении вместо этого есть.

2. @Alexpool Я использую oracle apex. показать ошибку не работает

3. Вы можете запросить user_errors , как предложил @Alexpool. Вы to_date звоните по date телефону, который не имеет смысла в дополнение к ошибке в update . Ваше update утверждение обновляет каждую строку таблицы, потому что в ней нет where предложения. На самом деле нет смысла открывать курсор, который повторяет каждую строку в таблице, а затем if в цикле есть оператор, который отфильтровывает большинство строк. Это должно быть where предложение в определении курсора.

4. Ваши входные параметры определены как ДАТА, но когда вы вызываете процедуру, вы даете строки. ДАТА-это внутренний двоичный тип данных.

5. DBMS_OUTPUT подходит для отладки, но не для формальной отчетности. Он не записывается на экран. Он записывает данные во внутренний буфер. Этот буфер затем передается обратно вызывающей программе после завершения процедуры, когда оперативное управление возвращается вызывающему. Затем вызывающий должен решить, обрабатывать буфер или нет. И некоторые вызывающие программы могут даже не быть запрограммированы на то, чтобы их можно было обработать.

Ответ №1:

Существует ряд синтаксических и логических ошибок. Вы не указали точно, что вы хотите, чтобы код выполнял или предоставил воспроизводимый тестовый пример, но я предполагаю, что вы хотите что-то вроде

 CREATE OR REPLACE PROCEDURE Salary_Update(
  p_Date1 DATE, 
  p_Date2 DATE) 
AS 
  CURSOR MYCR IS 
    SELECT ReportID, TotalSale, Rate, TotalSalary, ReportDate
      FROM WEEKLY_REPORT
     WHERE ReportDate BETWEEN p_date1 AND p_date2;
BEGIN
  FOR MYPOINTER IN MYCR 
  LOOP
    UPDATE WEEKLY_REPORT 
       SET TotalSalary = ((TotalSale/100)*Rate)
     WHERE ReportID = MYPOINTER.ReportID;
    DBMS_OUTPUT.PUT_LINE('The total salary for report ' || MYPOINTER.REPORTID || '  updated to ' || MYPOINTER.TotalSalary || 'dollars, which is ' || MYPOINTER.Rate || '% of the total sale of ' || MYPOINTER.TotalSale || 'dollars.');
  END LOOP;
END Salary_Update;
/
 

Строка, которую вы выводите, кажется, не имеет смысла- MYPOINTER.TotalSalary это значение курсора, которое будет значением до обновления. Если важно, чтобы вы распечатали обновленное TotalSalary значение, вам, вероятно, понадобится что-то вроде

 CREATE OR REPLACE PROCEDURE Salary_Update(
  p_Date1 DATE, 
  p_Date2 DATE) 
AS 
  CURSOR MYCR IS 
    SELECT ReportID, TotalSale, Rate, TotalSalary, ReportDate
      FROM WEEKLY_REPORT
     WHERE ReportDate BETWEEN p_date1 AND p_date2;
  l_newSalary weekly_report.totalSalary%type;
BEGIN
  FOR MYPOINTER IN MYCR 
  LOOP
    l_newSalary := (mypointer.TotalSale/100) * mypointer.rate;
    UPDATE WEEKLY_REPORT 
       SET TotalSalary = l_newSalary
     WHERE ReportID = MYPOINTER.ReportID;
    DBMS_OUTPUT.PUT_LINE('The total salary for report ' || MYPOINTER.REPORTID || '  updated to ' || l_newSalary || 'dollars, which is ' || MYPOINTER.Rate || '% of the total sale of ' || MYPOINTER.TotalSale || 'dollars.');
  END LOOP;
END Salary_Update;
/
 

Если ошибка, которую вы получаете, является ошибкой ORA-00942, которая weekly_report не существует, то в текущей схеме такой таблицы нет. Предполагая, что запрос работает при интерактивном запуске, потенциально таблица существует в другой схеме, в текущей схеме для этой таблицы есть локальный синоним, и владелец процедуры имеет доступ только к таблице, предоставленной через роль. В этом случае вам потребуется, чтобы администратор базы данных предоставил доступ к таблице непосредственно владельцу процедуры.