We help IT Professionals succeed at work.

Oracle Database

79K

Solutions

26K

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.

Hi Exeprts,
 I have Node.js and trying to read data directly from oracle database. I do have connection string. I am able to access those database through SQL Developer but when i use Node.js it's throwing the error " ORA-12560: TNS:protocol adapter error". I have used Oracledb module.

Can you please help me to identify the probelm?
var express = require("express");

var object_to_xml = require("object-to-xml");

var router = express.Router();

var db;

var dataQuery = "Select * from COUNTRY";
//console.log(dataQuery);
//router.use("/", function (req, res, next) {
var connString =
  "(DESCRIPTION = (enable = broken) (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1526))(CONNECT_DATA = (SERVICE_NAME = ABC)))";

var config = { tns: connString, user: "user1", password: "pass1" };
console.log(config);
db = require("oracledb");

db.outFormat = db.OBJECT;

//console.log(config);

db.getConnection(config, function (err, connection) {
  if (err) {
    console.error(err.message);

    return;
  }

  connection.execute(dataQuery, function (err, results) {
    if (err) {
      console.error(err.message);

      return;
    }
    console.log(results);
  });
});
//});

module.exports = router;

Open in new window

0
Hi Experts,
 I started using Node.js and Javascript for automation project. I need to read data directly from Oracle database and display as HTML table and export to excel. I have received the below connection string.

"ABC =
  (DESCRIPTION =
    (enable = broken)
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1515))
    (ADDRESS = (PROTOCOL = TCP)(HOST = Host2)(PORT = 1515))
    (CONNECT_DATA =
      (SERVICE_NAME = ABC)
    )
  )"

Can you please suggest me to use best module and best practice to read data by node.js? Also can you help me to write code to accomplish this requirement.

Thank You
0
Hi Experts,

How to get CURRENT DATE TIME IN MILLISECONDS.
0
Hi Experts,

Could you please explain what this query doing.
I am unable to understand.

 SELECT SUM (CASE WHEN FLAG = 'D' THEN -1 ELSE 1 END
                    * AMOUNT)
                    OPENDIFF,
                 ACCNUM,
                 TRUNC (DATE) DATE
            FROM TXNS
           WHERE     ACCNUM = '5008304'
                 AND TRUNC (DATE) =
                        TRUNC (
                           TO_DATE ('2020-04-02 09:00:00',
                                    'yyyy-mm-dd hh24:mi:ss'))
                 AND PROCESSINGTIME >
                        TRUNC (
                           TO_DATE ('2020-04-02 09:00:00',
                                    'yyyy-mm-dd hh24:mi:ss'))
                 AND PROCESSINGTIME <
                        TO_DATE ('2020-04-02 09:00:00',
                                 'yyyy-mm-dd hh24:mi:ss')
        GROUP BY ACCNUM, TRUNC (DATE)

Open in new window



Note:
CASE WHEN FLAG = 'D' THEN -1 ELSE 1
FLAG-value D means debit
else value Credit

what is OPENDIFF?
this is calculation addditon subtraction for amount based credit and debit

main idea is here by calculatin credit and debit
will get one value

for this trasaction D means amount is debited else crdited

why processing time  is required >'2020-04-02 09:00:00' and < '2020-04-02 09:00:00' ?
0
I have a Oracle database. Is there a way to prevent someone from accidentally rebuilding a database? We have some scripts that rebuild the database and sometimes people might accidentally run the rebuild scripts. It can probably be done on an individual basis with permissions, But I am wondering if their is some type of setting that would prevent a full database rebuild or even display a warning message.
0
Hello,

I search a script to get the below informations :
<ORACLE_SID>;<IP_SERVER>;<PORT>;<ORACLE_HOME>;<INITORA>;<SPFILE>;<SQLNETORA>;<TNSNAMESORA>;<LISTENERORA>;

Thank you

Best regards
0
In Oracle Scheduler  does trunc(sysdate +1) +2/24  mean tomorrow at 2:00 AM?
0
Hello Experts,

