A way to identify variables declared in a procedure that are not being used? I had thought plsql_warnings might do the trick but it does not.
I have inherited a lot of code that was ill conceived and unfortunately have started to notice a trend.
PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2,
P_Txn_Ccy IN VARCHAR2,
P_Allocation IN VARCHAR2,
l_lty_id IN VARCHAR2 ) IS
l_balance_fmt NUMBER := 0;
BEGIN
[code]....
it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..
how to nullify variables in a procedure in pl/sql
View 1 Replies View RelatedI'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30);
END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011'
INTO END_DT
FROM DUAL;
[code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
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 ?
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.
marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.
I have a problem...
I created a tablespace called my_ts:
CODECREATE TABLESPACE my_ts DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
ALTER DATABASE DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' AUTOEXTEND ON;
Its was sucessfully created and my_ts.dbf file has 5MB
charging with data...
CODEcreate table big_table tablespace my_ts as select * from dba_objects;
select * from big_table;
begin
for i in 1..10 loop
insert into big_table select * from dba_objects;
end loop;
end;
Now the my_ts.dbf file has 90MB
Now I want drop this table:
CODEdrop table big_table purge;
And my tablespace file still has 90MB.
I already tried to restart the database but doesn't works...
how can I reduce the size to the original (5MB)?
I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?
If you mark a column unused, is there any way to project it? I know the docs say you can't, but as the data is still there I would have thought it should be possible. I can see the column in dba_tab_cols, but the obvious ways of making it usable don't work:
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID
------------------------------ ---
LOC NO
DNAME NO
DEPTNO NO
orcl> alter table dept set unused column loc;
Table altered.
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID
------------------------------ ---
SYS_C00003_13071316:19:02$ YES
DNAME NO
DEPTNO NO
orcl> select "SYS_C00003_13071316:19:02$" from dept;
select "SYS_C00003_13071316:19:02$" from dept
*
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept rename column "SYS_C00003_13071316:19:02$"
2 to loc;
alter table dept rename column "SYS_C00003_13071316:19:02$"
*
ERROR at line 1:ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept modify "SYS_C00003_13071316:19:02$"
2 visible;
alter table dept modify "SYS_C00003_13071316:19:02$"
*
ERROR at line 1: ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?
Give sql query to find the list of unused tables and procedures.
SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 Replies View Relatedwhat happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.
My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?
Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.
is there a way to find out which unused columns in oracle?
View 8 Replies View Relatedim as using oracle 8 with sqltools i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them.for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable.but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
consider the trigger below,
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
AFTER UPDATE
ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
[code].....
The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?
Below is the code I am facing problem using tablename as variable.
I have five tavble is scheme Emp1,Emp2..Emp5
CREATE OR REPLACE
procedure emp_up as
tablename1 varchar2(30) ;
Begin
For x in 1..5
LOOP
tablename1 := 'EMP' ||to_char(x);
EXECUTE IMMEDIATE 'update '||tablename1 || 'set ename = ''ZZZZZ'' where ename in (''MILLER'')';
END LOOP;
End;
Error : Identifier EMP must be declare
what is use of cursor variables?
View 1 Replies View RelatedThe following code is getting the 'Not all Variables bound' error. There are only two variables so I don't see the order being an issue. Assume the integers are assigned elsewhere.
DateTime beginDT = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt);
DateTime endDT = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);
SQL.Append(" WHERE DATE >= :beginDTParameter ");
SQL.Append("AND DATE < :endDTParameter");
OracleCommand cmd = connection.CreateCommand();
cmd.Parameters.Add(new OracleParameter("beginDTParameter", OracleType.DateTime)).Value = beginDT;
cmd.Parameters.Add(new OracleParameter("endDTParameter", OracleType.DateTime)).Value = endDT;
Explain about Global variables in Plsql? What is use of these global variables and where do use these variables?
View 1 Replies View RelatedI was new to Oracle. When i am Executing this Query, I was getting the error "ORA - 010008 - not all variables bound" .
VARIABLE sathya NUMBER
BEGIN
SELECT sal INTO :sathya FROM emp WHERE empno=7902;
END;
HOW i can use the Substitution Variables in select statement in From phrase
we need execute select statement in different users in run time.
I have written follwoing code in form.
Declare
V_Empno Number:=51469;
Begin
Insert Into Emp(EmpNo,Ename)
[Code]....
When I run Above Code Then Error Is ORA-01008 Not all variables bound. Then I changed above code with following.
Declare
V_Empno Number:=51469;
Begin
[Code]....
Above code is working. but i am unable to understand why Dup_Val_On_Index is generating error ora-01008.
i have some confusion with bind and host variable.
View 4 Replies View RelatedI have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>>
declare
v_sal1 number(7,2) := 60000;
v_comm number(7,2) : v_sal1 * 0.20;
v_message varchar2(2000) := 'eligible for commission';
begin
[code]........
Create and invoke the GET_JOB function to return a job title.
a.Create and compile a function called GET_JOB to return a job title.
b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION:
CREATE OR REPLACE
FUNCTION GET_JOB(
p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35)
set serveroutput on
DECLARE
P_JOBID VARCHAR2(10);
v_jobtitle VARCHAR2(200);
[code]...
I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like
SELECT :var1 AS var1
FROM t1
WHERE
:var1 = 1
In the past I have seen where variables can be used in the WHERE clause like
SELECT c1
FROM t1
WHERE
c1 = :my_column_valueand this makes sense to me.
Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???
The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?
The only thing I can gather that the first query does is something along the lines of
SELECT 1 as var1
so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.
So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.
I am running this query and getting this exception ORA-01008 All variables are not bound .
SELECT EQMT_INGT_LOG_ID, EQMT_ID,
XMLSerialize(DOCUMENT XMLType(ingLog.BUCK_SLIP_XML) AS CLOB) BUCK_SLIP_XML
FROM TOS_EQMT_INGT_LOG ingLog
where BUCK_SLIP_XML is not null and ingt_date between to_date(:fromDate, 'MM/DD/YYYY HH24:MI')
[code]...