Create Schema That Needs To Be Converted Using Non-oracle Syntax

Jan 27, 2011

I am new to oracle and sql in general, I received an oracle create schema that needs to be converted using non-oracle syntax. I have never seen this syntax before.

What does the following syntax mean?
CODE,line_status(1:20) char(2) null

CODE,file_line(1:6) char(40) null

View 4 Replies


ADVERTISEMENT

OLE DB :: SQL Server Create Table Syntax?

Jun 28, 2012

am new to SQL server. I have below statement what is ON [PRIMARY]. I want to write a similar create script to be written for Oracle & need to understand the importance of ON [PRIMARY]

Create table [emp]
(
[e_name] [varchar] (255) NOT NULL,
[e_sal] [decimal] (20,0) NOT NULL
) ON [PRIMARY]

View 1 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.

How could I do this without using export import. I am using TOAD 9.0.1.

View 10 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Fire Whenever Any User Will Access Schema Of Oracle

Oct 31, 2011

I would like to create a trigger that will fire whenever any user will access to a Schema of Oracle DB (for each and every login). regardless the access will be through an application or SQLPLUS and this trigger must insert the below information into a table.

1) IP address
2) Machine Name
3) login time
4) logout time
5) name of accessed schema

writing this trigger and creating the table that will hold the required data.

View 6 Replies View Related

Server Administration :: Create / Drop User / Schema In Another Oracle Database Through Apex

Jul 7, 2012

Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.

View 12 Replies View Related

SQL & PL/SQL :: Syntax For Oracle Statements

Apr 26, 2011

The link from where I can get syntax of all the commands available in oracle?

View 6 Replies View Related

Update With Join Syntax In Oracle

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?

View 4 Replies View Related

SQL & PL/SQL :: Syntax For Optional ALL Parameter In Oracle SQL

Apr 17, 2012

I am trying to pass an Optional 'ALL' Parameter to Oracle in the SQL statement below...

USER.STATUS can be either 0 or 1 in the Source-Data (Inactive or Active). However, the :P_STATUS Parameter can be either 0, 1 or 2 (ALL).

I tried an IF/THEN/ELSE statement in the SQL below - but it doesn't work as is.

Gives and "ORA-00920: invalid relational operator" error...
-------------------
SELECT
i.LAST_NAME SURNAME,
i.FIRST_NAME GIVEN_NAME,
DECODE(u.STATUS, 1, 'Active', 0, 'Inactive') STATUS,
u.STATUS STATUS_CODE
[code].......

View 1 Replies View Related

Translating Query From SQL Server To Oracle Syntax

Sep 10, 2009

Translate following SQL query from SQL Server syntax to Oracle syntax.

SELECT ID,
[LMT(MTR)] = MAX(case when TYPE = 'LMT' then VALUE end),
[AAD(KGM)] = MAX(case when TYPE = 'AAD' then VALUE end),
[VOL(MTQ)] = MAX(case when TYPE = 'VOL' then VALUE end)
FROM yourtable
GROUP BY ID

View 2 Replies View Related

Forms :: How To Check Syntax Of Resulting Insert In Oracle 6i

May 23, 2013

I have a package,

PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),

[code]...

Package body is

PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
BEGIN

[code]...

what i want is: 'output vcInsert to a forms-item and check the syntax of the resulting insert'. How can I do this

View 7 Replies View Related

Sequence Name In Oracle Database Schema As Well As Timesten Schema?

Jan 13, 2011

How to use same oracle sequence name in Oracle Database schema as well as timesten schema?

View 1 Replies View Related

Security :: Import XLS File That Converted Into CSV

Jul 27, 2010

I'm trying to import a xls file that I converted into a csv. I have a notes column that has carriage returns in it. This is causing SQL LDR to error out with the following error:

second enclosure string not present

It is only grabbing up until the first carriage return.how I can get it to load?

View 1 Replies View Related

SQL & PL/SQL :: Timestamp Column Whose Values Are In CET Timezone Needs To Be Converted To EST

Mar 6, 2012

I have a requirement in which a particular a timestamp column (date1) whose values are in CET timezone needs to be converted to EST and day light savings should be taken care of in the conversion logic. I should not use any ddl statements for altering the timezone and all.

View 9 Replies View Related

PL/SQL :: Verify If Salary In Numbers / Decimals Is Converted Into Words?

Aug 30, 2013

How do i check in Table B whether it is converted correctly into words taking input or reference from table A

Consider below example:-

Table A Table B
$125 Dollar One Hundred twenty Five only
$45,542 Dollar Forty Five Thousand Five Forty Two Only
$145.56 Dollar One Forty Five and fifty six cents Only
$145,253
$35,256.65
$560,250.67

View 10 Replies View Related

PL/SQL :: Automatic Rounding Off Number Datatype While Converted To VARCHAR2

May 29, 2013

I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".

1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.

2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted

insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted

3) select * from test18;

COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296

4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter

select col1 || ',' || val as record_string
from test18;

RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296

"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.

Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296

View 10 Replies View Related

