SQL & PL/SQL :: Procedure To Accept Schema And Produce CSV File For Table
Mar 23, 2012
The Utility should have a procedure that will be able to accept a schema and table name and produce CSV file for that table.
This is what i have so far:
CREATE OR REPLACE Procedure print_table (schema_name varchar2, tab_name varchar2) IS
BEGIN--begin procedure
DECLARE
vpath varchar2(100) := 'C:UsersUserDocumentsDocsDBAProject';
[code].....
It works outside of the procedure but the nature of the question requires a stored procedure.
View 26 Replies
ADVERTISEMENT
Apr 8, 2012
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2)
is
begin
EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
'PASSWORD EXPIRE';
end addUsers;
/
[code].....
View 21 Replies
View Related
Oct 25, 2011
i was asked to built a report file that will run from oracle form using web. show_document() built-in object to produce a data extract. the objective is to built a dynamic SQL and use that to a cursor.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
[code]....
I found out that the procedure can only accepts a single column. When I run this code it works fine because I am using a single column on the SQL statement.
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
open rec_array For 'select p.muni from chips.project p, chips.proj_type pt where p.type = pt.id';
pass_ref_cur(rec_array);
CLOSE rec_array;
END;
The objective is to use a multiple column. How can I revised the procedure pass_ref_cur so that it can accept multiple columns?
View 17 Replies
View Related
Sep 20, 2013
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
View 3 Replies
View Related
Dec 17, 2010
I have got 2 users as user1 and user2.I have used the following statements from user 'user1':
create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;
In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.
create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;
[code]...
why I cannot access that table from procedure?
View 8 Replies
View Related
Oct 11, 2013
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....
View 7 Replies
View Related
May 6, 2012
is it possible to import only one or two table from a schema export file or from a full database export file.
View 2 Replies
View Related
Feb 23, 2011
Activity date1 date2
R1 1/1/2011 31/1/2011
R1 2/1/2011 2/28/2011
R1 ... ...
I have a particular activity like R1 where I need to find the results for some periods as above. To be clear, if the activity is completed in between Jan 2011 to March 2011, then can I get data as like the above format?
View 20 Replies
View Related
Mar 5, 2012
I'm having difficulty with a bit of SQL I'm trying to write to produce a report on defects. I'm trying to return project names, implementation dates and then 4 defect classifications.Whats happening is that only one of the three projects has any defects associated with it, but the values for that one project are also being returned for the projects with no defects
Select REQ.RQ_REQ_PRODUCT, REQ.RQ_USER_04,
/*count of sev1 functional defects*/
(SELECT count(distinct BUG.BG_BUG_ID)
FROM BUG
where BUG.BG_SEVERITY = '1-Critical'
[code]....
View 2 Replies
View Related
Oct 8, 2012
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?
noted that the data does exist 32k.
View 39 Replies
View Related
Dec 29, 2010
send me the procedure for loading the data in an oracle table into an excel file.
View 5 Replies
View Related
Apr 26, 2010
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.
View 10 Replies
View Related
Nov 3, 2012
I had done following steps,
schemas(toy,toys)
1) i open the session of toy schema
First i taken backup of table
create table bck20121103_himan as select * from himan;
Backup table is created.
After taking the Backup table
delete himan;(deleting the records)
2) i log in to another session(toys)
exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/
3) i log in to toy schema
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y
i tried the above statement it taken so much of time..
Later i tried
I log in to toy session
i rename the table with other name.
later i imported
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y
it's successfully imported.
View 3 Replies
View Related
Sep 15, 2011
following is my function,
which I need to pass parent_db_name as argument like schema name dynamically.
but i m unable to user it in cursor select query. Would any body tell me how will that be used in select query in cursor ?
create or replace procedure MyEvPro(new_court_code NUMBER, parent_db_name VARCHAR2) is
CURSOR c_evidence is SELECT GET_CASECODE(CASECODE,new_court_code) as NEW_CASECODE,COURT_CODE,EVIDENCE_NUM,ONDATE,REC_STATUS,UNITS_EARNED,CASECODE FROM parent_db_name.CASE_EVIDENCE_CHARGES WHERE GET_CASECODE(CASECODE,new_court_code) IS NOT NULL ORDER BY EC_CODE ASC;
[Code]....
View 6 Replies
View Related
Aug 2, 2013
I have created one procedure in SCOTT schema. just i need to see that same procedure information to TEST schema. how its possible.if im using
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','PROC_NAME','SCOTT')
FROM DUAL in scott schema mean i can see that full procedure information,like wise i need to see in TEST schema mean what command i need to use.
View 20 Replies
View Related
Apr 27, 2010
I have two schema SCHEMA_A and SCHEMA_B in the same Database.
I want to execute a procedure UPDATE_CONTACT() which is present in SCHEMA_A from SCHEMA_B.
let me know whether the following SQL is correct:
BEGIN
SCHEM_A.UPDATE_CONTACT();
END;
View 4 Replies
View Related
Jul 2, 2012
How to import data from excel(.xls) file to data base table
I have excel sheet(.xls) data details, I neet to upload details to data base table using procedure
excel sheet is not CSV file, so SQL Loader is not using
any alternative solution for this issue
View 3 Replies
View Related
Jul 8, 2012
I am new to Dynamic SQL..I create a procedure to get any DDL done against sample HR schema as follows.it goes well! Now when i try to test my procedure with some DDL command passing to the procedure i've created..strange! oracle throws an error as in the /*ERROR!!!!*/ block..
I don't understand why i am facing such an error..
/* Product an Version on my machine */
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]...
View 7 Replies
View Related
Jul 11, 2013
I am learning Regular expression I am solving one problem with regular expression. I am creating a table while inserting I have to restrict punctuation character.
create table emp
(
ename varchar2(30) ,
constraint ck_ename check ( regexp_like (ename , '[^[:punct:]]')));
and also how I can remove double space.
View 8 Replies
View Related
Aug 15, 2012
i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.
ex:1234
2367
1262(is not valid)
View 20 Replies
View Related
Jul 20, 2012
I am running rep_1, and before executing second rep_2 the user should enter department number, after accepting department no, the second rep_2 should be executed. But after accepting parameter the second rep_2 is not displaying any record.
View 1 Replies
View Related
Nov 21, 2012
Here's my table structure:
<h4><font color="Blue">CREATE TABLE "SSPFUSER05"."PERSONS"
( SSN NUMBER(13,0),
PIN VARCHAR2(7 BYTE)
);</font>
</h4>
And here's my procedure below:
<h4><font color="Blue">
CREATE OR REPLACE
PROCEDURE SP_SSN_BY_PIN(
V_REQUESTEDPIN IN VARCHAR2,
V_SSN OUT CHAR)
AS
BEGIN
SELECT
[code]....
As you can see the type of the SSN column is NUMBER(13,0). But I leftpad it with 0 and assign it to my output paramter V_SSN, whose type is CHAR. But I get 111196100099 instead of 0111196100099. I've tried TO_CHAR(LPAD(SSN,13,'0')) but still doesn't work. However, if I return the left padded SSN inside a SYS_REFCURSOR I get what I want.
View 1 Replies
View Related
Jan 18, 2011
I am trying to accept three numbers and one string as input, and then display the values on the screen. I don't understand why I am getting these errors.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 id_number NUMBER := &id_number_input;
3 trans_num NUMBER := &trans_num_input;
4 remar VARCHAR2(75) := &remark_input;
5 receipt NUMBER := &receipt_input;
[Code] .....
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00201: identifier 'HELLO' must be declared
ORA-06550: line 4, column 8:
PL/SQL: Item ignored
ORA-06550: line 10, column 61:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
View 3 Replies
View Related
Aug 10, 2010
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]....
View 4 Replies
View Related
Sep 17, 2012
I have used jaxb (used xjc.exe ) to parse a XML schema file.However after the class files were generated,one of the setter method is missing.Is it possible that this can happen or have i done something wrong.
the XSD file is
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="shiporder">
<xs:complexType>
[code]....
The classes that got generated are (snippet of it)
public class Shiporder {
@XmlElement(required = true)
protected String orderperson;
@XmlElement(required = true)
[code]...
This does not contain setitem() method
View 1 Replies
View Related
Mar 6, 2010
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
View 8 Replies
View Related
Jan 18, 2012
I am trying to remap schema from dump file to new user but my import fails and giving me error.
-----------------------------------
Using username "oracle".
Last login: Tue Jan 17 17:41:30 2012 from 192.168.100.11
[oracle@cvs ~]$ dbstart
Processing Database instance "cvsdbm": log file /home/oracle/oracle/product/10.2.0/db_1/startup.log
[code]....
View 1 Replies
View Related
Jul 22, 2011
Our product uses Oracle11gR1 and in new release we are going to use Oracle11gR2. For this we are performing following steps:
(1) Install Oracle11gR2 on a machine where our product (Oracle11gR1) is already installed.
(2)Upgrade Oracle11gR1 schema to Oracle11gR2.
(3)For using upgraded schema in our product installer we create clone of upgraded schema.
(4)For creating clone we are using Oracle11gR2 DBCA utility.
(5) Clone files are successfully created (DBC, CTL, DBF).
Now we performed same steps from another machine and DBF file size changed very much. On one machine it was 89 MB and on second machine it was 150 MB. There is no different in schema and both machines are Windows 7 machines.
Tor educe size of schema we tried different space reclamation commands but size is not changing.
View 3 Replies
View Related
Mar 24, 2013
i have a .dmp file and i want to use the data in this file for my further practices. so, i need to dump the data in the .dmp file to the any schema exists in data base.
View 1 Replies
View Related
Jan 4, 2009
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
View 4 Replies
View Related
