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
ADVERTISEMENT
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
Sep 13, 2012
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
View 12 Replies
View Related
May 23, 2012
I need to print temperature along with its symbol. how it is possible in oracle reports? for example 45 (degree centigrade in symbol format.)
View 5 Replies
View Related
Aug 5, 2010
I use SQL Developer. I cannot select/print any floating point values.
My script:
variable xx NUMBER;
declare
x number;
begin
x := 1.5;
:xx := x;
end;
.
run;
PRINT :xx;
Output:
anonymous block completed
xx
-
1
What is the problem? How can print a floating-point value?
View 7 Replies
View Related
Apr 6, 2011
Using 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;
/
View 9 Replies
View Related
Aug 27, 2011
I want to put some line in my .sql file. The below is my issue :-
SQL>spool /tmp/Tablespace_Remove.ksh
SQL>echo >#!/bin/ksh---- I want to enter this line to my Tablespace_Remove.ksh
SQL>spool off;
View 3 Replies
View Related
Feb 8, 2012
In 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 ?
View 1 Replies
View Related
Oct 26, 2010
i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.
even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:
procedure is:
create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;
i pass parameter as given below:
begin
testproc('abc & def');
end;
if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.
begin
testproc('abc &'||'def');
end;
View 9 Replies
View Related
Oct 3, 2011
I am trying to ultimately as the title says separate a user input list into one column of entries. I am doing this through Cognos not a normal SQL editor which is what makes this a little harder to do. So far I have gotten that in general I can use the
SELECT 'First Entry' Asset FROM Dual Union
SELECT 'Second Entry' Asset FROM Dual Union
SELECT 'Third Entry' Asset FROM Dual
and this will give me 3 entries of data in one column. More can be added as long as the last statement doesn't have the union on it. So, the next step it would seem is to have a for loop combined with an if then or case statement that would find the number of entries and loop until we reach the number of entries and give me either SELECT 'First Entry' Asset FROM Dual Union or SELECT 'First Entry' Asset FROM Dual if we are on the last entry. I don't know the lingo to do this though. I have tried to get this to work with a simple test like cat, dog, horse, cow, pig, etc but it's frustrating that I can't get it to work. I can do all the individual steps I just can't seem to get it to work together. I have all the functions I need, I just need to the syntext to do a for loop along with an if then or case statement where the outcome is a valid select statement.
View 5 Replies
View Related
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
View Related
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
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
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
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
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
Mar 24, 2011
I have this SQL statement:
WITH data
AS (SELECT user_id,
jc_name,
[Code]....
I wish to do something like
if results > 10 print an error message (and no results)
if results < 10 print the results/output
View 1 Replies
View Related
Sep 22, 2010
How i can get DBMS_OUTPUT.PRINT_LINE out put in TOAD.
View 6 Replies
View Related
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
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
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
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
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
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
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
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
Apr 5, 2011
To keep things simple: lets say that this is my procedure:
create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;
how would i be able to execute this from with in sqlTools to see the result ??? i have tried everything...
my goal is in the end to execute this from Magic 8 (to those who herd of it). p.s this needs to work in oracle 8
View 4 Replies
View Related
Apr 17, 2013
i want to print a message just before asking for input to a bind variable
SQL> SET serveroutput on;
SQL> DECLARE
2 pname varchar2(20);
[Code].....
but i want to print 'hello world' before asking value for bind variable like:
hello world
Enter value for num: 1
old 9: WHERE ID = #
new 9: WHERE ID = 1;
then how to achive it?
View 4 Replies
View Related
Jan 2, 2013
I am getting an error while printing a sql query which is in a string inside a stored proc. I used
dbms_output.enable(1000000);
dbms_output.put_line(v_query);
and error "I get ORU-10028: line length overflow, limit of 255 bytes per line"
How do I print everything?
View 3 Replies
View Related
Aug 4, 2010
Is it possible to print the output of a sql query in Excel format without using third party tools like Toad, Hora or any front end Application.
View 10 Replies
View Related