PL/SQL :: Test Data In Query?

May 29, 2013

I suspect it uses TABLE, CAST and/or MULTISET functions in a query .. but reading the docs on those, I can't seem to piece together how to do it.

When I piece together a sample/test query, I usually do something like this:

WITH data AS
( SELECT 123  id,  'abc'  col1  FROM dual UNION ALL
SELECT 234, 'def' FROM dual UNION ALL
SELECT 345, 'xyz' FROM dual
)
SELECT *
FROM data -- usually more complex query here

However, I saw somebody do this without using the UNION ALL .. they did something with TABLE ( ... ) ? it seemed "simpler" than the UNION ALL method I've used previously

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Fetch Steps Of Test Having Recursive Calls To Other Tests

Jul 17, 2012

I am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.

Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)

Columns:
STEP_ID (primary key - integer)
STEP_NAME (char)
STEP_DESC (char)
ORDER (integer)
TEST_ID (reference to table TEST.test_id)
[code].......

Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.

To start with I can write the following query to get steps of the test - "Empty trash from mailbox".
SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')

View 7 Replies View Related

Server Utilities :: Data Import From Production To Test

Jan 26, 2012

I want to automate the import from production to test.

1) export the production schema
2) import in to test server?

How can i automate that currently i am doing it manually as follow:

1) expdb the production schema
2) kill all connection on the test server to test schema
3) drop test user cascade;
4) recreate user;
5) impdb the production schema to test:

but i want it to automated or scheduled so i don't; have to log in every night!!

View 5 Replies View Related

SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?

Aug 31, 2010

I have the below data in table test_1.

select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100

test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3

My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.

SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)

create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)

View 4 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

SQL & PL/SQL :: Test Parameter?

Jan 12, 2012

I need to test a parameter in a script that must adhere to what is currently in the database.For example, when a user enters the value when prompted from this script, ("Enter the schema copy desired: " ) the value must be of the current values in the db:, it would be something like this:

Values currently in DB: copyofschema201110 (the 2011 is the year, and 10 would be the month of oct).

copyofschema201109
copyofschema201108

So, if the user enters the value 'abc' (an incorrect value), then I would like the script to stop the user, and kick back a message, say from dbms_output.put_line, and force the user not to go forward, but re-enter a correct value.

View 13 Replies View Related

SQL Query To Club Data If Range Data Is Continuous

Nov 26, 2006

For e.g.

if my data is as follows

EX_START_DATE EX_END_DATE EX_CHARGE

5/1/2005 5/5/2005 11:59:59 PM 10
5/6/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20

the record
5/6/2005 5/7/2005 11:59:59 PM 10
is in continuation for
5/1/2005 5/5/2005 11:59:59 PM 10 with the same ex_charge
so i need the output as

5/1/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20

and the remaining rows as it is
(Note :date in mm/dd/yyyy format)

View 6 Replies View Related

PL/SQL :: Not Show Last Row Of Test Number?

Feb 7, 2013

I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3 any records of mount which will not show last row of test number

mount     day     org1 test_No org2 org3     org4
299.00 1     0     1     1     0     7186
299.00     1     0     2     1     0     7186
299.00     0     3     3     0     7186

[code]...

but i want test_no should be in asceding order

View 3 Replies View Related

Copy Database From Production To Test?

Feb 22, 2011

Would like to know if I can replicate Production database (10.2.0.2) to Test (10.2.0.4).

tell me the process to do that on windows environment.

I wonder if I can do it with two different oracle versions?

View 4 Replies View Related

Windows :: Test If Database Is Running?

Sep 15, 2010

I have a unix script that will check if the database is up and running. I need a similar script but this time in windows batch file.

this is the snippet of the code from the unix script:

#! /bin/sh
sqlplus -s /nolog > /dev/null 2>&1 <<EOF
whenever sqlerror exit failure

[Code].....

View 2 Replies View Related

SQL & PL/SQL :: Update Test Table On ID Column

Feb 21, 2012

CREATE TABLE TEST
(
NAME VARCHAR2(1000 BYTE),
ID NUMBER
)

[Code]...

I want the update the TEST table with TEST1 based on id column,i tried as like.it's not working..

UPDATE test t1
SET t1.ID = ( select ID from test1 t2
where t1.name = t2.email and rownum=1 )

View 13 Replies View Related

SQL & PL/SQL :: Create Test Table - Instead Of Trigger

Dec 30, 2010

