Client Tools :: Display Output In Sql

May 15, 2010

having trouble displaying stuff on screen. It compiles the program successfully but just doesnt show any output Try using this (SET SERVEROUTPUT ON SIZE 4000) does nothing though. here my code

DECLARE
/*This program coverts a date from one fomat eg 1/31/10
into another format eg January 31,2010 */
/*declaring variables
assign a date that is in one format*/
EXAMPLE_DATE DATE := TO_DATE(' 1/31/06',' MM/DD/YY' );
/*finding the position of where the forward slash are*/
position_finder NUMBER := INSTR(EXAMPLE_DATE,'/');

[Code]....

View 2 Replies


ADVERTISEMENT

Client Tools :: Spooling Output In Windows

May 12, 2010

I am an IT auditor who has been using Oracle 10g Express to test some audit scripts we have created. I write the output of each query in the script to a separate file because it makes it easier for us to go through. However, I cannot get SQLPlus to spool the output files at all. I do not get any errors from running the script, and I can see the results in the SQLPlus window, but I cannot find where the files are actually spooling to, if they are spooling at all.

I didn't specify a network path for any of the spool files because the scripts need to be as generic as possible so they can run on any Windows or UNIX box. An example of my code is below:

spool Audit_Ver

SELECT * FROM V$VERSION;

spool off

But if I search my harddrive for "Audit_Ver" no such file can be found anywhere.

View 7 Replies View Related

Client Tools :: Delimited Output From Sqlplus

Mar 3, 2010

I 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.

View 10 Replies View Related

Client Tools :: Getting Each Record To Output On To One Line

Apr 16, 2010

Using SQL*Plus and having trouble getting each record to output on to one line. The fields I'm querying are:

TEMPLATEID NOT NULL NUMBER(10)
EXPERIMENTID NOT NULL NUMBER(10)
NAME VARCHAR2(200)
CREATEDATE DATE

Obviously, it's the NAME field causing me problems? Have tried using FORMAT, but no change in look of output.

COLUMN TEMPLATEID FORMAT 999999
COLUMN NAME FORMAT A40 WORD_WRAPPED
COLUMN EXPERIMENTID FORMAT 999999

View 7 Replies View Related

Client Tools :: Spool Output And Rollback

Oct 26, 2011

I need to do the following via the sql plus command prompt :

1. Execute a large ".pls" script file which does various inserts and updates to a table.
2. Spool the output of "select * from updatedtable;" to a text file to see the changes made.
3. Rollback all the updates and inserts in the script.

View 2 Replies View Related

Client Tools :: Spool Output In PDF Format?

Jan 3, 2011

I am looking forward for getting the output of audit records in a non-editable format as in pdf or something of that sort. Is it possible from Oracle 10g Rel-2?Can i spool output as PDF format?

View 6 Replies View Related

Client Tools :: How To Get DBMS_OUTPUT.PRINT_LINE Output In TOAD

Sep 22, 2010

How i can get DBMS_OUTPUT.PRINT_LINE out put in TOAD.

View 6 Replies View Related

Client Tools :: REF CURSOR To Print Output From Given Input?

Jan 26, 2012

create table a_user
(
u_name varchar2(30)
, grp varchar2(30)
)
;
INSERT INTO a_user VALUES ('abc', 'new');

[code].....

sql:

SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT

[code].....

getgrpname is a function to get grp when I input the u_name

Now I want to create a proc to display the output from the sql when I give the p_u_name as input. I am thinking some thing like this:

create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR) IS
IS
BEGIN
OPEN rc_data FOR
SELECT
a.grp

[code].....

how to print output in SQL developer

View 2 Replies View Related

Client Tools :: Unable To Print Query Output On TOAD

Jan 24, 2012

When i run the below mentioned query(similar query)on TOAD. It will run without any errors and i can see "PL/SQL Procedure successfully completed" in the task bar.....

However i am unable to see the output for the query.

print the the query output on TOAD ....

**** Sample Query Starts *****
DECLARE
i PLS_INTEGER;
BEGIN
SELECT NVL(i, 93)
INTO i
FROM DUAL;
--print i;
--dbms_output.put_line('i1: ' || i);
END;
**** Sample Query Ends*****

**** Actual Query Starts *****
-----------------------------------------------------------------
DECLARE
UnxDate number(6);
MyResult number(6);
Todaysdate date := TO_DATE('17-01-2012 00:00:00','dd-mm-yyyy hh24:mi:ss');
BEGIN
SELECT ROUND (Todaysdate - TO_DATE('01-jan-1970','dd-mon-yyyy') ) INTO UnxDate FROM dual;
[code]......

