Client Tools :: Substitution Variables

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


ADVERTISEMENT

Client Tools :: Inserting Data With Substitution Variable?

May 24, 2012

I want to insert the value with '&'. But this is treated as substitution variable by oracle.So how can it is possible to do like this

INSERT INTO DEPT (DEPTNO, LOC, DNAME)
VALUES (50, '&NEW YORK', 'SALES');

View 3 Replies View Related

Client Tools :: Escape / Quote Ampersand (&) Because No Variable Substitution

Apr 5, 2011

I wish to replace the string "Benutzername&Kontakt" with nothing.

SELECT REPLACE (role_owner, 'Benutzername&Kontakt', '')
FROM JC_NAME_62
WHERE jc_name LIKE 'SAP_R4_BIN5\_%' ESCAPE ''

But my Toad prompts me always to fill in the variable. I don't wish to do that, I wish to escape the &

View 5 Replies View Related

SQL & PL/SQL :: How To Use Substitution Variables In FROM Phrase

May 17, 2010

HOW i can use the Substitution Variables in select statement in From phrase

we need execute select statement in different users in run time.

View 15 Replies View Related

Client Tools :: SQL Select With Variables

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

Client Tools :: How To Use Unix Environment Variables

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

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

Client Tools :: Tools For Load Testing On Oracle - J2EE Application?

Jan 5, 2012

which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?

Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?

Does it come with Oracle Database license or we have to buy it separately?

View 1 Replies View Related

Client Tools :: Basic Tools For Oracle Version Control

Aug 26, 2011

I'm looking for 3 simple things.

1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.

2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).

3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.

View 9 Replies View Related

Client Tools :: Cannot Connect Using JDBC Oracle Thin Client With (New Method)?

Feb 21, 2013

The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.

# Host name is vmwwin7634.na.SAS.com
# SID is sting2
# SERVICE_NAME is sting2.na.sas.com

PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2
THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com

I am using SQuirreL Client version 3.4.0

# Here is the tnsnames.ora entry on the server.
STING2 =
(DESCRIPTION =

[code]...

# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.

# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)

# "New Method"
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works)
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors)
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")

View 10 Replies View Related

Client Tools :: Can Use Oracle Client Version To Create A Database In That Server

Jan 13, 2011

can we use oracle client version to create a database in that server.

View 3 Replies View Related

Client Tools :: How To Connect From Client To Oracle Database In Unix

Dec 11, 2012

I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.

So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following

export TNS_ADMIN=/usr/lib/oracle/network/admin
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/bin
export ORACLE_SID=VFIODSD1

when I try

# sqlplus
username :xxxxxxx
password : xxxxxxx

ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.

View 1 Replies View Related

Client Tools :: Oracle Client 32 Bit On Windows Server 2008 R2 64 Bit

Jul 20, 2012

Can i install Oracle 10g Client 32 bit on our Windows Server 2008 R2 64 bit?

View 1 Replies View Related

Client Tools :: Install Oracle To Get Latest SQL Plus Working On Client PC

Jul 11, 2012

I need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.

View 4 Replies View Related

Client Tools :: BR*Tools Appearance Not Complete

Jan 16, 2013

I want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.

View 5 Replies View Related

Client Tools :: Command Line Tools

Jan 22, 2011

I usually work in oracle using the different editors like toad, plsql developer etc. I felt command line is good utility because of various supporting points. I am not sure what kind of command line tools is available/used. I am using pretty straight command line
(run->cmd, then enter username and password).

The best command line tools available. What is generally used command line tools.

View 1 Replies View Related

Client Tools :: ODBC Instant Client Oracle 10g

Jun 28, 2010

The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi. for Unix AIX 5

I install the ODBC Driver Manager, Basic Instant Client, ODBC Instant Client packages (libsqora.so, odbc_update_ini.sh).

The error:

