SQL & PL/SQL :: Getting Back Whole Record Then Picking 1 Column?

Jul 22, 2010

I want a function that'll execute a query and return the whole table record then i need to somehow pick a column in that record and return the data in that column

So like

function something(p_param varchar2)
return table_record
something('blah).employee_number

where employee_number is a column in that table

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Picking Up Parameters For Oracle Procedure

Mar 6, 2012

My Oracle procedure works on two parameters, file type and file name.

exec xml_trans.load_xml('OMG','Sample.xml');

First parameter is xml file type and second parameter is xml file name. XML file is generated by by a web service and keep in a particular location.

Now the requirement is my procedure should pick up the in parameters, once the xml file is generated and my procedure should be running as per schedule. I can execute the procedure in schedule but how can i pick the file name and pass to procedure as soon the xml file is generated?

View 3 Replies View Related

Query Not Picking Function Based Index

Apr 3, 2012

One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan? let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.

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 :: Record To Column Format

Feb 1, 2011

I have to display a record in column format. I have tried doing it using COUNT & DECODE as in below script.

SELECT COUNT (DECODE (a1.application_status, 21, a1.ppid, NULL)) cnt_app21,
COUNT (DECODE (a1.application_status, 22, a1.ppid, NULL)) cnt_app22
FROM (SELECT a.application_status application_status, a.ppid ppid
FROM vp_recon_upload_dtls a, vp_ppid_upload_info b

[Code]...

This gives output as:-

cnt_app21 cnt_app21
3 1

The second query is little different from above as it has 2 more tables joined and more conditions than first query.

SELECT SUM (DECODE (a2.application_status, 21, a2.rate_amount, NULL)) sum_app21,
SUM (DECODE (a2.application_status, 22, a2.rate_amount, NULL)) sum_app22
FROM (SELECT a.application_status application_status,
d.rate_amount rate_amount
FROM vp_recon_upload_dtls a,

[Code]....

Second query output is :-

SUM_APP21 SUM_APP21
222 111

My requirement is to display all the four values in one row as:-

cnt_app21 cnt_app21 SUM_APP21 SUM_APP21
3 1 222 111

One easy way is to create two views and select values from them.

But for some reason I have to write in a query as there would be more dynamic conditions added to it.

View 2 Replies View Related

SQL & PL/SQL :: Column Reverse In Record?

Nov 7, 2011

I am trying to reverse data from couple record. I have display the output below of how I want it to be and is it possible to do it?

Quote:

This is how it is right now

ID|FROM_UOM_KEY|UOM_KEY|UOM|QUANTITY
1 |PK |PK |PK |1
2 |EA |PK |PK |3
3 |PK |PK |PK |1
4 |EA |PK |PK |3

I want it to be like this.

ID|FROM_UOM_KEY|UOM_KEY|UOM|QUANTITY
1 |EA |PK |PK |1
2 |PK |PK |PK |3
3 |EA |PK |PK |1
4 |PK |PK |PK |3

Here is the code

SELECT
from_uom_key,
vip.UOM_KEY,
--DECODE ( from_uom_key,from_uom_key,vip.UOM_KEY,from_uom_key),

[Code]....

View 21 Replies View Related

PL/SQL :: Only Display A Column Value One Time For A Record / Not A Static Value

Jan 7, 2013

I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.

select Date, Person, Language, Country
from TableATableA
Date            Person  Language   Country
01/25/2013       James   English
12/20/2012       James   English
US

[code]....

View 3 Replies View Related

Forms :: FRM-30058 - Invalid Name For A Record Group Column

Feb 2, 2010

earlier the query was running on same record group but i hav to delete some code so i did but after made such changes itreturns this error.but still query is correct i complied on toad.

View 10 Replies View Related

PL/SQL :: How To Insert Null Record (some Column) In Table Using Loop

Jul 5, 2012

How to insert null record (for some columns) in table using loop.

sample data of x_tab

order_id order_name

231 xxx
123
345
111 vvvv

View 5 Replies View Related

PL/SQL :: Average / Standard Deviation Of Multi-Column Record

Jun 28, 2012

I want to read a record that has 6 columns

RECORD     |Col1     |Col2     |Col3      |Col4     |Average      |Standard Deviation     
0001____|Null_|5___|8___|10__|8.75___|2.986079

With SQL I want to calculate Average and Standard Deviation.

View 4 Replies View Related

Loop Back Entry

Jun 1, 2013

How the loop back entry in /etc/hosts relates to listener?

View 1 Replies View Related

SQL & PL/SQL :: Data Should Be Updated Back To Table

Aug 16, 2011

The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?

create table tab1 (col1 number(5), col2 number(5), col3 number(5), col4 number(5));

Existing Data:

insert into tab1 values (101,521,3,1);
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,523,3,1);
insert into tab1 values (101,523,3,2);
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Deleted All Records From Table - How To Get Back

Nov 8, 2010

I have deleted all the records from the table.And I have committed.Now I want to get all the records back.

View 16 Replies View Related

Forms :: How To Go Back To Previous Block

Feb 21, 2012

I'm making a menu in my form, wherein it has FILE, TRANSACTION and REPORT. Under FILE it has BACK, and LOGOUT. In my back menu item, i want to go back to the previous block or previous module. I used previous_block but it's not working in some of my blocks.

View 8 Replies View Related

SQL & PL/SQL :: Any Draw Back Caching Sequence?

Mar 13, 2011

cache sequence all my sequence scripts has a cache of 20...here is the script

CREATE SEQUENCE APP_TEMP_SEQ
START WITH 1000400
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

is there any draw back caching sequence ? I noticed in my tables several sequences are skipped.

View 2 Replies View Related

SQL & PL/SQL :: Get Result Set Back From Stored Procedure?

Apr 9, 2012

I come from a world of MSSQL and have been thrown into doing some Oracle work. Great! Ok, moving on.. I work in an environment where I do not have direct access to the database tables that I need data from. As a workaround, I have been asked to create a stored procedure that will be loaded into our CRM system's production db once it goes through the internal "approval" process.Basically, I need to return a result set back to the client by calling a stored procedure.

Version 1 of this that was already in place was done with the following code.

procedure events_by_day (p_start_date IN OPERATION_LOG.DT%type,
p_end_date IN OPERATION_LOG.DT%type,
p_results OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_results FOR

[code]....

Then the code is executed from the client side like so:

events_by_day(p_start_date => to_date('2012-3-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
p_end_date => to_date('2012-3-29 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
p_results => r_cursor);
LOOP
FETCH r_cursor
INTO event_date, event_acct_no, event_type, event_count;

[code]....

As you can see, a SYS_REFCURSOR was used in this case to pass the data back.

View 3 Replies View Related

PL/SQL :: How SQL Queries Act In Back End To Give The Result

Jul 22, 2013

When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.

View 1 Replies View Related

Adding New Node Back To Cluster Via Documentation

Apr 10, 2012

I have a 3 node RAC server on Windows Server 2008. Last week the hard drive went out on one of the nodes and I have had to rebuild as I could not recover anything.

I went through and deleted the old node and now I have just finished adding the new node back to my cluster via documentation. Once I created the new instance on that server DBCA attempted to start and it failed gaving me a crs error. I found out later that the other 2 nodes went down and the new one that did not start correctly was the only one up!! I went and stopped the new instance and restarted the first 2. The associated services did not start with the instance so I had to start each manually. The trace files show an ORA-29702 error with cluster group service and the instance being stopped on both of the existing nodes. No other error messages stood out.

Now I cannot get any crs services to start on that 3rd node even if I attempt to start manually. I have also tried stopping all and restarting and that does not work. I found another post on this forum from you and followed it. The ASM service was fine the entire time through all the logs and I don't know how to verify LMON in Windows but I didn't see any LMON errors in the alert log. Also, the voting disks are online. Each node has their own and they are mirrored. Where else to look?

View 7 Replies View Related

How To Back Up Database Call Without Archived Logs

May 11, 2011

here i have an question with oracle database backup strategy.my question is

how to backup my oracle database call DB11G without archived logs while the database is open for user activity and also this should be the base for an incremental backup strategy?

View 5 Replies View Related

Generate Software That Used VB As Front End - Oracle 10G As Back End?

Mar 31, 2007

I am trying to generate a software that used VB as the front end and Oracle 10G as the back end. I have noticed that the date format is VB is : m/dd/yyyy whereas the date format in oracle is dd-mm-yyyy. Whenever i try to insert a tuple with either of the date formats, the program halts running and after a long time , i get a message saying a deadlock was encountered. I have never had any formal introduction to either of the two tools

View 1 Replies View Related

SQL & PL/SQL :: Revert Back Package Changes To Previous Version?

Mar 29, 2012

I overwritten the package and want to get the previous version.

Is there a way I can get it using FLASHBACK or any other feature?

My user_recyclebin is showing only tables.

SELECT object_name, original_name, TYPE
FROM user_recyclebin;

View 10 Replies View Related

PL/SQL :: How To Convert BLOB To BASE64 String And Back

Aug 15, 2012

We have a forms application in Forms 10g. We scan a document and gets it to a image item in the Form. Now, we need to pass this image to a 3rd party application (document mgt. system, ). They have given us a web-service for this. We have to convert the image to a BASE64 string and then invoke the web-service method and input it.

The, 3rd party application document management system will also send images as BASE64 strings through the web-service. We need to convert them back to a blob and put to an image item in the form so that it can be viewed by the end-user.

So, how can we convert a image item in form (as BLOB) to a base64 string and also do the reverse???

View 2 Replies View Related

PL/SQL :: Send Regexp Back Reference To A Function?

Jul 23, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf

[code]...

View 3 Replies View Related

RMAN :: Redo Log Sequence Gets Pushed Back

Jul 31, 2013

Today we reverted back our Virtual machine to the snapshot taken on 7/25. Sure enough it worked.When you revert back the snapshot, you are literally going back in time so that means the redo log sequence gets pushed back.  So, we start back old log sequence number which is where we were on 7/25.  The problem is that we took the RMAN today before the snapshot was taken, now that we reverted back to old date 7/25.  Now what happens to the backup pieces that we took after 25th July to till date in RMAN? do I need to delete those backups which were in there in RMAN taken after 7/25?

View 15 Replies View Related

Force Query To Always Bring Back Certain Amount Of Rows

Jun 24, 2010

Is there a way to force a query to ALWAYS bring back a certain amount of rows, even if some of the rows are blank?

I know there's a way in MSSQL but not sure how to in SQL+

View 7 Replies View Related

SQL & PL/SQL :: Convert Data Back To Special Character Values?

Nov 4, 2011

Enterprise Edition Release 10.2.0.5.0

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>

There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.

SQL> select * from vin_temp;
ATTR
--------------------------------------------------------------------------------
���9999
���9998
���9997
���9997

[code]....

Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...

It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.

SQL> SELECT attr, dump(attr), convert(attr,'UTF8', 'AL32UTF8') from vin_temp;
ATTR DUMP(ATTR) CONVERT(ATTR,'UTF8','AL32UTF8'
------------------ ---------------------------------------------------------------- -----------------------------
���9999 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,57 ���9999
���9998 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,56 ���9998
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997

[code]....

Here is the script to create table with those type of invalid records.

create table VIN_TEMP
(
attr VARCHAR2(50 CHAR)
);
insert into VIN_TEMP (attr) values ('���9999');
insert into VIN_TEMP (attr) values ('���9998');

[code]....

View 1 Replies View Related

Server Administration :: Flash Back Recovery Area

Mar 3, 2012

what is flash back recovery area? what is its main function and usage

View 1 Replies View Related

Server Administration :: Back Track OS Processes To RDBMS

May 5, 2013

I have two Oracle databases (test1- 10gr2,test2 - 11gr2) on rhel 5.5. is it possible to find which particular oracle process belongs to which home from OS level. was trying to find which db processes are consuming more resources on my host.

View 4 Replies View Related

Client Tools :: SQLPLUS For Oracle 11 Not Rolling Back

Sep 3, 2013

I have a shells script which invokes a SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.

My sql file has 3 lines 3 are correct and 1 is incorrect. For example:
INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);

Lets say file is called 1.sql

My shell script invokes this SQL as follows: (Where $File1 = 1.sql)

$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
connect $DB_USER/$Password1@$Database1
SET AUTOCOMMIT OFF
@$File1
WHENEVER SQLERROR EXIT ROLLBACK;
EOF
[code]......

So tried SET AUTOCOMMIT, tried SQLERROR ROLLBACK and tried few variations.

View 7 Replies View Related

Forms :: Changing Background Color Back To Default

Sep 17, 2010

I created a tab canvas and changed the background color of one of the tabs. Now I am unable to change it back to the default color.

I tried entering "None" in the Background Color property and even tried keeping the field blank. But it keeps on prompting me to enter a color value. Now it is looking funny with all the tabs in the default color and only one tab in some different color.

How do I get back the default color?

View 5 Replies View Related







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