SQL & PL/SQL :: ORA-00984 / Column Not Allowed Here

Oct 30, 2010

I have created the following package-

create or replace PACKAGE TABLE_PKG1 AS
PROCEDURE make(table_name VARCHAR2, colspec varchar2);
PROCEDURE add_row(table_name VARCHAR2, value_s VARCHAR2,cols VARCHAR2);
END TABLE_PKG1
CREATE OR REPLACE PACKAGE BODY TABLE_PKG1 AS

[code]....

The package and the package body compiles successfully. When I add table-

BEGIN
TABLE_PKG1.make('EMP_new','country varchar2(20), currency varchar2(20)');
END;

The table is created successfully.However when I invoke the package using the following anonymous block for adding the row-

SET SERVEROUTPUT ON
BEGIN
TABLE_PKG1.add_row('EMP_new','"India","Rs"','Country,Currency');
END;

The it generates the following error-

Error report:

ORA-00984: column not allowed here
ORA-06512: at "SCOTT.TABLE_PKG1", line 14
ORA-06512: at line 2
00984. 00000 - "column not allowed here"

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Special Character Not Allowed In Column Value?

Aug 8, 2012

I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE

Table values are for e.g

STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA

Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.

View 6 Replies View Related

SQL & PL/SQL :: ORA-01733 - Virtual Column Not Allowed Here

Nov 23, 2011

Does know why I am getting ORA-01733- virtual column not allowed here Following :

CREATE TABLE EMPL
(
EMP_ID NUMBER(10),
CNT NUMBER(10)
)
insert into empl(EMP_ID)
values(1)

[code]....

ORA-01733- virtual column not allowed here

View 33 Replies View Related

SQL & PL/SQL :: Value Larger Than Specified Precision Allowed For Column

Mar 21, 2013

I am trying to do bulk insert into a table. Attached is the script I am running:

But when I run this script I get exception : 'ORA-01438: value larger than specified precision allowed for this column'.

I have checked in my soucre table as well as in inserting table, everything looks fine to me.

how to handle such exception, which could actually tell me for what column and what data it is throwing exception.

View 7 Replies View Related

SQL & PL/SQL :: Creating Composite Unique Key With Only 1 Column Allowed To Be Changed

Jul 3, 2013

I have a table say MY_TAB with columns as below

emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)

insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');

How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.

insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.

View 13 Replies View Related

Getting Error - ORA-01438 (value Larger Than Specified Precision Allowed For Column)?

Dec 31, 2012

SQL> create table t51(t5 NUMBER(25,8));

Table created.

SQL> insert into t51 values (100000000000000000.00000);
insert into t51 values (100000000000000000.00000)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

View 5 Replies View Related

Constraint Specification Not Allowed Here

Aug 4, 2011

SQL> create table emp_column_level_cons(empno number(4) constraint empcol_empno_pk primary key
2 ename varchar2(15) unique,
3 job varchar2(20) not null,
4 sal number(7,2) check (sal between 1500 and 25000),
5 deptno number(4) constraint empcol_deptno_fk foreign key re
ferences dept(deptno) on delete cascade);
deptno number(4) constraint empcol_deptno_fk foreign key references dept(deptno) on delete c
*
ERROR at line 5:
ORA-02253: constraint specification not allowed here

================================================

View 1 Replies View Related

SQL & PL/SQL :: Sequence Number Not Allowed

Mar 2, 2013

I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.

I have query like this

"SELECT
AER_ID_TEMP.AER_ID,
AER_ID_TEMP.D__PRNO,
AER_ID_TEMP.D__PCNT,
CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID
FROM AER_ID_TEMP"

but still encountering a error "ORA-02287 sequence number not allowed"

View 2 Replies View Related

SQL & PL/SQL :: Further Changes To Block By Transaction Not Allowed

Jun 6, 2011

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.

ORA-08007: Further changes to this block by this transaction not allowed

UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,

[code]...

1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.

View 10 Replies View Related

SQL & PL/SQL :: Why Commit Not Allowed In Trigger

May 17, 2010

why commit is not allowed in trigger?

View 3 Replies View Related

PL/SQL :: Group Function Is Not Allowed Here

Mar 3, 2013

Am running the below query and getting the error below :

ORA-00934: group function is not allowed here
SELECT   CONTRNO,
            SUBNO AS USER_CODE,
            TOTAL_POINTS AS AMOUNT,
            STARTDATE + 30 AS DATE_F,
            'SUB' USER_TYPE

[Code]....

View 4 Replies View Related

Forms :: Frm-41009 Function Key Not Allowed

May 1, 2012

