SQL & PL/SQL :: Using NVL Function With Parameters In A Procedure?

May 16, 2011

I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.

I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.

The code for the procedure is

CREATE OR REPLACE PROCEDURE add_new_classes
(p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number
p_course_id classes.course_id%TYPE,
p_period classes.period%TYPE,
p_frequency classes.frequency%TYPE,

[code]....

I then use this to test it

BEGIN
add_new_classes(1002,'first','daily',3002);
END;

and the error I get is

Quote:ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
1. BEGIN
2. add_new_classes(1002,'first','daily',3002);
3. END;

View 5 Replies


ADVERTISEMENT

Function With Null Parameters

Mar 17, 2009

i have a function which takes in two variables and return a varchar.

ex: Function(var1,var2) return as varchar2.

in the function,i query a table for var1 and var2 and concatenate the result set to return a varchar. But if either var1 or var2 is null,then my query in the function fetches the result set for the other variable.

My question is,how would i pass a null value through the function and handle it in the function.

View 1 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 :: Calling A Function With Parameters?

Feb 27, 2011

When I replace the params manualy with dates in format of:

'27-feb-2011 08:00:00'

I get the real output.

But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).

Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):

FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for

[code].....

Why do I need to change in order to get the good output by calling the function with params?

View 5 Replies View Related

PL/SQL :: How Many OUT Parameters Can Be Declared In Function Header

Sep 4, 2012

How many OUT parameters can be declared in function header ?

A) None
B) Any Number
C) One for every return declared in the definition
D) Depends on what the RETURN datatype is

View 18 Replies View Related

Forms :: Parameters For App_window.set_window_position() Function

Jan 21, 2007

what are the parameters expected for app_window.set_window_position() function?

View 4 Replies View Related

SQL & PL/SQL :: Long Statement With Parameters - Too Many Arguments For Function

Sep 22, 2010

I have an issue with rather complicated function.Basically it is using DBMS_SQL to execute a very long statement with many parameters (~6000 of them) and binding them with DBMS_SQL.BIND_VARIABLE. Variables are called :1,:2,...,:6000.

When this arguments set is too large - I am receiving error "ORA-00939: too many arguments for function".

Currently I am thinking about dividing the query into subqueries and executing them all with performance decrease.

View 10 Replies View Related

How To Use Parameters In Plsql Procedure

Oct 21, 2010

I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.

Example:
in excel the column has : 'ABC','GEH','HGT',LTP'

create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;

This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.

View 2 Replies View Related

SQL & PL/SQL :: Created One Procedure With 4 In Parameters

Aug 20, 2013

i created one procedure with 4 in parameters and 1 out parameters(return the value) but whenever execute this procedure i got the error i.e pl/sql: Compilation unit analysis terminated.

PLS-00201:identifier T_USER_PLDATA' must be declared.

CREATE OR REPLACE PROCEDURE GET_USER_PLDATA1( V_PROD_LINE_CD IN VARCHAR2,
V_BUS_GROUP_CD IN VARCHAR2,
V_BUS_UNIT_CD IN VARCHAR2,
V_COUNTRY_CD IN VARCHAR2,
V_USER_PLDATA OUT T_USER_PLDATA)[c
[code]....

View 7 Replies View Related

PL/SQL :: To Create A Procedure Which Have 3 Parameters

Jan 11, 2013

I would like to create a procedure which have 3 parameters. suppose in Date_birth, in Dept , out number_of_records

if i pass null value to one of the parameter it should return all values . if a pass value to the parameter it should return as per parameter values

Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number,
p_recordset Out Sys_Refcursor) as
Begin
OPEN p_recordset FOR
Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records
From Emp_Master Where Date_of_birth =P_date_of_Birth
and Dept=P_dept ;
End ;

View 2 Replies View Related

SQL & PL/SQL :: Picking Up Parameters For Oracle Procedure

Mar 6, 2012

My Oracle procedure works on two parameters, file type and file name.

exec xml_trans.load_xml('OMG','Sample.xml');

First parameter is xml file type and second parameter is xml file name. XML file is generated by by a web service and keep in a particular location.

Now the requirement is my procedure should pick up the in parameters, once the xml file is generated and my procedure should be running as per schedule. I can execute the procedure in schedule but how can i pick the file name and pass to procedure as soon the xml file is generated?

View 3 Replies View Related

PL/SQL :: Calling Oracle Procedure With Two OUT Parameters

Jun 20, 2012

I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.

proc_Test (p_resultset=> My_cursor)

How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 1.

View 5 Replies View Related

SQL & PL/SQL :: Checking Values Of Procedure Parameters

Jan 19, 2013

