SQL & PL/SQL :: How To Set Environment Variable Inside Procedure
Nov 24, 2010
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"
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
i am reinstalling oracel 11g in my window 7 64 bit machine after i uninstalled it. however, i got an issue that is environment variable path failed in the installation process. this didn't happen when i firstly successfully installed oracle.
I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.
DECLARE P_attribute1 VARCHAR2(100) := '110000027'; P_attribute VARCHAR2(100); l_cnt NUMBER := 10; v_buffer VARCHAR2(500); v_buffer1 VARCHAR2(500); BEGIN FOR i IN 1 .. l_cnt LOOP v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!'; END LOOP; dbms_output.put_line('v_buffer :' || v_buffer); END; v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace 2 PACKAGE new_pack 3 AS 4 g_id employee_details1.employee_id%type; 5 PROCEDURE emp_project( 6 st_date IN DATE, 7 Prj_id out VARCHAR2, 8 prj_name out VARCHAR2, 9 Prj_location out VARCHAR2); 10 11 END new_pack; 12 /
Package created.
SQL> CREATE OR REPLACE 2 PACKAGE body new_pack 3 AS 4 PROCEDURE emp_project( 5 st_date IN DATE, 6 Prj_id OUT VARCHAR2, 7 prj_name OUT VARCHAR2, 8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR -------- ----------------------------------------------------------------- 12/12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table LONG_ double ref char time timestamp interval date binary national character nchar
Where I am copying the data from one database to another I am getting this error.
ORA-01458: invalid length inside variable character string
This is applicable for only character string.
The source Database is Oracle9i Release 9.2.0.8.0 - 64bit Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for HPUX: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production
The destination database is Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production PL/SQL Release 9.0.1.1.1 - Production CORE 9.0.1.1.1 Production TNS for 32-bit Windows: Version 9.0.1.1.0 - Production NLSRTL Version 9.0.1.1.1 - Production
I 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 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''
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.
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'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]........
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: .(*%&................
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]....
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.
I'm trying to move my backup sets from windows database environment, to OEL 5.7 environment on another server.
I've found a manual [URL] by which I am trying to do it.I took backup sets from last night's backup using RMAN,and the current parameter(initSID.ora) file from the running live database.Now i need to configure control files in the pfile accordingly.
1. can i take current control files from the running system, to restore and recover backup sets from last night, to the state the database was at backup time?
2. how can i find out if control files are backed up and know by RMAN? "list backup completed after '2012-JUN-19';" >> gives me Archive redo logs, datafiles, but don't see the control files(or don't reconize them).
I have created the following procedure. Since I am using this first time I don't know how to execute this.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package IS TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER; PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots ); END; /
