PL/SQL :: Bind And Define Variable
Apr 2, 2013What is the difference between these two variables and their usage?
View 6 Replies
ADVERTISEMENT
Forms :: Define Global Variable In Oracle 6i
Aug 12, 2010I want to define global variable in oracle form 6i....so this variable may i used anywhere in project...but i don't know where exactly global variable defined?
View 7 Replies View RelatedSQL & PL/SQL :: Want To Define Dynamic Type For A Variable In Oracle 10g
Jul 17, 2013I 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]....
SQL & PL/SQL :: How To Use Bind Variable
Jan 12, 2012I 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;
SQL & PL/SQL :: Bind Variable OLD Not Declared
Nov 28, 2011I 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..
SQL & PL/SQL :: Bind Variable In Views?
Dec 27, 2010how 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.
Bind Variable In Parsing?
Aug 13, 2013Identical 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 RelatedSQL & PL/SQL :: PLS-00049 Bad Bind Variable?
Nov 2, 2011PLS-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]...
SQL & PL/SQL :: Bind Variable - Invalid Table Name
Jul 13, 2010create 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.
SQL & PL/SQL :: SP2-0552 / Bind Variable NEW Not Declared
May 7, 2013I 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]....
PL/SQL :: Bind Variable As A Literal String Value
Nov 5, 2013Oracle 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}
Forms :: Bad Bind Variable Error?
Jun 29, 2013Whenever 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]...
SQL & PL/SQL :: Bind Variable Inside Procedure?
Jun 18, 2013I 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].....
Forms :: Error 49 Bad Bind Variable?
Dec 14, 2011i 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
PL / SQL - Constant Recognized As Bind Variable By Oracle?
Jul 24, 2013The 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
SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure
May 14, 2013I 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.
SQL & PL/SQL :: Print Message Just Before Asking For Input To Bind Variable?
Apr 17, 2013i 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?
Forms :: Error During Compilation - Bad Bind Variable
Jul 28, 2008I 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'
PL/SQL :: Open Ref Cursor Using Collection As Bind Variable
Feb 26, 2013Is 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]......
PL/SQL :: How To Use Bind Variable In The Query To Avoid Hard Parsing
Jul 27, 2012I 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?
Performance Tuning :: Avoiding Bind Variable From Sql In Java Code?
Jun 15, 2010We 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?
Performance Tuning :: Difficulty In Using Bind Variable To Check Explain Plan
Dec 12, 2011since 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);
SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record
Feb 25, 2011Is 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]....
SQL & PL/SQL :: How To Use DEFINE Command
Jun 5, 2012set serveroutput on
set verify off
declare
define t1='111';
begin
dbms_output.put_line(t1);
end;
/
define t1='111';
*
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ; not null range default character
The symbol ":= was inserted before "=" to continue.
SQL & PL/SQL :: Define A Cursor Using Dynamic Query?
Aug 29, 2010Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.
I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).
SQL & PL/SQL :: Declare A User Define Exception Name As Per Defined Name?
Jan 25, 2011Can we able to declare a user define exception name as per defined exception name?Ex: we have predefined exception called :"NO_DATA_FOUND "
Now in my PL/SQL block can i able to declare "NO_DATA_FOUND" like
Begin
no_data_found exception;
//statements
raise no_data_found;
end
RAC & Failsafe :: Define DB Connection URL For Two Different SID Names In Single URL?
Apr 3, 2012As you see in the following MY_DB_URL there are two hosts for one SID. This is works perfectly for oracle driver. When one of them is gone down, connection establishes with the other.
MY_DB_URL = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.104.18)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.104.19)(PORT= 1521))(FAILOVER=true)(LOAD_BALANCE=false)(CONNECT_DATA=(SERVER=DEDICATED)(SID=SIDNAME_1)))
But i have a problem. Our partner uses Golden Gate product, and they said that they must install two different oracle with two different SID names. (For Example one of them is SIDNAME_1 the other is SIDNAME_2) So i can't use this URL in my application.
Is there a way to define a DB connection URL for two different SID names in a single URL?
Client Tools :: How To Define ED Command In Sqlplus
Jul 25, 2012In my sql*plus when i am writing the command ed .It was showing the error as \
SQL> ed
SP2-0110: Cannot create save file "afiedt.buf"
i tried to define the editor by using the command define_editor=notepad;
But still my problem was not solved.
SQL & PL/SQL :: Can Use User Define Value In Nested Function Of A Query
Oct 14, 2010can we use user define value (how to use '&') in nested function of a query ?
View 7 Replies View RelatedPL/SQL :: Define SELECT Clause Once And Reuse It In Many Other Queries?
Mar 16, 2013Is there a way to define a SELECT clause once and reuse it in many other queries?
I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.