PL/SQL :: Sql For Creating Xml Data
Nov 14, 2012
I have a requirement to create an xml file from a table. The table has product number repeated for each values of attrbiutes. Is it possible to create an xml file with product_id only once and all the attributes and values listed under that.
Example
WITH tmp AS
(SELECT '0983433' AS product, 'color' prd_attr, 'blue' AS val
FROM DUAL
UNION
SELECT '0983433' AS product, 'size' prd_attr, '23' AS val
[Code]...
And the file should be like the following
PRODUCTS>
- <PRODUCT>
<ID>0384491</ID>
- <NATURE>
<SALE>Y</SALE>
[Code]....
View 4 Replies
ADVERTISEMENT
Jun 15, 2010
We are using oracle forms 6i as front end and oracle 10g as back end.When we are creating a new user, the password should be stored in back end table in encrypted format.
View 4 Replies
View Related
Aug 4, 2011
I've got a Source Data in complex relational formFor reporting purposes, a simpler, less normalized data model is neededThere are two Target views from the Source Data: one of them with full access to all datathe second one with access only to a subset of the data (same columns, but not all the records)For both target groups, a separate schema shall be available, each containing only relevant dataToday, these schemas are physically located on the same DB instance and host as the source dataA daily refresh is sufficientA later relocation of the reporting schemes to other DB instances shall be possible without major changes neededOracle 10g should be used I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem: when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.
I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).
View 1 Replies
View Related
Nov 18, 2011
I have a table with three columns mentioned below :
Col1 Col2 Col3
121
221
331
431
531
622
722
Col1 is the primary key.I need a sql query which will show the data set mentioned below :
Col1 Col2 Col3 Seq
121 1
221 1
331 2
431 2
531 2
622 3
722 3
I need all the 7 records to be displayed.
View 16 Replies
View Related
Feb 23, 2011
I have an excel sheet as follows: I have to import the data in the excel sheet to oracle database table through TOAD.
name1 name2 name3
ABS SDFG FHTR
DFR GHJK HJK
... ... ...
Now, I need an id column along with these three that would have id numbers like that of a sequence. That means, suppose I have 1000 records in the excel sheet, then the table should have as many numbers automatically after data import.
View 18 Replies
View Related
Aug 24, 2011
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
View 4 Replies
View Related
Sep 20, 2011
i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/
Meta table contains Table name and column name..i attached the table data in atext file
View 39 Replies
View Related
Jul 13, 2010
Is there any way to find out the division between the time taken for query parsing, creating execution plan and actual data retrieval seperately? If I enable 'set timing on' I see the elapsed time which is the total time taken for all these 3. Some of my queries are taking long time when I run it first time and so want to know what is it taking long? is it the parsing or creating the execution plan, if so what can I optimize.
View 3 Replies
View Related
Dec 14, 2010
I have a report with 2 groups Gheader and Glines.The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.However I only want to select this in the lines query so I do not get repeating records at the header query.
View 11 Replies
View Related
Dec 21, 2012
creating the standby database from Active database using RMAN and getting the below issue after i executed the duplicate command.
Version of Database:11g(11.2.0.1.0)
Operating System:Linux 5
Error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/21/2012 17:26:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
[code]....
provide any work arounds to proceed further in creating the standby database.
View 1 Replies
View Related
Feb 7, 2011
creating an ASM instance. I've never done this before and searched all known documentation. I just can't seem to figure this out. This is an 11g database on a WinXp machine for testing purpose before attempting to do this on a live production environment
D:\app\user\product\11.1.0\db_1\BIN>localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'Dummy\user', privgrp '
Operation successful.
Step 2: creating new CSS service
Failed to create the new CSS service, err(5)
failed to create CSS services
View 6 Replies
View Related
Feb 18, 2011
I have created a sequence and I would like to create a surrogate key in one of my tables that will take the values of the sequence. I searched for hours but couldn't find any example of how to do that.
View 1 Replies
View Related
Jul 19, 2011
I need to create a trigger for the below case:
index_id Time_vertex_id date rate pre_rate
4 1 17-06-2011 4.7 6.4
4 1 16-06-2011 6.4 7.4
4 1 14-06-2011 7.4
4 1 15-06-2011 8.4 7.4
the index_id and time_vertex_id will be unique and when the date is 17th i.e the first date will be inserted the current rate will be 4.7 and the previous rate will be blank and when another date is inserted i.e 16th the previous rate of 17th will be the current rate of 16th i.e 6.4.
when 14th is being inserted, the previous rate of 16th will be the current rate of 14th i.e 7.4 and if after 14th is being inserted, 15th is being inserted, then the previous rate of 16th should be updated as per the current rate of 15 say 8.5.
View 5 Replies
View Related
Jun 3, 2010
If iam create a new role in scott/tiger user why not creating. can any one explan me.
SQL> create role test_role;
create role test_role
*
ERROR at line 1: ORA-01031: insufficient privileges
View 2 Replies
View Related
Sep 18, 2010
I am writing the following PLSQL code-
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........
The new table used above has the following structure
Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE
However I get the following error on execution and am not able to make out why?
BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3
View 4 Replies
View Related
May 2, 2013
We make a 'SELECT' and then create a file with the results and send it by email... what we want to do is , if there are no results from that select , not sending the email neither creating the file..
View 5 Replies
View Related
Mar 14, 2013
How to create a new session from a pl/sql. My requirement is that i will package which should run on new session and execute it.
BEGIN
A; --Calling package
B; --Calling package and this should run on new session.
C; --Calling Package
end;
Now when I will run the this plsql block, then A,C should execute will run in this package only but B will run separate session.Whole idea to have better performance. I heard we can use PRGMA AUTONOMOUS_TRANSACTION, but this is not working.
View 3 Replies
View Related
Sep 12, 2010
I created a form that would allow a user to create another user.i tried creating a user in forms 6i using the following code on the when button pressed:
BEGIN
forms_ddl('create user'||:user_name||'identified by'||:pasword);
commit_form;
END;
pasword and user_name are captured in the form.
View 2 Replies
View Related
Feb 7, 2013
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies
View Related
Oct 16, 2006
how 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?
View 3 Replies
View Related
May 25, 2012
I would like to create a trigger on a table, but only if the table exists.
This is what i've got so far:
create or replace function tableExists (tablename in varchar2) return boolean
is
c int;
begin
[Code].....
This would give me the error:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update
View 1 Replies
View Related
Aug 24, 2008
I'm modifying a package which contains a function with different parameters (say cno, gno, pname etc) need to be passed though it and that function is called by the cursor later in the program. I need to create a cursor (select statement) for every class (a program set)...i.e. multiple select statements for a program set with different values. The parameters needed to be passed correspond to same columns in different tables...so do i have to refer those tables everytime i'm passing the parameters using a select statement? The Program accepts the parameters as an array (list of parameters) and returns a cursor and the program handles one program set (class) at a time. That parameter list will be in a loop..i.e. it will be repeated for every certain combination (say cno - pname combination). So ... How should i proceed and create a select statement with different parameters declared in the function of type string making it dynamic and returning them using ref cursor?
View 1 Replies
View Related
Jun 22, 2010
I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file. I guess pl/sql and unix needs to be used.
View 1 Replies
View Related
Jul 24, 2009
I am trying to create trigger through Procedure due to following reasons-
1. The name of the column on which trigger will execute, is to be fetched dynamically.
2. The name of the column to be updated, is to be fetched dynamically.
Here is the sample code-
CREATE OR REPLACE PROCEDURE test2
IS
var VARCHAR2 (4000);
uname VARCHAR2 (30);
attribtask VARCHAR2 (100);
mapcol VARCHAR2 (100);
BEGIN
[code].........
On execution, the procedure throws the error of 'Insufficient privileges'. The 'var' seems to be the main culprit because the issue disappears if var is set to 'select * from dual'. Also, if i take the output (value of var) given by DBMS_output.put_line function and execute it explicitly, trigger gets created.
In addition- The procedure is (and being executed) within the same user/schema under which trigger is going to be created.
View 2 Replies
View Related
Aug 6, 2010
error while creating a catalog db
error creating db
ORACLE error from recovery catalog database ORA-01552: cannot use system rollback segment for non-system tablespace 'TS'
ORACLE error from recovery catalog database:ORA-00942:table or view doesnt exist
RMAN-00571:--------------------------------------------------------------------------------------------------
RMAN-00569----------------ERROR MESSAGE STACK FOLLOWS-------------------------------------------
RMAN-00571:---------------------------------------------------------------------------------------------------
RMAN-06433:error installing recovery catalog
View 1 Replies
View Related
Aug 28, 2007
I need to create a report that show the overall performance of the system in hourly basic. I need to check all the transaction table in the system. So I try to find Cross Tab query. I need display hourly basic in column. From 00 hour to 23 hour.
In my table i will have a sysdate that track all the record DD/MM/YYYY HH24:Mi, so now i need so split it to a hourly in column. This is how it will look like. Think show in picture is easier.
Each row actually retrieve from a table. So basically i need to count each table, on that day got how many transactions then split all to a hourly basic and display it.
View 5 Replies
View Related
Sep 21, 2008
I have a table tblcustomer and a view called vworder. I need to create a trigger such that any data being added into vworder first checks if the field customerid has the data in customerid of tblcustomer.. all it has to do is spit out a error "Customer ID not found"
So I created a view such as
Quote: create or replace TRIGGER trig_order
BEFORE INSERT OR UPDATE ON vworder
FOR EACH ROW
DECLARE
cust_id VARCHAR2(20);
BEGIN
SELECT customerid INTO cust_id FROM tblcustomer WHERE customerid = :new.customerid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20000, 'Customer ID Does Not Exist');
END;
But it comes with error
Quote: ORA-25001: cannot create this trigger type on this type of view 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.
I am not an expert at all and I need to finish this today itself.
View 7 Replies
View Related
Oct 26, 2011
I am having trouble with creating a view based on the following: -
Total hours flown EVER, for each crew of the FBN001 on 20th October 2011.
Here is what i got so far (only works for each members flown on a particular flight, NOT on their whole hours flown in a period of a lifetime) : -
SELECT e.Employee_Number, e.FirstName, e.LastName,
SUM( ( (s.ESTIMATED_ARRIVAL) - (s.ESTIMATED_DEPARTURE) )*24)AS
TOTAL_HOURS_FLOWN
FROM SCHEDULE s, CREW_ASSIGNMENT ca, EMPLOYEE e
WHERE
s.Flight_Number = ca.Flight_Number
AND
s.Serial_Number = ca.Serial_Number
[code]...
View 20 Replies
View Related
Mar 12, 2011
Recently decided to try using Oracle. Previously when creating a procedure in SQL server I would have done the following.
Create PROCEDURE GetInfo AS
SELECT InfoID, Name, Description
FROM GetInfo;
How can I write this same procedure in Oracle?
View 10 Replies
View Related
Sep 18, 2012
I am trying to create one pipelined function but facing some errors as below
SQL> CREATE OR REPLACE
2 FUNCTION FUN_PIPELINED_EMP
3 RETURN org_typ Pipelined
4 IS
5 CURSOR c1 IS SELECT EMPLOYEE_ID,DESIGNATION,DEPT_ID,PROJECT_ID,MANAGER_ID FROM
6 EMPLOYEE_DETAILS1;
7 org_rec c1%rowtype;
8 begin
[code]....
View 13 Replies
View Related
