SQL & PL/SQL :: Write A Command That Will Output DD And Month Only?

Sep 13, 2010

I am trying to write a command that will output DD and Month only.

This is what I have so far:

set serveroutput on size 4000;
declare
todays_date date;
todays_month date;
current_month varchar(20);

[code].....

View 2 Replies


ADVERTISEMENT

PL/SQL :: Write In Sql Command Line Display CUSTOMER Number?

Sep 13, 2013

how to write this in sql command lineDisplay CUSTOMER number, name, rep, balance, and credit limit for all CUSTOMERs of Rep 20. 

In addition, display this information for CUSTOMERs of rep 65 who have a $10,000 credit limit.  Arrange the output by credit limit descending within rep ascending. no more than two decimal digits

View 3 Replies View Related

How To Structure Output Using Windows Command

Sep 4, 2008

The output below is looked mass..

1) the capital letter is the name of column

2) consists 8 data for each column name

Quote: STATISTICS_DATE INSTANCE_NAME GENESIS_PORT
-------------------- ---------------------------------------------------------------- ------------
COUNT TOTAL_TIME MAX_TIME MIN_TIME AVERAGE_TIME
--------- ---------- --------- --------- ------------

03-AUG-08 MatlMgr_UpdateInstance140028
35 6.8685906 .83870006 .06330299 .19624545

View 2 Replies View Related

Format Output File By Command Spool

Jan 20, 2011

I use sqplus in oracle to output the command output to text file .

I use below set environment variable.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;

In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;

any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"

View 1 Replies View Related

SQL & PL/SQL :: Redirect Output Of A Command To Variable Or File?

Jun 18, 2010

I have a command which get the versions of the schema's for which I dont have access to. It is a utility.

the command goes:

SQL>EXEC ite_dta_tool.dta_open_utl.get_db_version('HH2_SGH21_SS')
DATABASE_VERSION_ID
-----------------------
hh2_ax_db_0.8.1.2.0
Elapsed: 00:00:00.25
SQL>

'HH2_SGH21_SS' is a schema I dont have access to. I have a whole bunch of schemas like this and would like to get versions of all those schemas putting it in a loop in pl/sql.

1: whats the best way get this output to a variable.

2: whats the best way to get the output to a file.

View 8 Replies View Related

Client Tools :: Format Output File By Command Spool?

Jan 20, 2011

I use sqplus in oracle to output the command output to text file .

I use below set environment varialble.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;

In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;

any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"

View 8 Replies View Related

Client Tools :: Break And Compute Command Usage To Modify Output

Oct 14, 2010

I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:

Original script for the report:

set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
[code]....

The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.

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

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related

Brtools Command Versus Oracle Command For Shutdown And Startup (statistics)

Sep 13, 2013

we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?

View 3 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

SQL & PL/SQL :: For Each Month Sum Values From Month Before?

Nov 25, 2010

I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.

Date input

SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);

Result

ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250

View 17 Replies View Related

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

Sep 25, 2013

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);

[Code]....

My expected output should be like below:

Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40

how I can achieve the expected output using a single sql query

View 6 Replies View Related

Day Of Month Must Be Between 1 And Last Day Of Month

Sep 5, 2013

My goal is to:

A) extract subscriber numbers from TCME153.SUB then
B) bounce those subscriber numbers off of a disconnect date i.e. DISCO_DTE_SBB then
C) bounce A) and B) off of an item table i.e. ITV_ITEM_VALUE

My hope is that the first query works as intended. Second and third queries throw error 'ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH'.

1) SELECT DISTINCT SUB_ACCT_NO_ITV
FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
WHERE EXISTS (SELECT SBB_BASE.DISCO_DTE_SBB FROM (SELECT SUB
FROM TCME153.SUB));

2) SELECT DISTINCT SUB_ACCT_NO_ITV
FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Last Day Of Each Month

Jan 21, 2011

I am trying to query on the dates in the table that are the last day of each month. The last day of the month may not be in the table. Example 07/30/2010 is the last day in the table for July 2010, but 07/31/2010 is the last day of the month. My query should give me 07/30/2010 data since it is the last day of the month in the table.

I just need a where clause that will only give me the data for the last day of each in the table.

View -1 Replies View Related

SQL & PL/SQL :: How To Get Last Month Record

Jun 10, 2010

i want to get the last month starting from today onwards.

1) select the record of last month that is if this month is june then select the record for 1st may to 31st of may

View 7 Replies View Related

SQL & PL/SQL :: Find Last Day Of Given Month?

Sep 25, 2012

I am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer

select to_char(last_day(sysdate),'DD-MON-YY')from dual

