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?
I 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 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].....
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]....
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 :-
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.
I 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].........
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.
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.
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;
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
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.
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.
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]....