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]........
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????
I have two Tables, the one table is called (calves_per_breed) and contains all my query results. I then have another table (calves_per_breed_crosstable) which is used to place the generated count values in calves_per_breed to create a crosstable from the count data into calves_per_breed_crosstable.
I'm using the following procedure to generate the crosstable from the data inside the calves_per_breed table:
PROCEDURE pcalves_per_breed_genCrossTable
IS
BEGIN
---------------------------------------------------------------------------
--SEX CODES
update calves_per_breed_crosstable
set (m, f) = (select count(decode(geslag, 'M', 1, null)),
count(decode(geslag, 'F', 1, null))
from calves_per_breed)
[codee]....
Now this procedure works 100%, the only problem is it generates the ENTIRE table. if you know how a crosstable works, thetop right section of the table is exactly the same as the bottom left section of the table. I wish to optimize my code so that it only generates the values for the needed columns, and not ALL the columns, as values are generated twise now, which increases the query time! Here is a tipical output of the kalwers_per_ras_crosstable:
Note: You will notice that i used a Column called TID with string values to indicate the vertical columns for the crosstable. The Vertical Columns are the same as the top Columns(which are actual columns, and not row values as the vertical columns)
select *from kalwers_per_ras_crosstable:
TID M F NFR A B C SP RED BLACK SC1 SC2 SC3
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M 138 0 0 0 0 0 138 122 16 74 64 0
F 0 173 0 0 0 0 173 161 12 92 81 0
NFR 0 0 0 0 0 0 0 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0 0 0 0
[code]....
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 RelatedI 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
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
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.
How to create procedure for getting number of records of a table
View 24 Replies View RelatedI have question.Using procedure inside the function ?can I get better performance?
View 8 Replies View RelatedI am having a view say name vw_mytable , i need to call this view inside the stored procedure it is saying as Grant execute on
usera.vw_mytable to userb;ORA-02204: ALTER, INDEX and EXECUTE not allowed for views
how do i create a store procedure to call a view from another user.Usera is having a view and i need to create procedure in userb and call usera.vw_table.
I tried to Drop a procedure we have created, but an error is raised while droping it.
ora-04043 object does not exist
i'm issuing drop procedure generatePasswordForEportalUser
i have an
error ORA-00600: [kqlobjlod-no-result-from-proc$], [1403], [659858], [], [], [], [], [], [], [], [], []. i found on metalink then Doc ID 1267554.1
is about this error i'm using oracle 11.2.0.3 in this document there is an info that 11.2.0.2 solves the issue Interesting thing is that select * from user_procedures where upper(object_name) like upper ('generatePasswordForEportalUser');
generatePasswordForEportalUser6598581PROCEDURENONONONONODEFINERobject name is in lovercase it was created by create procedure generatePasswordForEportalUser asbeginnull;end ;without " name " why it is in lowercase in data dictionary
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.
I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:
before the select query i need to insert this environment variable : "set linesize 200"
create or replace
procedure TABLESPACE_USAGE
is
l_mailhost VARCHAR2(64) := 'ip address';
l_from VARCHAR2(64) := 'email id';
l_subject VARCHAR2(64) := 'TABLESPACE_USAGE1';
l_to VARCHAR2(128) := 'email id';
[code]......
I am trying to call procedure inside trigger.. but i get error ora-04098 ..
create table emp_hstry
as
select * from emp
where
1= 2 ;
create or replace procedure emp_del_hstry(v_empno NUMBER ,
v_ename VARCHAR2,
v_job VARCHAR2)
is
insert into emp_hstry (empno,ename,job)
values (v_empno,v_ename,v_job);
COMMIT;
end;
create or replace trigger emp_del_hstry1
after insert or delete
on emp
for each row
begin
if deleting then
emp_del_hstry(:old.empno,:old.ename,:old.job);
end if;
end;
delete from emp
where
empno = '7369'
AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..
I have a DML Statement inside a procedure and i use a cursor variable to get the values checked as below . I have attached my procedure not completely but the declaration part and the DML statement part.
The issue is my procedure is not inserting the records at all. It selects the values and then inserts accoringly but its not selecting because of the cursor reference R_LOC.LOCATION_GID.
when i hard code the value in the DML statemnt for the R_LOC.LOCATION_GID, the rows are inserted as expected. So i guess the way the procedure executes the value is not correct.
Modifying my select part which uses cursor variable R_LOC.LOCATION_GID under Insert statement.
select d.servprov_gid, d.depot_gid, replace(d.appointment_time,'':'') appmt_time, d.'||v_day_to_use||' DayUsed
from tesco_fresh_templates t, tesco_fresh_templates_d d, location_refnum r
where t.set_id= d.set_id
and d.depot_gid=r.location_gid
AND D.SERVPROV_GID=''R_LOC.LOCATION_GID''
and r.location_refnum_qual_gid=''TESCO.IVS SCHEDULING''
and (r.location_refnum_value=''YES'' or r.location_refnum_value=''Y'')
and t.default_set=''Y''
I have two procedure , from first procedure having some ref cursor output.
from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .
can I define bind variable inside the procedure , then how can I define it .
SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN
[code].....
Requirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.
View 3 Replies View Relatedi want to create a dropdown field it will display list of currencies for selection derived from
sy_curr_code (columns currency_code and des1)
in the s_sales_center(table )
-If INF_SRC_DB = AUS, will default to USD, otherwise default to BPS.
i have created this so far
vSQl := 'select toValueText(a.code1, a.descr) from (select currency_code code1, DES1 descr1 '||
'from sy_curr_code where active = ''Y'' '||
'and subcat1 = ''Y'' '||
') a ';
how to add the other logic into my code
how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.
procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)
[code]....
When I try to compile it, I get the error "component EXISTS must be declared".
I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?
View 4 Replies View RelatedCan 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]..........
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '
end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
I have to write a proc to display data based on user input for t_grp, and it should exclude grp0
So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.
table: target_data
create table target_data
(
t_mon varcahr2(6)
, t_grp varchar2(30)
, t_data varchar2(50)
)
;
INSERT INTO target_data VALUES ('Apr-11', 'grp1', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp2', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp4', 'some_data');
[code]......
Now the above proc is running fine, if I give the input as grp1 or any other t_grp.
forms_ddl('ALTER TABLE ACCOUNT.VOC_DETAIL DROP CONSTRAINT FK_VOC_NO');
forms_ddl('ALTER TABLE ACCOUNT.VOC_MASTER DROP CONSTRAINT PK_VOC_MASTER');
forms_ddl('ALTER TABLE ACCOUNT.VOC_MASTER ADD CONSTRAINTS PK_VOC_MASTER primary key(VOC_NO)');
forms_ddl('ALTER TABLE ACCOUNT.VOC_DETAIL ADD CONSTRAINTS FK_VOC_NO FOREIGN KEY(VOC_NO) REFERENCES VOC_MASTER(VOC_NO)');
the trigger works very well but when i add some trigger between drop and create constraints then first step drop constraint sucessfully but 2nd trigger when create new constraints not working examples of trigger are as under:-
declare
s_number varchar2(7);
cursor masters is
select voc_type,voc_date,voc_no from account.voc_masterorder by voc_type,voc_date,voc_no;
abc masters%rowtype;
cursor detail is
select voc_no,ref_no from account.voc_detail group by voc_no,ref_no;
[code]....
I would like to increase the size of my redo logs. For this I need to drop & recreate them. I have read the Oracle doc [URL] which covers this however :
I have both "normal" and "standby" redo logs (see below) on my primary database - can I drop the standby redo logs? Do these have a link to the redo logs on the standby database?
SQL> SELECt * from v$logfile order by 1;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------- 1 ONLINE D:ORACLEORADATAREDO01.LOG
2 ONLINE D:ORACLEORADATAREDO02.LOG
3 ONLINE D:ORACLEORADATAREDO03.LOG
4 STANDBY D:ORACLEORADATASTDBYREDO04.LOG
5 STANDBY D:ORACLEORADATASTDBYREDO05.LOG
6 STANDBY D:ORACLEORADATASTDBYREDO06.LOG
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
i want to store all rows of columns into single variable and then use in inside of SP
declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....
it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g
how to create tabs with drop down submenu items like the ones we have under products tab in the link below.
[URL]