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]......
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]....
While reading data from collection variable using ref cursor . I am getting the below two errors.
PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.
CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........
open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';
NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?
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]....
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..
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.
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 RelatedWhat is the difference between these two variables and their usage?
View 6 Replies View RelatedPLS-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]...
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.
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]....
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}
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]...
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].....
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
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
View 7 Replies View RelatedThe following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....
how to use collection variables as OUT parameters in procedure.
Declare
Cursor c1...;
Cursor c2...;
begin
open c1 ;
fecth c1 bulk collect into v1;
close c1;
[Code]...
Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?
declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;
[Code]...
The following procedure stores the retrieved data in o_ref_primary_dept collection variable,suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
BEGIN
l_primary_dept :=
'SELECT emp_obj(empno,ename)'
|| ' FROM emp';
EXECUTE IMMEDIATE l_primary_dept BULK COLLECT INTO o_ref_primary_dept;
END;
how to use collection variables as OUT parameters in procedure.
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 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 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?
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'
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies View RelatedI 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?
I have an existing Procedure that uses a ref cursor as a parameter. The procedure currently simply selects data into the ref cursor using an OPEN FOR and then exits.
I have a need to extend the processing of the procedure. This will mean retrieving extra columns in the select and also removing some of the retrieved records.
I have the constraint that the definition of the REF CURSOR (which is based on a record type as follows) cannot be modified.
TYPE t_charge
IS
RECORD (
id number,
date_from invoice_detail.from_date%TYPE,
date_to invoice_detail.to_date%TYPE,
description invoice_detail.desc_text%TYPE,
amount invoice_detail.amount%TYPE
);
TYPE t_charge_cursor IS REF CURSOR RETURN t_charge;
The procedure does this
PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
IS
OPEN bill_lines_list FOR
SELECT id, from_date, to_date, desc_text, amount
from invoice_table
where id = p_bill_id;
I am thinking instead of selecting directly into the bill_lines_list parameter I will use a local REF CURSOR to select into a COLLECTION. I can then do some work on the collection, similar to below:
PROCEDURE get_bill_lines (
p_bill_id IN NUMBER,
bill_lines_list OUT t_charge_cursor )
TYPE t_local_charge
IS
RECORD (
[code].....
This all works fine, but the question is, how do I then put the collection into the bill_lines_list REF CURSOR where the '*** HERE ***' comment is so I can send the existing REF CURSOR structure back? (Remember I cannot change any definition that is referenced externally)
In PL/Sql, I create a new collection using TYPE. Then populate it with BULK COLLECT.The next step I need is to load data from the collection to pass as WHERE condition to a ref cursor.How I can do this?Every test I did fais. It seems to me that I cannot use some kind of SELECT to retrieve all data from the collection, but I might use a cursor to read row by row. (I'm on Oracle 11gR2 DB )
View 3 Replies View RelatedWe 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?