PL/SQL :: Unable To Create Logic Sequence?

Oct 23, 2012

create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);

in place of 8 there can be any number between 1 to 100

if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on

i tried as below

select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;

but i cant write case for again and again upto nth

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Create A Sequence

Sep 11, 2010

Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.

View 5 Replies View Related

SQL & PL/SQL :: Create A Sequence In DML Trigger?

Apr 12, 2013

i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.

create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;

[code]...

trigger created sucessfully.And i try to delete data from the test

delete from test where id=5;

Output:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'

View 4 Replies View Related

Create Sequence Using Subselect / Value From Select

Jul 8, 2008

CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1

i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?

View 2 Replies View Related

Server Utilities :: How To Create Sequence

Jul 16, 2011

Every time sequence should be start from 1.can i know how to create sequence? for ex:-once i have uploaded 100 records this time sequence is generating from 1 to 100.next time i will upload again 100 records this time sequence is generating(Starting) from 200 to 300.but it should be generate from 1. how? i have created like this

CREATE SEQUENCE XX_SEQUENCE
MINVALUE 1
MAXVALUE 99999999999
START WITH 1
INCREMENT BY 1
CYCLE
NOCACHE;

View 2 Replies View Related

Forms :: How To Create Sequence In Form

Jan 13, 2013

I know how to create sequence in the database.But I want to create this sequence from the form by giving the name of the sequence from a filed in the form.

View 1 Replies View Related

Possible To Create Sequence Inside DML Trigger

Apr 12, 2013

i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.

create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '

end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;

Output:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'

View 9 Replies View Related

PL/SQL :: Create Sequence Using EXECUTE IMMEDIATE Statement?

Dec 14, 2012

Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.

DECLARE
TEMP   VARCHAR2(20);
BEGIN
TEMP     := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;

This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.

View 10 Replies View Related

SQL & PL/SQL :: How To Auto Increment Number - Create Sequence And Trigger

Nov 26, 2012

I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.

So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....

I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.

So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?

View 12 Replies View Related

Create A Sequence For Primary Key To Simply Auto-increment By Default Of 1

Mar 14, 2005

I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.

Here's a snippet from my script:

create table image
(
image_id int NOT NULL,
source_id int NOT NULL,
CONSTRAINT image_id_pk PRIMARY KEY (image_id),
CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
);

Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?

View 5 Replies View Related

SQL & PL/SQL :: Unable To Create DB Package?

Mar 1, 2012

I´m having a trouble creating a db package. I´ve changed my job and now i´m working full on DB instead of Forms Developer.

So my trouble is that i want to create some like a global cursor on DB package, i know you can create global variables and use it on all the procedures/function inside the package. But how about cursors?

My problem: I dont´know how to declare into the spec. I don´t know how to use it on body.

My actual

I have the structure below:

PackageSpec:
CREATE OR REPLACE PACKAGE [i]MyPackageName[/i]
IS
CURSOR [i]myGlobalCursor[/i](par1 IN VARCHAR2, par2 IN DATE)(
);
PROCEDURE [i]MyProcedure1[/i](par1 IN VARCHAR2, par2 VARCHAR2);

[code]....

View 13 Replies View Related

Unable To Create User On Oracle DB

Nov 18, 2010

I have encountered a problem with oracle db where by i'm unable to create a user.

oracle@kapstadt4:/export/home/oracle/scripts> ls -l
total 12
-rw-rw-rw- 1 oracle dba 1115 Sep 10 15:34 crdb.sql
-rwxrwxrwx 1 oracle dba 429 Sep 10 15:39 create_advantage_user.sql
-rw-rw-rw- 1 oracle dba 597 Sep 10 15:38 create_advantage_user_objects.sql
-rw-rw-rw- 1 oracle dba 408 Sep 10 15:37 create_advantage_user_roles.sql
-rw-rw-rw- 1 oracle dba 976 Sep 10 16:04 tbs.sql
oracle@kapstadt4:/export/home/oracle/scripts> cat create_advantage_user.sql

-- creates an advantage database user
-- the role 'advantage_admin_user' must be available!

define user_name = &1
rem define default_table_space = &2

