SQL & PL/SQL :: Call Function With Row Type Return In Oracle Select Statement

Jul 3, 2012

How to call a function with a row type return in an Oracle select statement.

For e.g. :

If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?

View 5 Replies


ADVERTISEMENT

PL/SQL :: How To Call A Function Having OBJECT Type As Return Type

Mar 26, 2013

I've the following function returning OBJECT type. how to call this function

CREATE OR REPLACE TYPE GET_EMP_OBJ is object
   ( emp_name varchar2(50) ,
     mgr_id   number,
     dept_id  number
   );

[Code]...

The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work

DECLARE
  t_emp_info_1  GET_EMP_OBJ ;
BEGIN
   t_emp_info_1 := get_emp(7566) ;
   for i in 1..t_emp_info_1.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ;
     END LOOP;
END;  

View 7 Replies View Related

PL/SQL :: Create Function To Return Table Type

Jul 25, 2012

I am trying to create a function which would return a nested table with 3 columns of a table as a type.

my query is like

select col1,col2,col3 from table_1;

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

SQL & PL/SQL :: Can Call A Function Within Decode Statement

Oct 19, 2011

Can we call a function within decode statement. I am able to do the same for simple example function . But In my actual procedure it's giving the error message . Are there any restrictions to call function with in decode statement?

View 4 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Function Raise No Error When Call In Select

Feb 17, 2010

why function does no raise error no_data found when call in select statement.

1) create one function.

CREATE OR REPLACE function fn_sal(v_id NUMBER) RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp where empno=0;
RETURN v_sal;
END;

2) call it in select statement.

SELECT fn_sal(e.sal),e.* FROM emp e

select satement cause no error , it displayes all the records but null for the function cloumn.

why it not gives no_data_found error.

View 4 Replies View Related

SQL & PL/SQL :: Function With DML In Select Statement?

Sep 14, 2013

How to use a function with DML operations in it in a select statement.

View 11 Replies View Related

SQL & PL/SQL :: Using Select Statement In Decode Function?

May 11, 2010

I am trying to use decode function in sql and inside decode function can I use select statement ?

here is my sql

select we.wf_entity_id, decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
) from wf_entity we
where

[code]....

see this

decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
)

will this work? Its not working for me?

View 2 Replies View Related

Executing Function To Return Username In Oracle

Feb 6, 2006

I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.

The code i have created is this:-

CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS
FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
[code]........

The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-

VARIABLE RETVAL VARCHAR2(12)

EXEC User_login.user_select('HELLO','HELLO',:RETVAL );

but i am getting the following error

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

View 5 Replies View Related

SQL & PL/SQL :: Can Oracle Database Function Return Images

Dec 12, 2012

Can a Oracle Function return Images? I have been getting and able to read lot of solutions but still unconfirmed. As in certain forums it has been mentioned that the difference between procedure and function is that. With procedures you are able to return images but not with function.

View 1 Replies View Related

Forms :: Call Function With Parameter In Oracle

Jul 17, 2012

i have this function

create function xxx_sal (p_number in number)
return number is
v_sal number;
begin
select sum(sal)
into v_sal
from emp
where empno = p_number;
return v_sal;
end;

how can called it in oracle forms

View 8 Replies View Related

Forms :: How To Call A Java Function From Oracle

Sep 5, 2007

I am having Oracle 9.2.0.1.0 client in my PC and jdk version is 1.6

I had configured below tools in my PC. (windows 2000) I am having Oracle 9.2 with Oracle Developer Suite 10g (10.1.2.0.2) which contains Oracle JDeveloper 10g (10.1.2.1) also.

How to call a java function from Oracle forms? code samples and how to integrate those thing?

View 13 Replies View Related

PL/SQL :: Oracle 11g Table Function Returns No Records On First Call?

Jul 13, 2012

On a Oracle 11g R2 I've a table function ( PIPELINED ) returning rows selected from a table.The first time the function is selected, in a session ( I've tried to disconnect and log in again ), it returns no rows.I've tried to log the call using DBMS_OUTPUT and from what I see the select on the table function returns no rows and no output is printed. So I presume Oracle is not calling the function.

