Forms :: Illegal Restricted Procedure Error

Aug 25, 2011

I have data block, after inserting records, in post insert i am using 'GO_ITEM' built_in as i have to move cursor to particular item in another block.

It's giving 'Illegal Restricted procedure' error. How to move cursor to particular item of another block after inserting record?

View 5 Replies


ADVERTISEMENT

Forms :: ORA-01036 - Illegal Variable Name / Number

May 30, 2012

I have developed a form which is running fine most of the time,

but some time it gives error

unable to insert record and if we see detail it shows

ORA-01036: illegal variable name/number

error.jpg is attached. if there is some problem, it should not pass any entry and if there is no problem then why it gives error of illegal variable?

View 3 Replies View Related

Forms :: Data Block Based On Procedure - Getting Compilation Error

Mar 13, 2013

I am trying to create a datablock based on a procedure , but im getting errors in compilation:

Errors are :
1) identifier 'HSM_WSH_DEL_UTIL.DEL_TBL' must be declared

2) PL/SQL ERROR 320 at line 7, column 27 the declaration of the type of this expression is incomplete or malformed

Heres my pkg spec and body for the data block:

CREATE OR REPLACE PACKAGE hsm_wsh_del_util
IS
TYPE del_record IS RECORD (delivery_id NUMBER);
TYPE del_tbl IS TABLE OF del_record
INDEX BY BINARY_INTEGER;

PROCEDURE do_query (p_del IN OUT del_tbl);
END hsm_wsh_del_util;
[Code] .....

View 1 Replies View Related

PL/SQL :: Illegal Use Of LONG Datatype

Aug 31, 2012

I've this problem:

create table t1 ( x long );
Table created.

create table t2 ( x long );
Table created.

insert into t1 values ( 'test long type' );
1 row created.

insert into t2 select * from t1;
                      *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

How can I issue this error, I need use also dblink with long type.

I do not want to use the copy command.

I know that you can solve this problem with a stored procedure or anonymous block.

View 8 Replies View Related

Removing Illegal Characters

Dec 27, 2006

I am working with Oracle 10G, and have been working on setting up little pl/sql checks to make sure that the data that is imported is in the correct format.

The wall I have hit is removing illegal characters from the data I import. I have started to set something up where the string for a certain column must be be made of only there characters:

"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-" (note that there is a - besides just letters) and I may want to add some other characters later. So basically the script will drop or replace any character not found in my definitions with "", thus removing the illegal character and joining the previous and next characters.

I thought for sure there would be a script posted somewhere online that did this but I can't find it and my syntax skills are lacking.

View 8 Replies View Related

ORA-00997 / Illegal Use Of LONG Datatype

Aug 13, 2013

We also face the same in oracle 9i version. we tried to re-org some tables for performance issue.But our tables have long and long raw datatype. then we approached the traditional way i.e 1. Export tables.2.Truncate tables.3.import tables.(use ignore=y).4.check the index validation.5.gather stats. Above was successfully done in our production environment.but some application downtime is required. 

View 1 Replies View Related

SQL & PL/SQL :: ORA-38818 Illegal Reference To Edition Object

Jul 2, 2012

When I am trying to create a public synonym for a package then I am getting this error. This error tells us that non editioned object(Public Synonym) can not refenrence the editioned object(Package in this case).One of the solution provided on one of t he site is to create local synonym instaed of public synonym but I need to create public synonym only.

View 6 Replies View Related

Replication :: ORA-00997 / Illegal Use Of LONG Datatype

May 5, 2010

I've got a dblink between two Oracle databases. There are one view that I specify a join between four remote tables.

One of the tables does have a column defined as a long raw. I do not need that column; the query doesn´t make reference to it.However, when I specify the query, I get this error: ORA-00997; illegal use of LONG datatype.

"how can I accomplish this query, over a dblink, given that one table has a long raw that is not part of my query ?

View 1 Replies View Related

SQL & PL/SQL :: ORA-02065 / Illegal Option For ALTER SYSTEM

Sep 8, 2012

SQL> ALTER SYSTEM SET NLS_LANG='AMERICAN_AMERICA.AR8MSWIN1256'SCOPE=SPFILE;
ALTER SYSTEM SET NLS_LANG=' PORTUGUESE_PORTUGAL.WE8MSWIN1252'SCOPE=SPFILE
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