View 9 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 :: How To Eliminate Blank Lines In Between Queries In Output

Apr 28, 2010

I am spooling to a text file some output for a client. The file has 4 queries in it, one creates a header row, another a comment row, another the data rows and finally a trailer.

Code looks something like this:

/*
Custom Extract
Project: Plan Data Extract
Product: EOWin 4.02 - Oracle db
Use: Script to create above extract and spool results to text file
Input Parameters: &1 Path and name of output file
*/

[code]....

and the output looks like this:

HDR,04272010,Plan Data

CMT,Plan Num,Plan ID,Plan Name,Shares Allocated

DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4

but the client and I want the output to look like this with no blank lines in between the queries:

HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4

View 3 Replies View Related

Client Tools :: TOAD - Query Output With Column Headers?

May 14, 2010

I can't figure out how to configure the query editor to copy the column headers with the query results on to the clipboard?

View 18 Replies View Related

Client Tools :: SQL Plus 10g Display Settings?

Feb 25, 2010

I am using SQL Plus 10g and when I execute a query my column widths are quite narrow, hence the titles and data are displayed as shown below. How can I change the settings so they are displayed side by side and the whole coulmn titles are visible?

TIMESTAMP
---------------------------------------------------------------------------
ACCOUNT_ CUSTOMER_ID BRANCH_ID TR AMOUNT ACCOUNT_BALANCE
-------- ----------- ---------- -- ---------- ---------------
23-NOV-09 13.45.14.000000
87654321 1002 1 WD 1700 -125

View 2 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

Client Tools :: Inhibit Displaying Messages In Script Output Windows

Feb 17, 2011

I began studying Oracle and SQL Developer and I have the following question: If I run a large number of INSERT commands into a table using a SQL Worksheet from the SQL Developer, the fact that in the "Script Output" window a "1 rows inserted" message appears for each created record. slows the execution. How can I inhibit displaying this message in the "Script Output" window during the execution of my INSERT commands?

View 5 Replies View Related

Client Tools :: How To Get SQL Query Output Into Text File Without Spool Statement

Mar 7, 2011

I would like to store my sql query output into text file.Like for example:

select name from emp where emp_id=101;
Here output should be in text file as
swapna.

I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.

View 1 Replies View Related

Client Tools :: Removing Final Carriage Return From Spooled Output?

Jul 29, 2010

I spool an output using the following script and get a final carriage return at the end. This fails our SSIS bulk insert task. Sample below:

SET SPACE 0
SET HEAD OFF
SET FEEDBACK OFF
SET TERMOUT OFF
set echo off
set newpage 0
set space 0
set pagesize 0

[code]....

View 13 Replies View Related

Client Tools :: Vertical Display Of Table

Jun 29, 2010

I would like to format my output for the select query.

Example:

SQL>select empno, ename from emp;

empnoename
1987766Jack Blake
187765Greg Ni

But I would like to get the output in the below format

EMPNO:.................1987766
ENAME:.................Jack Blake

EMPNO:.................187765
ENAME:.................Greg Ni

I will need printing the column name. I would like to know how do we address the column name to print.

View 24 Replies View Related

Client Tools :: Display Option In Sqlplus

Apr 12, 2010

I m currently written a sqlplus query and executing as below

sqlplus -s << !
set head off pages off trimspool on termout off
spool sysdate.txt
select sysdate from dual;
spool off
!

With this query the sysdate is getting written in the sysdate.txt but it is also getting printed in the screen/terminal., which I don't need to. Is their any options available for that.

Note: I came to know that "Termout off" will work only when we execute the query from a file and as a result I can still see the sysdate getting displayed in the screen.

View 3 Replies View Related

Client Tools :: How To Display The Column Heading

Aug 2, 2010

how to dispaly the column heading.

I tried to do the below.

03-AUG-10> set pages 0
03-AUG-10> set heading on
03-AUG-10> select count(*) numberofrows
2 from iceberg_mig_acnts_stage2 s2, tvp109workorder t109
3 where s2.no_account = t109.no_account

[Code]....

View 9 Replies View Related

Client Tools :: Column ROWID Doesn't Display?

Feb 13, 2012

the table is a view. But the column ROWID doesn't display in Toad. He just ignores that.

View 3 Replies View Related

Client Tools :: Display Windows On Oracle SQL Developer?

Sep 10, 2013

find in the menu of Oracle SQL Developer how to display the result of explain plan for .. I have just "Succès de l'élément plan FOR".

View 3 Replies View Related

Client Tools :: How To Display Chinese Characters In Sql Developer

Apr 22, 2013

