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


ADVERTISEMENT

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

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

SQL & PL/SQL :: How Many Maximum Number Of Bind Variables

Mar 10, 2010

How many maximum number of bind variables,can we use in Execute Immediate.

View 2 Replies View Related

SQL & PL/SQL :: Bind Variables / Unable To Fetch Value

Sep 8, 2011

CODE
Select
Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0)
From FnTrantt A
Where A.GrpCode=:1 And
A.CmpCode=:2 And';
A.DiviCode=:3 And';
A.SubDiVCd=:4 And';
A.FinYear>=''0001'' And';
A.VchDate Between :5 And :6' And
A.GlCode In :7;
[code]....

In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,

View 3 Replies View Related

PL/SQL :: Bind Variables And Expression Evaluation

Aug 29, 2013

i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem: 

select egp.bsn,egp.klantnummer as "Persoonsnummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from   pr_eigenschappen2      egpwhere(egp.bsn = :b1 or :b2 is null)and rownum < 51 egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics. when run with b1:=928818 and b2:=928818  (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2. if the query is changed to select egp.bsn,egp.klantnummer  as "Persoons nummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from   pr_eigenschappen2      egpwhere(egp.bsn   = :b1 or 928818 is null)

and rownum < 51the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.

View 23 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

Precompilers, OCI & OCCI :: Bind Variables API Obndrv()?

Apr 21, 2009

can i have some real time code piece for bind variables steps and obndrv(...)

View 1 Replies View Related

SQL & PL/SQL :: Create Table Using Bind Variables In EXECUTE IMMEDIATE

Feb 22, 2010

I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..

Following is the example :

Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;

After running above block it is giving error : ORA-01008: not all variables bound.

View 10 Replies View Related

Explain Plan Differences With Or Without Nvl On Bind Variables?

Jul 1, 2013

We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --

Just bind variable --select *from   MTL_MATERIAL_TRANSACTIONS mmtwhere  1 = 1and    mmt.INVENTORY_ITEM_ID = :p1and    mmt.ORGANIZATION_ID   = :p2and    mmt.TRANSACTION_REFERENCE = :p3--and    mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from   MTL_MATERIAL_TRANSACTIONS mmtwhere  1 = 1and    mmt.INVENTORY_ITEM_ID = :p1and   

[code]...

View 3 Replies View Related

Application Express :: Collection In 4.1 Using Bind Variables?

Oct 4, 2012

I am trying to create a collection using bind variables in APEX 4.1. I have the following procedure but all I get is an error in the debug page DOH ORA-20104:

create_collection_from_query_b Error:ORA-01006: bind variable does not exist

My code is

DECLARE
l_names wwv_flow_global.vc_arr2;
l_values wwv_flow_global.vc_arr2;
l_names(1) := 'EXPENSE_FROM';
l_names(2) := 'EXPENSE_TO';

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Bind Variables / Developing Stored Procedures In Developer

May 16, 2010

Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.

The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:

SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....

and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....

View 7 Replies View Related

Client Tools :: Providing Bind Variables As Values In Insert Statement?

Aug 23, 2011

I executed the following PL/SQL block in SqlDeveloper :

VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/

And it gave the error : Quote:Error report:

ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"

The same code when executed in iSqlPlus gave no error.

View 13 Replies View Related

SQL & PL/SQL :: How To Use Bind Variable

Jan 12, 2012

I have the below cursor 1 which is working already.For my requirement i want to use bind variable like second cursor.But its telling Bind Variable "p_col_list" is NOT DECLARED.

How to use bind variable Here.

Cursor1:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql ;
END;