table
CREATE TABLE "TEST"
("CONTRACT_REF_NO" VARCHAR2(20 BYTE) primary key,
"CHG_GL" VARCHAR2(9 BYTE),
"CHG_GL_1" VARCHAR2(9 BYTE),
"CHG_GL_2" VARCHAR2(9 BYTE),

[code]...

10 rows selected.

create or replace view test_view
as
select 1 id,CONTRACT_REF_NO,chg_gl
from test
union all

[code]...

11 rows selected.

I have done only for one id.

so the problem is the front end is showing this test_view that people will modify.but they have to actually modify the table ,so I can think of an instead of trigger(instead of insert and instead of update ones).

View 16 Replies View Related

Server Administration :: How To Test Row Movement

Sep 6, 2011

How to test row movement? I want to a row data reside in two different blocks. how to do the test?

View 13 Replies View Related

PL/SQL :: How To Test Procedure / Function / Package

Sep 16, 2012

How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .

View 6 Replies View Related

Upgrade From Oracle 11.2.0.2 To 11.2.0.3 In A Test Environment

Aug 29, 2012

recently i have tried to upgrrade from oracle 11.2.0.2 to 11.2.0.3 in a test environment (virtual machine).

there i have mentioned the SID as ORCL, but when we took the same snapshot to other machine (VM). iam unable to connect to the VM, unless i gave the local net service configuration as ORCL but it is unable to connect. but when i ran the query "select * from GLOBAL_NAME" output was ORCL. domain. com again i have configured in LNSC as ORCL.DOMAIN.COM it is working.

but i have given the sid as ORCL..

View 5 Replies View Related

RAC & Failsafe :: Test Environment - SAN Migration?

Feb 15, 2012

We have a RAC in a test environment.It's 2 nodes connecting to an IBM DS3512. The DS3512 has one pool of 8 hard disks (RAID10) which has been then sliced into logical drives:

2 x 1GB
then
2 * 400GB

I have been tasked with changing the current configuration as follow:

2 pools of 4HDs in a RAID10 configuration

Then from the first pool:
2 * 1GB
1 * 398GB

from 2nd pool:
1 * 400GB

What I intend to do:
1) shutdown
SQL> shutdown immediate;
./srvctl crs stop
2) backup
dd if=votingdisk of=voting_disk_backup
I already got backups of OCR in $CRS_HOME/cdata/crs/
3) Perform changes on SAN
4) recreate ocfs2 partitions
5) mount the disks to their folder.
6) recovery
recover voting from backup
recover ocr from backup
7) Turn service on again
./srvctl crs start
SQL> startup;

Would this scenario work. There can be some glitches like the disk names changing after the reconfiguration but I only need to modify fstab for that.

View 3 Replies View Related

Stress Test Of A Database In 2 Configuration

Sep 13, 2013

i've perform a stress test of a database in 2 configuration : 

1) Single instance 11.2 with 8gb Sga e 4gb Pga on Linux, 6core with instance caging =4, resource_manager=defaut_plan

2) 2 Nodes Rac 11.2 with 8gb Sga e 4gb Pga in both nodes on Linux, 32core with instance caging =4, resource_manager=defaut_plan.

I've used Swingbench and the transaction per minute are equal, Avg transaction per s in rac are Greater then single instance (4ms vs 40ms).I know that rac is not for high performance.My question is: is rac config best than single instance and if yes what is the %?

View 0 Replies View Related

SQL & PL/SQL :: Create Trigger Which Will Test Database Link?

Aug 29, 2012

I want to create a database trigger which will test the database link, if it is ok then it will use dblink and do its work.

If it fails then it will send the data into its own server logfile.

I Wrote:

CREATE OR REPLACE TRIGGER PERMIT.TESTTRG
AFTER INSERT OR UPDATE
ON PERMIT.TR_LP_M_H_COMPANY_25072012
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
Declare
l number; nIgn PLS_INTEGER; nRows PLS_INTEGER := 0;

[code]....

When I execute it it is giving error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PLS-00201: identifier 'EXEC_SQL.CONNTYPE' must be declared
4/11 PL/SQL: Item ignored
8/2 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

[code]....

View 4 Replies View Related

Automate Import From Production To Test Server

Nov 15, 2011

I would like to know that how can i automate the export from production to test server. I need direction to create process to import data from production (server A) to test server (server B).

View 6 Replies View Related

Forms :: Test Custom Jar Files With 6i From Desktop?

Dec 18, 2010

I want to test custom Jar files with Forms 6i from my desktop, but I cannot get the Run Form->Web to work. Is it possible for the run time to load the just jars for PJC when you do Run Form -> Client/Server? So far, when I have done this, it does not execute any PJC.

