PL/SQL :: No Output From Procedure

Dec 4, 2012

When 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]....

View 7 Replies


ADVERTISEMENT

How To Output Parameter To App C# Procedure PL/SQL

Sep 17, 2013

this PROCEDURE for Paging.

PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;

[code]...

View 1 Replies View Related

PL/SQL :: Calling Procedure From Another And Get Combined Output

Jun 3, 2013

I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:

CREATE OR REPLACE PROCEDURE sp_all_attribute_difference
(
i_product_id_new IN NUMBER,
o_product_attr_diff_list OUT product_attr_diff_list,
o_usage_attr_list OUT usage_attr_diff_list
)
IS
[code]....

I am not able to compile tis proc.

View 5 Replies View Related

PL/SQL :: Best Way To Send A Procedure / Function Output To The Application

Sep 8, 2013

What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc. 

View 49 Replies View Related

How To Transfer Output File From One Server To Another Using Stored Procedure

Apr 9, 2012

We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).

Functionality of the stored procedure:Generating a tab delimited file and place in a directory.

Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).

Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).

Attached File(s)
Procedure.txt ( 3.28K )
Number of downloads: 4

View 2 Replies View Related

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

Jul 14, 2011

how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...

View 7 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 Replies View Related

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

Oct 4, 2011

when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14

code which i have written ...

DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT

Procedure Name :

PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,

[code]....

View 2 Replies View Related

Reports & Discoverer :: Create A Procedure In Program Unit And Assign Output To A Field In Report?

May 8, 2013

i just want to know how can i create a procedure in the program unit and assign the output to a field in the report.

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

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related

SQL & PL/SQL :: How To Find Parent / Host Procedure Name Within Procedure

Sep 6, 2013

I have the following requirement.Say I have the following procedure created.create or replace procedure myproc (p1 varchar2, p2 varchar2) is begin

find_the_proc_name();
end;

In the above myproc, using find_the_proc_name() i should print the parent procedure name (or function) calling along with the parameters passed.

View 5 Replies View Related

PL/SQL :: Create A Procedure Inside A Procedure

Jun 25, 2012

DB version:11g

can we create a procedure inside a procedure...if not is there any alternative??

in db2 it is allowed so do oracle support this????

View 5 Replies View Related

SQL & PL/SQL :: Use Reg-Exp To Get Required Output?

Mar 11, 2013

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

View 6 Replies View Related

SQL & PL/SQL :: Not Getting Output For Other Schemas?

Feb 2, 2010

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?

View 9 Replies View Related

Forms :: Different Output In 6i And PL/SQL

Jan 12, 2012

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.

View 10 Replies View Related

Output Should Starts As Between?

Jun 13, 2013

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 Related

PL/SQL :: Frequency In Output

Jun 18, 2012

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

View 2 Replies View Related

PL/SQL :: Output Print More Than 255

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

Concatenation Of Fields For Output

Mar 15, 2007

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.

View 2 Replies View Related

SQL & PL/SQL :: Pivot Output In Equijoin

Feb 11, 2013

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?

View 2 Replies View Related

Groupby Query - Not Getting Output?

Jun 25, 2013

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

View 2 Replies View Related

SQL & PL/SQL :: To Get Output From Table - Possible To Combine A Row?

Mar 6, 2012

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

View 6 Replies View Related

SQL & PL/SQL :: How To Get Output Without SYSDBA Privilege

Mar 2, 2010

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

View 7 Replies View Related

SQL & PL/SQL :: Space Between Output Columns

Feb 15, 2012

How to add space between columns in the dbms output statement.I tried to do so in the following way which i attached.

View 8 Replies View Related

SQL & PL/SQL :: Formatting In Utl_mail Output

May 26, 2012

Formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is like very distorted and i have to have it as readable in single line .Please find below the code i have written along with test case, you may substitute your emailid and test, especially my main focus is on the lable aff.mark number where all the mark numbers are distorted and want to be arranged in line.I have attached the email format that i am getting at present also.


CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
[code]......

View 7 Replies View Related

SQL & PL/SQL :: How To Get Output In Excel Format

Nov 22, 2012

i need to extract data in excel file through sqlplus .

View 2 Replies View Related

SQL & PL/SQL :: Sum And Create New Columns For Output?

Dec 4, 2011

How do I modify SQL to sum and also add 2 columns to hold calculation results?

In functional terms, we need to sum two pay periods' retirement deduction amounts for a BDCA deduction code R03 to arrive at the monthly total for those deduction amounts.

In other words, to sum payroll deduction data for 2 pay periods PAYNO = 21 & 22 for (EMPLOYEE_AMT and EMPLOYER_AMT) and have the totals in new columns named EENOVR03 and ERNOVR03. Column names representing EE for employee, ER for employer, NOV for November, and R03 for deduction code.

Here's the basic SQL without calculations. I need to modify SQL to produce both EENOVR03 and ERNOVR03.

select SPRIDEN.SPRIDEN_LAST_NAME "LastName",
SPRIDEN.SPRIDEN_FIRST_NAME "FirstName",
SPRIDEN.SPRIDEN_MI "MiddleName",

[Code]...

View 12 Replies View Related







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