PL/SQL :: Create Function To Return Table Type
Jul 25, 2012I 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;
ADVERTISEMENT
PL/SQL :: How To Call A Function Having OBJECT Type As Return Type
Mar 26, 2013I'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;
SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable
Aug 30, 2011I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
SQL & PL/SQL :: Data Type To Return Decimal Values In Function
Feb 26, 2013I 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?
SQL & PL/SQL :: Call Function With Row Type Return In Oracle Select Statement
Jul 3, 2012How 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 ?
SQL & PL/SQL :: Create A Function That Return XmlType
Apr 29, 2010i want to create a function that build a return xml (XmlType):
create or replace
function plainLanguageSummary(nip varchar2,id number,code_language varchar2) return XmlType
as
[Code].....
but in the compilation i got the following error :
Error(10,62): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null others <identificateur> <identificateur entre guillemets> <variable bind> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <un littéral de chaîne avec spécification de jeu de caractères> <un nombre> <une chaîne SQL entre apostrophes> pipe <constante de chaîne éventuellement entre guillemets avec indication du jeu de
he seems to not like the first "select" he encounter!.
SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function
Feb 17, 2011I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit
create or replace
PACKAGE BODY BILL AS
FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;
ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........
SQL & PL/SQL :: Pipe Line Function Taking Time To Return Table Record
Mar 15, 2011I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.
View 2 Replies View RelatedSQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body
Jul 26, 2011CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table
Nov 11, 2012I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)
[code].....
DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]
SQL & PL/SQL :: Can Create Table Type Object By Using %ROWTYPE
Apr 20, 2012Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare
2 type table_emp is table of scott.emp%rowtype index by binary_integer;
3 employees table_emp;
4 begin
5 select * bulk collect into employees from scott.emp;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer;
2 /
Warning: Type created with compilation errors
SQL> show errors
Errors for TYPE DBO.TABLE_EMP:
LINE/COL ERROR
-------- ----------------------------------------------------------
1/19 PLS-00355: use of pl/sql table not allowed in this context
0/0 PL/SQL: Compilation unit analysis terminated
SQL>
How can I create global table type object with %rowtype
WebService - How To Create Record Type And PL/SQL Table
Oct 7, 2010I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.
here are the output values:
Customer_Id varchar2(20),
ErrorCode varchar2(30),
ErrorDesc varchar2(3000),
Fcount number,
FErrorCode -- this is array,-- if fcount >1 then these values will be repeted.
FErrorDesc -- this is array,
FStatusCode -- this is array,
FStatusDesc -- this is array,
Street varchar2(3000),
Street2 varchar2(3000),
Suite varchar2(20),
City varchar2(20),
State varchar2(2),
Zip_Code varchar2(10)
create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.
SQL Create Table Doesn't Support %TYPE / But PL/SQL Does
Oct 12, 2010oracle 10G who knows why Oracle SQL doesn't support %TYPE when create a table. But it supports it when we use it in PL/SQL.
SQL> create table wg_1
(account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE)
;
2 3 (account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE)
*
ERROR at line 2: ORA-00911: invalid character
SQL> desc JNL_WORK_LIST
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_NO NOT NULL NUMBER(10)
BILL_REF_NO NOT NULL NUMBER(10)
BILL_REF_RESETS NOT NULL NUMBER(3)
JNL_EARNED_THRU_DT DATE
[code]...
PL/SQL procedure successfully completed.
SQL & PL/SQL :: String Return Type
Feb 17, 2012I 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.
SQL & PL/SQL :: Convert Object Return Type Columns
Mar 25, 2010I 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.
PL/SQL :: Default Return Type For Weak Ref Cursor?
Feb 11, 2013what is the default return type for weak ref cursor?
View 6 Replies View RelatedFunction Return Parameters
Dec 5, 2006The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.
The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?
SQL & PL/SQL :: How To Return More Data From Function
Nov 14, 2011Following is the scenario:
CREATE OR REPLACE TYPE OBJ IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
[Code]..
I WANT USED VALUE TEST_ID AND TEST_DESC THE EXISTING IN FUNCTION FN_MY_DATA WITH A VARIABLES :
DECLARE
X NUMBER(9);
Y VARCHAR(30);
BEGIN
X := -- VALUE TEST_ID EXISTING IN FN_MY_DATA;
Y := -- VALUE TEST_DESC EXISTING IN FN_MY_DATA;
END;
SQL & PL/SQL :: Function Return Two Values?
Aug 2, 2010whether a function can return two values?
View 4 Replies View RelatedFunction To Return Last Friday Of Month
Sep 26, 2010I was just wondering that do we have any function available in oracle which returns the last Friday of the month.
In our company we close our monthly books on last Friday of the month and there are few activities that we have to do on the following Monday as a part of month-end activity. Now the following Monday can fall on the same month or at times it falls on the following month.
I have to schedule a report to be sent to a user on the following Monday after the month-end. I need to schedule it using the cron job.
We are using Oracle 9i on Linux platform.
SQL & PL/SQL :: How To Return Multiple Values From A Function
Aug 28, 2010I 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.
SQL & PL/SQL :: Insert Function / Procedure Return New ID
Jun 22, 2010i'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].....
SQL & PL/SQL :: Function Return Multiple Rows
Jun 23, 2006can i have a pl/sql function that can return multiple rows
may be the syntax will be like
create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;
..code to return the result of above statement to calling program..
end;
and functions will be called as
select candidateid from .. where candidateid in( select multiple());
PL/SQL :: Function That Return Multiple Rows
Jun 6, 2013I need a function that should return output of this query
SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;
This above function return 30 rows and I am not giving any input
Function using cursor,pipeline
PL/SQL :: Executing A Function With RETURN X%ROWTYPE Is Used
Jul 6, 2012This error is returned when executing a Function with the RETURN X%ROWTYPE is used.Here is the code.
CREATE OR REPLACE function FE_GET_addr (
p_PIDM IN addr.addr_PIDM%TYPE,
p_atyp1 IN VARCHAR2,
p_atyp2 IN VARCHAR2,
p_atyp3 IN VARCHAR2,
p_atyp4 IN VARCHAR2
[code]....
SQL & PL/SQL :: Use A Procedure Or Function If Have To Return Only 1 Datatype
Feb 1, 2012how to decide whether to use a procedure or function if i have to return only 1 datatype.
View 5 Replies View RelatedExecuting Function To Return Username In Oracle
Feb 6, 2006I 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
SQL & PL/SQL :: Can Oracle Database Function Return Images
Dec 12, 2012Can 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 RelatedPL/SQL :: To Create Function Based Index For Group Function Columns
Jun 15, 2012Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
SQL & PL/SQL :: ORA-02303 - Cannot Drop Or Replace A Type With Type Or Table Dependents
Feb 1, 2012i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /
Type dropped.
SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
Type created.
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?