PL/SQL :: Return Values From Procedure

Dec 19, 2012

the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.

CREATE OR REPLACE PROCEDURE test IS

cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;

BEGIN

open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;

View 14 Replies


ADVERTISEMENT

SQL & PL/SQL :: To Return 100 Values By Using The Procedure

Aug 26, 2010

I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.

Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.

View 1 Replies View Related

PL/SQL :: Procedure Doesn't Return Values

Nov 29, 2012

I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.

CREATE OR REPLACE PROCEDURE check_values (
     my_values            IN          emp.dept_no%TYPE,
     p_cursor        OUT sys_refcursor
)

[Code]....

/The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.

How can I resolve this issue?

View 15 Replies View Related

SQL & PL/SQL :: Return Column Name Corresponding To Values

Apr 28, 2011

I want to search a some values in oracle table and then return the corresponding column names.

View 1 Replies View Related

SQL & PL/SQL :: Function Return Two Values?

Aug 2, 2010

whether a function can return two values?

View 4 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

Aug 28, 2010

I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.

SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /

Table created.

SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);

1 row created.

SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);

1 row created.

SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);

1 row created.

SQL> COMMIT;

Commit complete.
[code]...

Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.

View 6 Replies View Related

Return Minimum Duplicate Values

Dec 16, 2009

I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.

Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.

select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)

View 4 Replies View Related

SQL & PL/SQL :: How To Use Case When Return Multiple Values

Mar 13, 2012

I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.

e.g.

Second_table
username code
A ALL
B 23
B 56
B 33

Third_Table
code
67
78

So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33

View 13 Replies View Related

PL/SQL :: How To Return Multiples Values Using Functions

Apr 8, 2013

I am using functions to return multiple values of two rows or multiple rows.

For example emp id = 100 and i need to return the value for this(empid) input and output of this first_name and salary.

I am tried in this way below but got errors (ORA-00932: inconsistent datatypes: expected NUMBER got HR.EMP_TYPE)

create or replace type emp_type as object(first_name varchar2(20),salary number);

create or replace function f1(empid in number)
return emp_type
as
emp_record emp_type;
begin
select first_name,salary into emp_record.first_name,emp_record.salary from employees where employee_id = empid ;
return emp_record;
end;
/

select f1(100) from dual;

View 12 Replies View Related

JDeveloper, Java & XML :: How To Return Multi-values To PL/SQL

Jul 3, 2013

