SQL & PL/SQL :: ORA-00922 - Missing Or Invalid Option?

Feb 26, 2013

create or replace p_aggop(dno number, maxi number,mini number,avgi number,tot number,cnt number) is
cursor c1 is select * from emp1 WHERE DEPTNO=dno GROUP BY DEPTNO;
v_emp1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v_emp1;
select min(v_emp1.sal),max(v_emp1.sal),avg(v_emp1.sal),sum(v_emp1.sal),count(*) into mini,maxi,avgi,tot,cnt from v_emp1;
exit when c1%notfound;
Dbms_output.put_line('EXECUTED ');
end loop;
dbms_output.put_line('The minimum salaray in dept is :'||v_emp1.mini);
dbms_output.put_line('The maximum salaray in dept is :'||v_emp1.maxi);
dbms_output.put_line('The average salaray in dept is :'||v_emp1.avgi);
dbms_output.put_line('The total salaray in dept is :'||v_emp1.tot);
dbms_output.put_line('The total members in dept is :'||v_emp1.cnt);
CLOSE C1;
end;

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-00922 Missing Or Invalid Option

Oct 18, 2010

I am creating a stored procedure to create a table during run time using 3 passed in parameters to build the table name. Oracle is giving me the ORA-00922 missing or invalid operation error message on the EXECUTE IMMEDIATE statement of my stored procedure. I am new to writing stored procedures in Oracle. I am pasting a copy of the stored procedures. I have also changed the names of the field names to be generic so I can post the code in this forum.

CREATE OR REPLACE PROCEDURE createTable(PARAMETER1 in string, PARAMETER2 in string, PARAMETER3 in string) IS

TABLE_NAME NVARCHAR2(50);
QUERY_STRING LONG;
BEGIN

[Code]....

View 29 Replies View Related

ORA-00922 / Missing Or Invalid Option

Mar 3, 2011

i have managed to put in 2 link tables but I cannot seem to manage the last.

I am getting the error:

ORA-00922: missing or invalid option

----LINK TABLE----

Code Used:

CREATE TABLE cs-lt
(
ID VARCHAR(4),
CASE_ID VARCHAR(4),
SOLICITOR_ID VARCHAR(4),
FOREIGN KEY ( case_id ) REFERENCES case(case_id), FOREIGN KEY ( solicitor_id ) REFERENCES SOLICITOR(SOLICITOR_ID), PRIMARY KEY ( CASE_ID, SOLICITOR_ID )
);

And the code for the other relating tables:

------CASE TABLE----------