create user &user_name
identified by &user_name#123
default tablespace IN_DEV
temporary tablespace TEMP;
grant connect to &user_name;
grant resource to &user_name;
grant advantage_admin_user to &user_name;
connect &user_name/&user_name#123
@create_advantage_user_objects.sql

[Code] ........

View 5 Replies View Related

Unable To Create Public Synonym

May 22, 2011

I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database? In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

View 2 Replies View Related

SQL & PL/SQL :: Unable To Create Trigger Dynamically

Dec 3, 2010

I have created a procedure to build trigger dynamically using Dynamic SQL. Here procedure created successfully, when we execute the procedure to build trigger getting the following error.

Note: We are able to create a Trigger, but it is INVALID. if we get the code of a trigger and execute, trigger created a successfully with Valid Status.

ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APPS.CREATE_TRIGGER", line 28
ORA-06512: at line 2

SQL*Plus internal error state 2087, context 47:0:0
Unsafe to proceed

View 2 Replies View Related

Unable To Create Database On Windows 7

Apr 3, 2013

I installed Oracle 10g XE on windows7 to create a database i opened "start database command interface" and created an SID and followed some steps afterwards i am facing "TNS ADAPTER NOT FOUND"

View 7 Replies View Related

Unable To Create Audit Trail File?

Jun 29, 2012

problem with oracle 10 g . i started service through server i got some error

ora-09925 unable to create audit trail file.

3 files are missing in admin folder these are three files adump, dpdump, pfile was missing i have not taken any backup also.

in admin file also the database folder name was missing.

View 2 Replies View Related

Server Administration :: Unable To Create Database?

Aug 21, 2013

I am installing Oracle 12c R1 on Windows 2012. The windows is configured as a domain controller. I am logged in as the domain administrator.I created a normal domain user -- DomainNameORACUSER. I provided this as the existing user id during Oracle installation.

The software installs fine, but when creating the DB using the database configuration assistant, an error is shown "error in process:...inoradim.exe. Enter password for Oracle service user: DIM-00019: create service error. O/S-Error: (OS 87) The parameter is incorrect". The DB is not created.

I skipped the db creation and completed the installation. When tried to create the db using the database configuration assistant after installation, I received the same error.I performed the same installation on Windows 8 and was successful in creating the db.

View 5 Replies View Related

Replication :: Unable To Create Materialized View

Mar 13, 2008

I have to create a materialized view for a table which does not have index on any field.

While creating a Mview i am getting an error "TABLE DOES NOT HAVE THE PRIMARY KEY CONSTRAINT".

application developers do not want to create an index on the base table onto which MView is to be created.

is there any way to create a materialised view for the table without index, or is it necessary to have the index on the base table before creating MView on it.

View 1 Replies View Related

SQL & PL/SQL :: Unable To Create Index On Large Table

Sep 30, 2012

I am trying to create a new index on large table of size around 100GB. but i am getting the following error:

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP.

temp tablespace size is : 20 GB.

does it mean that the whole index will be created at temp tablspace first?

View 3 Replies View Related

Installation :: (INS-32012) Unable To Create Directory?

Feb 20, 2013

i am trying to install oracle client on D:User-Applicationsoracle.

But i am getting this error: [INS-32012] Unable to create directory: D:User-Applicationsoracle.

i am installing on windows box my previous installations are sucessfull but i dont know why i am getting this

View 4 Replies View Related

Unable To Create INITIAL Extent For Segment In Tablespace

Jun 12, 2012

ORA-01658: unable to create INITIAL extent for segment in tablespace

I am getting above error when a batch job is running.Its insterting data in tables. I checked free space i dont know y its not using avalable space.

Database Version: 11202

select sum(bytes)/1024/1024/1024, sum(maxbytes)/1024/1024/1024 from dba_data_files where tablespace_name='test_data';

sum(bytes) sum(maxbytes)
---------------- -------------
60 0

select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='test_data';

sum(bytes/1024/1024/1024)
----------------------
50

View 2 Replies View Related

Server Administration :: Unable To Create Control File

Jun 3, 2010

I am not able to create control file from scrip . it showing following error

ORA-01081: cannot start already-running ORACLE - shut it down first
CHARACTER SET WE8ISO8859P1
*
ERROR at line 19:
ORA-02236: invalid file name

