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


ADVERTISEMENT

SQL & PL/SQL :: Where Clause - Literal Does Not Match Format String

Feb 28, 2011

In query I have WHERE clause like this:

WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') >
TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')

I have data like this:

PEROFOPFROMAIRCRAFTSTD

29/03/20102150
NULL NULL
NULL NULL
30/03/20102150

When I execute the query it always gives me the error "literal does not match format string".

View 7 Replies View Related

SQL & PL/SQL :: ORA-01861 - Literal Does Not Match Format String

Dec 6, 2012

I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,

create or replace function ssndate(p_date in varchar2 default '03-01-18',
p_time in varchar2)
return number
$if dbms_db_version.ver_le_10 $then
deterministic
$elsif dbms_db_version.ver_le_11 $then
result_cache
$end
as
begin
return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss')
- to_date('01-01-90','dd-mm-yy')) * (86400/2400));
end ssndate;
/

The function ssndate compiled successfully.The next step I took was to create a view through the following query,

create or replace view WEBLOG_VIEWS
as
select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT",
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK
from WEBLOG;

This was successful as well. The problem is in the next step where I try to do data grouping.

create table FINAL_WEBLOG as
select SESSION_DT, C_IP, CS_USER_AGENT,
listagg(WEB_LINK, ' ')
within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
from WEBLOG_VIEWS
group by C_IP, CS_USER_AGENT, SESSION_DT
order by SESSION_DT
[code]....

the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.

View 6 Replies View Related

SQL & PL/SQL :: Select Length - String Literal Too Long

Jun 21, 2011

If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.

select length('string with more than 4000 char) from dual;

View 10 Replies View Related

PL/SQL :: ORA-01861 / Literal Does Not Match Format String

Sep 3, 2012

I've created a table, i have to insert data into it. Herwith Create table statement:

CREATE TABLE IOSTAT_MAC (
IS_DATE DATE,
IS_RS NUMBER(4,2),
IS_WS NUMBER(2,2),
IS_KRS NUMBER(5,2),
IS_KWS NUMBER(5,2),

[code]....

herewith data I have to insert

insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');

Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.

View 4 Replies View Related

SQL & PL/SQL :: Insert Records Of One Table Into Another - Literal Does Not Match Format String

Sep 30, 2010

I'm trying to insert records of one table into another using the insert into table with select logic.

I'm trying to convert a two character value using CASE statement:

CASE REC_TYPE
WHEN '00' THEN to_number('0')
ELSE to_number('1')
END "REC_TYPE"

The target field is defined as number(1,0) and the source field is varchar2(2).

I keep getting an ORA-01861 literal does not match format string error.

View 10 Replies View Related

UPDATE Date / Literal Doesn't Match Format String

Nov 29, 2007

When I try to update a date column with this:

 UPDATE
        event_request
    SET
        REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi')
    WHERE
        eventID=123 

Oracle returns this error:

 ERROR at line 4:ORA-01861: literal does not match format string 

where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:

$date = '2007-12-25 08:35'; INSERT INTO (REQ_EVENT_DATE) VALUES (to_date('$date', 'yyyy-mm-dd hh24:mi'));

So - what is wrong with the UPDATE query?

View 1 Replies View Related

Precompilers, OCI & OCCI :: Error Implicit Conversion Of String Literal To (char) Is Deprecated?

Jan 30, 2011

i write a select statement in proc that contains 44 columns.

when i precompile it. it is showing the error: implicit conversion of string literal to "char *" is deprecated.when i compile the same select with 40 columns it is not showing any error.

but for more than 40 columns (41-44) it is showing the above error.

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

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

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

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

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

SQL & PL/SQL :: Print Message Just Before Asking For Input To Bind Variable?

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

Forms :: Error During Compilation - Bad Bind Variable

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

PL/SQL :: Open Ref Cursor Using Collection As Bind Variable

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

PL/SQL :: How To Use Bind Variable In The Query To Avoid Hard Parsing

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

Performance Tuning :: Avoiding Bind Variable From Sql In Java Code?

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

Performance Tuning :: Difficulty In Using Bind Variable To Check Explain Plan

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

How To Concatenate A String With Variable

Mar 5, 2008

I am trying to replace the string_name in the following execute immediate statement with a concatenated

string_||v_variable.

execute immediate
'select
table_name
from
user_tables
where table_name = :a'
into v_table
using 'string_name';

I tried different declaration and concatenations but nothing worked so far.

View 2 Replies View Related

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

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

SQL & PL/SQL :: Read Date From String Variable

Jul 29, 2013

In our mainframe file we have date coming as format June 1,2013 . How to read and convert it in 01/06/2013 format to load in oracle table?

View 11 Replies View Related







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