CREATE TABLE "CASE"
( "CASE_ID" VARCHAR2(4),
"CASE_NAME" VARCHAR2(30),
"CASE_START_DATE" NUMBER(12,0),
"CASE_DESC" VARCHAR2(50),
"CONTRACT_ID" VARCHAR2(3),
"SOLICITOR_ID" VARCHAR2(4),

[code]....

View 12 Replies View Related

SQL & PL/SQL :: Creating Table - Missing Or Invalid Option Error

Jun 10, 2011

I am getting error while trying to create a table

SQL>
SQL> CREATE OR REPLACE TABLE CEE_OSPCM_DETAILS
2 (
3 ORD_NBR VARCHAR2(10) NOT NULL,
4 ORD_APPNDX NUMBER(2) NOT NULL,
5 FRCOI_NBR NUMBER,
6 PRINT_NBR VARCHAR2(4),

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Creating Partition Table - Missing Or Invalid Option

Aug 4, 2011

I got a error while creating a partition table

I did following steps

SQL> show user
USER is "ROSE"
SQL>
SQL> create table sales (year number(4),
2 product varchar2(10),amt number(10,2))
3 partition by range (year)

[code]....

ORA-00922: missing or invalid option

SQL>
SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS

[code]....

View 8 Replies View Related

ORA-38302 / Invalid PURGE Option

Sep 24, 2012

I want to purge scott's recycle bin as user "SYSTEM". Because I do not know the user's password. How do I achieve it?

SQL> purge scott.recycle bin;
purge scott.recycle bin
*
ERROR at line 1:
ORA-38302: invalid PURGE option

View 21 Replies View Related

SQL & PL/SQL :: ORA-01735 / Invalid ALTER TABLE Option ORA-06512

May 12, 2011

I ran the following PL/SQL Code & I am getting the following Error:

Drop table MODIFIEDTABLE
/
Commit
/
create table MODIFIEDTABLE(TABNAMES varchar2(100))
/
DECLARE

[code].....

RESULT:

Table dropped.

Commit complete.

Table created.

DECLARE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 12

View 2 Replies View Related

Reports & Discoverer :: ORA-02248 - Invalid Option For ALTER SESSION

Aug 25, 2013

I got this error when try to connect to the database using discover administrator or user edition

ora - 02248 : invalid option for ALTER SESSION

i can connect to the database with sqlplus with no problem.

View 4 Replies View Related

ORA-02250 / Missing Or Invalid Constraint Name

Jul 13, 2011

I wrote a function:

create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2 authid current_user is
l_search_condition user_constraints.search_condition%type;
begin
select search_condition into l_search_condition
from user_constraints
where constraint_name = p_cons_name;
return l_search_condition;
end;

and ran it...then when I write the below command:

alter table employee drop constraint
select constraint_name from user_constraints where table_name = 'EMPLOYEE' and
get_search_condition(constraint_name) like '%(( ROLL_NUMBER > 0 ))%'
;

The system throws:

Error starting at line 1 in command:
alter table FL_ITEMSITE drop constraint
select constraint_name from user_constraints where table_name = 'FL_ITEMSITE' and
get_search_condition(constraint_name) like '%(( SS_QTY > 0 ))%'
Error report:
SQL Error: ORA-02250: missing or invalid constraint name
02250. 00000 - "missing or invalid constraint name"
*Cause: The constraint name is missing or invalid.
*Action: Specify a valid identifier name for the constraint name.

i want to drop constraint without specifying its name. i want the system to fetch it dynamically.

View 1 Replies View Related

Forms :: How To Get Browse Option

Nov 1, 2013

I want browse option in oracle forms.

View 3 Replies View Related

SQL & PL/SQL :: Option For Temp Table?

Feb 18, 2010

What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.

Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.

View 3 Replies View Related

SQL & PL/SQL :: Returning Values With MORE Option

Jan 19, 2012

Imagine I have the following scenario:

CREATE TABLE AAA_PLACE
(COD_PLACE NUMBER,
PLACE_NAME VARCHAR2(50 BYTE)
)
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (1, 'Munich');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (2, 'Lisbon');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (3, 'Monaco');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (4, 'Madrid');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (5, 'Milan');

Imagine I want to retrieve those records in only 3 distinct rows, like this:

Munich
Madrid
Others

Is there any way I can do this with an simple Statement?

View 6 Replies View Related

SQL & PL/SQL :: Multiple Filter Option

Apr 2, 2013

in many shopping cart applications they are providing multiple filter option.

suppose filter by brand,fitler by price ranges,filter by color,filter by shape etc..

so how they are achieving the performance.because lot of filter if applied then it should get slow execution.

how to achieve this.I tried as follows.

alter session set nls_date_format = 'dd-MON-yy';

CREATE OR replace PROCEDURE Multiple_filter (p_empno VARCHAR2,
p_ename VARCHAR2,
p_from_hiredate DATE,
p_to_hiredate DATE)

[Code]....

View 18 Replies View Related

SQL & PL/SQL :: Set MV As Incremental Refresh Option?

Aug 19, 2011

i already created metalized view

now i want to set MV as incremental refresh option?

is it possible to set this attribute after mv created..?

View 1 Replies View Related

Use Split Partition Option?

Jan 28, 2013

I am using 11.2.0.2 database.

I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.So I want to use split partition option through the below statement,

alter table
tablename
split partition
partmax at (XXX)
into
(partition partXXX, partition partmax);

I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.I have to do this on produciton. I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.

View 6 Replies View Related

Parallel Query Option

Oct 23, 2013

Just a general query on parallel query.  My customer having 4 cpus and running the database in 11.2.0.3 in AIX 5.3(One is in AIX 6.1). Under which circumstances, we can propose to user parallel query options.

View 2 Replies View Related

Loadjava Command With Encoding Option

Jan 5, 2011

I want to know what syntax I should use for encoding option with loadjava command?

here is the scenario--

Our oracle database is already compatible with UTF-8 characters i.e. charset encoding is set as 'AL32UTF8'. I am able to save Chinese characters in the database however when I trigger a stored procedure which eventually use a loaded java class I get an error.

I am suggested to provide -encoding option while running 'loadjava' command but I don't know what syntax I should provide for 'UTF8' encoding.

View 2 Replies View Related

SQL & PL/SQL :: Better Option To Use When The Quote Needs To Be Concatenated To A Varchar2 Value

Apr 13, 2006

which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.

In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)

However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''

View 24 Replies View Related

COMPRESS Option Along With CREATE TABLE

Sep 26, 2013

I am trying out the COMPRESS option along with CREATE TABLE. just wanted to understand if we need the "Advanced Compression" option enabled. Does this require extra license?

View 2 Replies View Related

SQL & PL/SQL :: Goto Option Not Working In EXCEPTION?

Jan 31, 2013

I have the code like below. I need to execute the lines after the error came in middle.

Simply I need the output as follows

1
2
3
4
5

declare
a number;
b number:=10;
begin
dbms_output.put_line('1');
dbms_output.put_line('2');
dbms_output.put_line('3');

[code]....

But the label (abcd) cannot be used in Exception.

View 10 Replies View Related

Application Express :: Page Zero - Option Not Available

Sep 5, 2012

Trying to create a "page zero". Usually in previous releases this is done executing the following:

1. On the Workspace home page, click Application Builder.

2. Select an application.

3. The Application home page appears.

4. Click the Create Page button.

5. For Select a page type, select Page Zero.

Note that the Page Zero option only appears if the application does not have a page zero.

6. Click Finish.

Since the upgrade to 4.2 this option is not available or has move. (I do not have a page zero already created.)

View 2 Replies View Related

Replication :: Materialized View - Using Refresh Option?

Dec 22, 2005

How can i use refresh option as 1 hr in creation of materialized view, Can i use unoin all in the materialized view and other clause like CONNECT BY PRIOR .

View 22 Replies View Related

Reports & Discoverer :: Blink Option In PDF Report

Jun 27, 2011

i m using oracle 10g pdf reports . is there any provision in reports ?

if i type any text on the report then it blink on the report

like

note := a for cancellation

it blink on the report

View 1 Replies View Related

SQL & PL/SQL :: Does Force View Option Works In Oracle 11g

Aug 13, 2010

when i am trying to excute below query.. it says ORA-00942: table or view does not exist.

create or replace force view v2 as select * from employee;

View 11 Replies View Related

Client Tools :: Display Option In Sqlplus

Apr 12, 2010

I m currently written a sqlplus query and executing as below

sqlplus -s << !
set head off pages off trimspool on termout off
spool sysdate.txt
select sysdate from dual;
spool off
!

With this query the sysdate is getting written in the sysdate.txt but it is also getting printed in the screen/terminal., which I don't need to. Is their any options available for that.

Note: I came to know that "Termout off" will work only when we execute the query from a file and as a result I can still see the sysdate getting displayed in the screen.

View 3 Replies View Related

Gathering Statistics With Cascade Option Is Slow

Oct 9, 2012

The database is 11.2.0.3 on a linux machine. I issued the following command, but the session was a little slow. The table size is about 50 GB and has 3 indexes. I specified "degree=8" for parallel processing.

When gathering statistics on the table , parallel slaves were invoked and gathering statistics on the table has finished fast enough. However, when it goes to gathering statistics on the indexes, only one active session was invoked, and thus "degree=8" option was ignored.

My question is :

Do I need to use dbms_stats.gahter_index_stats instead of "cascade" option in order to gather statistic on the indexes with parallelism?

exec dbms_stats.gather_table_stats(ownname=>'SDPSTGOUT',tabname=>'OUT_SDP_CONTACT_HIS',estimate_percent=>10, degree=>8 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE)

View 2 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

Security :: ORA-01932 - ADMIN Option Not Granted For Role

Aug 23, 2011

When i try to grant role to user , i get following error.

SQL>
SQL> GRANT MAINTAIN_TEO TO CEE_WIRELINE;
GRANT MAINTAIN_TEO TO CEE_WIRELINE
*
ERROR at line 1:
ORA-01932: ADMIN option not granted for role 'MAINTAIN_TEO'
SQL>

View 1 Replies View Related

Backup & Recovery :: Open Database With ResetLogs Option

Mar 14, 2012

When we open the database with ResetLogs option. Then in future is there a possibility of restoration from old log sequences.

View 5 Replies View Related

Reports & Discoverer :: How To Disable Printing Option In Oracle 10g

Sep 17, 2012

By setting the parameters in set_report_object_property parameters we can disable the printing option in 11g. Do we have some kind of option , to do the same in 10g.

View 1 Replies View Related







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