Client Tools :: Providing Bind Variables As Values In Insert Statement?
Aug 23, 2011
I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/
And it gave the error : Quote:Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"
The same code when executed in iSqlPlus gave no error.
View 13 Replies
ADVERTISEMENT
Feb 22, 2010
Having trouble with my variables in this staement.
SELECT WP_USERS.F_NAME || ', ' || WP_USERS.L_NAME AS NAME, WP_PROJECTS.TITLE, WP_PROJECTS.CLIENT_MANAGER,
WP_PROJECTS.PERSON_DAYS
FROM WP_PROJECTS, WP_PROJECTS_USERS, WP_USERS
WHERE WP_PROJECTS.PROJECT_ID = WP_PROJECTS_USERS.PROJECT_ID AND WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND
WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND (WP_PROJECTS.SITE = "@SITE") AND (WP_PROJECTS."SECTION" = '@SECTION')
View 7 Replies
View Related
Feb 22, 2010
How I go about writing a query that doesn't see the '&' symbol as a substitution character. The problem I have is:
select replace
( fnd_profile.value( 'XXBPO_URL')
, '&DOCID'
, ai_dfv.bpo__document_link
) document_link
from ap_invoices_all ai
, ap_invoices_all_dfv ai_dfv
where 1=1
-- where clause
The problem above is that instead of replacing the characters &DOCID with the required value, it is prompting for input.
View 4 Replies
View Related
May 25, 2011
An application uses one user as a container of objects(this user is stripped of ALL privileges), and other users that can access objects in this unique schema depending on their privileges.
There can be multiple installations of the application in one database, therefore many such schemes. An environment variable APP_UNIQUE_SCHEMA points to a current unique schema.
So instead of typing
select * from my_unique_schema.my_table I want to set a current schema for sqlplus session in glogin.sql.
It would have been nice if I could have entered in glogon.sql the following line
alter session set current_schema=$APP_UNIQUE_SCHEMA;
BUT it does not work. sqlplus does not resolve the reference to an environment variable. I cannot figure out how to get the value of an environment variable in sqlplus.
View 2 Replies
View Related
Apr 27, 2012
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
View 8 Replies
View Related
Oct 28, 2013
I wish to make this simple statement with Toad GUI
INSERT INTO EXCLUDE_xxx
VALUES ('xxx',
'xxx',
'xxx',
'xxx',
SYSDATE);
Insert record is greyed out. How to insert new rows with Toad (click click)?
View 7 Replies
View Related
Dec 22, 2011
i have some confusion with bind and host variable.
View 4 Replies
View Related
Sep 2, 2011
I have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>>
declare
v_sal1 number(7,2) := 60000;
v_comm number(7,2) : v_sal1 * 0.20;
v_message varchar2(2000) := 'eligible for commission';
begin
[code]........
View 8 Replies
View Related
Nov 20, 2012
Create and invoke the GET_JOB function to return a job title.
a.Create and compile a function called GET_JOB to return a job title.
b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION:
CREATE OR REPLACE
FUNCTION GET_JOB(
p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35)
set serveroutput on
DECLARE
P_JOBID VARCHAR2(10);
v_jobtitle VARCHAR2(200);
[code]...
View 5 Replies
View Related
Jun 3, 2010
I need to create a SQL insert statement with mutipleValues and an select statement I am trying this it works -
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
values
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,'TEST1', 'NITA')
This statement does not work (with or without keyword) Is there any alternate syntax
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,(select last_name, first_name from uwa_crew_ids where guid = '8795EAAFBE694F8EE0440003BA2AEC90' ))
View 3 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
Mar 10, 2010
How many maximum number of bind variables,can we use in Execute Immediate.
View 2 Replies
View Related
Sep 8, 2011
CODE
Select
Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0)
From FnTrantt A
Where A.GrpCode=:1 And
A.CmpCode=:2 And';
A.DiviCode=:3 And';
A.SubDiVCd=:4 And';
A.FinYear>=''0001'' And';
A.VchDate Between :5 And :6' And
A.GlCode In :7;
[code]....
In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,
View 3 Replies
View Related
Aug 29, 2013
i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem:
select egp.bsn,egp.klantnummer as "Persoonsnummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or :b2 is null)and rownum < 51 egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics. when run with b1:=928818 and b2:=928818 (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2. if the query is changed to select egp.bsn,egp.klantnummer as "Persoons nummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or 928818 is null)
and rownum < 51the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.
View 23 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 21, 2009
can i have some real time code piece for bind variables steps and obndrv(...)
View 1 Replies
View Related
Feb 22, 2010
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
After running above block it is giving error : ORA-01008: not all variables bound.
View 10 Replies
View Related
Jul 1, 2013
We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --
Just bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and mmt.ORGANIZATION_ID = :p2and mmt.TRANSACTION_REFERENCE = :p3--and mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and
[code]...
View 3 Replies
View Related
Oct 4, 2012
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]....
View 1 Replies
View Related
Mar 10, 2010
I have this following select statement
Select name
from names_list
where name like ('A%')
The output returns a list of names beginning with 'A'...What I want is for the output to repeat that list of names multiple times e.g. if the only two names returned are 'Andrews' and 'Apple', I want to have the output show
Andrews
Andrews
Andrews
Apple
Apple
Apple
View 8 Replies
View Related
Mar 29, 2012
I'm trying to insert an XML into an XMLType with the following value:
insert into Table1(
code, xmlColumn) VALUES
(1, '<script> > </script>');
the question is I need to have > in the text, but Oracle insists in putting it like <script> > </script>.
What do I have to do to solve this?
View 15 Replies
View Related
May 16, 2010
Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.
The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:
SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....
and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....
View 7 Replies
View Related
Jun 10, 2010
I am executing the SQL statement in a unix korn shell script. and the output of this SQL is stored in a file.
`sqlplus -S $OraUserID@$OraInstance/$OraUserPW <<endofsql >result
set heading off;
SELECT * from emp;
exit;
endofsql`
cat result
this code working fine and the output i see in file is as below.
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 9 23:09:33 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release <number> -
and Real Application Testing options
SQL> SQL>
30-APR-10
01-MAY-10
02-MAY-10
03-MAY-10
04-MAY-10
05-MAY-10
06-MAY-10
07-MAY-10
8 rows selected.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release and Real Application Testing options
How can to get only the SQL output in the file but not all the other oracle connecting messages.
View 1 Replies
View Related
Jan 3, 2011
I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
View 2 Replies
View Related
Jun 22, 2012
I want to insert data into three table
my first table is
firstemp
having columns
1.EMPID
2.NAME
3.STATUS
second table is
secimp
have same columns as of first table
third table is
thirdimp
columns are same as of first andsecond
I want to insert in Firsttable if EMPid=EMP_1h and insert in second table if empid=EMP_2u and insert into third table if empid=emp_3p
View 1 Replies
View Related
Sep 7, 2011
1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"
2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?
View 1 Replies
View Related
Dec 13, 2012
I have problem: I have 1 sql server already setup SQL Server 2012 Express and 1 Oracle Database server 10g. Now i want to insert data from SQL server to Oracle database through link server.
Some step i already make:
1. Setup oracle database 10g and configure listener (Finished)
2. Setup Sql server 2012 express on Windows 7 (Finished)
3. Setup ODTwithODAC1020221 on PC already setup SQL server (Finished)
4. Make Linkserver from SQL server to Oracle database (Finished), and can select data from Oracle Database on SQL server through Linkserver.
However when i insert data from SQl server to Oracle Server not success.
select * from OPENQUERY (QVHKTEST, 'SELECT * FROM QVSYSTEM')
After i run above script, result is OK
With: "QVHKTEST" is alias of Link server from SQL to Oracle server
: "QVSYSTEM" is a table on Oracle database, that table we want to get through Linkserver on SQL server
Both Server Database contain same name table is "QVSYSTEM"
-----------
INSERT OPENQUERY (QVHKTEST, 'SELECT BODY_NO,
MERCHANDISE,
MODEL_NAME,
LINE_NAME,
DATE_ENTRY
FROM QVSYSTEM')
values('VNF4619829','3227B002CA','L1068','01','2012/09/26 03:18:11');
If i run script above directly in SQL Window query can insert OK. This is code in trigger at table on SQL server:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author Name: Phuong Do Minh >
-- Create date: <Create Date: 10/12/2012>
-- Description:<Description: After data insert into table qvsystem on SQL server
-- This trigger will fire and insert that data into table qvsystem
[code].......
But when i make trigger after insert on table in SQL Server to insert data From SQL server to Oracle server, however not success and SQL server raise error below:
OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure Insert_data, Line 16
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" was unable to begin a distributed transaction.
I don't know how to configure them.
View 8 Replies
View Related
Jan 4, 2011
How can i insert 50 million records at a time
View 1 Replies
View Related
Apr 6, 2011
Using pl/sql block , i tried to have a certain input values from Sql prompt but it doesn't work when i invoke it.
Here the simplest
declare
a number(4);
begin
for i in 1..10 loop
a := &a;
end loop;
end;
/
View 9 Replies
View Related
Mar 7, 2011
I would like to store my sql query output into text file.Like for example:
select name from emp where emp_id=101;
Here output should be in text file as
swapna.
I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.
View 1 Replies
View Related