View 2 Replies View Related

PL/SQL :: Creating A Script To Populate A Test Table

Nov 1, 2013

I am creating a script to populate a test table. But I cannot auto increment the DATE column,

declare  v_idnumber := 1 ; v_datedate; begin while(v_id < 100 ) loop v_date:= TO_DATE( '07-23-2012', 'MM-DD-YYYY'); INSERTINTO datetest (ID,startdate) values( v_id,v_date);  v_date:= v_date +1 ; v_id:= v_id +1 ; endcommit;end;loop;

View 0 Replies View Related

Application Express :: How To Test File / URL Existence

Jun 18, 2012

I have a table with full path and name of a document or a URL.

Users can now open one of these documents by klicking on a link.
P:Document est.doc
or
URL....

Some times this document no longer exisits. How do I make sure the user gets a correct error message? URL....How do I make sure the user gets a correct error message?

View 4 Replies View Related

Forms :: Frm 40010 - Cannot Read Form Test.fmx?

Jun 19, 2013

I am running the login page on the server with the below URL .

[URL]...

However I am getting the below error .

Frm- 40010 Cannot read form test.fmx

View 1 Replies View Related

Access - Sql Statements Being Fired On Table (Test)?

Oct 4, 2012

Oracle 11gR2

I have on table 'Test' in database and I want to know currently which sql statements are being fired on table 'Test' ?

View 8 Replies View Related

Migrate Current Production Database To Test 10.2.0.4 On Windows

Feb 1, 2011

I have to migrate the current production database to test 10.2.0.4 on windows. Any non-export way to upgrade 9i to 10 g?

i have following steps

1) ALTER DATABASE BACKUP CONTROLFILE TO TRACE
2) shutdown oracle 9i database on server A
3) copy database file, controlfile, redolog, and other files to new server B
4) alter the controlfile backup with new location of bdump, udump, and log file and data file locations
5) user oradim ORADIM -NEW -SID SID [-INTPWD PASSWORD ]-MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
6) start dabase in upgrade mode
7) run catpat.sql and util102.sql
8) take backup
9) open database for users

View 3 Replies View Related

RAC & Failsafe :: Storage Options For Building Test Setup For DB

Jul 22, 2013

I am looking for different cheap storage options to build test RAC setup for DB on Linux@Dell PowerEdge T100 .

View 1 Replies View Related

Windows :: Error / Test Failed / Provider Cannot Be Found

Feb 25, 2013

We just upgraded one of our databases to 11g on a new server. The 3rd party application that accesses the db is 32 bit. The software needs to be running on the db server in order for some transactions to occur. Since the oracle client on the db server is 64bit do we need to install a 32 bit client as well. As of right now we are getting an error in the software when trying to connect to the db that says "Test failed. provider cannot be found. it may not be properly installed." This is a live production server so uninstalling and reinstalling oracle is not really an option at this time (unless absolutely necessary). How will installing a 32 bit oracle client effect everything (if that is the route we need to take).

View 3 Replies View Related

Recovery Manager (RMAN) :: How To Test Restore In Another Server

Feb 19, 2013

I want to test full backup (hot backup using RMAN) of my DB (11.2 SE) on another server (I didn't install DB on yet).

View 6 Replies View Related

PL/SQL :: High Consistent Reads / Cannot Replicate In Test Environment

Feb 8, 2013

I am having performance issues on a query in a production environment that I cannot replicate in our test environment. Our test environment is an import of production. Version information is:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE     10.2.0.3.0     Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - ProductionWhen I run the query in test, I get the following results using TKPROF
[code]....

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWSThis performance problem started a few weeks ago and the problem seems to be in the high number of consistent reads during fetch. The DBA tried restarting the instance and gathering fresh stats on the tables but that has not made a difference. Now he wants to export the tables, drop the schema and re-import.

View 8 Replies View Related

Application Express :: 4.1 Create Webservice Reference / Test

Aug 29, 2012

I created an report and made it accessible as Restfull server. When I checked the url, I got an XML back. So the service is working.

As test I wanted to create a webservice reference to that same page, but receive ORA-06502 PL/SQL: numeric or value error: hex to raw conversion error whenever I test the service. I tried all kind of options.

Settings:
URL ...........
HTTP MEthod : GET
Parameters
app
page
reportid
(and even parmvalues, lang, output)
outputformat : XML

When I test this service I receive above error. I use Apex 4.1

View 0 Replies View Related







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