I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
I am trying to insert the following values in a table called as inf_content
The table INF_CONTENT has following two cols (REQUEST_ID NUMBER(10,0) , FLTER_SET_XML "SYS"."XMLTYPE" NOT NULL ENABLE); =========================================================================================== INSERT INTO inf_content
[code]...
I am getting the error:
Error: SQL Error: ORA-06553: PLS-307: too many declarations of 'XMLTYPE' match this call
LPAD not behaving as expected. the main thing I'm trying to accomplish here is reading values from one table, and inserting them into another... but in the "other" table, they need to be inserted as 11 characters long, with leading zeros. in it's most basic form, this is the cursor I'm using:
CODEDECLARE CURSOR update_mpi_cur IS select distinct A.epn_nbr, A.mrn_nbr, B.mpi_nbr from table1 A, table2 B where B.external_id = A.epn_nbr and B.identifier_type = 'EPN'; [code]....
should have mentioned that this is Oracle 10.2.0.3, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.
I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.
Code in SQL SERVER :-
CREATE TRIGGER INSERT_AUDIT ON T_AUDIT FOR INSERT AS INSERT INTO T_AUDIT_LOG SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,* FROM INSERTED
Converted into Oracle:-
CREATE OR REPLACE TRIGGER INSERT_AUDIT BEFORE INSERT ON T_AUDIT FOR EACH ROW INSERT INTO T_AUDIT_LOG VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );
END;
how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.
the last 3 columns when are c_data1,c_data2,c_data3 as :- INSERT INTO T_AUDIT_LOG VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3); But this gives error:- PLS-00049: bad bind variable 'NEW.C_DATA1' PLS-00049: bad bind variable 'NEW.C_DATA2' PLS-00049: bad bind variable 'NEW.C_DATA3'
I am facing a problem while inserting primary keys using a sequence. Following is my case
I have a Master Table, say M_A, and 2 detail tables D_1 and D_2. I am trying to generate primary keys for the master and detail table as well as the reference keys for the detail tables using sequence.
I created a pre-insert trigger, say preInsertTRIG
proc_ABC(pri_key_master OUT VARCHAR2, pri_key_detail1 OUT VARCHAR2, fk_detail1 OUT VARCHAR2, pri_key_detail2 OUT VARCHAR2, [Code] ........
I am able to insert the P.K of the master table as well as P.K of one of the detail table. However, it fails to insert the P.K of 2nd detail table and reference keys for both the detail tables. I know there are other simple methods available in Forms, but I have to do it by this procedure only
I have a report, where there are opening balance and closing balance, so i have to store the closing balance values in a separate table during runtime and should show this closing value as opening balance for next month.
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES (sTraineeNo IN NUMBER, nReturned_O OUT NUMBER) IS ln_insert_cnt PLS_INTEGER :=0; nSequence NUMBER:=0; [code]......
I have a table AUD$ (containts some data) and aud_new(exactly structure same as AUD$ table with no data)
i want to schedule a job which will insert all data from AUD$ into table aud_new$ from aud$ table on next day again data from aud$ should be append to aud_new table and aud$ table should be truncated again.
The job should run on every day at midnight at 2 am
(note : If insertion in table aud_new get fail due to any reason , the aud$ table must not get truncated )
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I am searching the simplest way for ad hoc MINUS.I do:
SELECT * FROM uam_rss_user_XXXXXXX WHERE host_name IN ('XXX0349', 'XXX0362', 'XXX0363', 'XXX0343', 'XXX0342', 'XXX0499', [code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
I have a view ( from many tables) , an error view in wich i monitor errors that appear in my project. This view should be empt always but whenever a error occurs the view shows me this. I wanna put a trigger or something like that on that view to send me an e-mail whenever a line is inserted in that view. ( I don't wanna use a job for that to make a count because this will affect the entire database).
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE; CREATE TABLE BookingDM ( hNo NUMBER(3), gNo NUMBER(5), dFrom DATE NOT NULL, [code]......
This is the first set I'm attempting to insert hNo = 148 gNo = 11169 dFrom = 09/03/2009 dTo = 09/10/2009 rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202'); INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202') * ERROR at line 1: ORA-01843: not a valid month
i am trying to fetch the cursor returned from a stored procedure and insert into a physical table. but i am unable to populate the table. below is how my code looks.
declare p_out sysadm.CGF_PKG_REFCUR.rcDataCursor; a table1.node%TYPE; b table1.acct%TYPE; c table1.descr%TYPE; [code]......
In the above, stored_proc is the stored procedure from OLAP database. Above code i am executing is from EPM database.
p_out returns a result set of (select a,b,c from table2).
I am trying to get the result set from OLAP database and populate a table in the EPM database.
I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.
The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.
What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.
I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
Can we insert multiple dates within give range in a single insert statement say from 1st Feb to 31st MAY 04,something like after insertion it should resemble like below
I used decode & pivot insert for this,but the result is a failure.
SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
SQL> sno sname -------- 1 null null arun
AND
SQL> INSERT ALL 2 INTO test22 VALUES(no) 3 INTO test22 VALUES(name) 4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p; INTO test22 VALUES(name) * ERROR at line 3: ORA-00904: "NAME": invalid identifier
I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.
create or replace TRIGGER SRVCCLLS_RVW AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE
[Code]....
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex
