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


ADVERTISEMENT

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

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

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

Mar 31, 2004

ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.

View 3 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 :: 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 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

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

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

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

Server Administration :: Create View With Parameters?

Oct 26, 2011

I created a view with parameters l_id and l_name, how can i find them in oracle view?

Create Table tb_test
(
Id Number,
Name Varchar2(64)
);

Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;

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

Forms :: Create Table From Query With Passing Parameters

Oct 20, 2011

I want to create table from query with passing parameters

create table as temp
select * from emp
where hiredate between :sdate and :edate
and deptno = :dpt

when I tried in Toad after passing parameters it gives me error

ORA-01036: illegal variable name/number

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

Server Administration :: Create SPFILE - Failure In Processing System Parameters?

Oct 11, 2004

I have a small problem with creating the SPFILE,

These are the commands i had issued :-

1. startup nomount
2. create SPFILE from PFILE ;

Then i got an error :-

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:ORACLEORA92DATABASEINITORCL.ORA'

Ever since this happened i am unable to connect to ORACLE under any schema !

I get the following error : -

SQL> conn scott/tiger
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Warning: You are no longer connected to ORACLE.

WHY DOES THIS HAPPEN AND HOW CAN I OVERCOME IT ?

View 8 Replies View Related

Create Procedure To Create User In Oracle

Jan 19, 2012

I need to create PROCEDURE to create user in oracle

CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......

Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8

Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN

i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )

View 5 Replies View Related

PL/SQL :: Create A Procedure Inside A Procedure

Jun 25, 2012

DB version:11g

can we create a procedure inside a procedure...if not is there any alternative??

in db2 it is allowed so do oracle support this????

View 5 Replies View Related

SQL & PL/SQL :: Create DBMS-SQL Procedure?

Oct 7, 2010

I am creating the following dbms_sql procedure...

CREATE or replace PROCEDURE table_demo
(tabname IN varchar2)
IS
cursor_name INTEGER;
rows_processed INTEGER;

[code]...

There are no compilation errors.I call this code from the following anonymous block...

DECLARE
X CHAR:='T';
BEGIN
TABLE_DEMO(X);
END;

This also compiles successfully and without any errors. It runs properly as wellHowever when I run 'select * from T'. Then system throws up the error of table or view does not exist.

View 12 Replies View Related

SQL & PL/SQL :: Create A Trigger And Procedure To Go Along With DDL

May 11, 2010

I'm trying to create a trigger and procedure to go along with my DDL. Everything is created just fine, but when I try to execute an update on the table monitored by the trigger I get the following error:

update housing set group_num = 1 where room_num = 10

ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at "YANKEEFAN146.VIOLATION_CHK", line 6
ORA-04088: error during execution of trigger 'YANKEEFAN146.VIOLATION_CHK'

/* DDL */
create table violation_type
(violation_num number(1) primary key,
violation_def varchar2(100) not null)
organization index
tablespace mi257_data;
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?

Apr 14, 2013

I am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block

My code

CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE

[code]....

I called the procedure from an anonymous block

BEGIN
homework('Employees');
END;

It gives me an error

ORA-00905: missing keyword

View 3 Replies View Related

SQL & PL/SQL :: How To Create Procedure In Script

May 23, 2011

I have 5 create package scripts.Rather than executing as one by one in SQLPLUS ,i want to run all 5 create statments in shell script in background.

For example... i want to create one file called bala.sql and execute all 5 create package statements in one shot as a script called bala.sql.

View 21 Replies View Related

SQL & PL/SQL :: How To Create Procedure By Using Dblink

Jun 26, 2011

iN MY DB SERVER (CALLED A),THERE IS A DBLINK TO ANOTHER DB SERVER (CALLED B). DBLINK'S IS LINKB.
----------
BY USING LINKB,I CAN CREATE,DROP TABLE;DELETE,UPDATE,INSERT DATAS IN SERVER B.

Now,i want to create procedure on server b(but i can only deal with b using dblink linkb).

View 4 Replies View Related

Create A Procedure To Update Status?

Mar 10, 2011

create a procedure that will update the column status from active to closed after every two years.

View 4 Replies View Related

SQL & PL/SQL :: Create A Select Procedure With Parameter?

Feb 2, 2011

I have a query like this:

*************************
SELECT vendor_id, summary_flag
FROM ap_suppliers
WHERE vendor_id = :param; --'4551'

The Results:

************
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N

Then I create the procedure:

*****************************
CREATE OR REPLACE PROCEDURE myproc4 (
p_vendor_id IN ap_suppliers.vendor_id,
p_summary_flag OUT ap_suppliers.summary_flag
)
AS
BEGIN

[code]....

Warning: compiled but with compilation errors.

I want to create a procedure that call vendor_id (parameter) and the output like the this:

VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N

View 4 Replies View Related

Create Toad Stored Procedure?

Mar 8, 2007

I am trying to create a stored procedure in TOAD. I've found example code on the Internet to write a stored procedure, but it doesn't seem to work with Toad. I tried to write the procedure and execute it to save it to the DB, and instead I keep getting error messages like "an INTO clause is expected in this SELECT statement" when I used the

CREATE OR REPLACE PROCEDURE testing
AS
BEGIN
SELECT *
FROM CPK;
END;

or "bad bind variable 'CPKS'" when I tried the following instead:

CREATE OR REPLACE PROCEDURE testing
AS
BEGIN
SELECT *
FROM CPK;
Into :Cpks;
END;

post some example code here with short explanations for me on how to write a stored procedure.

View 4 Replies View Related







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