I am experiencing one issue on setting IDLE_TIME  under profile doesn't work , system still shows inactive sessions on Oracle database.

As per my understanding the setting should expire the sessions that are inactive and cross the setting time. in my case I have 180 as a value .

Any suggestions on any additional steps that I am missing ?
0
hi am geting the following error
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "Iusername.LOAD_IMAGES", line 17
ORA-06512: at line 1

am using this script
create or replace procedure load_images
(
   p_id         in   number,
   p_file_name      in   varchar2
)
is
   v_bfile          bfile := bfilename( 'IMAGES' , p_file_name);
   v_blob          blob;
   v_src_offset  number := 1;
   v_dst_offset  number := 1;
begin

     insert into noc_image_liabrary (id, image_name, image_file)
        values (p_id, p_file_name, empty_blob())
        return images into v_blob;

   dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
   dbms_lob.loadblobfromfile(v_blob, v_bfile, dbms_lob.getlength(v_bfile),v_src_offset,v_dst_offset);
   dbms_lob.close(v_bfile);
   commit;
end;
/

Open in new window

0
Experts,
I need to select records where My_Date is between yesterday at 6am and today at 5:59am

The date portion need to use SYSDATE -1 (or similar) so the query ran run scheduled every day without edit. the time portion can be fixed.

Thanks
0
I am trying to extract data from an Oracle table that matches a certain pattern. The "PORT" field in my table contains values similar to the following:

Eth1/21
Eth101/2
Eth3/1
Eth121/43

I am trying to select ONLY Eth1/21 and Eth3/1

I am using this SQL:

select status as LABEL, count(status) as value from MM_PORTS where REGEXP_LIKE(PORT,'Eth[1-4]{1}/.')
select status as LABEL, count(status) as value from MM_PORTS where REGEXP_LIKE(PORT,'Eth[1|2|3|4]/.')

I have tried several permutations of this select statement without luck. And I have not been successful finding a similar search using google.

Any help would be appreciated.

SteveJ
0
I was given an Oracle schema to install on our SQL.  Is it possible to install this schema into SQL and re-create the database from Oracle?  And if so how do I go about this?

Thanks,
John
0
Hi,

Kindly help on the following problem. I am using Oracle SQL to split comma separated string values. However, need to ignore the values with comma.

Below is the sample data, which needs to be separated/split (comma seperated)
'franchise': 'Px Oncology (Solid Tumors, all XA Onc projects)', 'zlinical_xombination_study': 'No'

Open in new window


sample output would be,
franchise: Px Oncology (Solid Tumors, all XA Onc projects)
zlinical_xombination_study: No

Open in new window



problem is, the value 'franchise': 'Px Oncology (Solid Tumors, all XA Onc projects)' is also having a comma within its value, need to ignore this comma while splitting

I am using the below code,
select LTRIM (REGEXP_SUBSTR (ERROR_TEXT, '[^,]+', 1, 1), ',')    AS part_1 FROM ERROR_LOG_TEMP_5;

Open in new window



The output I am getting is ['franchise': 'Px Oncology (Solid Tumors]

It should provide,  ['franchise': 'Px Oncology (Solid Tumors, all XA Onc projects)']
0
hi how can i import image in my database table am in oracle database 12c
i did this, the procedure is only loading one image i what to load all 5000 images same time i also what to put image name in column name

create or replace procedure load_images is

  l_dir    VARCHAR2(10) := 'IMAGES';

  l_file  VARCHAR2(20) := 'AA10008.png';

  l_bfile  BFILE;

  l_blob  BLOB;

BEGIN

  INSERT INTO lob_table (id, images)

  VALUES (1, empty_blob())

  RETURN images INTO l_blob;





  l_bfile := BFILENAME(l_dir, l_file);

  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));

  DBMS_LOB.fileclose(l_bfile);





  COMMIT;

END;

CREATE TABLE lob_table (id NUMBER,name,images BLOB);

CREATE OR REPLACE DIRECTORY IMAGES  as '/u01/images'

