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


ADVERTISEMENT

PL/SQL :: Executing A Function With RETURN X%ROWTYPE Is Used

Jul 6, 2012

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]....

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

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

Executing Function From Ksh Script?

Mar 5, 2009

I don't know how to control if a call to a PL function return errors.

I have this ksh:

executeFunction () {
echo "[executeFunction ]-> Phoebus DB started"
sqlplus -s /nolog <<!EOF!
CONNECT $USER/$PWD@$DB
@/sql/ejecutaFuncion.sql
EXIT;
!EOF!
}
executeFunction

And the code of "ejecutaFuncion.sql" is:

executeusers.startProcessing();
EXIT

How i can controle if the "startProcessing" function has any problems from the ksh?

View 1 Replies View Related

Executing Function In A Package?

Mar 24, 2008

I have created one function in the package.

function : Tier_wh
package : Order_DESC

Function defined as

function TIER_WH( message in out Xorder_desc)
...
...
end;

Xorder_desc is defined as

create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);

how to execute this function which is defined in the package .

View 1 Replies View Related

SQL & PL/SQL :: Executing Function From String Variable

Oct 2, 2013

I'm trying to find a simple way of getting around this. I have a PL/SQL procedure which loops through a list of values in a table. These values are the actual names of the functions I want it to run in sequence. The problem is I can get the string value each time but I need to store the return value of each function into a number variable e.g.

BEGIN
open all_tests;
fetch all_tests BULK COLLECT INTO test_tabl;
close all_tests;

for myindex IN 1..test_tabl.count LOOP

time_taken := test_source1 ==> this will work but how do I avoid hardcoding the name of the function to be executed and use the test_name value instead?? time_taken is declared as a number (ie the return value of each function is a number)

test_detail := test_tabl(myindex).test_name; ==> test_detail now contains the string name of the function
dbms_output.put_line('Test detail is ' || test_detail);
end loop;

View 2 Replies View Related

Executing Stored Procedure / Function From Database Link?

Feb 24, 2011

I have 2 databases:

Database A
Database B

In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.

I would like to execute my stored procedures and all associated functions in database A on my data in database B.

Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.

Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...

I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.

View 2 Replies View Related

Performance Tuning :: Removing Nvl Function Then Query Executing In 2 Min Condition

Aug 10, 2011

when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below

(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)

but when am using the same condition as below the querry executing in 2 min

(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)

my query given below

(SELECT cust.customer_number cust_no, cust.customer_name customer,
cnv.item_no, SUM(wd.shipped_quantity) shp_qty_nos,
0 rtn_qty_nos,
ROUND(SUM(cnv.cnf * wd.shipped_quantity), 3) shp_qty_tons,
0 rtn_qty_tons, 0 net_shp_qty_nos, 0 net_shp_qty_tons
[code]...

View 30 Replies View Related

Function Return Parameters

Dec 5, 2006

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?

View 1 Replies View Related

SQL & PL/SQL :: How To Return More Data From Function

Nov 14, 2011

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;

View 3 Replies View Related

SQL & PL/SQL :: Function Return Two Values?

Aug 2, 2010

whether a function can return two values?

View 4 Replies View Related

Function To Return Last Friday Of Month

Sep 26, 2010

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.

View 2 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

Aug 28, 2010

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.

View 6 Replies View Related

SQL & PL/SQL :: Create A Function That Return XmlType

Apr 29, 2010

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!.

View 9 Replies View Related

SQL & PL/SQL :: Insert Function / Procedure Return New ID

Jun 22, 2010

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].....

View 12 Replies View Related

SQL & PL/SQL :: Function Return Multiple Rows

Jun 23, 2006

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());

View 13 Replies View Related

PL/SQL :: Function That Return Multiple Rows

Jun 6, 2013

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

View 5 Replies View Related

SQL & PL/SQL :: Use A Procedure Or Function If Have To Return Only 1 Datatype

Feb 1, 2012

how to decide whether to use a procedure or function if i have to return only 1 datatype.

View 5 Replies View Related

Difference Between #username And ?username In SPARE1 Column?

Oct 31, 2010

whats the different between a #username and ?username in a SPARE1 column?

View 5 Replies View Related

SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function

Feb 17, 2011

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].........

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

Reports & Discoverer :: Clob In 6i - Error Function Return Must Char

Jun 23, 2011

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]........

View 3 Replies View Related

SQL & PL/SQL :: Pipe Line Function Taking Time To Return Table Record

Mar 15, 2011

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 Related

PL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function

Nov 22, 2012

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.

*Action: 

View 6 Replies View Related

Oracle 9i Default Username And Password

Sep 14, 2003

I Just got Oracle 9i installed. But can not login . What are the Default username and password for "Oracle9i Discoverer Administrator" and other Developers Suite apps?

Also What URL should i type to access the "iSQL *PLUS" through my browser, so that i can execute my SQL statements.

View 3 Replies View Related

Oracle Sqlplus Unique UserID / Username?

Oct 5, 2007

I each user in my table to have a unique username and user_id. So the table cant contain 2 users with the same user id and it cant contain 2 users with the same username.

oracle doesn't support more than one primary key in a table

so what could I use a secondary key?creating alternate keys in sql plus?

View 1 Replies View Related

SQL & PL/SQL :: Oracle - Create Procedure To Check Username And Password?

Aug 23, 2011

how can i create procedure to check username and password.

View 10 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

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]......

View 2 Replies View Related







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