Oracle Database

78K

Solutions

25K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

hi i have the folling function which is replacing value innstead of converting varchar to number am having issue with the logic the function pass varchar and number data typein this case when i pass vaarchar its replacing the varchar and leave the number whereas it must go to second sql when data not found
function12.txt
reallscript.txt
function12.txt
crttable.txt
insert321.txt
insertts.txt
0
Get HTML5 Certified
LVL 9
Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

i have a proc like
 create or replace update_table_A(month in varchar2)

fetch cursor c1 into
l_month,l_id
execute insert_or_get_val_table_B(l_month,l_id)
end;
/

the insert_or_get_val_table_B returns l_cid as out variable...
how to execute this and get the out variable from the  insert_or_get_val_table_B and use in the  update_table_A proc
0
hi am having this error when converting varchar to number am geting this error
ORA-06502: PL/SQL: numeric or value error: character to number conversion error i have attach the function this the value am passing
 select gen_func_tst('PM0151200') from dual
errorfunctio.txt
crttable.txt
insertts.txt
insert321.txt
0
I have a table with the following columns - Id, Acct_Date, App_No, Seq_no. One Id can have several App_No. And one App_no for an Id can have several Seq_no.

I need to pull all Id, Acct_date, Max App_no and Max Seq_o for that App_no. Basically this will be one record per Id listing the maximum App No and the maximum Seq No for that application number

Thanks for your help
0
Hello

I'm busy on a project where i have read only access to an oracle database.
Got a running webserver (LAMP) with a running OCI8 connection.
I'm trying to get a select query result from the following:

TableA
ID
DOC_ID
Date
Type

TableB
ID
DOC_ID (references tableA)
FILE_NAME
FILE_ID
FILE_TYPE

TableB can contain more then one result for the same DOC_ID.

The goal is to get the data from TableA appended with the results from TableB where the DOC_ID is the same and get them into a JSON format.
Looking like

{
	"data": [{
		"ID": "123456",
		"DOC_ID": "789456",
		"Date": "2/1/2016",
		"Type": "PI",
		"FILE_NAME": [
			"File1.pdf", "File2.docx"
		],
		"FILE_TYPE": [
			"PDF", "DOCX"
		],
		"FILE_ID": [
			"453215", "654687"
		]
	}, {
		"ID": "544569",
		"DOC_ID": "001223",
		"Date": "2/8/2016",
		"Type": "PI",
		"FILE_NAME": [
			"File1.txt"
		],
		"FILE_TYPE": [
			"TXT"
		],
		"FILE_ID": [
			"453215"
		]
	}, {
		"ID": "2225599",
		"DOC_ID": "0148966",
		"Date": "2/1/2017",
		"Type": "PI",
		"FILE_NAME": [
			"File1.xml", "File2.xml", "File3.xml"
		],
		"FILE_TYPE": [
			"XML", "XML", "XML"
		],
		"FILE_ID": [
			"00147788", "00114455", "00113210"
		]
	}]
}

Open in new window


Any advise on how to best go about this is welcome.
0
I have designed the APEX Application and that application's pagesI wanna attach to the menu...
can anybody give me steps to do this.....
0
I'm trying to use VBA to insert my SQL Developer query into the command text for a connection to an Oracle database in excel. My SQL code is:

select * from
 (SELECT *
  FROM PS_JOB
  WHERE PS_JOB.EFFDT =
    (SELECT MAX(J.EFFDT)
    FROM PS_JOB J
    WHERE PS_JOB.EMPLID = J.EMPLID
    AND PS_JOB.EMPL_RCD = J.EMPL_RCD
    AND J.EFFDT        <= SYSDATE
    )
  AND PS_JOB.EFFSEQ =
    (SELECT MAX(J1.EFFSEQ)
    FROM PS_JOB J1
    WHERE PS_JOB.EMPLID = J1.EMPLID
    AND PS_JOB.EMPL_RCD = J1.EMPL_RCD
    AND J1.EFFDT        = PS_JOB.EFFDT
    )
  ) pj

but if I enter this into my VBA procedure, I get a syntax error.