CREATE TABLE t2
(
id NUMBER,
ename2 VARCHAR2(20),
sal2 NUMBER,
job2 VARCHAR2(20),
conid NUMBER

[Code]...

My requirement is like when I am calling the procedure P1 with some values then it should check the table "t2".And table "t2" is linking with table "t3".

So what ever the column "verify" is there, it should check the incoming values against it. If matches success otherwise reject it.Later the incoming values is stored different tables.I am doing it in the above way by hard coding some value.

BEGIN
p1(1,'MILLER',500,'ADMIN'); --REJECT
p1(1,'MILLER',5000,'ADMIN'); --ACCEPT
P1(2,'MILLER',5000,'SALESMAN');--ACCEPT
END;

View 5 Replies View Related

Oracle 10g - Create A Stored Procedure Without Parameters

Oct 11, 2010

Iam using oracle10g . when i created a simple stored procedure,got an error

PLS-00428: an INTO clause is expected in this SELECT statement

here is my code

create or replace procedure sp_TrialLiswt
as begin
select * from mivrs_studyinfo;
end;

View 1 Replies View Related

Split Procedure Parameters Over Multiple Lines

Apr 4, 2007

When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:

BEGIN
send_mail('from@domain.com', 'to@domain.com, ???
'Subject', 'Message');
END;

What would I replace ??? with to extend the procedure to the next line?

I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.

When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.

View 3 Replies View Related

SQL & PL/SQL :: Create A Procedure That Inserts Parameters Into A Table

Oct 20, 2011

I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;

HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;

STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....

I think im close just that my syntax is off.

View 8 Replies View Related

PL/SQL :: How To Pass Changeable Actual Parameters Into Procedure

Mar 29, 2013

I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed successfully, but cannot insert any data into history table.

See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:

PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
BEGIN
INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
SELECT *
FROM TRC_XREF_CONT_EMAIL_MSGS
[code].......

View 10 Replies View Related

PL/SQL :: How To Get Some Optional Parameters Inside A Stored Procedure

Oct 1, 2013

Can we call one or more parameters inside a stored procedure call (like func_get_args in PHP) ? Example : 

create or replace PROCEDURE test_proc (
val1 in varchar DEFAULT NULL,
val2 in varchar DEFAULT NULL,
val3 in varchar DEFAULT NULL,
[code]..........

View 4 Replies View Related

SQL & PL/SQL :: Usage Of Loop - Creating A Procedure With 4 Input Parameters

Jun 6, 2012

I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.

create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/

View 6 Replies View Related

SQL & PL/SQL :: Stored Procedure That Takes List Of IDs As Input Parameters

Oct 22, 2010

I need a stored proc that takes list of ids as input parameters. For all these Ids. the proc will send out data from another table as out ref cursor. Sounds very simple yet I am stuck with how do I pass the input list of ids.

View 3 Replies View Related

SQL & PL/SQL :: Collections Fetch - Pass List As Input Parameters To Procedure

May 11, 2012

I have a record type and table of records as follows

type rec is record
(
empid employee.empid%type,
ename employee.ename%type
);

type tab_rec is table of rec;

Suppose data from employee table is fetched into this collection

Can you pls clarify if we can refer to all the rows of empid in the above collection ?

something like tab_rec.empid without using the subscript for referring to the nth row

My requirement isto pass this list as input parameters to a procedure(PL/SQL).

View 3 Replies View Related

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

Oct 4, 2011

when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14

code which i have written ...

DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT

Procedure Name :

PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,

[code]....

View 2 Replies View Related

Function And Procedure Execution

Jul 7, 2010

I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.

how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Procedure And Function

May 22, 2010

I know difference between procedure and function.if we want a return value from procedure, we can have OUT parameter. Similarly with function, in addition to returning a value from function, it can also send an OUT parameter value as a return value. That means, in one or the other way we are able to get a return value from both program units. Normally, I would fill a OUT variable with error message when an exception occurs. I use this varaible,after procedure call, to detect if an exception occurred or not. Similar task can be performed by a function, it returns a true/false and a value thru OUT variables.

both program units return values in the form of OUT parameters. Where exactly should we use a function, where exactly should we use a procedure?

View 4 Replies View Related

SQL & PL/SQL :: Procedure And Function Execution

Jul 8, 2010

I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.

View 2 Replies View Related

SQL & PL/SQL :: How To Call Outside Function In Procedure

Dec 3, 2011

How to call outside function in procedure

View 11 Replies View Related

SQL & PL/SQL :: Using Procedure Inside Function?

Nov 8, 2011

I have question.Using procedure inside the function ?can I get better performance?

View 8 Replies View Related

SQL & PL/SQL :: Does Procedure And Function Contain Data

Nov 10, 2011

Does procedure and function contain data?

View 3 Replies View Related

SQL & PL/SQL :: Procedure Versus Function?

Dec 27, 2011

Procedure and function. exact reason when we go for function or procedure?

View 3 Replies View Related

SQL & PL/SQL :: Stored Procedure Vs Function?

Jun 9, 2010

Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....

View 3 Replies View Related







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