I am trying to pass the parameters to query and I need to pass few parameters to query. Can I make it like like then I run it, then it come up and ask the parameter like this:
SQL> @text
para1 XXXXX
para2 XXXXX
And then the result of query will come up. I would like to put label for each parameter( likepara1).
I am setting the oracle_home,oracle_sid on windows server2008.
oracle client 10g and oracle server 11.2.0.1.0 are installed on my machine and i have created 2 database on my machine.
i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.
even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:
procedure is:
create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;
i pass parameter as given below:
begin
testproc('abc & def');
end;
if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.
begin
testproc('abc &'||'def');
end;
I am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:
substr(PBBDT,length(PBBDT)-1)
Calling <substr> with <2> parameters, but <3> are expected.
Just to let you know this new section in SQL*Plus FAQ wiki page: How to have optional parameters and default values in SQL*Plus_script.
View 1 Replies View RelatedI am trying to execute a STORE PROCEDURE from SQL*PLUS with no success:
SQL> execute PACKAGE.PROC(201011,'144792');
BEGIN PACKAGE.PROC(201011,'144792'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
In fact, when i do: desc PACKAGENAME . I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:
SQL> desc PACKAGENAME
PROCEDURE PROCEDURENAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PWEEK NUMBER IN
PCLIENT VARCHAR2 IN
CRESULTS REF CURSOR IN/OUT
After searching a bit, i try the following:
SQL> execute PACKAGE.NAME(201011,'144792','CRESULTS
'=>:C1);
SP2-0552: Bind variable "C1" not declared.
SQL>
This is a preview of the PACKAGE header:
CREATE OR REPLACE PACKAGE PACKAGENAME
AUTHID CURRENT_USER
AS
--
TYPE CurTyp_Supp IS REF CURSOR;
--
TYPE TabTyp_Supp IS TABLE OF VARCHAR2 (10 BYTE);
--
TYPE ObjTyp_Prmt IS OBJECT (p_schemaname VARCHAR, p_filename VARCHAR);
--
PROCEDURE PROC(pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp);
This what the PACKAGE BODY looks like:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
PROCEDURE PROC (pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp)
QUESTION:
HOW DO I MANAGE TO EXECUTE THIS PROCEDURE FROM SQL*PLUS
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?
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.
In my task I am trying to pass a timestamp datatype as one of the input parameter to a procedure in the package.But I am not sure how to give data for it while executing and testing it from anonymous block.
CREATE OR REPLACE
PACKAGE body ac
IS
PROCEDURE ac_ex_wbdb_keycats(
In_Sale_Location_Id IN NUMBER,
In_Start_Datatime IN TIMESTAMP,
In_Stop_Datatime IN TIMESTAMP,
v_refcur OUT sys_refcursor)
[code]....
I'm migrating forms 6i to forms 10g. I have a mune which has parameters ,which are working in forms 6i. But when i try to comile in forms 10g its poping up error's.
View 3 Replies View RelatedI had a requirement to execute a long running sql query. But the sql query had some parameters to be passed in and at some places i need to press "Enter" . I want to use nohup command and run the sql script using shell script concept. How can i pass the parameters and run the nohup command.
[URL]
I want to keep the sql script in a shell script and run it through nohup command.
how can i keep the above sql in a shell script and run through nohup command
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.")
can we use oracle client version to create a database in that server.
View 3 Replies View RelatedI 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.
Can i install Oracle 10g Client 32 bit on our Windows Server 2008 R2 64 bit?
View 1 Replies View RelatedI 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 RelatedI need generate a script like this.
spool loadvalues.sql
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM TABLE_NAME;
spool off;
I would like to substitute TABLE_NAME with each value from the following query;
select table_name from user_tables
where table_name like 'SALES%'
so there are about 45 SALES tables ex: SALES_IDAHO,SALES_MICHIGAN etc.so my spool file(loadvalues.sql) should look like this ex:
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_IDAHO;
sales_michingan should have script like
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_MICHIGAN;
I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed successfully, but cannot insert any data into history table.
See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:
PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
BEGIN
INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
SELECT *
FROM TRC_XREF_CONT_EMAIL_MSGS
[code].......
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].......
Here is the query I want to be able to run...
select * from NonExistingTableName
I want the query above to be ignored and return no results (rather than generating an error) when the tablename does not exist.
is it possible to write select query o/p in html tag?
View 6 Replies View RelatedI need to say I am an absolute NOOB when it comes to SQL.I need a script to run in TOAD that will reference a CSV file saved onto my local hard drive. I'll try and describe exactly what I need to do.
The current script which I use via TOAD on our companies READ ONLY database is this:
SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN ('1230001', '1230002', '1230003')
This will return a result for each number that exists within the table.number table along with the status of each number i.e. active or inactive. A very basic query.
What I need to be able to do is run that query but instead of having to copy each number into TOAD manually, I need TOAD to check a .csv file of said numbers and then return the results.So I imagine the query would look something like:
SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN (check
is it possible to write sql query o/p in HTML page?
View 3 Replies View Relatedi do have a set of monthly process, where i need to run query one after the other. Some query will take one hours time to complete but some will take 10 hours or more to execute. My problem is that we have wait or machine shouldn't hang or log off during that time ...
I am executing the query in TOAD and windows SQL*Plus. Query can run Parallel at a time.
Is the there anyway to run the query in the background like what we do in the unix nohup command in toad?
I have a problem here.Normally, we use &p_where inside a sql script in condition sectione.g :
select name from member where name like 'a%' &p_where order by name;
may i use this kind of parameter in table section?e.g :
select name from &p_table where name like 'a%' and status = 'a' order by name;
the reason i need to do is there are 2 different server. but i need retrieve same info.server ABC have table A but don't have table B and server DEF have table B but don't have table A.
Is there any other method to solve this problem?
How to pass null values in OracleCommand as a parameters
i am doing like this
((OracleCommand)cmdMySql).Parameters.Add(":1" ,Varchar2).Value = DBNull.Value;
Its giving error when dataadapter is going to fill.
I want to execute a query which automatically saves the result of a set of queries in a text file and have to send that as an attachment thru a mail (using UTL_MAIL or UTL_SMTP) thru a stored procedure...saving the result in a text file in Toad.
View 2 Replies View RelatedWhen i run the below mentioned query(similar query)on TOAD. It will run without any errors and i can see "PL/SQL Procedure successfully completed" in the task bar.....
However i am unable to see the output for the query.
print the the query output on TOAD ....
**** Sample Query Starts *****
DECLARE
i PLS_INTEGER;
BEGIN
SELECT NVL(i, 93)
INTO i
FROM DUAL;
--print i;
--dbms_output.put_line('i1: ' || i);
END;
**** Sample Query Ends*****
**** Actual Query Starts *****
-----------------------------------------------------------------
DECLARE
UnxDate number(6);
MyResult number(6);
Todaysdate date := TO_DATE('17-01-2012 00:00:00','dd-mm-yyyy hh24:mi:ss');
BEGIN
SELECT ROUND (Todaysdate - TO_DATE('01-jan-1970','dd-mon-yyyy') ) INTO UnxDate FROM dual;
[code]......
I'm running this query on sql developer trying to export large file but its not executing.
set head off
spool c:myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name where rownum<'10';
spool off
set head on
Error:- line 1: SQLPLUS Command Skipped: set head on
The date is different for same query in Toad and Sql Developer.
Below is the query
In SQL Developer
SELECT start_date, length(start_date) from my_table where p_id = 1;
09-MAY-5249 9
In Toad
SELECT start_date, length(start_date) from my_table where p_id = 1;
blank 9
Did some queries below for research.
SELECT start_date, DUMP(start_date, 1016)
from my_table
where p_id = 1;
09-MAY-5249Typ=12 Len=7: 30,37,d5,a,0,a2,d5
SELECT start_date, DUMP(start_date, 1016)
from my_table
where p_id = 2;
01-JAN-2013Typ=12 Len=7: 78,71,1,1,1,1,1
Why is same query returning two different results in two tools.