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
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
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
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
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
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
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
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
Sep 14, 2013
How to use a function with DML operations in it in a select statement.
View 11 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Feb 11, 2013
what is the default return type for weak ref cursor?
View 6 Replies
View Related
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
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
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
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
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
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