SQL & PL/SQL :: Create Tables From Different Schema?

Mar 18, 2012

I have created 2 different databases and each have their own schema user for eg we have database ALIVE with user allive and the other database RLIVE with user rllive , actually we have implemented new module in ALIVE database and in the process we created many tables , synonyms , index and other objects now we want to list out all the tables ,sequences as scripts which are not present in RLIVE and create them in RLIVE as new objects.

View 3 Replies View Related

Create One Trigger Before DML On All Schema Tables

Nov 28, 2011

We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install

I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).

So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.

Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)

View 11 Replies View Related

Security :: Create Various Users On A Schema

Nov 19, 2010

I do a schema in oracle ... an m my question is if I can create 2 o more users for access to a schema..for example i have schema Project and i need that one administrator and 2 more users with others permission.

View 3 Replies View Related

Security :: Create Table Others Schema

Oct 18, 2010

how can i grant a user permission to create tables and indexes in other user schema.Grant create table permits the user to create tables in his own schema which is part of Resource role.Grant any table will permit him to create table in any schema including system which i don't want.

View 8 Replies View Related

Security :: How To Create User Under Schema

Dec 5, 2012

To make users under one schema.

1) user should have access of all objects with limited privileges.

How can i make it under schema?

View 3 Replies View Related

Enterprise Manager :: How To Create XML Schema Using OEM

Mar 28, 2011

How to create & register XSD schema using OEM ?

View 1 Replies View Related

How To Create A User (Owner Of Coeus Schema)

Dec 9, 2010

On the database instance where you want to install Coeus, create a user who will be the owner of Coeus schema. This user should have at least the following privileges. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE. If you choose, you can grant DBA role to this user.

Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.

View 3 Replies View Related

SQL & PL/SQL :: How To Create HR Schema Table Using Stored Scripts

Jan 3, 2012

I dropped HR schema from database by mistake.

How to create HR Schema and its all tables using oracle stored scripts.

My operating system is Windows XP and oracle version is 10g.

View 9 Replies View Related

PL/SQL :: Reverse Update - Create Synonym In Different Schema

Aug 8, 2012

I have table a in database 1 . Now i have created the MV for table a in database 2.

then i am going to create a synonym in different schema in the database 2.

Now i want to update the synonym in database 2 the same as to refresh in table a database 1.

How to achieve this??

View 2 Replies View Related

Performance Tuning :: Converted Non-partitioned Table (1 Million Data) Into Range-partition

Mar 28, 2013

As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .

View 9 Replies View Related

PL/SQL :: Cannot Create Table After Change Schema With ALTER SESSION

Sep 1, 2012

SQL> ALTER SESSION SET CURRENT_SCHEMA = CLA_T3;

Session altered.
SQL> select sys_context('USERENV','SESSION_USER') current_user,
2 sys_context('USERENV','SESSION_SCHEMA') current_schema
3 from dual
4 ;

CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
CSR_ETL
CLA_T3

SQL> set linesize 300;
SQL> /

CURRENT_USER
----------------------------------------------------------------------------------------------------
CURRENT_SCHEMA
----------------------------------------------------------------------------------------------------
CSR_ETL
CLA_T3

SQL> create table cla_t3.test (r number, b char(2));
create table cla_t3.test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> create table test (r number, b char(2));
create table test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges

After Setting current schema to 'CLA_T3', I am unable to create table in cla_t3 schema.

View 5 Replies View Related

Export/Import/SQL Loader :: Trying To Create 3 Schemas From One Schema

Aug 23, 2012

DB version : 11.2.0.2 Enterprise Edition
Platform : RHEL 5.6

I have an expdp dump of a schema (HRTB_AP_PROD). I wanted to create 3 schemas from this dump in one go. So i tried this

## The parfile I used

DIRECTORY=DPUMP_DIR
DUMPFILE=HRTB_AP_PROD%u.dmp
LOGFILE=TheThreeSchemas-imp.log
remap_schema=HRTB_AP_PROD:HRTB_AP_DEV1
remap_schema=HRTB_AP_PROD:HRTB_AP_DEV2
remap_schema=HRTB_AP_PROD:HRTB_AP_DEV3
exclude=statistics
parallel=2

nohup impdp '/ as sysdba' parfile=impdp-aug23.par &But i encountered

ORA-39046: Metadata remap REMAP_SCHEMA has already been specified.When I googled it found the following link in which Dean Says , it is not possible.

Re: one dump file inport into multiple schema

So, I had to run 3 separate imports (impdp) to do this.

This is a bit wierd. I am surprized that Oracle guys haven't done anything about this . This is like DB2 !

View 2 Replies View Related

SQL & PL/SQL :: Create Block Of Code That Would Search In All Tables In Schema For Column_name

Jun 14, 2011

I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.

begin
for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop
begin
execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null';
commit;
[code]........

View 6 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Create Procedure To Create User In Oracle

Jan 19, 2012

I need to create PROCEDURE to create user in oracle

CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......

Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8

Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN

i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )

View 5 Replies View Related







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