SQL & PL/SQL :: How To Create Table Replica Dynamically

Oct 22, 2013

i need to create a replica of an existing table in same schema dynamically so as to reflect the modifications on replica table which has been done on primary table.this table we need to maintain as snapshot,like if a column is added or deleted then it should automatically be added or deleted on the replica table.how should i approach this.

View 4 Replies


ADVERTISEMENT

Replication :: Create Same Database Replica In 25 Systems?

Jan 2, 2010

I need to create same database replica in 25 systems. Which method can i use for that...each servers will hold exactly same data and structures

View 1 Replies View Related

Create Table Dynamically

Oct 14, 2011

In a pl/sql procedure code, I created a normal table (create table) using dynamic sql. Then I used that table in procedure for further processing. But while compiling, it gave error that table does not exist. I can understand that he table is not present in DB, so the error came. But at the same time I need to create a table dynamically, use it and drop it. Does it mean that I need to make every query referencing that table as dynamic ??

View 1 Replies View Related

Application Express :: How To Create Radio Groups Dynamically Based On Records In A Table

Nov 29, 2012

below requirement..

We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..

SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4

If we add another row, XMLP in that table additional radio button should be displayed automatically..

View 4 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

SQL & PL/SQL :: How To Create Dynamically ALTER INDEX Command

Nov 15, 2010

how I can create an index dynamically? The DBA wants me to put this code below in the beginning of a PL/SQL package. At first, I tried by just putting the below syntax in the code, but I get an 'Alter' is not a valid identifier.

alter index ZZAP_selected_invoices_n1 rebuild online;

And then once the job completes...

alter index ZZAP_selected_invoices_n1 unusable;

View 8 Replies View Related

Forms :: How To Create Folder Dynamically Using Oracle 6i

Oct 12, 2013

How to create folder using oracle form. I used HOST command but its not working.

HOST ('md C:Emp_Photo2013');

View 5 Replies View Related

Forms :: How To Create Form Dynamically At Runtime

Dec 26, 2011

I am working with forms 6i..Is it possible to create form dynamically at run time..

View 2 Replies View Related

Forms :: Create Lov And Record Group Dynamically At Run Time?

Apr 10, 2013

i want to create a lov and record group dynamically at run time. i have the code in when-form-instance..

Declare
v_lov lov;
rg_id recordgroup;
gc_id groupcolumn;

[Code]....

whether i create first a lov and record group at design time or not? if i create a lov and have the recordgroup column then what is the benefit of dynamically lov.?

View 5 Replies View Related

Reports & Discoverer :: How To Create Link File Dynamically In Oracle 6i

Oct 27, 2012

how to create link file dynamically in oracle reports 6i

View 3 Replies View Related

Server Utilities :: SQL Loader - How To Dynamically Create Multiple Records Out Of One CSV Input Line

Jul 28, 2010

I need to load data from a CSV file where one of the CSV values determines how many records should be inserted.

Example of the input data:

KEYWORD;2;REC1_COL1_X,REC1_COL2_X;REC2_COL1_X;REC2_COL2_X
KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y,REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y
KEYWORD;4;REC1_COL1_Z;REC1_COL2_Z,REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z,REC4_COL1_Z;REC4_COL2_Z
If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.

As a result I hope to achieve this:

SELECT Column1, Column2 FROM testTable

REC1_COL1_X,REC1_COL2_X
REC2_COL1_X;REC2_COL2_X
REC1_COL1_Y;REC1_COL2_Y
REC2_COL1_Y;REC2_COL2_Y
REC3_COL1_Y;REC3_COL2_Y
REC1_COL1_Z;REC1_COL2_Z
REC2_COL1_Z;REC2_COL2_Z
REC3_COL1_Z;REC3_COL2_Z
REC4_COL1_Z;REC4_COL2_Z

I learned how to import data using Oracle SQL loader for cases where one input line more or less matches a (new) row in a DB table.

View 9 Replies View Related

Oracle Machines - Develop A Daily Replica From A To B?

Mar 13, 2011

The Oracle machine A (11R1) is placed in San Jose.
The Oracle machine B (10R2) is placed in Sydney.

I need to develop a daily replica from A to B. I don't need the entire db, but only some single schema.

The entire DB size is 1TB.The exp pump gzipped dump size will be 5+GB.Tnsping from A to B takes 500+ ms.The regular A exp pump -> ftp -> B imp pump will be too slowly because of the poor network.Which way it would be the best to implement it?

View 2 Replies View Related

Forms :: Replica Of Windows Explorer - Read UNIX File System

Jun 14, 2011

I would like to develop a form which will be replica of windows explorer.It should be able to read all files or files from a specific folder from an unix platform.

if yes can i have a template. I do not want to start from the scratch since I do not have much time.

View 5 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

How To Give Table Name Dynamically In A Select Query

Aug 23, 2012

I have 12 different tables

Record_jan
Record_feb
.
.
.
Record_nov
Record_dec

Based on the month i need to do some i/o operations from these table.

For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.

If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.

var:=Record_month;
Select * from var;

this gives error.

View 3 Replies View Related

PL/SQL :: Change Table Name Dynamically Based On Parameter

May 10, 2013

