I have two databases runing on same server. Both databases are oracle 11g R2 and platform OEL 5.5 When I run "show configuration"
DGMGRL> show configuration
I wrote an sql script (spooler) which runs a select query and produces results into a file.
I want to have the filename with the year attached. Can i pass a parameter when i run the script from SQL*PLUS
@'B: Year Of FinancingPLSQLspoolersSpooler_File_Creation' 01/01/2010 2010;
the first parameter 01/01/2010 is passed to the select query and taht works fine.
Since the output file contains 2010 data, I want the spooled file to mention in its name the 2010, so User's can know the file contains 2010 data in it.
So i wrote the statement within the spooler script
SPOOL X:HARSOrganizationsFinancialDataFor&2.txt
&2 should take 2010 has its value and produce the file FinancialDataFor2010.txt
how to use Substitution variables to produce the Filename i want.
I'm cloning a database from an existing dbca template. The clone is failing with datafile does not exist, because during the copying step, all the datafiles' name are changed, how do I force it to force dbca to keep the same names from the original database?
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times from ( select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date , mod(rownum,14) mod_result from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
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]....
I have a requirement to change the instance parameter of the primary database and standby in the data guard. Any procedure to do the same. I have to do it directly on production and there is no test set up so I cannot experiment . The procedure I am thinking of is as below.
1. Stop the standby
2. backup the primary
3. Change the parameter and restart to make it effective
4. Start the primary
5. Go to stand by
6. Change the parameter and mount the database.
7. Check if both are in sync and apply logs if not.
I tried to write a dynamically changing filename and file type so that i can dump some data. But the code I wrote is not producing any file at all even though it compiles with no errors. I run this code at XE database which comes free from the oracle website. I did all the directory settings. No problem with it because I can produce file with different codes. The code is as following:
CREATE OR REPLACE PROCEDURE dump_csv_file IS
TYPE number_array IS VARRAY(10000) OF NUMBER; TYPE string_array IS VARRAY(10000) OF VARCHAR2(100); TYPE date_array IS VARRAY(10000) OF DATE; TYPE v_file_array IS VARRAY(10000) OF UTL_FILE.FILE_TYPE;
RMAN-06169: could not read file header for datafile 40 error reason 1 RMAN-06169: could not read file header for datafile 41 error reason 1 RMAN-06169: could not read file header for datafile 40 error reason 1 released channel: oem_disk_backup RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 06/14/2012 07:54:43 [code]...
Could we skip the backup of the above missing files using RMAN -> backup database skip inaccessible;On further research, this would compromise tablespace SYSAUX recoverability...Questions:
1) What caused the above missing files to get created? 2) What is recommended: Recover the datafiles?
I wrote the one query which is return the following output..
Query:
SELECT RMC_N_ID,NVL(DISCOUNT_N_ID,NULL) AS DISCOUNT_N_ID,NVL(NULL,'') AS LOADINGS_N_ID,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_DISCOUNT WHERE RMC_N_ID =224 AND DISCOUNT_N_ID IS NOT NULL UNION SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(LOADING_N_ID,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_LOADING WHERE RMC_N_ID =224 UNION SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_WARCOMB WHERE RMC_N_ID =224 UNION SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID, ENDORSECOMB_N_ID FROM UWRMC_ENDORSECOMB WHERE RMC_N_ID =224
my data looks like:- Ename Job salary ----- ----- ------- A manager 10000 B clerk 60000 C assistant 30000 A manager 50000 B clerk 20000 D accountant 40000
I want the result while select all data should look like:-
A B C D manager clerk assistant accountant 10000 60000 30000 40000 50000 20000
i have a problem ! i have developed my database in oracle 10forms with 10 database! Now i want to convert my 10G forms into 6i as connected with 10G database! how i will configure 10gdatabase with 6i forms and is it possible to convert my 10g forms into 6i forms?
I pretty new on Oracle and I'm facing with a issue. I don't know how to convert 'DD-MON-YY HH24:MI:SS' to 'DD-MON-YY Seconds'.So '29-APR-2010 16:32:23' will be ' 29-APR-2010 59543' (16*3600+32*60+23) Is this possible ?
Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-
This is input data in hex value:- STARTTIME : 080b1317021a
This is decimal value after convert from hex to decimal:- STARTTIME : 081119230226
My problem is how could i convert hex to decimal in oracle? Below are my coding:-
case when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99' or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12' or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31' or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59' or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59' [code]....
I tried creating the required view using 'CASE' statement and group by but its returning multiple rows.
select case when PropertyID=1 then VALUE end as Attrib1, case when PropertyID=2 then ValueID end as Attrib2, case when PropertyID=3 then ValueID end as Attrib3 case when PropertyID=4 then ValueID end as Attrib4 from ( select Phone, PropertyID, ValueID,Value from PropertyValue group by Phone, PropertyID, ValueID,Value )
I have to change some queries from SQL to Oracle but I couldn't convert these queries because they use some system tables in SQL that I don't know the equivalent Oracle tables. Following are SQL Queries
1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name
2. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')
3. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)