GRANT READ, WRITE ON DIRECTORY images TO db_users;



i also try this  host sqlldr username/pswd@dbname control=load_image.ctl

am geting this error

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]

ORA-12541: TNS:no listener

0
hi am having this error when adding vm in my virtual box
path
0
I have MySQLWorkbench installed on my work laptop. I do not have administrative rights. I also use TOAD for Oracle to connect to Oracle using TNS. I am a SQL developer,

I would like to connect MySQLWorkbench to an Oracle database. It is supposed to be possible. I would like to use the ER diagramming tools in MySQLWorbench for the Oracle database.

When I try to create a connection to Oracle, I use TCP, the host name from the TNS file (I can ping it), the port form the TNS file, and my Oracle user name & password. I get an error message "Failed to Connect"  "Lost connection to MySQL server at 'reading initial communication packet', system error: 0"

Since I don't have admin privileges, I suspect I may have to request stuff to be installed, which I can do. I would just like to know what to ask for.
0
We are upgrading an Oracle Enterprise server from a Dell R810 with 4 attached MD1200's (sas2 w/6g bus) to a Dell R730 with three attached MD1420's (sas3 w/12g bus).  R810 has four cores with 4 cpu threads and the R730 has two physical cores (2.6 GHz) with 8 way cpu's (3.2 GHz).  Drives on both systems are 15k.  Ram in each is 256gb (of course, the ram in the r730 is faster).

Problem is that we're only seeing 10-15% performance lift in the new server.  We expected much more. Lastly, the drives in both machines are SED.

Any ideas on getting better Oracle performance?

Thanks--
0
hi am having this error when starting oracle database 12c in solaris
errdatabase
0
How would I work this scenario into my "Where" clause.  Posted what I have so far but isn't right. I think I have everything before the OR. This table may also be helpful AP_CLAIM_PX_2.LINE

Scenario:
DOS 02/16/2019-2/17/2020 and
All claims that contain a service line with either 76376 or 76377 (regardless of what component group this hit) and
Any other service lines that hit any of the following component groups 1005000742;G; 1005000743;G; 1005000745;G on the same claim.
OR
All claims that contain ONLY a service line with either 76376 or 76377 and no other service lines present.



Where Code so far:
AP_CLAIM.DATE_RECEIVED between '02/16/2019' and '02/17/2020' and  
(PE.PROC_CODE in ('76376','76377') and 
AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000742;G' or AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000743;G' or AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000745;G') or

Open in new window

0
Using PL/SQL, I have a query where I am trying to find all claims that do not have an "Original_Claim_ID" in them.  The problem is that each "Claim_ID" can have 1 or more sequences and I am running it at the "Claim_ID" level.

The table is named "DB_Claims"
It has 3 fields - Claim_ID, Sequence, Original_Claim_ID

If I run this query:

select Claim_ID, Original_Claim_ID
from DB_Claims
where Original_Claim_ID = ' ' ;

It will still pick up sequence 1 and 3 below and identify it as a claim with no Original_Claim_ID.

Claim_ID  Sequence  Original_Claim_ID
759Q114G  1      
759Q114G  2          644R458B
759Q114G  3      
759Q114G  4          644R458B
759Q114G  5          644R458B
759Q114G  6          644R458B
759Q114G  7      

What I need is a query that will disregard the claims where there is an Original_Claim_ID on any of the sequences.

Thanks,
Scott
0
I need to update or insert data into a field without removing the existing data that is already in the field. Need to add the data after a line feed or carriage return. The example below shows what I wish to do. Add Note: FMV: $12588 after any existing data already there.

Ex.

Do not buy this PN

Note: FMV: $12588
0
Hi Team,

I have created an externalt table which pulls data from a fixed wifth record data in a file . When I run the query i get the below error .Any help is really appreciated


CREATE TABLE emp_t
      (id    number(3),
       name   varchar2(15),
       desig  varchar2(15),
       sal number(5)
       )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY inputdata
      ACCESS PARAMETERS
        (RECORDS FIXED 38
         FIELDS (id POSITION(1:3) NUMBER(3),
                 name POSITION(4:18) varchar2(15),
                desig POSITION(19:33)  varchar2(18),
                sal POSITION(34:38) number(5)
                )
        )
      LOCATION ('fixedposdata.dat')
     );



