PL/SQL :: How To Give Space Delimited Input Values In A Query
Dec 21, 2012Example:
select * from emp
where empno in (123 234 345 124)
ADVERTISEMENT
SQL & PL/SQL :: Converting Comma And Pipe Delimited Input String To Nested Table
Nov 12, 2010I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
SQL & PL/SQL :: Take Input From File And Give Output To Other Txt File?
Oct 15, 2013i need a script which can take input from a file and give output to other txt file, something like below :
input.txt
001
002
003
004
Query:
select name from employee where employeecode=<ENTRY FROM INPUT.TXT FILE>
Result should go to OUTPUT.TXT File.
SQL & PL/SQL :: Convert Column Value To Delimited String Using Query?
Jan 10, 2012I want to convert a column value to a delimited string using a query.
Example
TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X
Select Col3 From TableA where Col2 = 'x200'
Expected Output:
'MIS-X','BTS-X','TYR-X'
How To Give Table Name Dynamically In A Select Query
Aug 23, 2012I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
SQL & PL/SQL :: Query That Will Give List Of Views Using Three Tables?
Aug 16, 2011I wanted a query that will give me the list of views using three tables
when i fire
select * from dba_views where text like '%SELECT%';
it is giving me error as expected number got long.
Which Query Give Lock Conflicts History In Database
Aug 29, 2013I would like to know 2 days back lock conflicts session information which user held the lock,sql text,when the lock released. any sql query or view in oracle 11g db.
View 1 Replies View RelatedClient Tools :: Having Multiple Input Values
Apr 6, 2011Using pl/sql block , i tried to have a certain input values from Sql prompt but it doesn't work when i invoke it.
Here the simplest
declare
a number(4);
begin
for i in 1..10 loop
a := &a;
end loop;
end;
/
Client Tools :: How To Restrict Input Of NULL Values
Feb 8, 2012In SQL*Plus i am accepting value like below examples
1] ACCEPT v_number prompt 'Enter Number ='
2] column db_owner new_value db_owner;
select a.SETTINGVALUE db_owner
from ABC a, DEF b
where a.application_id = b.application_id
I want to confirm after executing this .sql that this variables v_number and db_owner should not have NULL value. I did searched a lot for SQL*PLUS commands. Only way i can check this is by writing anonymous PL/SQL block but i dont want to go for that option. How to restrict input of NULL values ?
SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function
Dec 23, 2012My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
Application Express :: Fetching Values From HTML Textbox Or Any Input Type?
Jul 27, 2012I am working on APEX 4.1.1 on Linux server. I have created one application in which I have take a HTML region where i have written the following code:
<input type="text" id="APP_ITEM1" name="APP_ITEM1" />
I am able to see the text box in page, but not able to fetch the value from it.
PL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function
Nov 22, 2012resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
SQL & PL/SQL :: Exception Query - Alphanumeric Input?
Dec 27, 2012I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.
I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.
I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?
DECLARE
v_sal NUMBER (12) := '&Enter_salary';
v_last_name VARCHAR2(10);
BEGIN
SELECT last_name
[code]...
SQL & PL/SQL :: Date Query - Input Year In Specified Range
Dec 26, 2012Getting problem with below query.
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error starting at line 1 in command:
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
Query For Blob Space
Oct 26, 2011explain the difference in numbers between the queries? I am acutally more concerned about the ETM_XML clob since the descrepancy appears to be bigger.
SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = 'ETM_RAW_XML';
ETM_RAW_XML
IFD_XML
SYS_LOB0007260522C00012$$ 87870668800
ETM_RAW_XML
ETM_XML
SYS_LOB0007260522C00011$$ 125199974400
SQL> SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
83848300852 ~ 78.0898154266179 GB
SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
61907953222 ~ 57.6562743838876 GB
SQL & PL/SQL :: Space In Every Character Of A String With Single Query
Mar 19, 2012How can we get 'space in every character of a string with Single select query'
for example:-
string 'INDIA'
result should be 'I N D I A'
Query To Check Disk Space Usage For Oracle
Apr 21, 2010What is the query to check disk space usage for oracle 10g ? What is the query to check percent of disk space usage for oracle 10g ?
View 1 Replies View RelatedSQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters
Jul 1, 2013I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
Any Way To Free Space From Sysaux Table Space?
Oct 3, 2012Just now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
PL/SQL :: Update A Column With Tab Delimited
Jul 18, 2013I need to update a column with tab delimited ie that column value should be tab delimited. Eg:- Url_name is the column and column values =[URL]. THe space between [URL] should be tab delimited. I wanted to use a update query to update this values with tab delimited.
View 2 Replies View RelatedPL/SQL :: Find And Replace Value In Delimited String
Sep 4, 2013I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
Extract Big Table To A Delimited File?
Mar 17, 2013A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,the column delimiter is "&|" and row delimiter is "$#".I cannot do it from TOAD as it is hanging while extraction of big table.
View 9 Replies View RelatedClient Tools :: Delimited Output From Sqlplus
Mar 3, 2010I have a requirement to get a delimited output file by executing a select query.
For e.g.
select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23
i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23
But i want to remove those pipes in between the column name.
I tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23
some unwanted spaces in between...but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23
the query which am using is stored in a .sql file.
SQL & PL/SQL :: Spooling Data In Pipe Delimited File
Feb 15, 2010I am trying to Spool the data in pipe delimitted csv file but some of the records going on another line from the same records. Currently some of the data going to next line as below oulined in the 2nd and 3rd line (in bold - |Home & Family) . I have following sql setting in my spool file:
set linesize 4000 pagesize 0 trimspool on feedback off verify off echo off
set define off
spool Stk_hold_Sec_Tsk.csv
I tried increase linesize to 5000 but its not working.
Ex.
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
Data should be like into the file:
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager|House & street
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User|House & street
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
I think it should be something with linesize or pagesize but not sure
SQL & PL/SQL :: Convert ID And Comma Delimited List To Rows
May 31, 2011how can I convert
select 1 as id, 'role1,role2,role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
to
select 1 as id, 'role1' as roles from dual union all
select 1 as id, 'role2' as roles from dual union all
select 1 as id, 'role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
?
I would prefer sql then plsql. Script for creating a test table:
create table CONVERT_LIST(id integer, roles varchar2(100));
insert into CONVERT_LIST values(1,'role1,role2,role3');
insert into CONVERT_LIST values(2,'role1');
SQL & PL/SQL :: Producing Pipe Delimited Flat File
Oct 20, 2011I have a task to code a procedure and function in sql developer that will extract data within a date range (Jan 1 to April 3) from a source (source_name: expenses)and produce a text-file in pipe-delimited format.
View 2 Replies View RelatedPL/SQL :: Split Delimited Cell Into Multiple Rows
Aug 6, 2013I have a table that has about 20,000 rows.
There is a column called Keyword which has values like below:
File_IDKeyword1SMITH;ALLEN;WARD;JONES; BRADY2S&P500;TOPIX3SMALL;LARGE;MEDIUM
I want to output the data like this: FILE_IDKEYWORD1SMITH1ALLEN1WARD1BRADY2S&P5002TOPIXetc
I'm using this query and it works: SELECT STG.FILE_ID, REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) AS KEYWORD FROM STG_TABLE STGCONNECT BY REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) IS NOT NULL
But its sooooo slow, its unusable. Is there a quicker way to return this output? Other info:KEYWORD is varchar2(4000) but rarely more than 100 bytes are usedOracle 11g2 !
Transform Fixed Delimited File To XML Format
May 8, 2013Need to transform a fixed delimited file to an XML format.
A WSDL file is given which is composed from a header and body. We need to map the fixed file to the body node.
Let me know the steps and also a sample xml for the same if possible.
SQL & PL/SQL :: DML Query To Interchange Row Values
Feb 24, 2010I need a DML Query to interchange row values
for example: i had a table called Tab1.there are 3 columns in that table
Tab1:
id name salary
----------------------
1 raj 5000
2 ram 7000
3 ravi 10000
i need query to display like below
id name salary
----------------------
1 ravi 5000
2 raj 7000
3 ram 10000
One DML Statement to get the above result.
SQL & PL/SQL :: External Table For CSV Delimited File Skipping Columns?
Jan 24, 2013I need to load csv file using an external table.
Structure of External Table:
---------------------------
create table A (col1 varchar2(30), col3 varchar2(30), col5 varchar2(30));
CSV FILE:
-----------
col1,col2,col3,col4,col5
A,B,C,D,E
1,2,3,4,5
The table data should look like
COL1 COL3 COL5
A C E
1 3 5
need to skip the columns in CSV file.