I have a directory defined as TDM_IN where a directory has been created. There is a table which has been created as
create table abc.IFP_GRP_D_INT
(
IFP_GRP_DIM_KEY VARCHAR2(10),
.....
[Code]...
Now how can i select the records from this table?
I am trying to do the following-
CREATE DIRECTORY my_dir AS 'd:oraclefiles';
GRANT READ,WRITE ON my_dir TO PUBLIC;
The above throws up an error-
ORA-00942: Table or view does not exist
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I am new in external table so i have tried following cmd.
create directory dir_1 as 'E:ora_dirt' ;
grant read, write on directory dir_1 to HR;
select * from all_directories;
create table emp_ext
(emp_id number,
emp_name varchar2(30)
[code]...
since I am not able to see DIR_1 in E: drive due to which i havnt created 'emp.dat' file and on executing select on external table i m geting expected error *"ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source: EMP_ID"*
how to create that file in directory "DIR_1" .
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedI am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
View 4 Replies View Relatedhow to i insert a constraint of words into the table example below.I am new to this stuff man.
Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);
The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.
How am I suppose to enter this constraint value into the creation of this table?
If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;
/
ALTER TRIGGER "P_M_YES" ENABLE
/
I get an invalid trigger specification.
I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:
Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....
How do I return the table (referenced by a cursor) without creating objects?
I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.
Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...
If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:
select * from
(select claim_nbr,sum(amtpay) paysum from my_table
group by claim_nbr
order by claim_nbr) m,
(select claim_nbr,sum(amtpay) paysum from new_table
group by claim_nbr
order by claim_nbr) n
where
m.claim_nbr = n.claim_nbr and
m.paysum <> n.paysum;
It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.
Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.
Here is the backwards way around using min that drops whole claims sometimes but works fine other times:
CREATE table new_table
as
(select claim_nbr,other data...,amtpay,ymdeff,ymdpaid
from
(select claim_nbr,other data... ,sum(amtpay) amtpay
from my_table
group by claim_nbr,other data...
[Code] ...........
While creating external table how can I specify a particular decode condition for a date field that comes in as '2099-99-99' i want to change it to '2099-01-0001', how i can translate it
I already have this in the access parameters..
Incoming_DATE CHAR(20) DATE_FORMAT DATE MASK "YYYY/MM/DD"
I have a query ,
I need to create a structure DATABASE=>SCHEMA=>TABLE as
DB=>SC=>EMPLOYEE ...but after connecting database i could create table only user my user schema(own schema)only . I want to create a new schema called SC as public and need to create a table .
While creating temp backup table it shows error ORA-00904 'invalid identifier'
CREATE TABLE xxom_valvelink_vlvs_temp AS
SELECT msie.inventory_item_id,FDFC.application_column_name,
flv.tag,
[Code]....
creating a sql table im getting error " ORA-00907: missing right parenthesis"
CREATE TABLE Campaign
(campaign_id NUMBER(5) PRIMARY KEY,
Account VARCHAR(20) NOT NULL,
SalesLead ID,
SCLead ID,
[code]....
the problem is "ID" and "ID+R", i need these to conect with other sql tables
I have an application that calls a procedure to perform some action.
I have a routine written to create multiple tables on the fly .But most of the time my script gets hanged while creating a table... But it gets executed once restarted. I dont know the reason for that contention why it get so....
Below is the line of codes....
CREATE TABLE TEMP_ABC12 NOLOGGING PARALLEL(DEGREE 20) TABLESPACE XYZ AS
SELECT /*+INDEX(BAL IDX_TRANSEOD_ACCTNO_TRANDTE) */ BAL.ACCOUNTNO,
TRUNC(CASE WHEN BAL.TRANSACTIONDATE > TO_TIMESTAMP('28-04-2010', 'DD-MM-YYYY') - 1 THEN TO_TIMESTAMP('28-04-2010', 'DD-MM-YYYY') ELSE BAL.TRANSACTIONDATE END)
- TRUNC(CASE WHEN BAL.LAG_DATE < TO_TIMESTAMP('28-04-2010', 'DD-MM-YYYY') - '30' THEN TO_TIMESTAMP('28-04-2010', 'DD-MM-YYYY') - '30' ELSE BAL.LAG_DATE END) AS DATE_DIFF,
BAL.LAG_EOD_BAL
[code].....
I'm trying to create a table 'a' with column name as 'flag' and datatype as 'boolean' ie. create table a(flag boolean);
is it possible in sql or pl/sql in oracle?
If its possible to create a table and also let me now inserting row and what boolean should return.
I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.
CREATE TABLE special_rate
(
rate_id VARCHAR(3),
rate VARCHAR(5),
rate_expiry_date NUMBER(12),
PRIMARY KEY( rate_id ),
FOREIGN KEY( contract_id ) REFERENCES contract(contract_id)
[code]....
That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.
creating sql query for producing result as per below from dual table.
SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc
from above query the result is
NOV-2011
but i need the result as per below
APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH
i WANTED TO SIMPLIFY THIS CODE SO THAT I NEED NOT DO ALL THESE manipulations but still get the result of
UNI_CUG_SITE,
Create Table CUG_SITE compress nologging As
Select C_Key, S_Key From A
group by C_Key, S_Key
/
commit
/
--Creating a table for all Single Sited Customer ---
[code]........
When i try to create a trigger , i ended up with error.
SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
I am creating a script to populate a test table. But I cannot auto increment the DATE column,
declare v_idnumber := 1 ; v_datedate; begin while(v_id < 100 ) loop v_date:= TO_DATE( '07-23-2012', 'MM-DD-YYYY'); INSERTINTO datetest (ID,startdate) values( v_id,v_date); v_date:= v_date +1 ; v_id:= v_id +1 ; endcommit;end;loop;
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
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]....
we are creating item_codes in our master table called om_item ,and then at one point of time every day we are shipping these newly created items based on date and tmie stamp as items.sql to our brance office and the person there will run these scripts there.Actually there are two persons involved in this process and i want to remove this manual intervention.All i need is to create a trigger for this insert statements and write it in text file as .sql.
create table om_item (item_code varchar2(20), item_name varchar2(60),item_cr_dt date)
insert into om_item values ('a','aaaa',sysdate);
commit;
insert into om_item values('b','bbbb',sysdate+1);
commit;
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]....
I'm getting an error as follows .
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
where
a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
and a.SRC_SYS_DESC = 'FACET');
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
*
ERROR at line 1:
ORA-00957: duplicate column name
In APEX I've always created form on report so as to have the list of records of a table and the possibility of inserting/updating every sinle record by means of the form on it, ok?I have an application concerning classic costumers, orders, ... with a table for every item.
The problem is that I have to gather all information ao as to produce a classic bill with a typical layout where you have on your left some descriptions and on your right an amount but of course the layout is not based on a classic table, do you mean?Do I have to create a table in which I'll add data and null values so as to get the layout desired
I have created a wallet (11g R2 OEL 5.5) using the OWM.Tried opening the wallet (encryption_wallet_location set in sqlnet.ora). then while creating a table it said the master encryption key is not present. Have created the master key using the following command.
alter system set encryption key identified by "Password";
Here the strange thing i observed is that when we create a wallet using the OWM, it asks for the password and when i open the same wallet the master key is not created and it allows the master key to be generated with the same password that i have created the wallet in the first place with the OWM, with any other passwords it says that the wallet is not open.
After creating the wallet and creating the master key... I have the following questions, and its becoming quite hard to find the solutions as well.
1. Can we have multiple encryption keys... say i want to encrypt a table or column with one key and other with an another key.
2. How many keys can we have for objects in the table? or can we have only one key and many certificates.
3. wallet created, and encrypted tables present, the wallet is not in auto open mode, but somehow the database open after it is shutdown, here no encrypted tablespaces are present.
4. while creating an encrypted tablespace the default storage (encrypt ) has to be added to the add tablespace clause.
based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.
The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:
SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
here is my demo SQL, which you should be able to execute against the SCOTT schema without any change
declare
type employee_numbers is table of emp.empno%type index by binary_integer;
type employee_names is table of emp.ename%type index by binary_integer;
type employees_record is record (empno employee_numbers, person_name employee_names);
records employees_record;
[code]...