ans : 30-SEP-2012

But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.

View 2 Replies View Related

SQL & PL/SQL :: Range By Day And Month

Sep 20, 2010

I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.

For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.

e.g. a range could be from today:Sept,20 to Apr,18.

so what i was trying is to select doing the following.

select TABLE_X_ID, TABLE_X_BIRTH_DATE
from TABLE_X
where to_date(TABLE_X_BIRTH_DATE, 'DD/MM')
between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM')
and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')

first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.

View 13 Replies View Related

SQL & PL/SQL :: First Business Day Of A Given Month?

Dec 6, 2010

I need to calculate first business day of a given month . Below is complete explanation

Business day=sould not include weekends and holidays.

In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.

So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.

I'll be passing the month begin date as parameter..so the function should return the first business day for that month.

View 16 Replies View Related

SQL & PL/SQL :: Last Thursday Of A Month

Oct 26, 2010

How to find the total number of Thursday in the current month ?

View 5 Replies View Related

PL/SQL :: Order By Month Name

Jun 20, 2012

I've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:

SELECT DISTINCT
CASE
WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date)
[code]....

The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause

ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));

But I got an ORA-01866: the datetime class is invalid. I'm using Oracle 10g xe (outdated, I know). get the months to show up in order?

View 7 Replies View Related

SQL & PL/SQL :: Order By Month

Jan 31, 2013

How I can see the order by employees,year wise and monthwise (month should start from january and so on..)

SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') YEAR,TO_CHAR(HIREDATE,'Month') MONTH ,count(*) total
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');

View 8 Replies View Related

How To Write Exception Handler For PL / SQL

Jan 22, 2007

How to write an exception handler for the error "PL/SQL: ORA-01031: insufficient privilege"

View 2 Replies View Related

Can't Write SQL Scripts On Oracle G10

Apr 8, 2009

I just install the oracle g10 on my laptop, which runs windows XP and it seems to work fine BUT it will not allow me to write Scripts I go to the Script Editor, i can write on the "Script Name" but below i cant ever put the cursor to write the script!

View 3 Replies View Related

How To Write Audit Script

Oct 14, 2008

To write a audit script, that will analyze SQL usage and the performance of both individual statements and the overall memory utilization.

View 1 Replies View Related

SQL & PL/SQL :: How To Write Package For Deletion

Apr 21, 2012

how to write the package for deletion?

View 1 Replies View Related

SQL & PL/SQL :: How To Write Correlated Sub Query

Sep 13, 2010

I have employee, location, city tables, I have written following code to use joins

select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';

The same can be written by using non- correlated sub query as follow..

select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))

I need to implement the same concept using correlated sub query...

View 15 Replies View Related

How To Write Trigger In Oracle 10g

Apr 12, 2011

I am using SQL*Plus to run this Oracle10g. Here is my problem I have to solve: After a new record is added to the task table, write a trigger that reports the new project balance to the user while updating the customer balance to reflect this additional task cost. Insert a new record to show the trigger works. I somewhat understand triggers,I think I have to combine tables to solve this. Here are my tables/attributes I can use to solve it:

CREATE TABLE customer
(
custnoNUMBER(3)CONSTRAINT ccustpk NOT NULL PRIMARY KEY,
custname CHAR(20) CONSTRAINT ccustnamenn NOT NULL,
phone CHAR(12),
curr_balance NUMBER(9,2) CONSTRAINT ccustbalancenn NOT NULL

[code]...

View 8 Replies View Related

SQL & PL/SQL :: How To Write LAST_INSERT_ID () In Oracle

Sep 25, 2013

I have a procedure in mysql that need to be converted to oracle.I tried to convert it ,everything looks good except the function LAST_INSERT_ID().

mqsql procedure

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_ac_message`(
IN in_process_run_id INT,
IN in_processID INT,
IN in_messagename VARCHAR(25),
IN in_message VARCHAR(200))
BEGIN
[code]......

oracle procedure

create or replace
PACKAGE body Mysql_To_Oracle
IS
Procedure Add_Ac_Message(In_Process_Run_Id In Number,
In_Processid In Number,
In_Messagename In Varchar2,
In_Message In Varchar2)
[code]....

Error:
ORA-00904: "LAST_INSERT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"

View 6 Replies View Related

SQL & PL/SQL :: How To Write Column To Row In A Query

Nov 17, 2005

How to write column to row...in a SQL query?

For example..

SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40

The above output to be written in a single row like given below.

7369 20 10 40

1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other

2) It should be in SQL query only..Not in procedures or functions.

View 9 Replies View Related







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