[oracle@d4 oracle] $ isql OracleODBC-10g user passtemp -v
[01000][unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_10_2/libsqora.so' : file not found
[ISQL]ERROR: Could not SQLConnect

Configuration

odbcinst.ini
[Oracle10gODBCdriver]
Description = Oracle ODBC driver for Oracle 10g
Driver = /u01/app/oracle/product/10.2.0/db_1/lib/libsqora.so
Setup =
FileUsage =
CPTimeout =
CPReuse =

The documentation explic with exists the file libsqora.so.10.1 but not exists in the ODBC Instant Client packages.

I applied ln -s libsqora.so.10.1 libsqora.so but the connection not function

odbc.ini
[oradns]
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
CloseCursor=T
[code].......

View 1 Replies View Related

Client Tools :: How To Connect To Oracle 7 Using Client

Dec 15, 2011

I have Oracle 7 database on Server1. I need to connect this using Oracle client from windows XP.

I have downloaded Oracle client 3.0 and when I am trying to connect it saying "Unable to connect: Unsupported Oracle version".

View 8 Replies View Related

Client Tools :: Identify Oracle Client

Oct 3, 2011

I would like to know if there is some specific file who identifies the installation of Oracle Client in some machine.

View 4 Replies View Related

Client Tools :: How Can Isqlplus Available For Windows Client

Sep 23, 2010

I have an Oracle server on CentOS 5.4, I can visit isqlplus on server, but I can't visit isqlplus on a windows client. ping works between server and client. I remember client doesn't need to install software via isqlplus, but I am not pretty sure.

View 2 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

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

SQL & PL/SQL :: Using $ Substitution In Select Statements?

Oct 11, 2011

Is it possible to have $ substitutions in Select statements ?

For example

Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00

For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?

View 2 Replies View Related

SQL & PL/SQL :: Substitution Variable In Sqlplus

Jul 20, 2011

Here is a simple sqlplus line command with & substitution run time variable.

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;
Enter value for sal:

Now as you see, oracle asks to Enter Value for substitution variable in line just below the SQL>. I wonder if there is one or more line could be left there so to improve the readability. So it should look like:

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;

Enter value for sal:

View 2 Replies View Related

SQL & PL/SQL :: Variable Substitution In Using Clause

May 5, 2011

Below is the code, I am trying.

I have all the bind variables in "using_stmt" variable. How can i make this work. Currently I am getting error

ORA-01008: not all variables bound
--------------------------------------
DECLARE
SQL_STMT varchar2(200);
using_stmt varchar2(200);
dept_id number := 50;
dept_name varchar2(14) := 'PERSONNEL';
loc varchar2(13) := 'DALLAS';
rec_count number;
[code]......

View 13 Replies View Related

SQL & PL/SQL :: Substitution Variable With And Without Value Assigned

Jun 14, 2011

I have a table emp with columns [id,name,job_id,dept_id]. now I make a simple select query.

select id,name,job_id,dept_id
from emp
where id = &a
and name = &b
and job_id =&c
and dept_id =&d;

now in this case it will ask the values for all the columns and will according print result.

Now my questions is what will happen if I just pass the value for only id and name not for other two...?

View 35 Replies View Related

SQL & PL/SQL :: Block Using Substitution Variable

Oct 3, 2012

Declare a variable called sal to store the salary of an employee. In the executable part of the program, do the following:

a.Store an employee name in a substitution variable.
b.Store his or her salary in a variable.
c.If the salary is less than 5,000, give the employee a 10% raise and display the message "<Employee Name> salary updated" in the window.
d.If the salary is more Last Name Message than or equal to 3,000, print the employee's salary in the format, "<Employee Name> earns ............. No raise is given."

So the result should ask for the last name

- Himuro and message should show [Himuro's salary updated]
- Greenberg [Greenberg earns 12000. No raise is given.]
- Patel [Patel's salary updated]

Here is my PL/SQL

set serveroutput on

DECLARE
v_emp_sal employees.salary%type;
v_emp_last_name employees.last_name%type;
BEGIN
v_emp_last_name :=initcap('&emplastname');
SELECT salary
INTO v_emp_sal
[code]......

question:
How can I get the apostrophe in the dbms_output.put_line. Attached is the create employees table code.

View 4 Replies View Related

Client Tools :: How To Add Value From Sqlplus

May 14, 2013

I want to add a value from sqlplus.

My scenario is like that i want to add the following value

select count(*) x from tab1

i need to connect several database, and each time i need to add with previous one. like that

connect a/a@db1;
select count(*) x from tab1;

then when i connect another db

connect b/b@db2;
select count(*) x from tab1;

x will add with previous one.

View 6 Replies View Related

Client Tools :: How To Connect To Sql Plus

Nov 18, 2010

I have to connect to sql*plus to get explain plan.When I conect in Toad, the credentials are as:-
User/schema :- TestDb
Password :- TestDb

Under direct tab:-
Host :- 172.17.252.96 Port :- 1521

Service Name :- xe

I am not able to connect to sql*plus using:-

Username :- TestDb
Password :- TestDb
Host String :- 172.17.252.96

Not sure how to login when I have IP address

I also tried login as:-

Username :- TestDb
Password :- TestDb
Host String :- xe

View 5 Replies View Related

Client Tools :: How To Use Isql*plus

Jan 30, 2010

I want to work on isql*plus but i don't know how?!!?. whether i have to install it anywhere or what is the procedure to use it.?

View 2 Replies View Related

Client Tools :: Unable To Run SQL *Plus?

Oct 5, 2012

I'm not able to run SQL *Plus on my machine. It is giving me below error.

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Setting of PATH environment variable is as below. If I remove below highlighted part I'm able to run SQL * Plus. I cannot remove this because it is required to run my application.

C:oracleproduct10.2.0client_1in;C:OSIBANKDLLBIN;C:OSIBANKBat_Exe;C:OSIBANKDLL;c:Program Files (x86)InteliCLS Client;c:Program FilesInteliCLS Client;C:Program Files (x86)AMD APPinx86_64;C:Program Files (x86)AMD APPinx86;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;%SYSTEMROOT%System32WindowsPowerShellv1.0;c:Program Files (x86)Microsoft SQL Server90Toolsinn;%TFSPowerToolDir%;%BPADir%;C:Program FilesIntelIntel(R) Management Engine ComponentsDAL;C:Program FilesIntelIntel(R) Management Engine ComponentsIPT;C:Program Files (x86)IntelIntel(R) Management Engine ComponentsDAL;C:Program Files (x86)IntelIntel(R) Management Engine ComponentsIPT;C:Program Files (x86)IntelOpenCL SDK2.0inx86;C:Program Files (x86)IntelOpenCL SDK2.0inx64

View 5 Replies View Related







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