Can someone advise how I can enter this code in VBA?
0
Hi,
We are working on the requirement.
We are getting transaction data to one staging db ( The table having clob column and store this data as xml). we need to parse this xml and insert/update data in three different tables.

xml  like this::

Batch
   document
       Field information...

Based on the xml.. we need to update
Batch Table
Document Table
Field Table


Regards,
Praven
0
I have a table Tb1 as follows:

Col1 Col2 MCol1 MCol2   MCol3
3       4        1         1            X
3       4        1         3            X
3       4        1         6            X
4       5        1         1            X
4       5        1         2            X

I need to update MCol3 to Z where MCol2 is not the minimum value for a certain Col1 and Col2. So the values I want after the update are:

Col1 Col2 MCol1 MCol2   MCol3
3       4        1         1            X
3       4        1         3            Z
3       4        1         6            Z
4       5        1         1            X
4       5        1         2            Z

I have a pretty convoluted way of doing this with a lot of different SELECTs but I was wondering if anybody has any lean solution.

Thanks.
0
I am developing an application that needs to connect to an Oracle database. I've added the required Nuget packages to connect to the database. If I have the DBA credentials to a schema, I can connect fine and expand the tables in the schema to add them to my model. However, on another schema I only have permissions to SELECT from a table. So, when I go through the wizard to set up my EF model I can get to the page to select the Tables I want to add to my model. However, I can't get the Tables node to expand in order to select the table that I have SELECT permissions on. The first screenshot show the screen I am stuck on. I Cannot click the check-box or expand the node to show the tables. The second screenshot shows the same behavior in VS2015 Server Explorer in that the node under 'Tables' can't be expanded to show that table I have select access to.

As I said before, there are other schemas where I have DBA privileges and these work fine (I can expand all the nodes) . Surely we are not restricted to users with DBA privileges when  using EF?

Any help, much appreciated
Thanks
C--Users-Thampi_m-Desktop-efo1.PNG
C--Users-Thampi_m-Desktop-efo2.PNG
0
Visualize your virtual and backup environments
LVL 1
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

I'm using 11.2.0.4 for Windows 64bit. It comes with Java 5 as part of the install (I'm assuming so you don't need to have Java installed to use Oracle). How do I update that version of Java? I'm running into an error where I need the unlimited crypto package but I can't use it on Java 5 (I tried and it grenaded parts of Windows if I update the security files). I did then install Java 8 on the system but Oracle seems to look at it's own vs what is installed outside of Oracle.
0
We have a small java program that connects to an Oracle (11.2.0.4 Windows) DB. There is a jks file that has the certs in it (4096 key size). When we try to connect we get:

sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: Algorithm constraints check failed: MD5withRSA

If I make changes to entries below in the java.security file in Java:

jdk.certpath.disabledAlgorithms
jdk.tls.disabledAlgorithms

and remove MD5 and MD5withRSA parameters it works. From what I've read this is supposed to be an issue in JRE 7.4 and above but it is only supposed to happen when they key length is 1024 or less. Not sure why it's happening with a 4096 key length cert.
0
Db: Oracle 11g

We have an etl job run via a third party scheduler that connects to oracle database via db link and fetches the data into a file.

The query is as follows:

select
<bunch of colums>
from
user.view_object@remotedb

This fails on the first run with the following error:

Error code: 8103, Error message: ORA-08103: object no longer exists.

On the re-run it runs just fine.

The object always exists on the remote database. Cannot understand why it fails on the first run?

Any thoughts? Please let me know if you need more information
0
Regards All,

What options are out there if I am to make something to export a quesry result to tab-separated file, and store it on network place.
Size can be about 100Mb, so quite amount of data.

I'd rather not make it Integration service/job but perhaps directly from the Oracle Database.

so:
1. run a query, generates 500k rows
2. export the result to a network place as data.txt (tab-separated)

Maybe so instead,
1. run scheduled query to store data into a temp table
2. run export from temp table to text file.

Preferably as scheduled work, cronjob, anything similar so one doesn't have to do this manually.

/BK
0
We have a system in place which is hosted (SaaS) by the software manufacturer/supplier.

We have a small internal team in place to run the day-to-day admin and management of the system, like setting up new users, running reports etc.