i create form,when i press F11, it displays this error:Frm-41009 function key not allowed ,press ctrl+k for list of valid values.And i can't enter value into fields to query records.

View 11 Replies View Related

SQL & PL/SQL :: ORA-02253 / Constraint Specification Not Allowed Here

Jan 6, 2011

I've got the following SQL script that I'm trying to run against a database: -

CREATE TABLE jdp_parameters (
id NUMBER(9) CONSTRAINT pk_jdp_parameters
PRIMARY KEY
CONSTRAINT ck_jdp_parameters_id
CHECK (id = 0),

[code]....

On running it I get the following errors: -

pricelevel_id NUMBER(9) CONSTRAINT fk_jdp_pricelevel_id
*
ERROR at line 10:
ORA-02253: constraint specification not allowed here
Sequence created.
REFERENCES jdp_parameters(id)

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Materialized View - Zero Length Not Allowed?

Dec 8, 2010

I am trying to create one Materialized view from a Select Statement and I am getting the following error

SQL Error: ORA-01723: zero-length columns are not allowed

I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?

Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.

CREATE OR REPLACE VIEW mx_test
AS
SELECT t."EXTN_SERVICE_REQUEST_NO",
t."EXTN_SERVICE_TYPE",

[Code]....

Materialized View :

CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test

Error :

Error starting at line 1 in command:
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error at Command Line:3 Column:16
Error report:
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause:
*Action:

Do I need to use NVLs for all columns which may result in NULL value ?

View 9 Replies View Related

Security :: Allowed Characters In 11g Password

Jan 8, 2013

where do I find a list or a description of allowed characters for a 10g password?

View 8 Replies View Related

PL/SQL :: ORA-02287 / Sequence Number Not Allowed Here

Dec 5, 2012

create sequence seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
create table t ( id number, b date);
insert into t values (seq.nextval,sysdate);
update t set b=sysdate-1 where id=seq.currval;
update t set b=sysdate-1 where id=seq.currval
*
ERROR at line 1:
ORA-02287: sequence number not allowed here

how can i update as above ..

View 7 Replies View Related

ORA-24777 / Use Of Non-migratable Database Link Not Allowed

Mar 18, 2011

1. we have to use shared server mode with Xa transactions, if not we get this error:

QUOTE ORA-24777: use of non-migratable database link not allowed

Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multi threaded server process

2. The SGA and PGA_AGGREGATE targets are limitted to 4GB as an Oracle consultant's recommendation [I don't know the reason, but I have been informed that it is recommended so I don't dare to increase it]

Taking into consideration the above points and the below information, what should I do then... I'm not willling to change the workarea size policy to manual as I'm affraid of the consequences...

QUOTE MTS becomes downright dangerous when Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) is in place.

When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . .

AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request. First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"

View 1 Replies View Related

PL/SQL :: ORA-03127 - No New Operations Allowed Until Active Ends?

May 15, 2013

I'm a software developer, not an Oracle DBA

Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.

View 7 Replies View Related

PLS-00642 - Local Collection Types Not Allowed In SQL Statements

Feb 20, 2011

I write plsql code as following:

DECLARE
TYPE id_collection IS TABLE OF NUMBER;
TYPE title_collection IS TABLE OF VARCHAR2(60);
id ID_COLLECTION;

[Code]...

I got following error: PLS-00642: local collection types not allowed in SQL statements.

View 3 Replies View Related

SQL & PL/SQL :: PLS-00653 - Aggregate / Table Functions Not Allowed In Scope?

Jul 8, 2010

I have a function that returns the situation for one month for some database. I need to implement it in some report medium for one year. The one year function works ok.

My problem is when trying to make another function that runs the monthly function 12 times and that error is "PLS-00653: aggregate/table functions are not allowed in PL/SQL scope".am trying to get around some restrictions and somehow until this part things seem to be ok.

I tried to use a union with 12 blocks but it works very slow in the reporting environment and now i want to try to make another function that runs another function 12 times depending on the parameter.