I have a table named invoice_info(INV_NUMBER number,BILL_TO_NAME char(55),SHIP_TO_NAME char(55)) which is having chines characters in the BILL_TO_NAME & SHIP_TO_NAME fields.

If i am displaying this tables data on my sql developer it's showing data like some junk chars like ?????????¨¬???? for the fields BILL_TO_NAME & SHIP_TO_NAME .

How can i display them as chines chars on sql developer.

View 3 Replies View Related

Client Tools :: Display Clob Column In Text File In Sqlplus

Mar 30, 2012

I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file

1) Outfile file size is huge high compare then table segment data.

2) last column having extra space.

The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.

#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH

[Code]....

View 2 Replies View Related

Client Tools :: Number Data Type Column Does Not Display As Entered?

Jun 28, 2010

Now i have a problem which i am facing for the first time. The problem is:

I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered.
SQL> create table test1
2 (
3 startno number(25),
4 endno number(25)
5 );

Table created.

SQL> insert into test1

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Output Layout And Display Changes?

Oct 11, 2011

SQL

SELECT ug_name, rss_user_name
FROM ru_ug
WHERE rss_name = 'EXAMPLE' AND ug_name LIKE 'cn=SNG-PWI11_%'
ORDER BY ug_name

Output

UG_NAMERSS_USER_NAME
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname55,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname49,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname19,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname932,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname56,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname77,ou=Netgroup,dc=TMM,dc=netops

But I wish other output:

UG_NAME1 UG_NAME2
RSS_USER_NAME1 RSS_USER_NAME3
RSS_USER_NAME2

Table:

CREATE TABLE RU_UG
(
RSS_USER_NAME VARCHAR2(255 BYTE) NOT NULL,
UG_NAME VARCHAR2(255 BYTE) NOT NULL,
RSS_NAME VARCHAR2(32 BYTE) NOT NULL,
RSS_TYPE VARCHAR2(12 BYTE) NOT NULL,

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Display Output IDs Which Are Having Status Both Y And N?

May 11, 2012

CREATE TABLE stud(id number,Status CHAR(1))

INSERT INTO stud values(10,'Y')
INSERT INTO stud values(10,'Y')
INSERT INTO stud values(10,'Y')
INSERT INTO stud values(10,'N')

[code]...

ID STATUS

10 Y
10 Y
10 Y
10 N
20 Y

[code]...

I want to display the out put the IDs which are having the status both 'Y' and 'N'The output should be

ID STATUS

10 Y
10 Y
10 Y
10 N
20 Y
20 N
20 N

View 12 Replies View Related

SQL & PL/SQL :: Display In List Non-Existent Value In Output

Mar 6, 2012

I am not sure if this is even possible however I will give it a try here. is there a way to take a value from an In List that doesn't exist in the table and get SQL PLus to output that value with an assigned decode construct. example.

1 select decode(count(object_name),1,'EXIST',null,'NOT EXIST',0,'NOT EXIST'),
2 object_name, object_type
3 from dba_objects where object_name in (
4 'GV_$METRICNAME',
5 'GV$METRICNAME',
6 'GV_$METRICGROUP',
7 'GV$METRICGROUP',
8 'ABC'
9 )
10* group by object_name, object_type
SQL> /

DECODE(CO OBJECT_NAME OBJECT_TYPE
--------- ------------------------- -------------------
EXIST GV_$METRICNAME VIEW
EXIST GV_$METRICGROUP VIEW
EXIST GV$METRICNAME SYNONYM
EXIST GV$METRICGROUP SYNONYM

and have it look like this:

DECODE(CO OBJECT_NAME OBJECT_TYPE
--------- ------------------------- -------------------
EXIST GV_$METRICNAME VIEW
EXIST GV_$METRICGROUP VIEW
EXIST GV$METRICNAME SYNONYM
EXIST GV$METRICGROUP SYNONYM
NOT EXIST ABC

View 4 Replies View Related

SQL & PL/SQL :: Display Output For Null Column

Jul 24, 2012

I am having following table, and below are the out put and desired output.

CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;

[Code]....

Query:

SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;

Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13

Desired Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0

How can this be acheived?

View 1 Replies View Related

Sql To Display Output In Horizontal View?

Sep 8, 2012

I need to display the sql query output in horizontal manner. Below I have given the script.

create table TestTable (id number, name varchar2(10))
insert into TestTable values (1, 'John')
insert into TestTable values (2, 'Mckensy')
insert into TestTable values (3, 'Valneech')
insert into TestTable values (4, 'Zeebra')

[code]....

But for my requirement, I need to display in horizontal manner.

ID   1       2       3           4
Name John    Mckensy Valneech    Zeebra

View 3 Replies View Related







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