View 9 Replies View Related

Oracle 11g Client / ORA-12533 Illegal ADDRESS Parameter

Jul 10, 2011

I am working on a database application which works as a data access layer. The application uses OCI interface to connect and query to the Oracle Database. This application is certified on Oracle 9i and 10g client and works fine without any issue, but when I am certifying the application on Oracle 11g client, I am getting connection failed problem and following Oracle Error is displayed.

ORA-12533 illegal ADDRESS Parameter.

There is no change in the environment setting between Oracle 10g and Oracle 11g.

Following are the details of the environment

Version of Oracle 11g client is 11.1.0.6.0
Version of Oracle Database is 11.1.0.6.0.
Platform - Solaris 5.9 on sparc v9

On enabling client trace with support level also does not work. It shows TNS error TNS-12533 illegal ADDRESS Parameter.

what could be the possible reasons that the connection is failing with Oracle 11g client, while the same is working with Oracle 10g client. As I said there is no environmental difference when performing this test. The only change is ORACLE_HOME setting for the corresponding Oracle client version.

View 2 Replies View Related

Server Administration :: Long Data Type Illegal?

Dec 26, 2012

when I try comnd create table a as select * from b where 1=2; it says illegal datatype long..i m bemused what sin has the long datatype done?

View 4 Replies View Related

SQL & PL/SQL :: Find All Partition Names - Illegal Use Of LONG Datatype

Mar 5, 2012

I need to find all the partition names below or equal given value.

select
partition_name
from
user_tab_partitions
where
table_name = 'RB' and
HIGH_VALUE <= 1234

Above Query is giving error "ORA-00997: illegal use of LONG datatype"

Any other alternative ? (I know its because of LONG Datatype of HIGH_VALUE ) ...

View 9 Replies View Related

Server Utilities :: Export Illegal Parameter Value In OCI Lob Function?

Jan 21, 2011

We are trying to export our production data .We got this error

. . exporting table EA_BLOB
EXP-00056: ORACLE error 24801 encountered
ORA-24801: illegal parameter value in OCI lob function

how to overcome this error ?

View 2 Replies View Related

Application Express :: ORA-00997 / Illegal Use Of LONG Datatype

Jul 26, 2012

I am trying to copy structure of table through database link but getting an error while running the command :

SQL> create table TOAD_PLAN as select * from TOAD_PLAN_@db_link where 1=2;
create table TOAD_PLAN_TABLE as select * from TOAD_PLAN_TABLE@to_paceview where 1=2
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

how can i create it through database link or through any other utility.

View 2 Replies View Related

Networking And Gateways :: ORA-01741- Illegal Zero-length Identifier For DBlink

Mar 7, 2010

I tried to create a DB link that connects to SQL server.Right now I could connect the SQL server and able to test the connection by tnsping. When I tried to create a dblink, it failed and the message shows

CREATE PUBLIC DATABASE LINK "ODBC" CONNECT TO "user" IDENTIFIED BY "" using 'hsodbc'

ORA-01741: illegal zero-length identifier

The problem is that our sql server does not have any password while identified by is a required field.

View 1 Replies View Related

SQL & PL/SQL :: Find Constraint Name From User Table - Illegal Use Of LONG Datatype

Apr 30, 2010

I want to find the constraint name from User_Constraints table using the following query:

Select * From User_Constraints Where Table_Name='CHARGECODE' and Constraint_Type='C' And Search_Condition = '"PERCENTAGE" IS NOT NULL';

Then it shows "ORA-00997: illegal use of LONG datatype" error.

Is there any way to compare with long type value.

View 11 Replies View Related

SQL & PL/SQL :: Compilation Error In Procedure

May 5, 2013

SQL> create or replace procedure procedure_test(id in number, city in varchar2(20) , name in varcha

r2(20) ) as
2 begin
3 insert into procedure_name(vid,vcity,vname) values(id,city,name);
4 end;
5 /

Warning: Procedure created with compilation errors.

There is a table named procedure_name with these columns exactly in database. what this Compilation Error is?

View 5 Replies View Related

SQL & PL/SQL :: Error In Compiling Procedure

Jan 3, 2012

i am new to oracle,when i am compiling below procedure,

create or replace procedure GetTempPolicyDetails2(i_officeid in number,pr_cursor out SYS_REFCURSOR) as