The same function on a similar environment ( same db versions, patches and database structure ) works fine. The second environment is a production environment so it has more memory and some other settings enabled.

View 6 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

Using WITH Statement In Oracle Procedure Or Function

May 24, 2011

Is it possible to use WITH statement in an Oracle procedure or function?

I've had problems compiling a procedure with "WITH" statement.

View 1 Replies View Related

SQL & PL/SQL :: String Return Type

Feb 17, 2012

I have two Oracle instances, both are 10.2.0.3

Executing the following results in two different results:

select N'Test" "result" from dual;

On one the output is:

resu
----
Test

and on the other the output is

result
----------------
Test

This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.

Not being an Oracle DBA, I am not sure where to look.

View 6 Replies View Related

How To Get SQL Statement That Caused Exception In Oracle Function

Jul 18, 2011

I am trying to search a way to get the SQL statement that caused an exception withing an oracle function.
I tried:

SELECT sql_text
from v$session ses, v$sql sql
where sql.sql_id = ses.prev_sql_id
and ses.sid = sys_context('userenv','SID') AND ROWNUM = 1;

but this doesn't always return the last statement that the function has executed. if needed i can send the complete script for the function and its tables and stored procedures for testing.

View 3 Replies View Related

PL/SQL :: Oracle 10g - Write Select Statement With XML

Sep 26, 2012

I have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.

<?xml version="1.0"?>
<ROWSET>
<Emp>
<ENAME>SMITH</ENAME>
<EMPNO>7369</EMPNO>
<SAL>800</SAL>
</Emp>
<Emp>
<ENAME>ALLEN</ENAME>
<EMPNO>7499</EMPNO>
[code]...

I want to write a select statement which gives me result as follows (all ENAME and EMPNO from the xml)

ENAME EMPNO
SMITH 7369
ALLEN 7499
WARD 7521

View 4 Replies View Related

Display String Using Select Statement In Oracle 10g

Jul 27, 2010

i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.

example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'

i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.

View 14 Replies View Related

Select Statement To Oracle DB - Time Converting?

Aug 27, 2009

My company use a sybase database that runs business jobs. Currently we run SQL queries from Perl to gather time information on the jobs. Now we have an application that is using Oracle. The server it is on, doesn't have perl, so I am using a shell script to login to sqlplus and run a query for a job and it's end time. I have accomplished this. However, here is the 2 problems I am having.

1. The query reults are returned in Scientific time, I'm able to convert that to EPOCH time in the SQL syntax, however, it comes back with a 13 digit time, instead of 10. The last 3 digits are zero. How can you remove the last 3 digits in the query or convert the 13 digits to Human Time. Right now when you see the select statement, I am doing a to_char to get it to EPOCH time.

2. How to only show the latest time in the query and not show ALL job end times from it's past runs.

Here is my shell script, and I do realize this maybe a select statement syntax solution to one or both, but the UNIX time stamp is puzzling.

#!/usr/bin/sh
sqlplus -S username/password@JAWSPROD <<eof> myfile
set heading off feedback off verify off
select JAWS_APP.JAWSJOB.JOBNAME, to_char(JAWS_APP.JOBRUN.ENDTIME) from JAWS_APP.JAWSJOB, JAWS_APP.JOBRUN where JAWS_APP.JAWSJOB.JOBID = JAWS_APP.JOBRUN.JOBID and JAWS_APP.JAWSJOB.JOBNAME in ('pa_box_settle');
exit

View 1 Replies View Related

SQL & PL/SQL :: Convert Object Return Type Columns

Mar 25, 2010

I have created a object type as

create type emp_obj_dtl as OBJECT (ename varchar2(50),mgr NUMBER)
create type emp_dtl_obj_typ as TABLE of emp_obj_dtl

Using the these object i have created on function as

CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER)
RETURN emp_dtl_obj_typ
AS
lv_emp_dtl emp_dtl_obj_typ := emp_dtl_obj_typ ();
BEGIN
SELECT emp_dtl_obj_typ(emp_obj_dtl (ename, mgr))
INTO lv_emp_dtl
FROM emp
WHERE empno = peno;

RETURN lv_emp_dtl;
END;

Now if i am executing query as

SELECT empno, emp_test_func (empno) emp_dtls
FROM emp

