is it possible to create tables using variables in pl/sql such as the following example which is actually in php. i would like to create a table dynamically.
$create_samp_table = CREATE TABLE $MCSCHEMA.".$samp_id." AS SELECT * FROM $MCSCHEMA.".$samp_src_table." WHERE ROWNUM = 0
I have two tables A and B.
A
--
variable value
-------- ------
a 10
b 20
B
--
Exp
---
b-a
b*a
b/a
How can the variables be replaced with values(10,20) using a single query...?
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......
SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.
create or replace procedure my_proc(p_user in varchar2) is
l_cursor sys_refcursor;
l_query constant varchar2(1000) :=
'select a'
|| 'from ' || p_user || '.user_table'
|| 'where param0 = :x'
|| 'and param1 = :x'
|| 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
After running above block it is giving error : ORA-01008: not all variables bound.
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
I am trying to create view ..Using totally Five tables....
The main driven table almost 10 GB Data
When i am creating a view..
End of communication channel Error raise...
At the same time I am running query ..Running Fine...
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 I need to run 5 scripts to create 5 tables, seems each table needs to take 2 hrs, so I plan to put all 5 commands together
so i would have tables.sql to contain the following
do you know how to add time in beginning and end , so I can figure it out how long does this take to create 5 tables, use spool too?
create table a as select * from ........ ;
create table b as select * from ........ ;
create table c as select * from ........ ;
create table d as select * from ........ ;
create table e as select * from ........ ;
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedWe 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)
I have a table with over 100000 records.
The format and data is something like this:
Region Code,Name,Surname,ID_Number
1,John,Doe,67
1,Sarah,Jason,45
2,Bob,Marley,69
3,Steven,Johnson,900
2,Harry,Potter,890
3,Sandy,Bay,567
3,Else,Taylor,789 .....
I have about over 100 region codes and each region would like to get their own data. I can manually do the extraction and create a new table for every region, but it's going to take too long.
Suppose two users (CONNECT and RESOURCE roles) A and B who work on the same project. How can B create synonyms of all tables of A without asking admin to do that? Of course, we assume that these users have the CREATE SYNONYM privilege. My problem is that B does not have access to user_tables of A, so he cannot obtain the list of A's tables to create synonyms.
View 3 Replies View RelatedI have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.
Below is example
TABLE1
ACOL1 ACOL2 ACOL3
A1 A2 A3
B1 B2 B3
C1 C2 C3
TABLE2
BCOL1 BCOL2 BCOL3
11 12 13
21 22 23
31 32 33
COMBINED_TABLE
ACOL1 BCOL2 BCOL3
A1 12 13
B1 22 23
C1 32 33
I tried below query but no luck. It gives below error:
Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum
Error : ORA-01747:"invalid user.table.column, table.column, or column specification"
we create index on multiple tables in oracle
View 12 Replies View RelatedI'm trying to do is create 4 simple tables. This is my first project using SQL so I'm totally new to it, not just SQL itself but database design/management, including foreign/primary key concepts. I think that's why the errors are being generated due to a duplication of foreign key names (perhaps?) but I really don't where I'm going wrong with the design structure in terms of the FK's and the relationships.
Added the error report in the attachment.
Create Table Hotel
(Hotel_No Char(4 Byte) Not Null,
H_Name VarChar2(20 Byte) Not Null,
H_Address VarChar2(30 Byte),
Constraint Hotel_PK Primary Key (Hotel_No));
Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));
Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No),
Constraint Hotel_No_Room_No_FK (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));
Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));
I have a table as shown below,
SQL> select * from Main_Table;
AA
--------------------
SUB1
SUB2
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample
as
select * from SUB1 union all
select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off
i will end up in a union all "extra" , if i do like this.
how can achieve this ?..
I want to create a stored procedure where tables of two different databases are to be used.
View 4 Replies View Relatedi have 20 tables in schema and wanted to take count using store procedure.
how to create a sp for taking count of multiple tables
using oracle 10g currently create many external tables like so..
CREATE TABLE "XT_UNITS"
(
"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....
is there any way I can use 1 flat file (csv) to populate many external tables ?
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE
-reg_id
-name
ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id
PROCESS_TABLE
-srv_id
-start_time
-end_time
This is what I would like the logging table to look like:
LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time
How can I go about creating this type of trigger?
I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.
View 10 Replies View RelatedI 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]........
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;
[code]....
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 )
I have a requirement to put together a Oracle SQL template to create re-usable DDL/DML Scripts for Oracle databases.Only the Oracle DBA will be running the scripts so permissions is not an issue.
The workflow for any DDL is as follows:-
1) New Table
a. Check if the table exists from the system/admin views.
b. If table exists then give message "Table Exists"
c. If table does not exist then execute DDL code
2) Add Column
a. Check if Column exists for a given table from system/admin views
b. If column exists in the specified table,
b1. backup table.
b2. alter table to make changes to the column
b3. verify data or execute dml script convert from backup to the new change.
c. If Column does not exist
c1. backup table
c2. alter table to add column
c3. execute dml to populate column with default value.
The DML scripts are for populating base tables with data required for business operations.
3) Add new row
a. check if row exists by comparing old values of each column with new values to be added for the new record.
b. If exists, give message row exists
c. If not exists, add new record.
4) Update existing record (We have createtime columns in these tables so changes can be tracked)
a. check if row exists using primary key.
b. If exists,
b1. deactivate the record using the "active" column of the table
b2. Add new record with the changes required.
c. If does not exist, add new record with the changes required.
I'm just wondering because i didn't find any example / test databases for Oracle. Thus MS has its "northwind", mysql its "world" or "sakila".
Additionally, the tables view is quiet confusing, referring to all those generated tables (
[URL]........
I tried right-click + apply filter *remove generated* but it didn't work.
i want drop all tables in oracle8
View 2 Replies View RelatedAre there any recommendations to replicate oracle tables from 10.2.0.4 to 9.2.0.6 ?
I am new to oracle, thinking about streams and material views. And not sure which option should we choose ?
Any pros and cons (especially related to the versions). Can I use streams to replicate 10.2.0.4 to 9.2.0.6 ?
CREATE TABLE MTCH_TBL
(
S_PAID VARCHAR2(100),
S_SEC NUMBER,
H_PAID VARCHAR2(100),
H_SEC NUMBER
);
[code]....
My requirement is to match the records in MTCH_TBL and HIST_TBL on the basis of joining S_SEC(MTCH_TBL) with SEC_ALIAS(HIST_TBL) and INSTANCE (HIST_TBL) as 100 and choosing the record with max EFF_DATE from HIST_TBL.
I have come up with a query as:
select a.h_sec, a.s_paid, a.h_paid
FROM MTCH_TBL a,
HIST_TBL b
where a.S_SEC=b.sec_alias(+)
and b.instance(+)=100
and b.EFF_DATE =
[code]...
join using the EFF_DATE field also and get the expected results.My results are appearing as BLANK. However I need to produce the results as stated below:
Now, i need the results as:
H_SEC= 526
S_PAID= Q00
H_PAID = Q00