I have a PL/SQL function where i call java class. In java i`m making some calculations and i need to return to body of PL/SQL function some values ( like 6 to 10, few numbers, string, 1 xml and 1 html).

I will show pl/sql function..

CREATE OR REPLACE FUNCTION GET_TAB(P_CURS SYS_REFCURSOR) RETURN TYP1_TAB PIPELINED IS
OUT_REC TYP1:=TYP1(NULL,NULL,NULL,NULL);
V_NAZWA VARCHAR2(5);
V_NUMER NUMBER;

[code].....

View 26 Replies View Related

SQL & PL/SQL :: Insert Function / Procedure Return New ID

Jun 22, 2010

i'm trying to make a sp/function for inserting a record and return the new index.The previous code used regular inserts and needed an additional round-trip to get the id before inserting,, since this is part of a import routine performance is an issue.

CREATE OR REPLACE PROCEDURE SaveAisHeader (
P_Id Out Number,
P_ImportedOn IN Date,
P_Aisimporttypeid In Number,
P_Description In Varchar2,
P_Importedby In Varchar2
[code].....

View 12 Replies View Related

SQL & PL/SQL :: Use A Procedure Or Function If Have To Return Only 1 Datatype

Feb 1, 2012

how to decide whether to use a procedure or function if i have to return only 1 datatype.

View 5 Replies View Related

SQL & PL/SQL :: Query To Return Not Null Column Values With Priority

Dec 22, 2011

I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.

I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.

WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......

DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes

The results I am expecting is

DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14

I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.

SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN

But I don't think my manager will allow me to deploy a Multi Row function .

View 2 Replies View Related

SQL & PL/SQL :: Data Type To Return Decimal Values In Function

Feb 26, 2013

I have the following database function.

GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);

The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78

what data type I should use so that my output is returns all the decimal values?

View 5 Replies View Related

PL/SQL :: RETURN Translate - Greek Letters Replace By Their Corresponding Values

Sep 20, 2012

Imagine I have the following function:

FUNCTION normalize(str IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN TRANSLATE(LOWER(str),
'äàáâãăāåąæčçðďéèëêěĕėęğģġîĭïīìíłļľŀñńňņöóòôõσøőřśŝšşţüúùûǔųūůŵýÿżźžżαβßγδεζŋηικλμµνξπρσςτυφω',
'aaaaaaaaaaccddeeeeeeeegggiiiiiillllnnnnoooooooorsssstuuuuuuuuwyyzzzzassydeznniklmmnxprsstufo'
  );
END;

I'm tired to add missing characters in this list...

What I would like is that all characters with an accent or diacritics should be replace by their "base" letter (ë -> e) and Greek letters to be replace by their corresponding values (ω (omega) -> o)

View 1 Replies View Related

Return Multiple Rows From Stored Procedure?

May 26, 2010

How to return multiple rows from the stored procedure in ORACLE..

View 2 Replies View Related

PL/SQL :: Return Results Before Procedure Execution Completes

May 21, 2013

Is there any way of returning output parameter values to calling environment before completion of procedure execution. I may achieve it by using GTTs, looking for any other way (because calling environment again need to issue select statement to retrieve data from GTT).

Example case:

Procedure have multiple ref cursors as out parameters.
....
...
if exp1=exp2
then
open v_ref_var1 for select ...from ... ;
end;
[code]..........

If the first if condition satisfies, ref cursor - v_ref_var1 data should be immediately available for the calling environment.

View 4 Replies View Related

Reports & Discoverer :: How To Return Values In A Text Or Boiler Plate

Feb 15, 2012

Well i like to display the Values in Boiler Plate/Text. For Example, i have Report with Address Columns. for Tin No: i have Hard coded as TIN: 34XXXXXX01 and i like to change this hard coded item as TIN: 34XXXXXX02 so during Runtime if the input is changed the above item to be changed.

Inputs given in Report Parameter;

if the input is given as 81

TIN: 34XXXXXX01

if the input is given other than 81

it should print as TIN: 34XXXXXX02

How to give it in the Boiler plate.?

View 6 Replies View Related

PL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function

Nov 22, 2012

resolve issue while modified the user function code for returns the values as timestamps

---function code

create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin

[Code].....

Input:-
select fun_test_timestamp('5') from dual;

Output:-

SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;

Modified Fun Code:

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);

[Code]...

Input:-
select fun_test_timestamp('5') from dual;

Output:-

ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"

*Cause:    identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.

*Action: 

View 6 Replies View Related

Windows / .NET :: Return String From Oracle Stored Procedure Using Excel 2003 VBA

Apr 12, 2013

I am trying to return a value of an oracle stored procedure using Excel VBA.

OS: Win XP SP3
Excel 2003
Ora Client: 11g

By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:

'-----------------------
Sub Extract_Data()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db_name, UserName, Password As String
cn.Open db_name, USerName, Password

[code]....

When debugging the connection string, I find the Provider=MSDASQL5.1.

View 2 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

SQL & PL/SQL :: How To Get Column Values In Procedure

Apr 5, 2010

My task is I want to retrieve all the Insert Statements of the rows that are present in each table of a particular Schema.

When I give input as Schema name(Scott) in a Procedure, Then the procedure retrieves all the Insert statements of rows of all tables that are present in scott user.

For this I started like this..

CREATE OR REPLACE PROCEDURE MYPROC(UID DBA_OBJECTS.OWNER%TYPE)
AS
CURSOR MYtab
IS
select o.owner, o.object_name
from dba_objects o where O.OWNER=UID

[code]....

When I execute this procedure I got the result as follows.

EXEC MYPROC('SCOTT');
insert into DEPT(DEPTNO,DNAME,LOC) values (
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (
insert into BONUS(ENAME,JOB,SAL,COMM) values (
insert into SALGRADE(GRADE,LOSAL,HISAL) values (
insert into PROJECT(PNO,PNAME,PMGR,PSTART,PEND,BUDGET) values (

[code]....

As you see that I have executed successfully till each table columns.But How to retrieve the values in this?

View 2 Replies View Related

SQL & PL/SQL :: Checking Values Of Procedure Parameters

Jan 19, 2013

CREATE TABLE t2
(
id NUMBER,
ename2 VARCHAR2(20),
sal2 NUMBER,
job2 VARCHAR2(20),
conid NUMBER

[Code]...

My requirement is like when I am calling the procedure P1 with some values then it should check the table "t2".And table "t2" is linking with table "t3".

So what ever the column "verify" is there, it should check the incoming values against it. If matches success otherwise reject it.Later the incoming values is stored different tables.I am doing it in the above way by hard coding some value.

BEGIN
p1(1,'MILLER',500,'ADMIN'); --REJECT
p1(1,'MILLER',5000,'ADMIN'); --ACCEPT
P1(2,'MILLER',5000,'SALESMAN');--ACCEPT
END;

View 5 Replies View Related

SQL & PL/SQL :: How To Pass Shell Script Values To Procedure

May 30, 2012

I have one procedure , which calls the DBMS_scheduler.run_job , which calls the Shell Script. now based on the shell execution i need to return the message lines to procedure.

View 1 Replies View Related

SQL & PL/SQL :: How To Grab The Update Values And Insert Into Procedure

Jun 8, 2011

how can i make this script into a function or procedure which instead of user change the crime_id :=4 or 5 directly it actually can grab the id from a update statment like

UPDATE CRIME_STATUS SET CRIME_STATUS = 'open'
WHERE CRIME_ID = 9;
pick 9 and insert into the above statment so it runs as normal

[Code].....

View 20 Replies View Related

SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors

Oct 6, 2011

The goal is to create a stored procedure that will retrieve multiple values from a table.

GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.

So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------

I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.

When I intend to run it by executing the script "Execute Procedure name ", I get errors.

In SQL Developer, I open new SQL file and key in

EXECUTE emp_test;

Highlight it and run the script, here is the list of errors that I get.

-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------

Issue 2:

Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________

CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________

Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.

View 7 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.

Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......

The result of this is:

Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868

As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.

I need the output to be

Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

I am searching the simplest way for ad hoc MINUS.I do:

SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....

and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?

View 6 Replies View Related







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