It is returning me the data as

EMPNO | EMP_DTLS
7500 | (DATASET)
7382 | (DATASET)
7569 | (DATASET)
7800 | (DATASET)

But I want the result set as

EMPNO | ENAME | MGR
7500 | SMITH | 7863
7382 | JAMES | 7896
7569 | KING | 7856
7800 | SANGR | 7456

How to get the resultset as above.

View 4 Replies View Related

PL/SQL :: Default Return Type For Weak Ref Cursor?

Feb 11, 2013

what is the default return type for weak ref cursor?

View 6 Replies View Related

Find Data From Select Statement - Oracle 11g After Changing Connection?

Apr 3, 2013

Yesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement

insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1')
insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')

Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?

View 2 Replies View Related

SQL & PL/SQL :: Returning Value Of Dynamic Select In Oracle Function?

Jul 20, 2011

I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!

CREATE TABLE AFESD.MAJOR_ACCOUNT
(
NUMBER0 NUMBER(2) NOT NULL,
SHORT_NAME CHAR(35 BYTE) NOT NULL,
FULL_NAME CHAR(50 BYTE)
)

--Actually any table can do

CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2)
RETURN NUMBER
AS
I_RETURN NUMBER;
S_SQL VARCHAR2(300);
--S_DB_ERROR VARCHAR2(100);

[code]....

B.S. I didnt delete the commented lines to have your review comments.

View 21 Replies View Related

PL/SQL :: Have A Function In Oracle To Select Not Null Columns At Beginning

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.

Here is a sample data :

Employee table :
Employee_id   Emp_fname  emp_lname  emp_mname  dept salary emp_height  emp_weight
   1               aaa        ddd                d1   100      6           180
   2               bbb                ccc             120                 169
   3               dfe                           d2            5.9         223

The expected result is :
result1 result2   result3 result4  result5  result6 result7 result8
1        aaa        ddd     d1       100     6        180
2        bbb        ccc     120      169
3        dfe        d2      5.9      223

View 13 Replies View Related

PL/SQL :: PLS-00306 - Wrong Number Or Type Of Argument In Call To MULTISET_UNION_ALL

Sep 7, 2012

I'm trying to use MULTISET UNION to append several collections into a single recordset.The select queries shown for the CURSOR are simple beta versions of the real queries which have about 350 fields to each of them. I will be appending data from 15 of these queries which each will with no more than 20 records per query into a new single recordset (not sure if 'recordset' is the correct phrase or term for what I mean, it could be ARRAY, OBJECT, COLLECTION or something else all together).

Here is text of the ERROR message:

ORA-06550: line 63, column 1:
PLS-00306: wrong number or type of argument in call to 'MULTISET_UNION_ALL'

ORA-06550: line 62, column 1:
PL/SQL: Statement ignored

ORA-06550: line 71, column 36:
PLS-00487: Invalid reference to variable 'VARCHAR2'

ORA-06550: line 71, column 5:
PL/SQL: Statement ignored

I think the error has to do with some defect in my declare statements.

DECLARE
  MyTID varchar2(10);

  CURSOR Cursor_rst1 IS
      SELECT d_owner_internal_id,
             d_internal_id,
             d_tid,
             d_entity_name,
             d_form_seq
        FROM rtns.itas_rtn_ct_1120_cor tblRecords
       WHERE d_form_seq = '2710' --Tax Year =2003
         AND D_TID = MyTID;
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

Return Separate Result Set From Each Statement

Jul 19, 2008

I am simply trying to execute a couple of select statements in a single dynamic query. I also want it to return a seperate result set from each statement (something quite easy in T-SQL - but seemingly impossible in PL-SQL!)

This query is being run from C#, but i also get the same results from Sql Developer.This is what i'm trying to execute:

begin
select * from studies;
select * from studyprogressions;
end;

The error i get is "an INTO clause is expected in this SELECT statement" for each of the select lines - As you may already have worked out - I don't use Oracle very often and am starting to feel very stupid considering i have certifications in SQL Server/T-SQL!

I have searched online for a solution, but it seems that everyone with the same error is trying to do so much more.Why is nothing in Oracle simple? Give me SQL Server any day of the week.

View 14 Replies View Related







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