I Would like to delete a records based on the parent / child relationship. So I would need to delete a record from parent table , first delete inner most child record then other child then master. To write generic way of Oracle 10g PL/SQL code.
View 2 RepliesFirstly the table structures :
Create table cd_patient (pat_mrn varchar2(100)) ;
Create table cd_encount (pat_mrn varchar2(100), encounter_id varchar2(1000));
I need to write a code which dynamically reads the table name from user_tables (starting with cd) & load the data from remote database which has the same table name & structure based on the load number..
Static PLSQL statement would be :
declare
BEGIN
INSERT INTO "cd_patient" C-- DYNAMICALLY GET THE TABLE NAME
SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME
FROM REMOTE_DB.CD_PATIENT@XXX C1
WHERE C1.LOAD_NUMBER > 2;
[code]....
I have a client program that needs to communicate with another server program. My client program is implemented in PL/SQL using utl_tcp running on Oracle 9 while the server program is a 3rd party software running on Windows server 2003.
Basically my client program needs to send a request to the server, waits for the response (wait for the incoming string which ended /w chr(3) ) and then acknowledge (sending chr(6) ).
My problem is that if I wait for the chr(3) and then acknowledge. My send routine (l_sent := utl_tcp.write_text (g_conn, p_content) will raise a Oracle generic network error. It seems like the connection is closed. I checked /w the vendor and they said it might be I am taking too long to response ( I question timeout is the problem). I have also attempted to change my read routine from read_line to read_raw but it only make things worst. I cannot even read the complete string. At least with read_line, I can read back the entire string.
My current work around is to send out the ack before I receive the chr(3) (which is not recommended by our vendor).
I tried to write a dynamically changing filename and file type so that i can dump some data. But the code I wrote is not producing any file at all even though it compiles with no errors. I run this code at XE database which comes free from the oracle website. I did all the directory settings. No problem with it because I can produce file with different codes. The code is as following:
CREATE OR REPLACE PROCEDURE dump_csv_file IS
TYPE number_array IS VARRAY(10000) OF NUMBER;
TYPE string_array IS VARRAY(10000) OF VARCHAR2(100);
TYPE date_array IS VARRAY(10000) OF DATE;
TYPE v_file_array IS VARRAY(10000) OF UTL_FILE.FILE_TYPE;
[code]...
In order to display non-english characters properly in Business Objects reports, We have changed the NLS_LANG parameter at BO server level from ENGLISH_UNITED KINGDOM.WE8MSWIN1252 to AMERICAN_AMERICA.AL32UTF8
After this change non-english characters are coming properly but some of our existing reports started giving below error after this change.
A database error occured. The database error test is: (CS) "Error on Fetch: Generic Application exception" (WIS 10901).
I need to select all names that can possibly have ether accents or french characters what is the easiest way?
View 1 Replies View RelatedWe are planning on interfacing Oracle to SQL server connectivity, I'm researching on how to create a dblink to SQL server 2005. I'm a little confused between Generic connectivity and Transparent Gateways..
I think Transparent Gateway is not free and needs to be licensed, is it better in terms of performance? Also, do I need to configure this gateway on a seperate server?
I think Generic connectivity can be configured on the same Oracle database server itself.. but I see there could be some performance issues with it..
We are running on Oracle 10.2, looks like HSODBC is not available in 10g?
I just install the oracle g10 on my laptop, which runs windows XP and it seems to work fine BUT it will not allow me to write Scripts I go to the Script Editor, i can write on the "Script Name" but below i cant ever put the cursor to write the script!
View 3 Replies View RelatedI am using SQL*Plus to run this Oracle10g. Here is my problem I have to solve: After a new record is added to the task table, write a trigger that reports the new project balance to the user while updating the customer balance to reflect this additional task cost. Insert a new record to show the trigger works. I somewhat understand triggers,I think I have to combine tables to solve this. Here are my tables/attributes I can use to solve it:
CREATE TABLE customer
(
custnoNUMBER(3)CONSTRAINT ccustpk NOT NULL PRIMARY KEY,
custname CHAR(20) CONSTRAINT ccustnamenn NOT NULL,
phone CHAR(12),
curr_balance NUMBER(9,2) CONSTRAINT ccustbalancenn NOT NULL
[code]...
I have a procedure in mysql that need to be converted to oracle.I tried to convert it ,everything looks good except the function LAST_INSERT_ID().
mqsql procedure
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_ac_message`(
IN in_process_run_id INT,
IN in_processID INT,
IN in_messagename VARCHAR(25),
IN in_message VARCHAR(200))
BEGIN
[code]......
oracle procedure
create or replace
PACKAGE body Mysql_To_Oracle
IS
Procedure Add_Ac_Message(In_Process_Run_Id In Number,
In_Processid In Number,
In_Messagename In Varchar2,
In_Message In Varchar2)
[code]....
Error:
ORA-00904: "LAST_INSERT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
I have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.
<?xml version="1.0"?>
<ROWSET>
<Emp>
<ENAME>SMITH</ENAME>
<EMPNO>7369</EMPNO>
<SAL>800</SAL>
</Emp>
<Emp>
<ENAME>ALLEN</ENAME>
<EMPNO>7499</EMPNO>
[code]...
I want to write a select statement which gives me result as follows (all ENAME and EMPNO from the xml)
ENAME EMPNO
SMITH 7369
ALLEN 7499
WARD 7521
How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.)
View 5 Replies View RelatedI have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies View RelatedI have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
I have a file studnet.txt in following order
JAMs|1231|PHYSICS
SAM|1232|PHYSICS
ALI|1233|CHEMISTRY
I want to read and write data from a file in | seprated mode. file READ and WRITE using Oracle Forms6i. I have a knowledge of file handling in C++ but not use it in Oracel Form before this.
I would like to connect to oracle database and would like to execute series of SQLs and write those SQL results into Excel file (sheet1).
my requirement is simple :
------------------------
db_user_id scott
db_user_pwd tiger
db_sid orcl
select count(*) from emp;
select count(*) from dept;
print the same SQLs in A column and result should be in B column in excel file.
column A column B
select count(*) from emp; 14
select count(*) from dept; 4
thats it.
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
I want write a program using oci to login into oracle database.Advance security option for the oracle server is set to kerberos authentication. Is it possible to login to the oracle database through olog() function.
Initial ticket is required in kerberos authentication. So is it also needed durinfg the login from my programme.
I will have to provide read/write access to an oracle user. What privileges should i grant to the user so as to enable the user to read from and write to the files?
View 2 Replies View Relatedhow can i write a trigger to insert the system clock time in the oracle form?
View 15 Replies View RelatedI am attempting to install Oracle 10g R2 on Oracle Enterprise Linux 5 update 5 but facing some errors. Can't believe i have done everything right as far as oracle docs n my understanding concern. when i try to invoke runInstaller for CRS it fails with following error:
------------------------------
[oracle@server01 clusterware]$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2 Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-11-15_04-36-47PM. Please wait ...[../stage/Components/oracle.swd.oui.core/10.2.0.1.0/1/DataFiles/filegroup1.jar]
End-of-central-directory signature not found. Either this file is not
a zipfile, or it constitutes one disk of a multi-part archive. In the
latter case the central directory and zipfile comment will be found on
the last disk(s) of this archive.
note: ../stage/Components/oracle.swd.oui.core/10.2.0.1.0/1/DataFiles/filegroup1.jar may be a plain executable, not an archive
Error in writing to directory /tmp/OraInstall2010-11-15_04-36-47PM. Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
: Success
[oracle@serverr01 clusterware]$
--------------------------------------------------
I am also attaching the cluster verfication result, if you wish to have a look at this.
I tried more to write Bangla in Oracle 10gR1, or 10gR2 or Express but failed. When I write bangla in the "emp_name" field for employee name this can be save, But when I execute to see the data again then the data comes with a big change like some symbol for some character.
View 3 Replies View RelatedI'm using the following piece of code to run reports from Oracle Forms 11g.
DECLARE
repid REPORT_OBJECT;
v_rep VARCHAR2(100);
BEGIN
repid := FIND_REPORT_OBJECT('REP_TEST_R');
v_rep := RUN_REPORT_OBJECT(repid);
END;
On execution, the screen does not respond.Do I need to use WEB.SHOW_DOCUMENT along with RUN_ REPORT_OBJECT?
I want to wrap the source code in oracle, i am able to do it using wrapping utility (through DOS prompt command i.e wrap iname= inputfilename.sql oname=outputfilename.plb).
Other option is using DBMS_DDL package as follows
*********************************************
DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrap DBMS_SQL.VARCHAR2A;
[Code]....
This above will replaces the original function source code with wrapped code, but here they are concatinating the code manually.
My requirement is i will get the source code (text) from user_source view in a cursor and line by line code will get in a loop. I am sending that output to above function dynamically , but it raising this error.
ORA-24230: input to DBMS_DDL.WRAP is not a legal PL/SQL unit.
I am using Developer 10g R2. I have a form with two fields named input_value and output_value and a button named ok. I have a java class named Factorial which return the factorial of the input value. Now i want to use this class into pl/sql in ok button.
View 4 Replies View Relatedi need a connectivity code to connect oracle DB in UNIX.pls send me a code exactly.
View 2 Replies View RelatedIn oracle 11g windows8 - OS
Suppose, there is a package p1 with proc A, Proc B and Func C.Now when any of the package proc or func gets invoked from a stored proc G then a common code which reads the cols of table xyz should be executed. How to do that?
I do the codding procedur in oracle form code to export data to Excel.But when i run the form and call that procedure. The data is come to excel, but it's autometic create a new sheet(as "Sheet4").
The problem is.. How can specify the Sheetname or fix it as "Sheet1"?
---Following it's my code
PROCEDURE Export2EXCEL_FILE(P_MAXCOL_NUM NUMBER) IS
cursor dtl is select * from SG_TMP_REPORT_RESULT
where SOURCE_ID = :BLK_CTRL.NB_SOURCEID
ORDER BY COLTYPE;
row_num number;
col_num number;
cell_val number;
[code]....
i have oracle database 10g with RAC. Before this, we upgrade our storage and completed the process. After bring up our database and it's ok.
But the problem is, when i try connect our database using ODI then database hang and i got error closed connection. When i checked details in alert.log, i got error - ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], [].
Printing a bar code (such as a pick slip number e.g. 223456) in Oracle Applications 10.7 char, reports 2.5 on a genicom (dot matrix)..I was unable to find any fonts for this when I searched.
View 3 Replies View Related