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.

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

Sign up to Post

Is there any process to load jpeg images into oracle database using a procedure ? The database column will be a blob. I googled, but could not find any that I can use.
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Oracle

I have an sql that asks me for a date
and when the box comes up its displays:
oracle pic for date

WHERE
    trunc(r.lastmodified) IN (
        :dateseplist

I'm oracle sql developer.
nd i need to amend/change this
so it ask for a date range ?  not just one day ?

I dont know what ":dateseplist"

so i can change it ? if so where in oracle developer ?

Thanks
fordraiders
0
oracle dates:

I have a select statement for some date fields:
SELECT
    p.projectid,
    p.startdate     submitdate,

The dates are being displayed as :

13-NOV-18 02.17.44.000000000 PM

Is there a way to cast them as :

yyyy-mm-dd hh:mm:ss  AM/PM   ?

I do not work in oracle...so a little vague to me as to why time stamps  have a  .   and  :

Thanks
fordraiders
0
I have a bash shell script that I want to call a Oracle DB stored procedure with (procedure is in a package with public interface), I am running it on Cygwin:

#!/bin/bash

if [[ $1 = '' ]]; then

  echo
  echo "usage : copy_client_div.sh <master conect string> "
  exit 0
fi

test_db1=`$ORACLE_HOME/bin/sqlplus -s $1 << EOF
set heading off
set pages 0
set feedback off
select 999 from dual;

EOF`

test_db=`echo $test_db1`
echo test_db: ${test_db::-1}
echo ${test_db}


if [[ ${test_db::-1} != 999 ]]; then

  echo "Can NOT connect to database with the given connect string"
  echo
  exit 0

fi

CONNECT_STRING=$1

`$ORACLE_HOME/bin/sqlplus -s $CONNECT_STRING << EOF > /c/temp2/log.txt

set heading off
set pages 0
set feedback off

begin
      --GRANT EXECUTE ON COPY_STUDIES.ins_trial2 TO tsm10;
      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;
end;
EOF
`

If I run the procedure by itself in a sql script, it works.  In the bash script, nothing happens and no error gets returned.  Can someone tell me what I am missing?  Thanks.
0
We are experiencing the Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000 after an upgrade from 11g to 12c which involves a MINUS query. The individual queries execute without the error.
In other words lets say the query is SELECT A, B FROM HN1 MINUS SELECT A, B FROM HN2
The statement SELECT A, B FROM HN1 executes without error. Likewise SELECT A, B FROM HN2.
However when they are combine the above error shows. There are about 18 columns in the query and when Oracle does internal query processing creating inline temporary tables it may strike the 1000 limit. At the moment I will not reduce the amount of columns in the query. Is there an Oracle hint I can use to make it function or any other strategy.
0
how to insert the image in the tables using BFILE or BLOB ??
Tell me the easy way to insert
Please give the detailed programs for the above questions
0
How to concatenate dblink which will change dynamically in a Oracle SQL Statement at the end of the table name
For ex.
Select * from gv$session@:<dblink>
0
Hello Experts,

I have a table that contains 546471 rows out of which I need to delete 386201 rows based on date column.

Now I tried two ways where it is taking huge time to remove the rows.


First the table has a primary key c_key1  and c_dt is a date column based on which I need to delete.

The delete statement is :

DELETE FROM MY_TABLE 
WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ;

Open in new window


The date column is not having any indexes and even after creating the index it takes more then 15 mins to execute the delete statement.


So adding the index on c_dt , tired using forall mapping the primary key i.e. c_key1 column.


Here is the sample code which is taking again huge time:

DECLARE
  TYPE t_chk_tab IS TABLE OF my_table.C_KEY1%TYPE;

  l_chk_tab    t_chk_tab   := t_chk_tab();
  l_start     NUMBER;

BEGIN
  -- Populate collections.
  FOR i IN (
  select C_KEY1 from my_table
  WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ) LOOP
    l_chk_tab.extend;
    l_chk_tab(l_chk_tab.last)     := i.C_KEY1;
    
  END LOOP;

  l_start := DBMS_UTILITY.get_time;

  -- Time bulk delete.

  FORALL i IN l_chk_tab.first .. l_chk_tab.last
    DELETE FROM my_table
    WHERE  C_KEY1   = l_chk_tab(i); 
    
  DBMS_OUTPUT.put_line('Bulk Deletes   : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  ---commit;
END;

Open in new window



Any suggestion on to make it faster, the only issue is I cannot move the data to another table and delete the rows and rename it. As the same table is been used in multiple application for the delete operation all the application will not be using till weekend but still cannot drop the original table
0
Hello Expert,

A large web-based application is connected to an Oracle 12c database.
The application has a feature which puts a record in Draft status to isolate the record while changes are made.
The Draft record is deleted when the record is Saved and Approved. Or it is supposed to be.
For some reason, under investigation, the Draft records are sometimes not deleted.
This create an invalid data state we lovingly call a Stray Draft.
A Stray Draft causes an error if a user ever tries to edit the record again, I think
because the new Draft record ties to use the same primary key as the old, undeleted draft.

That is the context.

So the boss says 'Delete all old (last_update_date > than timestamp - 90) drafts.
The only fly in the ointment is there are about eighty draft tables with name syntax
D_tablename and it is known by this writer that some of them have parent-child
relationships. That is, for instance, the D_PAYER table is Parent to the Child D_PAYER_ADDRESS
table and the D_PAYER_ADDRESS table is Parent to the Child D_PAYER_ADDRESS_TYPE and
the  D_PAYER_ADDRESS_USAGE tables.
One cannot delete Child records that have a foreign key relationship to Parent
records.

With eight tables getting everything in order empirically is pretty tough. So
visiting with one of the developers, he said 'If you look at table meta data (Columns, Data Model,
Constraints, Grants, Statistics, Triggers, Flashback, Dependencies,
Details, Partitions, Indexes, SQL) for the…
0
Hello Experts,

Any better way to connect to SQL Server using Oracle JOBS and get the data into Oracle database ?

There are existing methods like SSIS and others but looking very specific on Oracle JOBS.
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hello Experts,

I want to update multiple row with same sequence.

Here is my object:

create table mytab (id number, name varchar2(400), source varchar2(400), key_val number );

insert into mytab
select 1 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 2 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 3 as ID, 'name2' as name, 'source2' as source , null as key from dual
union
select 4 as ID, 'name3' as name, 'source3' as source , null as key from dual
;
commit;

create sequence key_seq  increment by 6666 start with 1;

Open in new window


Now if I run my select statement then I can see that the first and second row have the same values so I need to update the key column with same sequence number to make a track.

select 
id, name, source, key_val,dense_rank() over (order by name, source) dr 
from mytab;

Open in new window


 Result:
1	name1	source1		1
2	name1	source1		1
3	name2	source2		2
4	name3	source3		3

Open in new window


Now the expected result would be getting same sequence to row 1 and 2 and follow nextval to other 2 rows.
0
I need to run a update query with a join with multiple matching criteria.  Relevant tables and columns for reference are as follows.

OPMAT TABLE
arinvt_id
stdcost
backflush

ARINVT TABLE
id
itemno

I am provided with a list of itemno values that I need to locate the corresponding ID for in ARINVT.  Join that ID to the opmat.arinvt_id and update the values of stdcost and backflush.

Normally, I accomplish this by running multiple select statements against arinvt using the itemno's provided to locate the id's
select id from arinvt where itemno ='12312312321'
I run this over and over in a begin / end loop and get a giant list of arinvt.id's returned.

I then run a update statement update opmat set stdcost = 'y', backflush = 'y' where arinvt_id = '232132123' (the values returned) and I repeat all of that in a big update loop.


This requires a lot of excel junk to make my statements.


I'm trying to simplify this with the update and join combination with multiple case statements so I'm more efficient with this.
0
Convert a .txt file to a .csv file through coding using 1.plsql oracle and 2.java
0
I'm executing a query inside SSRS and receiving an ORA-00933: SQL command not properly ended.  It's just a SELECT statement but the error appears to be in the Where clause.  Am I specifying the parameter, @PaperBill, incorrectly?

Select ACCTUSAGE_MAIN_V.BILLING_DATE
,ACCTUSAGE_MAIN_V.BILLING_CYCE
,ACCTUSAGE_MAIN_V.BILLING_MONTH
,ACCTUSAGE_MAIN_V.BILLING_YEAR
,ACCTUSAGE_MAIN_V.INVOICE_NUMBER
,ACCTUSAGE_MAIN_V.ACCOUNT_NUMBER
,ACCTUSAGE_MAIN_V.DESCRIPTION
,ACCTUSAGE_MAIN_V.CNT_LAST_NAME
,ACCTUSAGE_MAIN_V.CNT_FIRST_NAME
,ACCTUSAGE_MAIN_V.CNT_ADDR_1
,ACCTUSAGE_MAIN_V.CNT_ADDR_2
,ACCTUSAGE_MAIN_V.CNT_CITY
,ACCTUSAGE_MAIN_V.CNT_STATE
,ACCTUSAGE_MAIN_V.CNT_ZIP
,ACCTUSAGE_MAIN_V.CNT_ZIP_EXTEN
,ACCTUSAGE_MAIN_V.RECEIVE_PAPER_BILL
,ACCTUSAGE_MAIN_V.EMAIL
,ACCTUSAGE_MAIN_V.MIN_INVOICE_NUMBER
,ACCTUSAGE_MAIN_V.MAX_INVOICE_NUMBER
From COMIT_CUSTOM.TAMU_ACCTUSAGE_MAIN_V as ACCTUSAGE_MAIN_V
Where ACCTUSAGE_MAIN_V.RECEIVE_PAPER_BILL = @PaperBill
Order by ACCTUSAGE_MAIN_V.ACCOUNT_NUMBER,ACCTUSAGE_MAIN_V.BILLING_CYCE
0
Hi APEX masters,

I am using Oracle APEX 18.1 and can anyone let me know how to implement hours in the charts X-axis , please find attached.

Thanks,
R
88C341A5-BDA1-4C47-B563-C624248BC17.jpeg
0
I need some help with a CASE statement.

I am looking at sales order lines and linking out to a reference table. I want the case to output 'MY STRING' If at least 1 line on the order has the value 'WILLIAMS'. Nothing output if no lines contain this value.

So far I wrote a query to return the reference, then added a count where the reference equals Williams.

Select count(ITEM) from (
    select D5.ORD_NUM, D5.ORD_LEV1 ITEM, D3.ALT_INVT_REP_CODE BRAND
    from E_ORD_D5 D5
    LEFT JOIN M_ITEM_D3 D3 on D3.COMP_CODE = D5.COMP_CODE and D3.CUST_CODE = D5.CUST_CODE and D3.ITEM_CODE = D5.ORD_LEV1
    where D5.comp_code = 'W1' and D5.ord_num = 935373 
    )
WHERE BRAND = 'WILLIAMS'

Open in new window


Not sure how to make this part of a CASE. (of if CASE is even the best approach)
0
Need best websites (e.g. plus utube video if possible) for upgrading an Oracle (3) node RAC database under Solaris 11.  Need to upgrade 12.1 RAC database to 12.2.
0
I am a novice to Oracle Administration and am using it primarily as a backend resource to demonstrate an Auditing application.

Oracle 12c, installed on Windows 2012R2.  I can logon to the Windows server as the local administrator but while I can logon to Oracle locally, I cannot logon remotely.

ORA-01017: invalid username/password; logon denied.

ORA-28000: the account is locked.
0
hi am geting this error in database 12c in solaris i did this

Enter user-name: /as sysdba

Connected to an idle instance.

 

 

SQL> startup

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 5243264M

SQL> create pfile from spfile;

 

 

File created.

 

 

SQL> startup

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 5243264M

Open in new window

0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hi am having error opening form builder in window 7 my form version is 11.1.2.2.0, i do have the file
form
0
Hi,
I'm looking for a regexp_substr result = HIGH and LOW from the following two strings

1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGH FLOOR
2. TIRE, MICHELIN 305/70R22.5 X INCITY Z LOW FLOOR

Output:
HIGH
LOW

This can be 2 separate SQL statement regexp_substr.

Thanks,
0
Need to create an Oracle database privileged database user account, but don't want this user to be able to drop database.

     Sqlplus>  create user  InfSysAd IDENTIFIED BY xxx_XXX;

     SqlPlus>  grant connect, resource, DBA to SInfysAd;
     SqlPlus>  revoke drop database;

Is this good enough to all this new InfSysAd to create other database users, tables, etc?
0
Oracle Sql Developer.

I'm new to  Oracle sql  developer and i keep getting this error message everytime i want to run a query.
even on a simple sql statement.

select * from  mmmm

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 1 Column: 3,310


Any guidance would be appreciated.
Thanks
fordraiders
0
Hi Expert,

If there anyone working on Oracle Retail V16 SaaS Interface here,
Cloud anyone please guide how to prevent Duplicate file processing to SaaS using PL/SQL or UNIX?

Thank You!
0
hi am having the folowing eror in my code i have attach the code
error report -
ORA-06550: line 19, column 4:
PLS-00103: Encountered the symbol "FUNCTION"
ORA-06550: line 19, column 31:
PLS-00103: Encountered the symbol "RAW" when expecting one of the following:

   (
ORA-06550: line 78, column 7:


procedure val_pswd(in_paswd in varchar2, out_msg out varchar2) is
passwd         varchar2(30);

  raw_key raw(128):= hextoraw('0123456789ABCDEF');

  raw_ip raw(128);
  pwd_hash varchar2(16);

  cursor c_user (cp_name in varchar2) is
  select   password
 from sys.user$
  where password is not null
  and name=cp_name;
  
  procedure unicode_str(userpwd in varchar2, unistr out raw)
  is
    enc_str varchar2(124):='';
    tot_len number;
    curr_char char(1);
    padd_len number;
    ch char(1);
    mod_len number;
    debugp varchar2(256);
  begin
    tot_len:=length(userpwd);
    for i in 1..tot_len loop
      curr_char:=substr(userpwd,i,1);
      enc_str:=enc_str||chr(0)||curr_char;
    end loop;
    mod_len:= mod((tot_len*2),8);
    if (mod_len = 0) then
      padd_len:= 0;
    else
      padd_len:=8 - mod_len;
    end if;
    for i in 1..padd_len loop
      enc_str:=enc_str||chr(0);
    end loop;
    unistr:=utl_raw.cast_to_raw(enc_str);
  end;
  
   function crack (userpwd in raw) return varchar2
  is
    enc_raw raw(2048);
    --
    raw_key2 raw(128);
    pwd_hash raw(2048);
    --
    hexstr varchar2(2048);
    len number;
    password_hash varchar2(16);
 

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.