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.
I want to merge the following two sql statements into single output.
select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;
select id,code from dbo;
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
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?
below query is returning two rows.The thing now happening is the query is returning the output for both the case statements.But what is need only when the first case staement is NULL then it should go for second case.
SELECT DISTINCT CASE
WHEN esc.x1 = Substr(inp.y, 0, 3)
AND esc.x2 = Substr(inp.y, 4, 2)THEN esc.cc
WHEN esc.mcc = Substr(inp.y, 0, 3)
AND esc.mnc = Substr(inp.y, 4, 3)THEN esc.cc
[code]....
I tried using rownum=1 but it filters out valid records.correcting the above query so that if the first case is null then only it should go for second case.
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 RelatedI am stuck with this query.
I have a table "xyz" as -->>
SQL> select * from xyz;
A B
---------- ----------------------------------------
1 Hello
2 Hello
3 No Hello
4 No Hello
5 Hello
6 Hello
7 Hello
I want to print the output of this table as -->>
A B
---------- ----------------------------------------
1
2 Hello
3
4 No Hello
5
6
7 Hello
To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.
Either of SQL or PLSQL would d for me.
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
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]......
In Sales Order Form there is a print receipt button. In current situation On Clicking the 'Print Receipt Button' it submits a XML Publisher report(PDF output) to the concurrent manager. Currently to print that report i need to go view--> requests--> view output and then print. According to my new Requirement i need to print that report directly to local printer on one simple Print receipt button click in form.
I am trying to call the printers on server using custom.pll. The report is running successfully but it is not printing output to the local printer. I need to print the report to the local printers based on responsibility. Local printers are available on apps server.
Is it possible to print the document using custom.pll? Is there any other alternative to print the report on simple button click in form.
I searched many forums but i found customizations on form level. I am trying to customize the form using custom.pll. I found many examples like URL..... but i don't think i can use these practices in custom.pll. ADD_PARAMETER & RUN_PRODUCT dont work in custom.pll. I need to complete this requirement immediately.
Environment: Apps 11.5.10.2, Forms 6i
Here is the code that i am using in my custom.pll.
if (v_form_name = 'OEXOETEL' --and v_block_name = 'LINES_SUMMARY'
and name_in('parameter.ACTIONS') = 'PAYMENT_RECEIPT' )THEN
l_organization_id := Name_In('PARAMETER.OE_ORGANIZATION_ID');
l_order_header_id := Name_In('ORDER.header_id');
select to_number(oe_sys_parameters.value ('SET_OF_BOOKS_ID')) into l_sob_id from dual;
xml_layout := FND_REQUEST.ADD_LAYOUT('XXAFP','XXAFPOEXPMTRCRTF','en','US','PDF');
[code]....
Having a select query where the output looks as below, as you can see when there is no record for a particular date(01-JUn-12,03-JUN-12) the count will be zero.
SELECT TRUNC(updated_date) DATE1 , COUNT(1) COUNT FROM Temp_a
WHERE ZU_flag='N' AND circle_id 'NA'
AND TRUNC(updated_date) >=TRUNC(SYSDATE-18) AND TRUNC(updated_date) <=TRUNC(SYSDATE)
GROUP BY TRUNC(updated_date)
ORDER BY TRUNC(updated_date) DESC;
O/p
DATE1 count
10-JUN-12 14208
08-JUN-12 307825
06-JUN-12 138790
05-JUN-12 167562
04-JUN-12 51870
02-JUN-12 130582
01-JUN-12 239806
But i require a query in such a way that when there is no record for a date , the date has to get populated with zero count
Excepted O/P
DATE1 count
10-JUN-12 14208
09-JUN-12 0
08-JUN-12 307825
07-JUN-12 0
06-JUN-12 138790
[Code]....
I have tried with Case conditions and try to fetch that data through Sequence generating Date(not useful). I can do this with Cusror, but i need this to be done through single select query
Version oracle 11g
Is there a way to avoid multiple UNION statements and get statitc values in single SQL?
select 'Week1' as term from dual
UNION
select 'Week2' as term from dual
UNION
....
....Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that
as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.
We must show the output as evidence of doing the work.
Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!
BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/
Following is my table structure:
14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;
ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C
now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,
ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60
I'm struggling with a problem for quite a while now and can't find any solution online so far ...
OK I got three different Tables
cust_tbl:
customer:id
nuxx :1
naxx :2
baxx :3
suxx :4
[code]...
As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
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
EMP table
create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);
insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);
commit;
select * from emp1;
actual output :
empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000
Expected output :
empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000
I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.
I have to return only one row based on dept values for one empno. how to do in sql or plsql?
Following query is giving me the required output but i want to use reg_exp from same output.
SELECT TO_NUMBER ((SUBSTR (narr,
INSTR (narr, '#') + 1,
(INSTR (narr, 'DT')) - (INSTR (narr, '#') + 1)
) "Bill No"
FROM (SELECT '25 NOS. BILL BOOK FOR FAISALABAD @80/- TH A.R. PRINTER AGST BILL#21 DT:31-01-2013 TH
[code]...
Required Output using Reg_Exp
SQL>/
Bill No
----------
21
9
One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?
my code is..
CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
[code]....
but when i select the object of another schema, got output.
SELECT * FROM TEST_NEW.DEPT;
Which privilege i need?
Here i have one PL/SQL block which will returns the age of an employee using his id.
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER;
v_days NUMBER;
[code]...
It will returns an output of 27..I tried the same in Forms 6i using a text field and a button with a trigger "when_button_pressed". when i am entering the same id of employee i am getting a totally different answer
DECLARE
v_num NUMBER;
v_days NUMBER;
BEGIN
SELECT to_date(sysdate)-to_date(dob)
INTO v_num
FROM customer_details
WHERE application_id=:block3.day;
v_days :=floor(v_num/366);
MESSAGE(v_days);
MESSAGE(v_days);
END;
It is giving me a result of -73...i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.
percentage_pointsmarksage95336.590346.575376.550496.595347903777549750337 if i fired the statementselect percentage_points from table1 where marks=33 and age=6.5 then output should beuser has score percentage between 90 and 95i can write case condition between 1 to 100 percentages but any other condition which easy
View 1 Replies View RelatedWhen I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.
So what am I going wrong here.
set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN
[Code]....
I have created scripts as follow
CREATE TABLE STUDENTS(
STD_ID Number,
STD_NAME Varchar2(25 ) CONSTRAINT SYS_C002716 NOT NULL,
PHONE_NUMBER Char(20 ),
FATHER_ADDRESS Varchar2(100 ),
[Code]...
-- Add keys for table STUDENTS
ALTER TABLE STUDENTS ADD CONSTRAINT SYS_C002717 PRIMARY KEY (STD_ID)
/
-- Table CLASSES
CREATE TABLE CLASSES(
CLASS_NAME Char(40 ),
CLSS_NUM Number(38,0) CONSTRAINT SYS_C009724 NOT NULL,
LEV_ID Number(38,0)
)
[Code]....
------------------------- ----------
abk amr
saad amr
abk OSM
saad OSM
I expected the query gives me student_name in what class name and didn't expect to get frequency in output
It started out pretty simple where I had to update about 40 contacts in the database and I would have 40 separate update statements I would run. The task has jumped to about 300 contacts and I don't want to run 300 update statements. I would like to run this all at once. For example:
update contact
set name = 'Name1'
where row_id = 'row_id1'
update contact
set name = 'Name2'
where row_id = 'row_id2'
update contact
set name = 'Name3'
where row_id = 'row_id3'
My DB is oracle 10, and TOAD is version 9.
I have the following fields:
Addressln1
Addressln2
Suburb
Town
I know how to concatenate them
trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.
I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.
Standard result of joining two tables looks like below:
deptno dname empname
10 ACCT SCOTT
10 ACCT MILLER
20 SALES JOHN
20 SALES XYZ
30 FINANCE AAA
Now I need the output as below as per a report requirement.
entity_type name/no
DEPT 10
EMP SCOTT
EMP MILLER
DEPT 20
EMP JOHN
EMP XYZ
I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?
I have a table with data as follows
Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N
I am trying to get output as follows
Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0
this PROCEDURE for Paging.
PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;
[code]...
To get output from a below mentioned table? can we combine a row like that?
Table: Test_Fruits
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------
A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?
CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
[code]....