here is the code (there might be some name misuse since i had to change the names of the original code -

CREATE OR REPLACE
FUNCTION anual(monthh in varchar2, year IN VARCHAR2)
return anual_REP_var PIPELINED is
BR anual_REP:=anual_REP(NULL,NULL, NULL,NULL);
contor INT(2);

[code]...

View 2 Replies View Related

Forms :: Calling Form Has Unapplied Changes - Save Not Allowed

Sep 4, 2012

Iam facing a problem while calling a form.

iam opening a form,inserting data in empty columns of an existing record and with out saving iam calling another form.Inserting and updating data in the called form and again coming back to old form and then saving the form.

while doing this, when i am trying to save on the called form its giving an error

FRM-40403 CALLING FORM HAS UNAPPLIED CHANGES,SAVE NOT ALLOWED.

View 5 Replies View Related

Forms :: Design Time Insert And Update Allowed For Block

Jan 10, 2011

I create databalock - LC_REQ_PO_ITM_DTLS...In design time Insert and update allowed for that block - YES. While i execute query, i write procedure like this

GO_BLOCK('LC_REQ_PO_ITM_DTLS');
EXECUTE_QUERY;
SET_ITEM_PROPERTY('pb_item_cd', enabled, property_false);
SET_BLOCK_PROPERTY('LC_REQ_PO_ITM_DTLS',INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY('LC_REQ_PO_ITM_DTLS',UPDATE_ALLOWED, PROPERTY_FALSE);

but when i press execute button , then data comes properly on that bloack..but i click on that block, error comes like this - 'You can not create record here '.and data disappearing..

View 3 Replies View Related

JDeveloper, Java & XML :: Missing Expression And Then Group Function Not Allowed Error

Feb 23, 2011

I have 2 tables used in this problem: ODETAILS and ORDERS.

ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST

UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);

In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.

View 3 Replies View Related

Precompilers, OCI & OCCI :: PLS-00642 / Local Collection Types Not Allowed In SQL Statements

Oct 2, 2008

I have an anonymous PL/SQL procedure executed from OCCI like:

declare
type temp is table of emp%rowtype;
vemp temp;
begin
select * bulk collect into vemp

[code]...

and a cpp code like:

string stmt = "see above";
stmt->registerOutParam (1, OCCICURSOR);
stmt->execute ();

The "execute" gives me the error (of course): PLS-00642: local collection types not allowed in SQL statements

I cannot select from an PL/SQL table.But how to transfer these PL/SQL table "vemp" into my OCCI program?

Note: the first select is for information only, so I can not select the data in question from that directly.

View 6 Replies View Related

Data Guard :: ORA-01154 / Database Busy - Open / Close / Mount And Dismount Not Allowed Now

Sep 21, 2010

I am trying to create the Physical StandBy Database in the same server. Till last 2 Final steps, everything went on well. In the final steps, when I try to open the StandBy Database, it throws the following Error:

*******************************************************
SQL...> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
*******************************************************

I tried creating the Physical Standby with the following Steps.

Environment: Oracle Release 10.2.0.1.0 / Windows 2003 Server Enterprise Edition SP2
Primary DB = 'PrimDB'
StandBy DB to be created ='StBy1DB'

In the same Server, location for

PrimDB datafiles='F:oracleproduct10.2.0oradataPrimDBData',
StandBy Datafiles='E:StandBy_DBData'
PrimDB Control Files='F:oracleproduct10.2.0oradataPrimDBControl'
StandBy Control Files= 'E:StandBy_DBControl'

[code]....

Step 1 Create the Oracle Service for StandBy DB "StBy1DB' and Create the Standy DB Password file.

ORADIM -NEW -sid StBy1DB -intpwd passwd -startmode manual

Step 2 Shut down the primary database.

SQL> SHUTDOWN IMMEDIATE;

Step 3 Copy the datafiles to 'E:StandBy_DBData'. (including Temp01.dbf is it Right?)

Step 4 Restart the primary database.

Restart the primary database:

SQL> STARTUP;

Step 5 Create a Control File for the Standby Database (Should this command be given in Mount Status or Open Status?)

From Primary database,

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'E:StandBy_DBControlStByControl01.ctl';

Step 6 Prepare the Initialization Parameter File to be Copied to the Standby Database

SQL> CREATE PFILE='E:StandBy_DBPFileinitStBy1DB.ora' FROM SPFILE;

Step 7 Set Initialization Parameters on a Physical Standby Database

db_cache_size=444596224
java_pool_size=4194304
large_pool_size=4194304
shared_pool_size=150994944
streams_pool_size=0

[code]....

Now When I type the following command, it throws an error:

SQL...> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Application Express :: Hyperlink Column With Link Parameters As Region Column Values

Apr 10, 2013

On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.

I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.

So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:

a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?

The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.

---

"Application Express 4.2.1.00.08"

View 2 Replies View Related

Reports & Discoverer :: REP-1241 Circular Column Dependency Originating With Column?

Jul 13, 2010

I receive the error,

REP-1241 Circular column dependency originating with column 'cenvat_opening'

I have the following return statement for a column total_credit;

function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;

Now i am using this total_creditFormula for the error formula column 'cenvat_opening'

function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN

[code]...

here the formula column 'total_credit' has the following return statements;

RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)

so how can i use this formula column? is there any other option to return the value in the same column.

View 6 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related







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