View 4 Replies View Related

Forms :: FRM-30087 - Unable To Create File (Developer 9i)

Jun 5, 2003

i have successfully compuled and run my Developer9i form on the web but when i make a slight change in the form (add any control or do anything with form) and then recompile it it gave me error: FRM-30087: Unable to create form file why forms show this error and how it can be solved?

View 5 Replies View Related

Enterprise Manager :: Unable To Create EMCA In Oracle 11g

Mar 28, 2011

I am using Oracle 11g Rel1 on Window 2003 Server R2 Enterprise Edition x64 with SP2. I am trying to configure DB console using command throuth emca. But I am getting the below mentioned error. Even I tried after manually deleted the repository then tried to create using emca command.

==========================================================
Mar 28, 2011 3:11:16 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 28, 2011 3:16:16 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at D:appAdministratorcfgtoollogsemcamonetaemca_2011_03_28_15_09_53.log for more details.
Could not complete the configuration. Refer to the log file at D:appAdministratorcfgtoollogsemcamonetaemca_2011_03_28_15_09_
53.log for more details.

C:Documents and SettingsAdministrator>
===========================================================

logfile attached for your reference.

View 1 Replies View Related

Server Utilities :: Unable To Create Public Synonym?

May 22, 2011

I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database?In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

View 3 Replies View Related

Server Utilities :: Unable To Create Control File?

Mar 8, 2011

I tried a lot to load data to table from excel(.csv) using sql*loader the oracle version of sql*loader doesn't support the control file created using notepad(.ctl).Though i given a filename with extension as .ctl it seems as a .txt file. Is there any alternate way to create it?

View 3 Replies View Related

Unable To Create Database (Oracle 12c R1 12.1.0.1.0 / Windows 2012 )

Aug 30, 2013

I have been trying out the new 12c on Windows 2012 Essentials with the same type of errors.  The OraDim step fails and cannot create the services.  It fails a bit differently if I choose "Create New Windows User", choose a "Use Existing Windows User", or choose the Windows Built-in user. 

View 3 Replies View Related

Forms :: Unable To Create Object For Excel 2007 / Windows 7?

Aug 11, 2010

The below Oracle Forms code works fine for Excel 2003/Windows XP/IE6 but doesn't work for Excel 2007/Windows 7/IE8.

*********************
application := Client_OLE2.CREATE_OBJ('Excel.Application');

Client_OLE2.SET_PROPERTY(application,'Visible','TRUE');

workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');

args1 := Client_OLE2.create_arglist;
************************

There is no error while executing the form, by debugging we found that the control skips all the above code.

I'm using Oracle forms 10.1.2.3 and jre 1.6.0_16.

View 3 Replies View Related

SQL & PL/SQL :: Unable To Create Materialize View (Refresh On Fast Commit)

Nov 19, 2010

I am not able to create materialized view with 'Refresh Fast on Commit' when the table is residing on the another user environment, I am not able to find the reason why?

You can re-generate the scenario at your end with the following code-

--Login to System-
create user manu identified by manu;
grant connect, resource to manu;
create user arpit identified by arpit;
grant connect, resource to arpit;
grant create materialized view to manu;

[code]....

but when I am doing it with on demand options, materialized view is being created-

--Login to Manu-
create materialized view arpit_view
build immediate
refresh on demand
as
select * from arpit.arpit
/

View 5 Replies View Related

Export/Import/SQL Loader :: Unable To Create Master Table

Jul 2, 2013

While exporting on 11g xe on windows 32, i got messageORA-31626: job does not existORA-31633: unable to create master table

"System.Sys_export_schema_06"ORA-06512: at "SYS.DBMS_SYS_ERROR", LINE 95ORA-06512: at "SYS.KUPV$FT", line 1020ORA-01658:

unable to create INITIAL EXTENT FOR segment in tablespace SYSTEM I checked and found there is no pace in table space 600(allocated) 600(used mb) 0(free mb) 1(used) Q1) Is there any table which i can empty to free some space in system schema.Q2) Is there any way to shift some space from other table space to system becuse i have free space inother table space.i could find many segments of system table space, can i  delete few of them, if they are not used ?

View 7 Replies View Related







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