Below is the data present in the file
fixedposdata.dat

101tam            programmer     10000
102novin          teamlead       23000
103Kishna         projectlead    23000


Below error i get when ii run the query

Select * from emp_t;

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, no, nullif, oracle_date, oracle_number, raw, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: NUMBER
KUP-01007: at line 2 column 35
29913. 00000 -  …
0
hi,

any step by step procedure to install oracle on linux VM if I have the RPM package ?

please give configure guide too.
0
I have a function. Please see below.
 function func_get_mdi_window_title ( p_div_no in varchar2 )
      return varchar2 is

      v_title    varchar2(100);
      v_db_name  varchar2(100);
      v_instance varchar2(100);
      v_title1 varchar2(100);
      
      begin

         select global_name into v_db_name
            from global_name;

         if v_db_name = 'DEV.SITE.COM' then
            v_instance := 'Dev'; 
         elsif v_db_name = 'TEST.SITE.COM' then
            v_instance := 'Test'; 
         elsif v_db_name = 'PROD.SITE.COM' then
            v_instance := 'Production';
         elsif v_db_name = '2.SITE.COM' then
            v_instance := 'Prod2';
	     elsif v_db_name = 'TEST2.SITE.COM' then
            v_instance := 'Test2';
         elsif v_db_name = 'DEV2.SITE.COM' then
            v_instance := 'Dev2';
         elsif v_db_name = 'GVN.SITE.COM' then
            v_instance := 'GVN';
         elsif v_db_name = 'GVN2.SITE.COM' then
            v_instance := 'Gvn2';
         elsif v_db_name = 'GVN3.SITE.COM' then
            v_instance := 'Gvn3';
         end if;
         if nvl(v_title,'x')='x' then
            v_title := 'Div '||p_div_no||'-IMOPS '||v_instance||' Application System';
        
         end if;
         
        return(v_title);
      exception when others then
         v_title := 'Div '||p_div_no||'-app System';
         return(v_title);
      end func_get_mdi_window_title;

Open in new window


Can we set the font/color  of the return value?
Any help is appreciated.
0
Oracle database function-  window title- set title color based on instances

 marked title is derived like below


   set_window_property
    ( forms_mdi_window
     ,title
     ,pkg_application_standards.func_get_mdi_window_title(:global.g_div_no));

pkg_application_stadards.func_get_mdi_window_title(:global.g_div_no) gives that marked title

  SELECT name
  INTO   v_name
  FROM   v$database;

function func_get_mdi_window_title ( p_div_no in varchar2 )
      return varchar2 is

      v_title    varchar2(100);
      v_db_name  varchar2(100);
      v_instance varchar2(100);
      begin

         select global_name into v_db_name
            from global_name;

         if v_db_name = 'DEV.SITE.COM' then
            v_instance := 'Dev'; 
         elsif v_db_name = 'TEST.SITE.COM' then
            v_instance := 'Test'; 
         elsif v_db_name = 'PROD.SITE.COM' then
            v_instance := 'Production';
         elsif v_db_name = '2.SITE.COM' then
            v_instance := 'Prod2';
	     elsif v_db_name = 'TEST2.SITE.COM' then
            v_instance := 'Test2';
         elsif v_db_name = 'DEV2.SITE.COM' then
            v_instance := 'Dev2';
         elsif v_db_name = 'GVN.SITE.COM' then
            v_instance := 'GVN';
         elsif v_db_name = 'GVN2.SITE.COM' then
            v_instance := 'Gvn2';
         elsif v_db_name = 'GVN3.SITE.COM' then
            v_instance := 'Gvn3';
         end if;
         if nvl(v_title,'x')='x' then
            

Open in new window

0

Oracle Database

79K

Solutions

26K

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.