I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?
ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");
I am simply trying to execute a couple of select statements in a single dynamic query. I also want it to return a seperate result set from each statement (something quite easy in T-SQL - but seemingly impossible in PL-SQL!)
This query is being run from C#, but i also get the same results from Sql Developer.This is what i'm trying to execute:
begin
select * from studies;
select * from studyprogressions;
end;
The error i get is "an INTO clause is expected in this SELECT statement" for each of the select lines - As you may already have worked out - I don't use Oracle very often and am starting to feel very stupid considering i have certifications in SQL Server/T-SQL!
I have searched online for a solution, but it seems that everyone with the same error is trying to do so much more.Why is nothing in Oracle simple? Give me SQL Server any day of the week.
I am having issues passing variables to a separate sqlplus script invoked by the shell script, e.g.
#!/bin/sh
DB_NAME=TEST
PWD1=PA55W0rd
echo exit | sqlplus / as sysdba @${DB_NAME}.sql ${DB_NAME} $PWD1 >> ${DB_NAME}.sql
exit 0
The script picks up the $DB_NAME variable fine, and therefore invokes the required sql script. However, when I pass the variable $PWD1 to the sql script I get an error. The script creates a database link:
create or replace procedure new.link
is
begin
execute immediate 'create database link TEST
connect to TESTSCH identified by '$PWD1'
using ''TEST''';
end;
/
exec new.link;
/
output is:
ERROR at line 1: ORA-00911: invalid character ORA-06512: at "NEW.LINK", line 4 ORA-06512: at line 1
A function returns the comments of all objects of the schema using metadata api. I used DATABASE_EXPORT as object_type for open function. which name (eg: base_object_name, base_object_schema...) should i use to get comments of single object. My code is..
1 CREATE OR REPLACE function f_depen_obj
2 return clob
3 as
4 a number;
5 b number;
[code].....
I got comments of all objects when i commented 11th,12th lines. Now i want the comments of EMP table of SCOTT schema. I got the following error ..
SQL> select f_depen_obj from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "EMP"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "SCOTT.F_DEPEN_OBJ", line 17
no rows selected...i got output (comments of single object) when i use COMMENT instead of DATABASE_EXPORT in open function(line no: 9). But i need to use DATABASE_EXPORT as a object_type. Which name is suitable for set_filter procedure instead of 'NAME'?
I am using 10g.
I want to do a select statement that will give me the name of the fields in a table. And I also want to know the field type and field size.
Can this be done?
I am new to oracle. I am creating a view, where in the inner query returns below kind data.
column1 column2
-------------------
a y
a y
b y
b n
c y
d n
I want to extract all column1 values which always has "y" in column2.
In this case, output has to be
output
------
a
c
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name
I ran one long running update statement in my sqlplus session, where it took hours together since we have millions of rows on the table.lock the system and left the office , the next day I found my system is rebooted, hope the session would have performed the long running update statement.
Now, I need to issue a commit for the session, since my system is rebooted I don't have opened session with me. so, How to issue commit to the session, which I have issued long running update statement.
I'm new to the sqlplus,
User ID Failed_timestamp
----------------------------------
userA 20100801 11:22:33
userB 20100802 11:22:33
userA 20100802 12:22:44
userA 20100803 13:34:55
userC 20100803 20:21:23
userB 20100804 16:34:56
I wanna to get the last failed time from each user. what is the select statement?
my expected result will be:
userA 20100803 13:34:55
userB 20100804 16:34:56
userC 20100803 20:21:23
I am in the process of converting my skill in oracle and this time PRO*C from Windows to Linux. I have oracle 11g R2 installed on a UBUNTU (12.04) server and have installed the instant client as described in
[URL]
This has a query which will run in SQLPLUS but fails with PRO*C
:~/Projects/proc/proctest1$ proc INAME=proctest.pc SQLCHECK=NONE ONAME=proctest.c LNAME=proctest.lis LTYPE=long
Pro*C/C++: Release 11.2.0.3.0 - Production on Mon Apr 22 21:00:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /home/neill.rutherford/instantclient_11_2/precomp/admin/pcscfg.cfg
PCC-W-02109, SQLCHECK=NONE is no longer supported.. using SYNTAX
[code]....
I am trying to import a schema backup using sqldeveloper and getting this syntax error:
ORA-00900: invalid SQL statement
Here is the import statement:
impdp sys/*****@ccpsp schemas=ccpsp_staging directory=dmpdir DUMPFILE=CCPSP_USERCCPSP_STAGINGSCHEMABACKUP_07MAY2010.dmp LOGFILE=IMPDP_CCPSPStagingSchemaImport.log;
I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
I m executing below PLSQL procedure . Its getting executed successfully but not displaying the O/P. I have set serveroutput on also.
set serveroutput on
DECLARE
lc_file_handle UTL_FILE.file_type;
lc_file_dir VARCHAR2 (100);
lc_file_name VARCHAR2 (50);
[code]...
Has 2 tables with the following columns
1.abc_y (notes,frmt)
sample data:
notes:
i live in texas and work in AIG.
2.abc_z(tags_name)
sample data:
tags_name:
live in work.We shoud look for tags_name in notes field of abc_y. If we encounter any tags_name in notes field they should be removed and inserted in frmt field Now the column format should be updated to 'i texas and AIG'.abc_y has 2 million rows and abc_z has 120 rows.wrote the code sucessfuly but cannot bulk update it, which is really needed for me.
I am havin some problm in FOR LOOP after FORALL in the folowing.
DECLARE
l_sql_strng VARCHAR2 (20000);
TYPE var_tab IS TABLE OF VARCHAR2 (20000)
INDEX BY BINARY_INTEGER;
l_text_arry var_tab;
CURSOR c1
[code]...
I made one code for understanding purpose its complied successfully, but not show result...its give me message procedure successfully completed.
Code is :
BEGIN
For emp_rec in (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
END LOOP;
END;
/
I've created one procedure with following code.
PROCEDURE MaxNo IS
A Number;
BEGIN
Select Max(Supplier_ID)+1 into A from supplier ;
END;
SQL> Select Max(Supplier_ID)+1 from supplier;
MAX(SUPPLIER_ID)+1
------------------
6
when I execute it in a SQL PLus
SQL> SET SERVEROUTPUT ON
SQL> exec MaxNo;
PL/SQL procedure successfully completed.
I have one problem. i am using client_to_db function to store the file to database table.During upload of file,System showing me message "File successfully transferred to database". but when i am downloading the file from database, its not working.
View 5 Replies View Relatedhow to know that report is generated successfully because i want to one flag only if report is generated successfully other wise no need.
View 3 Replies View RelatedI have a ORACLE 10g(10.2.0.1.0) database that I am running on windows. I have export backups batch file running every day through windows scheduler. I would like to know If there is some way that sends me an email after the backup is finished and also send me if it does not finish with error.
I have successfully done this om my SQL Server machine but don't know for ORACLE as I have not purchased it and have license
When i do run export command through command prompt, its shows below error.
Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>Exp disdeva/disa123@distesta file=D:DAI LY_BACKUPdistesta.dmp log=D:DAILY_BACKUPdistesta.log statistics=none buffer=5 000000 owner=distesta
Export: Release 10.2.0.3.0 - Production on Thu Dec 6 15:06:21 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
EXP-00010: DISTESTA is not a valid username Export terminated successfully with warnings.
C:Documents and SettingsAdministrator>
When i connect from sqlplus, it doesn't give any erro. What could be the reason?
SQL> conn disdeva/disa123@distesta
Connected.
SQL>
SQL> select greatest ( 1000,null) from dual;
GREATEST(1000,NULL)
-------------------
SQL> with t as
2 ( select 1 a from dual union all
3 select null from dual)
4 select max(a) from t;
MAX(A)
----------
1
Why greatest returns null ( as the greatest value among the list of values) as the greatest value while max returns 1 in the above case?
How to make the automatic deletion of all the archivelogs when it is applied successfully on the standby database? We have 1 primary database and 1 standby database using oracle physical dataguard.
In the primary database, we have daily backup job so that the applied archivelogs are deleted automatically, no problem
But in the standby database ( physical standby) I have to go there and delete manually because I don't issue backup on this database. Is there any solution for this problem.
I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.
-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...
PL/SQL procedure successfully completed.
running a jquery code after submit button has successfully completed, how do i integrate these 2 events ? clicking the submit button and if submits successfully execute the small jquery code based on the same submit button
View 33 Replies View RelatedI have created custom table in inventory and after register table & columns, i create event alert on it. event alert is not firing.
View 1 Replies View Relatedwhen I am trying to send report for prints then I received this error.
"
REP-57054: In-Process Job terminated: The report generated successfully but distribution to destination failed.
REP-50159:The report generated Successfully but distribution to destination failed.
REP-50151: Destination ID Currency failed with error An error occured while sending the file to the printer :HPofficejetpro7700Series- Cannot run program "COrcaleMiddlewareasinst_5Config
eportsin
wlpr":
create process error=2, The system cannot fine the file specified."
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
how to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')