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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
Aug 27, 2007
I got a table table1 with 3 columns: id, name, value
im trying to create a procedure to update the table.
create or replace
PROCEDURE TEST1 (
x IN varchar,
y IN varchar,
z IN varchar
) AS
BEGIN
update table1 set value=x where name=y and id=z;
commit;
END TEST1;
that doesnt seem to work
View 3 Replies
View Related
Oct 30, 2012
I have a table TABLE1 which has following columns
proc_name
table_name
insert
update
delete.
The table contains a proc name, all tables used in that proc,operation is done on the tables. like sample data.
proc_name tab_name insert update del select
----------------------------------------------------------------------------------
Proc1 Table1 Y Y N Y
Proc1 Table2 y Y Y Y
I want to update this table throu running a generic procedure.Can it be possible to craete a procedure which parses through all procs of database and get above info and update TABLE1??
View 1 Replies
View Related
May 14, 2013
How to create a list in oracle stored procedure and that list contains two columns.
View 1 Replies
View Related
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
May 21, 2013
how can i create this query on a procedure:
Insert into COMPUSOFT.PESAJE@DB_2
(PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA,
PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD,
USR_COD, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO)
select /*+ FULL(Tbl1) */
[code]..
plus i would like to insert also that when it runs the query also change a value in table pesaje column dmn_cod to "yes" default "no" in db_2
View 5 Replies
View Related
May 18, 2011
I have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:
create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;
Then as vackar_temp:
create type temp_col as table of number;
I get the following error:
ORA-01031: insufficient privileges
I've also tried:
grant resource to vackar_temp ;
But that doesn't work either If I run this:
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
[code]...
I can see that the user does have the create type role:
User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
[code]...
View 8 Replies
View Related
Aug 5, 2010
Need to create a procedure that will retrun me monthly count of records
create table sample ( S_name varchar2(20), S_Date Date);
insert into sample values('1','01-JAN-2005');
insert into sample values('1','1-JAN-2006');
insert into sample values('2','2-JAN-2007');
insert into sample values('3','4-JAN-2005');
insert into sample values('4','11-JAN-2004');
insert into sample values('3','2-JAN-2005');
insert into sample values('2','12-JAN-2006');
insert into sample values('1','21-JAN-2005');
insert into sample values('2','11-JAN-2005');
insert into sample values('7','01-JAN-2005');
[code]....
View 5 Replies
View Related
Aug 3, 2010
I want to create procedure who should run for both montly as well as yearly report for monthly report i will pass Year and Month as argument for year i will pass Year only
for annual report i a counting all the user, new entry for item, new enty for customer, new enty for admin for montly report i am counting new item, new group.
View 16 Replies
View Related
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
Feb 3, 2013
I want to create a procedure that will be checking passwords. A password should contain numbers and letters and be at least 8 characters long.
View 1 Replies
View Related
Jul 6, 2011
How to create procedure for getting number of records of a table
View 24 Replies
View Related
Jun 12, 2012
create or replace procedure my_proc(p_user in varchar2) is
l_cursor sys_refcursor;
l_query constant varchar2(1000) :=
'select a'
|| 'from ' || p_user || '.user_table'
|| 'where param0 = :x'
|| 'and param1 = :x'
|| 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
View 6 Replies
View Related
Jun 10, 2013
I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.
I cant track the error. Why is it so?
CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........
View 18 Replies
View Related
Feb 22, 2012
I want to create a stored procedure where tables of two different databases are to be used.
View 4 Replies
View Related
Feb 14, 2011
What is more efficient?
- To create one trigger on the table and make it call two unrelated procedures
OR
- To create 2 triggers and each trigger will call only one procedure.
View 13 Replies
View Related
Nov 1, 2013
how do I create a procedure for a SELECT query like the following?
When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE MyProcISBEGINselect 'Dakota' as ALIAS ,A.StartDate ,B.EndDatefrom Customer A ,Clients bwhere a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND MyProc;
View 17 Replies
View Related
Feb 17, 2010
I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.
Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...
I am using the following command:
sqlplus u/p@<someserver> @<path_to_sqlfile>.sql
this sql file contains create procedure pl/sql code
Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?
View 8 Replies
View Related
Dec 8, 2010
I have three tables fixtures, fixture_teams and team_tbl
fixtures consists of:
create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....
creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.
View 13 Replies
View Related
