Following 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;
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?
The 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?
whether a function can return two values?
View 4 Replies View RelatedI 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.
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.
i 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!.
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].....
can 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());
I 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
This 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]....
how to decide whether to use a procedure or function if i have to return only 1 datatype.
View 5 Replies View RelatedI 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
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 RelatedI 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].........
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;
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 ?
I having issue when i try to use CLOB as varchar2 is not enough in my case. I'm developing function column in oracle report. I'm using developer 6i. I get error function return must char?
How I can use CLOB in oracle report?
function CF_RnoFormula return Char is
--v_release_num CLOB;
v_release_num varchar2(32767);
begin
FOR rec IN
[code]........
I 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 Relatedresolve 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:
I 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]......
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;
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?
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 null
2666054111 06-154 77.000 85.000 null 85.370
2666054111 06-154 77.000 85.000 null null
I used select distinct(work_order) to come up with the three different possible scenarios the problem is that i need all this information on a single row
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 85.370
this is a conversion for distance. when i get this to work properly, it will generate reports on thousands of work orders with their converted distance markers.
I have a proc/func which has a string having comma separated numbers :
'1,2,3,4,5'
I have a query where I need to use the string in way like :
SLEECT * FROM ABCD WHERE ABCD.A IN (1,2,3,4,5);
..and whatever is selected has to be returned as a ref cursor or as a table type object.
How can we prepare a statement to do so
how can i control the sequence to return same value for repeating data.
inter_no
10
10
11
12
12
13
14
what is required is as below
inter_no seq_val
10 1
10 1
11 2
12 3
12 3
13 4
14 5
is is on select statement will will insert the data once i get the sequence val is same for repeating one
I have problems in Oracle returning data from a stored procedure. In MS SQL it's quite simple.
i.e.
CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/
next the stored procedure:
CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUTGLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;
Next trying to run the Stored procedure:
VARIABLE resultSet REFCURSOR
EXEC mytest (:resultSet);
Result always returns this error:
REFCURSOR - Unrecognized type.
I have tried using sample on the internet, but all returns the same error, what am I missing
The below query takes more than 30 minutes to return data.All the objects used are views. There is no direct reference to any table.The views with _mnth_ have data for 7 distinct months. The base table for all the views have a composite PK on the columns AR_ID (or ACCT_AR_ID),MSRMNT_PRD_ID
I need the order by, as the query is part of informatica code, and the order by works in the further processing.
SELECT ac.ar_id AS acct_ar_id, m.msrmnt_prd_dt AS msrmnt_prd_dt
--removed the rest of column list to reduce size of code.
FROM edxf.ar_rsrv_mnth_v ac,
edxf.crdt_acct_mnth_v c,
edxf.crdt_acct_v ca,
(SELECT msrmnt_prd_id, msrmnt_prd_dt
FROM edxf.msrmnt_prd_v
WHERE msrmnt_prd_id =
[code]....
Also, the count of data in the views is as below.
ViewTotal countCount for 1 msrmnt_prd_id
---------------------------------------------------------
ar_rsrv_mnth_v1841892281945
crdt_acct_mnth_v664941457087369
crdt_acct_v12258728NA
I am starting to use the interactive reports in version Apex 4.1.1 but I am unable to get the report to show null records on the first load. Is there a setting that would stop the data from loaded on first load or would I have to customize that option.
Some of the columns have a lot of data and therefore take a long time to load so I would like to avoid that.
The below query is taking more than 5minutes to return the data for any criteria.The big tables are
SECURITY_POSITION_SUMMARY -- 60Million
WEB_TEAM_X_ACCOUNT_BM -- 26Million
and the rest of those are small tables..All the indexes are in place and I have tried with few hints but this query is slow.
WITH REPS
AS (SELECT DISTINCT REP_SET.FILTER_TOKEN
FROM (SELECT /*+ INDEX (wdsd WEBDATASETDTL_PK_TEAM) */
DISTINCT
WDSD.DATA_SETTING_ID, WDSD.FILTER_TOKEN
FROM WEB_DATA_SETTING_DETAIL WDSD,
[code]....