SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure
May 14, 2013
I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:
Procedur MyProc
(
In_EmpID Number default null,
In_EmpName Varchar2 default null,
in_JoinDate Date default null
[code]....
I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.
View 2 Replies
ADVERTISEMENT
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
Jul 27, 2012
I have a query which is using literals
strquery:='SELECT SUMTOTAL FROM tab1 WHERE BATCHNO = '''
|| gBNo
|| ''' AND A_ID = '''
|| g_id
|| ''' AND L_ID = '''
|| g_LId
|| ''' AND S_Code = ''C_3'' ';
execute immediate strquery; I have been asked to use a bind variable to avoid hard parsing.How do i do it?
View 2 Replies
View Related
Apr 9, 2012
I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.
For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".
DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....
For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?
View 8 Replies
View Related
Nov 11, 2012
Just explaining what I am trying to achieve:
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is
[Code].....
View 5 Replies
View Related
Dec 17, 2012
I have the following procedure body in a package.
PROCEDURE getrecordsForinspection(i_table_name in varchar2, i_thread_id in varchar2, i_max_count in number default null, o_results out sys_refcursor)
AS
v_sql varchar2(1000):= null;
begin
v_sql := 'update '||'i_table_name||' set status = '||'''IN_PROCESS-'||i_thread_id||''''||' Where final_status = '||''''STATUS_ACCEPTED'''||' and ('||i_max_count||' is null or rownum <= '||i_max_count||');';
EXECUTE IMMEDIATE(v_sql);
commit;
end;
when I execute the above procedure it gives the following error.
ORA-00911: invalid character
cause: Identifiers may not start with any ASCII characters other than letters and numbers.$#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes(q'#....#') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL language reference Manual.
I think dynamic sql is not executed because of the pipe character in the sql statement.
View 2 Replies
View Related
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
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
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
Aug 13, 2013
Identical statements from this link : Parsing in Oracle — 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
Apr 2, 2013
What is the difference between these two variables and their usage?
View 6 Replies
View Related
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
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
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
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
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
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
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
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
Apr 17, 2013
i want to print a message just before asking for input to a bind variable
SQL> SET serveroutput on;
SQL> DECLARE
2 pname varchar2(20);
[Code].....
but i want to print 'hello world' before asking value for bind variable like:
hello world
Enter value for num: 1
old 9: WHERE ID = #
new 9: WHERE ID = 1;
then how to achive it?
View 4 Replies
View Related
Jul 28, 2008
I have developed a form based on a database of books. The information displayed during run time is Sr No, Book name,Author, Copies, Description and Image(of the book)
I have written a PL/SQL code to display the image of the respective book for every new record. E.g Book1 should display image1, book2 displays image2 and so on. This should happen at runtime. The code is:
declare
gif_image varchar2(80):='c:ProjectBooks';
photo_filename varchar2(80);
begin
photo_filename := gif_image||lower(:books.sr_no)||'.gif';
[code].......
The error i get during compilation is
Error 49 at Line 5, column 37
bad bind variable 'books.sr_no'
View 4 Replies
View Related
Feb 26, 2013
Is it possible to bind collection while opening a ref cursor. Find below the code that I am trying. My goal is to open cursor once using collection variable. Can it be done using DBMS_SQL ?
DECLARE
TYPE typ_emp_rec_in IS RECORD
(
deptno NUMBER,
sal NUMBER
[code]......
View 4 Replies
View Related
Jun 15, 2010
We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?
As example lets say the query is:
SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';
PL/SQL procedure successfully completed.
SQL> select * from emp where ename=:vn;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
1 row selected.
SQL> EXEC :vn :='KING';
PL/SQL procedure successfully completed.
SQL> select * from emp where ename=:vn;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
1 row selected.
But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?
View 9 Replies
View Related
Dec 12, 2011
since the optimizer (during explain plan) assumes all bind variable to be of varchar type, while checking plan for SQL statement using bind variable of numeric and date type shall we convert (typecast) it as following?
variable n_sal number
variable dt_joining date
exec n_sal:= 1000
exec dt_joining := '12-dec-2005'
select first_name from emp_data where sal=to_number(n_sal) and joining=to_date(dt_joining);
View 12 Replies
View Related
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
Apr 26, 2010
In the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.
In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.
For each record
if change_program =1 then v_tabl = 201
elseif change_program =2 then v_tabl = 202
elseif change_program =3 then v_tabl = 203
else v_tabl = 201
how to declare and use variables.
SELECT account,
change_program,
task_code_01,
task_code_02,
task1.longdesc,
task2.longdesc
FROM work
[code]....
View 4 Replies
View Related
Jul 17, 2013
I m trying to give table name as parameter to this function. It is compiling properly.But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.
//Function
create or replace function instant_tabula(tabula in varchar) return sys_refcursor
as
cur sys_refcursor;
str varchar2(20000);
begin
str := 'select * from ' || tabula;
open cur for str;
return cur;
end;
[code]....
View 7 Replies
View Related
Feb 25, 2011
Is it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
View 3 Replies
View Related
Jul 25, 2010
I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:
select * from scott.emp
and i want to write it as:
select * from variable.emp
But at compilation of the form the compiler should know the above variable name.
I have tried to use following select statement but it does not work.
select user into :global.username from user_users
I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.
View 4 Replies
View Related
Dec 5, 2012
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies
View Related