I am new to PL/SQL, worked mostly on SQL server, I have to change the table name dynamically based on the parameter.and used a ref_cursor to display the results in a report. when I execute it throws me an error.

create or replace procedure test1 (
p_eod_date IN VARCHAR2,
p_link IN NUMBER,
c_rec IN OUT SYS_REFCURSOR)
[code]....

View 10 Replies View Related

SQL & PL/SQL :: ORA-00905 - Get Values From Lookup Table Dynamically

Aug 15, 2013

i need to get values from lookup table dynamically,i am getting the missing keyword error.

create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
AS
vsql varchar2(2000);
CURSOR CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
BEGIN
[code]....

View 14 Replies View Related

SQL & PL/SQL :: Generate Columns Dynamically By Depending On The Rows In A Table In 11G?

Nov 27, 2012

Is there any way to generate columns dynamically by depending on the rows in a table in 11G .

Ex: If the deptno in DEPT table is not constant,then how to generate the N numbers of columns based on the deptno. Below query is working when we hard coded the deptno (10,20,30,40).What else if we more number of departments and we don't know the departments also.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as dbo

SQL> SELECT *
FROM (SELECT deptno, job, sum(sal) sal
FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
20,
30,
40));

[code].....

View 4 Replies View Related

SQL & PL/SQL :: Write A Code Which Dynamically Reads The Table Name From User_tables?

Oct 7, 2013

Firstly the table structures :

Create table cd_patient (pat_mrn varchar2(100)) ;
Create table cd_encount (pat_mrn varchar2(100), encounter_id varchar2(1000));

I need to write a code which dynamically reads the table name from user_tables (starting with cd) & load the data from remote database which has the same table name & structure based on the load number..

Static PLSQL statement would be :

declare
BEGIN
INSERT INTO "cd_patient" C-- DYNAMICALLY GET THE TABLE NAME
SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME
FROM REMOTE_DB.CD_PATIENT@XXX C1
WHERE C1.LOAD_NUMBER > 2;

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Dynamically Filling Data In Table And Rebuilding Indexes Gathering Stats

Jan 26, 2012

I have created a table like below-

PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....

Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--

--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';

insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);

code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-

1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?

2. dbms_stats is good approach or should I use dynamic sampling.

3. what about the approach using CTAS instead of inserting data through insert.

4. What about pl/sql table with index or with clause query.

5. Do I need to rebuild index after inserting data into table.

View 3 Replies View Related

SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table

Jun 4, 2013

I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.

1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);

2. List table content:
select * from T1;

account_no area_code bill_month consumption

Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......

3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12

Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182

With the conventional query I hope this is impossible,

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?

Jul 14, 2012

what command is used to create a table by copying the structure of another table including constraints ?

View 2 Replies View Related

Create Temp Table From Existing Table

Sep 2, 2010

I want to create temp table, for this i am using:

CODEcreate global temporary table help_temp
as
select * from help;

but this is creating only the table structure, not copying the table data.

View 5 Replies View Related

SQL & PL/SQL :: Create Table By Using Another Table With Specific Condition?

Jan 25, 2013

can we create table with copying of another table with some specific condition.

example.suppose we have one table which name is emp with three columns.

empid
empname
empjoindate

i want create a table emptemp by using emp table where empjoindate between two dates.

View 2 Replies View Related

Create A Table Which Contain History Of Main Table

Sep 8, 2008

I have to create a table which contain history of a main table. like this:

if the main table is
========================
nametypelengthnot null
Avarchar5Y
Bvarchar5N
Cvarchar5N
Dvarchar5N
========================
[code]....

I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :

if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......

I decided to select column name from the data dictonary using this SQL:

SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';

and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):

BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;

but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.

View 5 Replies View Related

SQL & PL/SQL :: Create New Table (b) From (a)

Feb 17, 2011

I want to create a new table 'b' from table 'a'.I know the query will be " create table b as select * from a;" But there is some twist in my question. I do not know how many table are there in table 'a' all I know is that there is one column named "timestamp" in table 'a'.

Now I want to create table 'b' with the difference that the column "timestamp" should contain the value "sysdate" instead of the earlier values which were there in table 'a';

Note: I have to do this in single query. I can not first create table 'b' from 'a' and then update the values of column "timestamp" to sysdate.

View 8 Replies View Related

Create Table As With Partitions?

Jan 8, 2011

If I try create table from the following syntax

create table a as select * from table b;

Then I could get only base table structure alone, I would like to get partition syntax as well.

View 1 Replies View Related

Create Table To Increment ID?

Sep 1, 2012

I am trying to create a table that will increment my ID by one using the following commands:

/*Creates the log needed to increment ID*/
create sequence seq_log;
CREATE TABLE MESSAGE_LOG_TABLE
(
IDNUMBER(10)NOT NULLPRIMARY KEY,

[code]...

When I run the above my create sequence completes successfully but I get a ORA-00955: name is already used by an existing object error message on the create table. I have dropped all tables and sequences before running my command but I still get the same error message.

After it bombs out it appears that SQL+ want's more information for it begins to give me line numbers as if it is looking for a ";" to end the above command. I have to exit SQL+ and log back in to continue working.

View 1 Replies View Related







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