However I’ve become concerned as I’ve been informed that the host/supplier can access the database, setup users within the database and general write directly to the tables.  This is obviously bypassing all the application controls that are in place.

We can, and probably will run reports on users so we should be able to identify any news users that might have been set up in this way.  However this is only a small fraction of what could possibly be done via the tables, for example changing the bank details for payments to your own etc.

We can try and monitor this as much as possible but it has its limits and it is detective rather then preventative controls.
The problem I think we have is that they developed this system and now they host it, so in theory they can do whatever they want.  Has anyone else been in this situation, is there anything we can put in place to prevent this happening rather than just detecting it?  At the moment the only thing I can think of is making them promise they won’t do this, which is pretty useless.

They do provide some system support so we can’t just remove their access all together and as we don’t have control over the database, we can’t insist access is disabled until they come through us to enable …
0
I have some users trying to access tables / views via Excel.

Details:
Server: Oracle 12.1.0.2 Standard Edition on Linux OEL6
Client: Windows 10, Oracle 11.2.0.4 32 bit Client
Excel 2016 from MS Office 32 bit

I've tried this on a couple of different servers accessing a couple of different databases.  Results are consistent across all.

Define a new ODBC dataset and verify connection using the 32 bit ODBC administrator

Then:
Open a new worksheet
Select Data Tab
New Query
From Other Sources
From ODBC
Enter the ODBC connection, select any Schema, any table or view (even a single column view)

Generates the following error:
DataSource.Error: ODBC: ERROR [07006][Oracle][ODBC] Restricted data type attribute violation
Details:
  DataSourceKind=Odbc
  DataSourcePath=dsn=testdb
  OdbcErrors=Table

It appears to be an ODBC thing.  You can go through the same procedure - Data / New Query / From Oracle Database / Enter the TNS Alias / Select Schema / Select table or view - preview of the data appears.

I've spent a couple of hours so far researching ODBC configuration options, Oracle Support and everything else I can think of and have come up short.  It seems to be a fairly common thing in PowerBI, but I'm not using PowerBI.

Any help appreciated.
0
Hello Team,

Can you please help me in inserting below query with n number of OrderId at a time. Below is query for single order insert statement.

INSERT

INTO OMS_ORDER_MILESTONE

  (

    ORDMILESTONE,

    CREATED_DATE,

    COMPLETED_DATE,

    CWDOCID,

    CWORDERID

  )

  VALUES

  (

    '10',

    sysdate,

    sysdate,

    'orderId', --Internal OrderId

    'orderId'  --Internal OrderId

  );

COMMIT;
0
I have an XML file extract with names of people and other details. How do I make this data available on a form of some sorts so end users can perform all kinds of searches on the data. i. e. First Name/Last name text search or even filter the data further using drop down that users can choose from?

I am thinking of loading the XML file into an Oracle database and then display the data as a form on Oracle Apex. However I have no prior experience in Oracle Apex, so I'm assuming it has text search, drilling down features etc.

Ps - this XML data load could end up being a weekly /monthly data load.

Really appreciate your help/suggestions.

Thanks, Ben
0
Not tested but you might try -
LOAD DATA
INFILE 'yourdatafile'
APPEND INTO TABLE yourtable
FIELDS ENCLOSED BY ','
(
desc "substr(:desc,1,1)"
)


first i would like to say thanks

i tried this my ctl script was successful

but when iam triying to retriving the data from the table it will show no rows selected

can u tell me the reason
0
[eBook] Windows Nano Server
LVL 1
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

/*that's procedure*/
create or replace procedure updatesalary(did in number , sumsalary out employee%rowtype)
is
begin
update employee set salary=salary*2 where dno=did;
select * into sumsalary from employee where dno=did;
end;
/*------------------------*/


/*-----------------*/
SET SERVEROUTPUT ON;
declare
out_ employee%rowtype;
tmp employee%rowtype;
cursor cur;
begin

UPDATESALARY(1,out_);

/*how to fetch data from out_*/

end;
/*-----------------*/
0
Hi All