L_QUEY VARCHAR2(4000):= 'select POLTMP.POLICY_ID,POLTMP.CORPORATE_NAME,POLTMP.POLICY_FROM_DATE,POLTMP.POLICY_TO_DATE,
POLTMP.NETPREMIUM_WITH_SERVICE_TAX,
IOF.OFFICENAME,UW.NAME,REG.REGNAME,brmstr.brokername,created_on
from policies_temp POLTMP
INNER JOIN ISSUINGOFFICE IOF ON IOF.ID=POLTMP.Divisionid
INNER JOIN REGION REG ON REG.ID=IOF.RID
INNER JOIN underwriters UW ON UW.ID=REG.UWID
LEFT OUTER JOIN broker_master BRMSTR ON BRMSTR.ID=POLTMP.Broker_Name
where POLTMP.Policy_Status=1 where 1=1'
L_WHERE VARCHAR2(4000):='WHERE 1=1';
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Compilation Error In Procedure?

Apr 25, 2013

I am having 3 procedures a,b,c. Procedure a is calling b,procedure b is calling c, and procedure c is calling procedure a.There is compilation error in procedure a but while compiling it is saying dependent procedure is uncompiled.

How to compile all the 3 procedures..

View 3 Replies View Related

PL/SQL :: Error Compiling Procedure?

Jul 31, 2012

I'm getting the following error on a procedure that I was able to compile until today after some changes.

ORA-06550: line 354, column 20:
PLS-00103: Encountered the symbol "AND" when expecting one of the following:
. then or
The symbol ". was inserted before "AND" to continue.

View 6 Replies View Related

Direct Path Exported Dump File Contains Illegal Column Length

Apr 28, 2011

I am trying to import the database and i see the following error:

IMP-00051: Direct path exported dump file contains illegal column length imp abruptly stops. my source and destination database is as follows:

Source: 9.2.0.8
Destinatin: 11g-r2.

I used exp with the following options:

direct=y
buffer=899989898
recordlength=64000
File=1.dmp,2.dmp,3.dmp,4.dmp,5.dmp,6.dmp,7.dmp,8.dmp,9.dmp,10.dmp,11.dmp,12.dmp,
13.dmp,14.dmp,15.dmp,16.dmp,17.dmp,18.dmp,19.dmp,20.dmp,21.dmp,22.dmp,23.dmp,24.
dmp,25.dmp,26.dmp,27.dmp,28.dmp,29.dmp,30.dmp
grants=y
compress=y
FILESIZE=25g
owner=BRM

Using imp with the following option:

indexes=n
buffer=99989898
recordlength=64000
File=1.dmp,2.dmp,3.dmp,4.dmp,5.dmp,6.dmp,7.dmp,8.dmp,9.dmp,10.dmp,11.dmp,12.dmp,
13.dmp,14.dmp,15.dmp,16.dmp,17.dmp,18.dmp,19.dmp,
20.dmp,21.dmp,22.dmp,23.dmp,24.dmp,25.dmp,26.dmp,27.dmp,28.dmp,29.dmp,30.dmp
analyze=n
grants=y
fromuser=brm
touser=brm
statistics=none

why my imp failing?

View 4 Replies View Related

Error In PL/SQL Procedure In Timesten In UNIX

Feb 22, 2012

create or replace procedure log_file
(
odsm_profile IN VARCHAR2
)
AS
handle utl_file.file_type;
begin

[code].....

i have error when executing this pl/sql procedure in timesten in UNIX

this is the excution error:

Command> exec log_file('hi');
8507: ORA-29280: invalid directory path
8507: ORA-06512: at "SYS.UTL_FILE", line 41
8507: ORA-06512: at "SYS.UTL_FILE", line 478
8507: ORA-06512: at "SIUA.LOG_FILE", line 8
8507: ORA-06512: at line 1

The command failed.

View 6 Replies View Related

SQL & PL/SQL :: Invalid Cursor Error In Procedure

Feb 2, 2011

I have this stored procedure as part of package.

PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER,
in_SPLY_REORD_NO IN CHAR,
in_OHB_QTY_CARTONS IN NUMBER,
in_OHB_QTY_UNITS IN NUMBER,
in_SPLY_TOT_OHB_QTY IN NUMBER,
in_OHB_INPUT_CTNS_MIN IN NUMBER,
in_OHB_INPUT_CTNS_MAX IN NUMBER,
in_UNITS_PER_CARTON IN NUMBER,
in_OHB_INPUT_UNIT_CONSTANT IN NUMBER,
in_TOTAL_CARTONS IN NUMBER,
out_ALLOW_OHB_INPUT_FLAG OUT CHAR,
out_ERR_CODE OUT NUMBER,
out_ERR_MESSAGE OUT VARCHAR2)

When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor

Here is the execution script
DECLARE
IN_CALL_ID NUMBER;
IN_SPLY_REORD_NO VARCHAR2(32767);
IN_OHB_QTY_CARTONS NUMBER;
IN_OHB_QTY_UNITS NUMBER;
IN_SPLY_TOT_OHB_QTY NUMBER;
IN_OHB_INPUT_CTNS_MIN NUMBER;
[code]...

There is no cursor used in the procedure. It just inserts records in the table for given input values.

View 10 Replies View Related

SQL & PL/SQL :: Oracle - Getting Error While Writing Procedure?

May 3, 2012

I am Getting the error when writing the procedure. How to Solve this Error .

Error: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

View 5 Replies View Related

SQL & PL/SQL :: Error In Calling Collection Procedure

Jun 4, 2013

In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are

"UpdateRelatedOrders" and "SetGeoFraudScore"

with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures

"UpdateRelatedOrders" and "SetGeoFraudScore".

I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....

View 19 Replies View Related

Data Guard :: ORA-00274 / Illegal Recovery Option SESSION Physical Standby DB

May 6, 2011

oracle version: 10gr2
os:windows 32

I have configured a physical standby database.

I have set the physical standby database to open read mode for a short while.

according to [URL] in order to set db in for from open read-only to applying redo data

Quote:

To change the standby database from being open for read-only access to performing Redo Apply:

Terminate all active user sessions on the standby database. Restart Redo Apply. To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> DISCONNECT FROM SESSION;

To enable real-time apply, include the USING CURRENT LOGFILE clause:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> USING CURRENT LOGFILE;

this is what happen

SYS@ngdr> alter database recover managed standby database disconnect session;
alter database recover managed standby database disconnect session
*
ERROR at line 1:
ORA-00274: illegal recovery option SESSION
SYS@ngdr> alter database recover managed standby database using current logfile;

okay since I encountered the illegal recovery options session, I decided to use alter database recover managed standby database using current logfile; now the session seems to be hanging forever.

View 3 Replies View Related

Getting Error While Trying To Debug Procedure In Oracle SQL Developer

Apr 16, 2012

I tried to debug the procedure in Oracle SQL Developer and got the below error,

Connecting to the database TEST.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '****', '4000' )
ORA-30683: failure establishing connection to debugger
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database TEST.

Here,

TEST is my database name,
**** is IP address of my local machine
4000 is the port number, also I have set the debug port range as 4000 - 4999 in SQL DEVELOPER.
firewall is not enabled in my machine.

View 2 Replies View Related

Executed Procedure - Buffer Overflow Error

Nov 16, 2011

when i am executing the procedure, i am getting the below error.

I have added below lines in the procedure.

DBMS_OUTPUT.ENABLE(100000);

Even though i am getting the error.

ORA-20000: ORU-10027: buffer overflow, limit of 100000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "Procedure name", line 199
ORA-06512: at line 1
20000. 00000 - "%s"

*Cause: The stored procedure 'raise_application_error' was called which causes this error to be generated.

*Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.

View 6 Replies View Related

SQL & PL/SQL :: Stored Procedure Function - Numeric Or Value Error

Apr 16, 2010

I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7

The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"

and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"

The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.

create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....

View 26 Replies View Related

SQL & PL/SQL :: Error When Tried Sending Email Through Stored Procedure?

Jan 6, 2011

I have this stored procedure that does implements some business logic and then sends an email.

I got the below error when it tried to send the email

Quote:Returned values:
ERR_CODE =-29279,
ERR_MESSAGE = "MSS DATABASE INCONSISTENCYOTHERS EXCEPTION in XCOM_X080_SUBMITORDER - SQL -29279 SQLERRM: ORA-29279: SMTP permanent error: 501 5.5.2 Syntax error in parameters scanning "TO""

How can I handle this error using exception and in case sending email fails instead of abnormal exit ?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved