SQL & PL/SQL :: How To Nullify Variables In A Procedure

Dec 13, 2012

how to nullify variables in a procedure in pl/sql

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Unused Variables In A Procedure

Jul 30, 2010

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.

View 8 Replies View Related

SQL & PL/SQL :: Variables In Loop Procedure

Feb 15, 2010

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..

View 3 Replies View Related

PL/SQL :: Executing Procedure Using Variables

Feb 6, 2013

I'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.

View 6 Replies View Related

SQL & PL/SQL :: Bind Variables - Create Or Replace Procedure

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

SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors

Oct 6, 2011

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.

View 7 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

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;

View 8 Replies View Related

How To Use Variables Within Select

Apr 5, 2011

im 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.

View 2 Replies View Related

Using Variables In A Trigger?

Oct 24, 2007

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?

View 2 Replies View Related

SQL & PL/SQL :: Using Table Name As Variables?

Aug 23, 2010

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

View 15 Replies View Related

SQL & PL/SQL :: Use Of Cursor Variables?

Mar 11, 2012

what is use of cursor variables?

View 1 Replies View Related

ODP.NET :: Binding SQL Variables

Nov 5, 2012

The 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;

View 2 Replies View Related

SQL & PL/SQL :: Use Of Global Variables

Feb 24, 2012

Explain about Global variables in Plsql? What is use of these global variables and where do use these variables?

View 1 Replies View Related

PL/SQL :: Not All Variables Bound

Sep 4, 2012

I 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;

View 10 Replies View Related

SQL & PL/SQL :: How To Use Substitution Variables In FROM Phrase

May 17, 2010

HOW i can use the Substitution Variables in select statement in From phrase

we need execute select statement in different users in run time.

View 15 Replies View Related

SQL & PL/SQL :: ORA-01008 Not All Variables Bound

Jun 26, 2012

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.

View 3 Replies View Related

SQL & PL/SQL :: Bind And Host Variables

Dec 22, 2011

i have some confusion with bind and host variable.

View 4 Replies View Related

SQL & PL/SQL :: Bind Variables Message

Sep 2, 2011

I 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]........

View 8 Replies View Related

SQL & PL/SQL :: FUNCTIONS - Bind Variables?

Nov 20, 2012

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]...

View 5 Replies View Related

PL/SQL :: Variables In SELECT Statement

Feb 19, 2013

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.

View 5 Replies View Related

PL/SQL :: ORA-01008 All Variables Are Not Bound?

Feb 4, 2013

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]...

View 2 Replies View Related

PL/SQL :: Select INTO Multiple Variables

Apr 23, 2013

I have a SELECT INTO statement as follows:

-----
SELECT SUM(s1.PRODUCT_QTY) INTO anz
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
          AND          s2.REGION = 'ANZ'
AND s1.GCM_OPP_ID = s2.GCM_OPP_ID

[Code]..

The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.

The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like

-----
SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE ..................
-----

View 2 Replies View Related

PL/SQL Loop Types - Variables Incrementing Together

Nov 8, 2012

set server output on;

DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
WHILE num < 5 LOOP
[code]...........

the output for this loop is:
1+1=2
2+2=4
3+3=6
4+4=8

this is what I wanted. Both variables are incrementing together. However, when I try to use a FOR loop to do the same thing:

set serveroutput on;

DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
FOR i in 1..4 LOOP
[code].......

I get the result:

1+1=2
1+2=3
1+3=4
1+4=5
2+5=7
2+6=8
2+7=9
2+8=10
3+9=12
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 8

I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops.

View 4 Replies View Related

How To Set Apache Environment Variables From Forms

Dec 27, 2012

We have an application, where we invoke a URL pointing to a folder on the Application Server, so that users can download files from the same.

There is user authentication provided for the URL.

Example :

Our application ABC (example) which is built with Oracle Forms 10g, hosted on Application server (itxxx123) inovkes a module GEN9999 which on pressing a button calls a URL which is open in the Internet Explorer browser.

If the URL has been called from the Forms or the Application (ABC), then it should be allowed.

If the URL is called standalone, by Opening IE Browser and invoking the URL, this should not be allowed.

However, there have been security concerns raised during Audit, saying that users can access the URL directly from the Browser instead of the Application. Also the URL is saved the Browser history, so even if the user is no longer working in the Application Team, and has moved out of the team, then the ex-user can still access the URL and view and download files.

Is there any way to restrict access to the URL such that is only accessible when it is called from the Application, and that access is restricted when directly called from the Browser.

I have seen that environment variables in httpd.conf may be usable for the above. However, not sure how to use them from Oracle Forms.

View 1 Replies View Related

Oracle - Create Tables Using Variables In PL/SQL?

Feb 3, 2010

is it possible to create tables using variables in pl/sql such as the following example which is actually in php. i would like to create a table dynamically.

$create_samp_table = CREATE TABLE $MCSCHEMA.".$samp_id." AS SELECT * FROM $MCSCHEMA.".$samp_src_table." WHERE ROWNUM = 0

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Where Clauses And Bind Variables?

Jun 6, 2012

I have a requirement where I have to return data as per filter clauses provided on the front end, which may or may not be filled as per the users' choice.

To create a test case, I have created a query joining the emp and dept tables and I add the where clauses dynamically depending on whether the filter has been provided or not.

CREATE OR REPLACE TYPE emp_ot AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),

[Code]....

It works very well, the 'literals' are being converted into bind variables (due to CURSOR_SHARING being set to SIMILAR) and the optimizer is able to figure out the correct execution path every time, although it is true that potentially 5 versions of this query will be sitting in the shared pool, but the DBAs are happy to live with that.

Going forward in version 12, CURSOR_SHARING=SIMILAR will be deprecated and the DBAs are not happy for us to write this sort of code anymore.

Is there a suitable way to achieve what this piece of code does?

I have tried and tested this method:
SELECT emp.empno, emp.ename, emp.job, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.empno = NVL(p_empno, emp.empno)
AND emp.ename LIKE NVL(p_ename, emp.ename)||'%'
...

but the query takes far longer to execute in this manner (that is using my real tables).

View 5 Replies View Related

Client Tools :: SQL Select With Variables

Feb 22, 2010

Having trouble with my variables in this staement.

SELECT WP_USERS.F_NAME || ', ' || WP_USERS.L_NAME AS NAME, WP_PROJECTS.TITLE, WP_PROJECTS.CLIENT_MANAGER,
WP_PROJECTS.PERSON_DAYS
FROM WP_PROJECTS, WP_PROJECTS_USERS, WP_USERS
WHERE WP_PROJECTS.PROJECT_ID = WP_PROJECTS_USERS.PROJECT_ID AND WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND
WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND (WP_PROJECTS.SITE = "@SITE") AND (WP_PROJECTS."SECTION" = '@SECTION')

View 7 Replies View Related

SQL & PL/SQL :: Comparing Variables In Trigger Block?

Jun 29, 2010

I am having some issues in creating the following trigger. It gets created but with compilation errors.

create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;

[code].....

when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.

View 12 Replies View Related

SQL & PL/SQL :: Creating Global Variables In Sqlplus On 11g

May 4, 2011

I have taken some procedures code from SVN repository.These procedures are split into different schema. There are some client and common parameters used in the from clause of table in these sp.for example

select *
from client.emp;

during the compling procedure, we replace the client value into some schema name.

select *
from scott.emp;

The client and common parameter should be different for every schema.

Is any possible to create global varaible for client and common parameter and subsitute value in it.i.e without editing client and common parameter?

View 8 Replies View Related

Client Tools :: Substitution Variables

Feb 22, 2010

How I go about writing a query that doesn't see the '&' symbol as a substitution character. The problem I have is:

select replace
( fnd_profile.value( 'XXBPO_URL')
, '&DOCID'
, ai_dfv.bpo__document_link
) document_link
from ap_invoices_all ai
, ap_invoices_all_dfv ai_dfv
where 1=1
-- where clause

The problem above is that instead of replacing the characters &DOCID with the required value, it is prompting for input.

View 4 Replies View Related







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