Here are my doubts:
Everything done in oracle DB.
I have a table X that is partitioned. (partitioned by surrogate key)
I am creating a temporary table Y by storing partition wise from  table X .
I have also stored with partition-name and partition number.(no partition) in temp table Y.
(I  will do some manipulation in table Y and merge them back to parent table X)
Is it possible to merge all partitions at once. (sample query structure please..)
Do I need to partition the temporary table in the same way as Parent table X to enable merge by partitions.(sample query please)

thanks in advance

Regards
Jags
0
I down loaded Oracle SQL Developer.
I extracted it into C:\Users\fanie\Documents\SQLDevelloper\sqldeveloper.
When I click sqldeveloper and I get a pop screen say it runs for about 1 min closes on the progress bar it was about 15 % disappears and nothing happens!

Please Help.
0
set SERVEROUTPUT ON;

declare
cursor cur is select fname from employee;
var1 employee.fname%TYPE;

begin
cur.open;
fetch cur into var1;
cur.close;
end;
/
0
ARUP_CUSTOMER_ADDRESS.docxARUP_CUSTOMER_ADDRESS.docxHi experts,
 I have a query mentioned below, for a little issue looking for solution .

SELECT COUNT(*)
FROM
COPY_CAM.AH_ADDRESS_USAGE_QUALIFIER AAUQ
INNER JOIN COPY_CAM.ACCOUNT_HOLDER AH
ON AH.ACCOUNT_HOLDER_SID = AAUQ.ACCOUNT_HOLDER_SID

INNER JOIN COPY_CAM.TRADING_CONTRACT TC
ON TC.ACCOUNT_HOLDER_SID = AH.ACCOUNT_HOLDER_SID

INNER JOIN COMMON_DM_TEST.CUSTOMER_REF_LOOKUP CRL
ON TC.CONTRACT_NUMBER = CRL.CONTRACT_NUMBER

INNER JOIN
(SELECT
ARL1.CAM_ADDRESS_SID AS ADDRESS_SID,
ROW_NUMBER() OVER ( PARTITION BY ARL1.CIM_ADDRESS_SID ORDER BY AAUQ1.DATE_START DESC ) AS RN
FROM
COPY_CAM.AH_ADDRESS_USAGE_QUALIFIER AAUQ1
LEFT OUTER JOIN COMMON_DM_TEST.ADDRESS_REF_LOOKUP ARL1
ON ARL1.CAM_ADDRESS_SID = AAUQ1.ADDRESS_SID
)S1
ON S1.ADDRESS_SID = AAUQ.ADDRESS_SID AND S1.RN = 1

LEFT OUTER JOIN COMMON_DM_TEST.ADDRESS_REF_LOOKUP ARL
ON ARL.CAM_ADDRESS_SID = AAUQ.ADDRESS_SID

LEFT OUTER JOIN CIM_DM_SIT.CIM_ADDRESS_TYPE@CIM_DM_SIT.DB CAT
ON CAT.ADDRESS_TYPE_CODE = AAUQ.ADDRESS_TYPE

LEFT OUTER JOIN COPY_CAM.AH_ADDRESS_BOOK_ENTRY AABE
ON AABE.ACCOUNT_HOLDER_SID = AAUQ.ACCOUNT_HOLDER_SID
AND AABE.SPECIAL_DELIVERY_INSTRUCTION IS NOT NULL

LEFT OUTER JOIN COPY_CAM.AH_ADDRESS_BOOK_ENTRY_ADDR_UQ AABEAU
ON AABEAU.AH_ADDRESS_BOOK_ENTRY_SID = AABE.AH_ADDRESS_BOOK_ENTRY_SID
AND AABEAU.DATE_END IS NULL

EXAMPLE: Please find the attachment .ARUP_CUSTOMER_ADDRESS.docxARUP_CUSTOMER_ADDRESS.docx

FROM 3RD ROW ONWARDS FOR ROW_NUMBER = 1 I WANT THE RECORDS.eXCEPT ROW 1 AND ROW 2 RESULTS
0
Hi Expert,

I have a table "A" which i want to track it's row change operation with table "B" .

ex:- Suppose  table "A" contains 10 row if some one add 5 rows in table "A" that should be seen to table "B" .

How to solve it using Procedure .

Thanks in Advance .
0

Oracle Database

78K

Solutions

25K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.