Cursor2:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(:p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql using p_col_list;
END;

View 2 Replies View Related

Unable To Bind - OLD And NEW

Mar 18, 2009

Whenever I try to create this trigger, it asks for values for :OLD and :NEW for some reason, and then when I attempt to put those in, it just says

Unable to bind ":OLD"
Unable to bind ":NEW"

CREATE OR REPLACE TRIGGER audit_grade_change
BEFORE UPDATE OF final_letter_grade ON enrollments
FOR EACH ROW
BEGIN
UPDATE grade_change_history SET old_final_grade=:old.final_letter_grade, new_final_grade=:new.final_letter_grade;
end;

am I using :OLD and :NEW incorrectly?

View 1 Replies View Related

SQL & PL/SQL :: Bind Variable OLD Not Declared

Nov 28, 2011

I am facing the same problem: SP2-0552: Bind variable "OLD" not declared. When my script create_trigger.sql is executed,there is no error but when i execute it inside a pl/sql block it get above error...In the trigger we are using if conditions

if(:new.sumthing=1)and (:old.sumthing=0)the
do this..

View 1 Replies View Related

SQL & PL/SQL :: Bind Variable In Views?

Dec 27, 2010

how to avoid the bind variable in view.

The query is correct but it contains bind variable Based on this query, View has to be created for report

like example select * from emp where deptno = :deptno

How to get the correct result by avoiding bind variable because view does not accept bind variable.

View 2 Replies View Related

Bind Variable In Parsing?

Aug 13, 2013

Identical statements  from this link :  Parsing in Oracle &mdash; DatabaseJournal.com d. The bind variable types of the new statement should be of same type as the identified matching statement. i am getting confuse here .. when  parsing occurs some links saying about bind variable.but official document  never said about bind variables.

View 3 Replies View Related

PL/SQL :: Bind And Define Variable

Apr 2, 2013

What is the difference between these two variables and their usage?

View 6 Replies View Related

SQL & PL/SQL :: PLS-00049 Bad Bind Variable?

Nov 2, 2011

PLS-00049 BAD BIND VAIRABLE 'OLD.REMARKS' When i create or replace the following trigger

CREATE OR REPLACE TRIGGER T_TASKHISTORY AFTER UPDATE ON S_TASK
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO S_TASKHIS (HIS_DATE,SUBJECT,DESP, SCHEDULED_DATE, SCHE_TIME ,USER_MOB_NO

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Bind Variable - Invalid Table Name

Jul 13, 2010

create table t (
col1 varchar2(2),
col2 varchar2(1),
tab_name varchar2(50));

[Code]....

In this case :v will be replaced with t1. I got the error invalid table name.

where table t1 has its own structure.

View 5 Replies View Related

SQL & PL/SQL :: SP2-0552 / Bind Variable NEW Not Declared

May 7, 2013

I am trying to create a simple trigger for practice. I am getting SP2-0552: Bind variable "NEW" not declared. error.

Below is the trigger script:

create or replace User_Cxt_Trigger
AFTER
INSERT or UPDATE OR DELETE ON NAME_VALUE_PREFS
referencing new as new old as old
FOR EACH ROW

[code]....

View 17 Replies View Related

PL/SQL :: Bind Variable As A Literal String Value

Nov 5, 2013

Oracle Database 10g

Enterprise Edition Release 10.2.0.5.0

- ProdPL/SQL Release 10.2.0.5.0
- ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0
- ProductionNLSRTL Version 10.2.0.5.0
- Production.

I have a problem when creating a dynamic statement. The problem is in the bind variable  ':OLD.CUST_NAME' ,..my question is is there an escape character to treat the bind variable as a literal string?

{code}v_str2 := '''CUST_NAMES='''||'||'|| ':OLD.CUST_NAME' ; 
 EXECUTE IMMEDIATE   'create or replace trigger trg_'  || SUBSTR (rec_cur.table_name, 1, 26) || ' before insert or update or delete on '                             || rec_cur.owner || '.' || rec_cur.table_name || '   declare   begin     if UPDATING then   FIFAPPS.ibug.log_errors('|| v_str2 ||'  );    end if;   end;';

{code} 

I want the output in a trigger something like this:{code}

if UPDATING then   FIFAPPS.ibug.log_errors('CUST_NAMES='||:OLD.CUST_NAME );{code}

View 11 Replies View Related

Forms :: Bad Bind Variable Error?

Jun 29, 2013

Whenever I am compiling the code, following error is coming.

BLOCK: WHEN-NEW-FORM-INSTANCE(Form),5 errors
Error 49 at line 42, column 1
bad bind variable 'tool.dsp_heading'
Error 49 at line 42, column 1

[code]...

View 6 Replies View Related

SQL & PL/SQL :: Bind Variable Inside Procedure?

Jun 18, 2013

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

View 10 Replies View Related

SQL & PL/SQL :: Passing Bind Char Correctly

Dec 7, 2010

I tried google and most of bind example using number instead of character, ?

I have the code as below:

variable oldId varchar2(5)
exec :oldId :='&1'
variable newId varchar2(5)
exec :newId :='&2'

[Code]...

Since in TAB1, ID is defined as varchar2(5), and I have defined both oldId and newId as varchar2(5) in my code, I wonder why it doesn't work.

It returned this error:

DECLARE
*
ERROR at line 1: ORA-01722: invalid number ORA-06512: at line xx

After supply the value for oldId(val2) and newId(val1)

SQL > @thisScript val1 val2

I use dbms_output.put_line to print the command executed, it seems it is doing

UPDATE TABLE1 SET ID = val1 WHERE ID = val2

without the single quote ('val1'), ('val2'). But if I put in single quotes, the output statement

UPDATE TABLE1 SET ID = ':newId' WHERE ID = ':oldId'

The same bind logic works in SQL prompt

SQL > variable oldId varchar2(5);
SQL > variable newId varchar2(5);
SQL > exec :oldId :='val2'
SQL > exec :newId :='val1'
SQL > UPDATE TABLE1 SET ID = :newId WHERE ID = :oldId;
1 row updated.

I am wondering what should I do then? With or without single quotes?

View 9 Replies View Related

Forms :: Error 49 Bad Bind Variable?

Dec 14, 2011

i am getting this error when compiling (error 49 bad bind variable) is there anything wrong in this
(

declare
total_rec number(2);
begin
:new_id:=null; :new_sal:=null;

[code]...

the code in the add-new command when_button_pressed trigger am adding employee to the employee table by this form

View 1 Replies View Related

PL / SQL - Constant Recognized As Bind Variable By Oracle?

Jul 24, 2013

The idea is to use some constant value in PL/SQL code with requirement to feed it to Oracle as value but not bind variable. Such constants used in multiple places in the code, so wants to declare it but from DB point of view it should be value. In my case Oracle will choose much better execution plan with real value for the table.

I tried to use constant, e.g:
CODEdeclare
const1 constant number := 1;
beging

[Code].....

But in sqlarea it represented as: SELECT SUBSCRIBER_ID FROM SUBSCRIBERS WHERE STATUS = :B1

View 5 Replies View Related







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