SQL & PL/SQL :: Dynamically Inserting Data From Set Of Tables And Cols On Where Clause
Apr 20, 2012
I am trying to create a simple parametised procedure when all I am doing is dynamically inserting data from a set of tables and cols on a where clause.
And for the result set I am also inserting these input variables for future ref.
----------------------------------
CREATE OR REPLACE PROCEDURE DATA_TEST (IN_TABLE_NAME IN VARCHAR2, IN_ATTRIBUTE IN VARCHAR2 )
iS
FirstClause VARCHAR2(3000) := 'INSERT INTO ABC (
COL1, COL2, COL3,COL4,COL5, COL6) ';
selectClause VARCHAR2(3000) :=
[Code]...
Now, the problem is that the proc is not compiling, because of that escape character.
Is there anything wrong that I am doing or some other settings needs to be done?
View 9 Replies
ADVERTISEMENT
Feb 15, 2009
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
View 2 Replies
View Related
Mar 13, 2007
how can I insert data into tables on another user. They both are in the same table-space.
View 1 Replies
View Related
Apr 19, 2011
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
how to take care of this sequence no.
View 15 Replies
View Related
Sep 12, 2012
table 1
empno, salary
1001,9800
1002,10000
1003,4000
1004,6000
table 2
sal_from, sal_to, rate
1000, 3000, 0
3000, 4500, 10
4500, 5500, 12
5500, 6500, 30
6500,999999 50
now how can i compare values from table 1 in table2 and apply RATE on table1 SALARY like (SALARY*RATE)
View 3 Replies
View Related
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies
View Related
Aug 6, 2013
I have a requirement to transpose the column to rows like the one below, i am not getting the logic on how to do it, If any of you have come across this logic,
Existing Table:ABCD1234
Hypothetical table: ColumnValueA1B2C3D4
View 2 Replies
View Related
Sep 25, 2012
There is an error after error, that's why I came here
What's is wrong? error message: virtual column not allowed here
CREATE TABLE test2(
id NUMBER PRIMARY KEY,
surname VARCHAR2(20) NOT NULL);
CREATE TABLE test1(
[Code]...
It would be nice to describe this situation and where does this error come from.
View 1 Replies
View Related
Jan 25, 2013
im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
View 2 Replies
View Related
Dec 14, 2012
I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.
SQL> create or replace
2 PACKAGE
casadm.sis_load_cpl_sis_reb_pgm_hist
3 IS
4 /**********************************************************************
******************
[code]....
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
View 16 Replies
View Related
Aug 2, 2012
I am using apex 4.0.2.00.07. I have a page with two regions. The first region has only one row and I have no problems accessing it and inserting those values into the table this is what I used to access one column from that one row APEX_APPLICATION.G_F03(1)
The second region is an interactive report, to which I have managed to add a checkbox and a textbox.
1)So if a checkbox has been checked off the row should be inserted
2) if the textbox has been entered it should replaceone of the values.
My question is how to access the second region? I started a loop based on the rows in the collection but that is as far as I got.
FOR idx IN 1..l_collectionTable_name
loop
end loop
View 0 Replies
View Related
Aug 9, 2012
I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?
Let say i have view that contain salaries of 2011
Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.
if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.
I am using oracle RAC 11g R2
View 5 Replies
View Related
Aug 19, 2012
is it in Orcale not possible to refenerence two tables in a with clause?
With CTE
AS
(
Select ...
),
[code]...
If I do a select on my first table I get this error: ORA-32035: unreferenced query name defined in WITH clause - With CTE AS
In Sql Server it works fine ...but how can I create two CTE Tables in Orcacle?
View 17 Replies
View Related
Aug 30, 2010
I have a query regarding nested tables while exporting. Are we not allowed to use Query clause during the export of nested table?
I am getting the error:
"EXP-00053: unable to execute QUERY on table NT_LP_COREBAL_MAINT because the table has inner nested tables
Export terminated successfully with warnings."
The exp command we are using is:
exp XXXX/XXXX@XXXX file=test1.dmp log=test1.log tables=nt_lp_corebal_maint query='where domain_id=10110307'.
View 1 Replies
View Related
May 26, 2013
I am trying to add a new column in a table and insert data from another column of same table.
alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit
As there are huge number of records in the POSITION table ...its taking for ever to execute this query.
View 1 Replies
View Related
Dec 3, 2012
below SQL I'm pulling data manually(by entering specialist name) instead of that I want this query to be modified so that the sys is pulled dynamically, I.e. like select specialist from t.
CODESELECT
type, Hari,Rakesh,Santhosh, Total
FROM (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh,
Sum(cnt) total
from (
select
[Code]...
View 1 Replies
View Related
Jul 17, 2009
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.
View 6 Replies
View Related
Feb 21, 2013
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
how to insert XMLTYPE data using DBLINK.
View 16 Replies
View Related
Mar 7, 2012
create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);
I want to insert data from stg1, stg2 into stg
Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2
I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;
it gives me table does not exist error:
so by stgi, i mean it should take stg1, stg2 etc
View 4 Replies
View Related
Oct 23, 2010
I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0
TEST22:
-----------
|sno |sname |
-----------
| | |
-----------
TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------
Required outcome is
TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------
Also this should be applicable for more than one value in the column col2 of table TEST22P.
ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------
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
View 4 Replies
View Related
Nov 8, 2010
In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.
that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.
View 2 Replies
View Related
Sep 3, 2012
I am trying to insert a column into a variable from a trigger.
Here is the code that i have:
CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/
When I run the above I get the following compiler error:
[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier
Since ID is defined in my BUYER table I do not understand what the error means.
Here is my create table statement:
CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);
View 1 Replies
View Related
Feb 13, 2013
I am facing problem while inserting data into Core Table from staging (both Core Table and Staging are on same database but different schema)
I am using below command:
INSERT /*+ APPEND PARALLEL("CORE TABLE", DEFAULT, DEFAULT) */ INTO
SELECT DATA from Staging
CORE TABLE is quit big contains millions of record partition on date and having old stats of 2007. Data fetching from staging is very fast appx 1 million record in 2 mins. we are inserting one day data daily into CORE TABLE from staging and its taking 3 Hrs.
View 2 Replies
View Related
Aug 21, 2006
I have to Insert data from one table to another table. First table contains some clob datatype columns and second table have carchar2 datatype.
Below are the structures of both tables;
SQL> DESC TBL_MAINSQL> DESC TBL_MAIN2
Name Type Name Type
----------------------------- ----------------------------------------- --------------
TXT_FEIN_NUMBER VARCHAR2(9) TXT_FEIN_NUMBER VARCHAR2(9)
TXT_QUOTE_NUMBER VARCHAR2(12)TXT_QUOTE_NUMBER VARCHAR2(12)
TXT_POLICY_NUMBER VARCHAR2(12)TXT_POLICY_NUMBER VARCHAR2(12)
TXT_AGENT_CODE VARCHAR2(10)TXT_AGENT_CODE VARCHAR2(10)
[code]....
The Table contains 5000 records. how to convert CLOB to VARCHAR2.
I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.
View 10 Replies
View Related
Sep 24, 2011
I want to insert excel data into Oracle.
Excel File Name : Product 01
Excel columns
File Send on 13/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection
01/02/2011 00002 Y 2 Y
03/02/2011 00001 Y 10 Y
04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y
File Send on 14/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection
01/02/2011 00002 Y 2 Y
03/02/2011 00001 Y 10 Y
04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y
---New Updated Data
05/02/2011 00002 Y 2 Y
06/02/2011 00001 Y 10 Y
05/02/2011 00005 Y 14 Y 05/02/2011 00006 Y 74 Y
I just want to insert data according to my structure But if again the same file send with updated data it will only update the new data because previous data is imported.
Oracle Structure
Arrival Date Date,
Product Code char(5),
Quantity Number
View 9 Replies
View Related
Jul 22, 2012
I have a table created with the following code:
CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),
[Code]...
I have altered the table to have the following:
ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);
I am trying to insert new data into said table that was updated:
INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;
Then I get the following error:
Error starting at line 1 in command:
INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
View 3 Replies
View Related
Aug 8, 2013
I have an XML of the following format:
<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students>
Now i have to insert this xml into DB , the table consist of following columns ( row number , property name , value ) Expected out put is (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is
1. how to get the tag names and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?
View 8 Replies
View Related
Jul 22, 2013
I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.
CREATE OR REPLACE type sam as OBJECT
(
v1 NUMBER,
v2 VARCHAR2(20 CHAR)
);
---Nested Table---
create or replace type t_sam as table of sam;
--Inserting data----
insert into table(t_sam) values(sam(10,'Dsouza')); Error Message:
[code]........
View 4 Replies
View Related
Mar 6, 2011
CREATE OR REPLACE procedure fast_proc (p_rows out number)
is
TYPE object_id_tab IS TABLE OF all_objects.object_name%TYPE INDEX BY BINARY_INTEGER
lt_object_id object_id_tab;
CURSOR c IS
[Code]....
Warning: Procedure created with compilation errors.
Errors for PROCEDURE FAST_PROC:
LINE/COL ERROR
-------- ---------------------------------------------------------
13/7 PL/SQL: SQL Statement ignored
13/22 PL/SQL: ORA-03001: unimplemented feature
I am not able to do INSERT but I am able to do UPDATE/DELETE? What is this inbuilt functionality?
View 3 Replies
View Related
Nov 22, 2012
I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table A:-
C1 C2 C3 C4 C5 C6
-- -- -- -- -- -----
1 2 3 4 3 6
4 5 6 5 5 5
Table B:-
Table_Name Column_Name
--------- ----------
A C1
A C2
A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
View 2 Replies
View Related
