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
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.
I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:
before the select query i need to insert this environment variable : "set linesize 200"
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;
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..
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.
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
- 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}
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
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'
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]......
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?
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?
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);
I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.
DECLARE P_attribute1 VARCHAR2(100) := '110000027'; P_attribute VARCHAR2(100); l_cnt NUMBER := 10; v_buffer VARCHAR2(500); v_buffer1 VARCHAR2(500); BEGIN FOR i IN 1 .. l_cnt LOOP v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!'; END LOOP; dbms_output.put_line('v_buffer :' || v_buffer); END; v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace 2 PACKAGE new_pack 3 AS 4 g_id employee_details1.employee_id%type; 5 PROCEDURE emp_project( 6 st_date IN DATE, 7 Prj_id out VARCHAR2, 8 prj_name out VARCHAR2, 9 Prj_location out VARCHAR2); 10 11 END new_pack; 12 /
Package created.
SQL> CREATE OR REPLACE 2 PACKAGE body new_pack 3 AS 4 PROCEDURE emp_project( 5 st_date IN DATE, 6 Prj_id OUT VARCHAR2, 7 prj_name OUT VARCHAR2, 8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR -------- ----------------------------------------------------------------- 12/12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table LONG_ double ref char time timestamp interval date binary national character nchar
Where I am copying the data from one database to another I am getting this error.
ORA-01458: invalid length inside variable character string
This is applicable for only character string.
The source Database is Oracle9i Release 9.2.0.8.0 - 64bit Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for HPUX: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production
The destination database is Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production PL/SQL Release 9.0.1.1.1 - Production CORE 9.0.1.1.1 Production TNS for 32-bit Windows: Version 9.0.1.1.0 - Production NLSRTL Version 9.0.1.1.1 - Production
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 ?
