I have cretaed an external table to read data to a table from a csv file placed in "c:oracleoradataspmap1utl". The csv file name is say "pildata.csv"
I have cretaed a package to read data from the exteranl table and to insert it into a table.
INSERT INTO M_PILEINT SELECT
A.AREA AS "AREA",
A.SUB_FAC_DESC AS "SUB_FAC_DESC",
A.SCOPE_DETAIL AS "SCOPE_DETAIL",
A.MTO_ISSUE_DATE AS "MTO_ISSUE_DATE",
A.MTO_TAKE_BY AS "MTO_TAKE_BY",
A.COMMODITY_CODE AS "SECTION"
A.PILE_NAME AS "PILE_NAME"
FROM M_EXE_PILE A
(where M_EXE_PILE A is the external table which is reading from pildata.csv)
The package runs good and data is populated to M_PILEINT .Is there a way , I can rename the csv file (say to pildata_logxxxx.csv.. something like that) from within the package.Whenever the package is run , it will copy the data from exteranl table and renames the csv file to something else..?
I want to use UTL_FILE package to create OS file. How to resolve this error. Oracle11g under XP.
SQL> create directory my_dir as 'c: emp';
Directory created.
1 create or replace procedure test_1(md in varchar2) 2 is 3 file utl_file.file_type; 4 begin 5 file := utl_file.fopen(md,'abc.log','w'); 6 utl_file.put_line(file,'EMPLOYE REPORT'); 7 utl_file.fclose(file); 8* end; SQL> /
Procedure created.
SQL> execute test_1('MY_DIR'); BEGIN test_1('MY_DIR'); END;
RROR at line 1: RA-06510: PL/SQL: unhandled user-defined exception RA-06512: at "SYS.UTL_FILE", line 98 RA-06512: at "SYS.UTL_FILE", line 157 RA-06512: at "SCOTT.TEST_1", line 5 RA-06512: at line 1
I need to create a bat file which include a query to run a package. I use plsql developer to develop the package. its username,password and database is user,pswd,db1 respectively. the query to run the package is "SELECT
COLUMN1 AS "LAST NAME", COLUMN2 AS "FIRST NAME", COLUMN3 AS "LOCATION"
FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010')) "
I'm trying to load xml file into table using dbms_xslprocessor.read2clob package, it loads for small file, but it throws READ ERROR for big file. I have given READ, WRITE permission on directory and also DBA role to user. Also I have attached file (change file extension to xml)
check below given code.
CREATE TABLE loadxmlfile (xmldata CLOB); CREATE TABLE loadxml (id NUMBER, xmldata XMLTYPE); create or replace PROCEDURE load_xmlfile
[Code]...
Note: Reason I'm doing is to remove NON BREAKING SPACE (NBS) character from xml file
I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
I need to rescue some valors from Excel, and then close the excel file without saving changes and without messages.
I am using THe DDE package and when using
DDE.EXECUTE(convid, '[Save()]', 10000);
there is no problem, but the changes are saved. I have tried '[Exit()]' and '[Close()]' but always have error message ORA-106555. Is there any way of doing this without errors?
i have a problem in renaming the column of a table and it throws a strange error-"ORA-14155: missing PARTITION or SUBPARTITION keyword " ,but i have used correct syntax which is that " alter table temp rename id to num; " where is the problem.
I would like to try to rename a newly created dB. Referring to the topic posted on your website[URL]... had some clarification before executing the dbrename.sql mentioned there:
After modifying the the init.ora and tnsnames.ora, do I have to rename all OLD, existing directory path (for controlfiles, datafiles, etc.) with the new-- meaning I don't have to create another directory for it's 'NEW' name?
I've 2 schemas namely department, hrNow department schema is having one table EMP . And a view is created on that table with the name R_EMPSchema Name :
departmentTable Name : EMPView Name : R_EMPGRANT SELECT ON R_EMP TO HR ; In HR schema a synonym has been created on view R_EMP with the name R_EMP .CREATE OR REPLACE SYNONYM hr.R_EMP for department.R_EMPSchema Name : hr Synonym Name: R_EMP
Now I want to rename the view name R_EMP to V_EMP from HR schema only So, I tried the folloiwng syntax in HR schema (All the operations should be done from this schema only )[code] RENAME department.R_EMP to department.V_EMP ;
Error: ORA-01765: specifying table's owner name is not allowed[/code] How to change the syntax to make the statement work from HR schema .
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId) from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%' group by A.OrigRef, A.DisplayName, A.ExtCode order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?
LSNRCTL> status Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production Start Date 09-NOV-2012 19:55:06 Uptime 0 days 0 hr. 24 min. 26 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:oracleproduct11.2.0dbhome_1 etworkadminlistener.ora Listener Log File c:oraclediag nslsnrTESTQ51listeneralertlog.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NDCORCKSDQ51.global.loc)(PORT=1521))) Services Summary... Service "testdb" has 1 instance(s). Instance "ksdtest", status READY, has 1 handler(s) for this service... Service "testdbXDB" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... The command completed successfully
i am trying to rename the logfile "c:oraclediag nslsnrTESTQ51listeneralertlog.xml" to "c:oraclediag nslsnrTESTQ51listeneralert estdb.log"
so far i tried
LSNRCTL> set log_file testdb.log Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) TNS-01251: Cannot set trace/log directory under ADR LSNRCTL>
When a user is renamed in Active Directory, they can no longer connect to the Oracle DB thru OS authentication. There is no OID/DIP integration.
sqlnet.ora SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) NAMES.DEFAULT_DOMAIN = cal.com.br create user "CALRENATOH" IDENTIFIED EXTERNALLY GRANT CREATE SESSION TO "CALRENATOH" AD User CALRENATOH can connect to DB as 'sqlplus /'
But after renaming AD User CALRENATOH to CALRENATOH1 and dropping DB user CALRENATOH and creating DB user CALRENATOH1 drop user "CALRENATOH"; create user "CALRENATOH1" IDENTIFIED EXTERNALLY;
Now OS authentication 'sqlplus /' fails 'ORA-01017: invalid username/password; logon denied'..Once I recreate the DB user with old AD user name 'CALRENATOH', OS authentication succeeds. create user "CALRENATOH" IDENTIFIED EXTERNALLY;
C:Windowssystem32>set username USERNAME=RENATOH1 C:Windowssystem32>sqlplus /@rmlab001 SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 3 15:16:46 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning and OLAP options
Why the Database is still looking for old AD user name? Does Oracle cache information about OS authenticated users?
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR -------- ---------------------------------------------------------------- . ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.
The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.
what type of PL/SQL output we should use here?
Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.
I ran the dbms_rectifier_diff.differences package successfully just to know the differences after it i droppped the all tables from the schema but now I have some funny table names exist in my schema like BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0. I want to remove it but dont know how, when I run the following command on sql prompt i get error message says 'sql command not properly ended'.
I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.
step 1 : procedures are inserting data using some joins into a temp table. step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).
as i have duplicate data on temp table i am not able to update data on target table.
I have created a PL/SQL package and complied it and I got a meaasge with no errors.But I dont know how do I run the package to check whether the data has been populated in tables or not.I want to know do I need to write any code for execution to see the output.If I need to write how can I do it in TOAD.
