Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

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

Hello,

I am using the following script to insert data into a table in Oracle and I'm getting this error:

Capture.JPG
My script is:
INSERT INTO Movie (CatalogID, Title, Genre, ReleaseDate, HirePrice) VALUES ('1', 'Sexy in the City', 'Comedy', '2012-12-12', '5.00');

Table script was:
CREATE TABLE Movie
(CatalogID CHAR(5) NOT NULL,
Title VARCHAR(50) NOT NULL,
Genre VARCHAR(50) NOT NULL,
ReleaseDate DATE NOT NULL,
HirePrice NUMERIC(5,2) NOT NULL,
CONSTRAINT CatalogID_pk PRIMARY KEY      (CatalogID));

Can anyone help?

Thanks.
0
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

This code is a small portion of a view creation that develops a perfect answer except that one track_thing_id/thing_id combination exceeds the 4000 byte listagg limitation.  Because that one combination exceeds the 4000 character/byte limitation of listagg, I believe I need an XML conversion.  

    left outer join
           (  select mi8.track_thing_id,
                     listagg (
                            nvl (p.cipher, '')
                         || '-'
                         || mip.kellogg_nur
                         || '-'
                         || nvl (pls.situation, 'Nsitn'),
                         ', ')
                         within group (order by p.cipher)
                         as "thing/sit"
                from ml_track_thing mi8
                     join ml_track_thing_stuff mip
                         on mi8.track_thing_id = mip.track_thing_id
                     left outer join stuffstuff p on p.id = mip.endpr_id
                     left outer join lic_endpr_permit pl
                         on (    pl.endpr_id = mip.endpr_id
                             and pl.house_id = mi8.place_id)
                     left outer join lic_endpr_permit_situation pls
                         on pl.permit_situation_id = pls.id
            group by mi8.track_thing_id
            order by mi8.track_thing_id) spcl

Open in new window


So what I need from the above code is the XML equivalent..    and I am hoping the above is sufficient  for a resident expert to return the needed SQL..  And would gladly consider any alternative solution to the listagg limitation.

All pointers and suggestions appreciated ..  I have tested, and yes, there is only one combination (out of a large number) that come anywhere close to the 4000 byte limitation, and it clearly does exceed.. It is entirely possible that future data entry could cause additional  track_thing_id/thing_id combinations to exceed the limitation.
0
DECLARE
 alert VARCHAR2(100);
      rowcount      number;

begin
alert:=show_alert('FLAG_ALERT');
 
if (alert=Alert_button2)then
exit_form;

elsif (x=Alert_button1)then
      SELECT COUNT(*) INTO rowcount FROM om_customer WHERE cust_credit_ctrl_yn = :Rep_value_3;
      
      if (rowcount>0) then
      UPDATE om_customer SET cust_credit_ctrl_yn='Y'
   WHERE CUST_CODE IN
   (SELECT CUST_CODE FROM
   (SELECT * FROM om_customer
   WHERE cust_credit_ctrl_yn= :Rep_value_3
   and cust_code= :Rep_value_2));
      else
0
Hi Expert,

The following plsql code works fine.
But, if I un-comment the commented codes, it fails with error "ORA-20001: comma-separated list invalid near ".

Any advice woold be appreciated.

Thanks a lot!

Luchuan
--------------------------------------
DECLARE
   v_InitialString VARCHAR2(100) := 'Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George';
   v_Table DBMS_UTILITY.UNCL_ARRAY;
    v_FinalString VARCHAR2(100);
    v_TabLen BINARY_INTEGER;
 BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial string: ' || v_InitialString);
    DBMS_UTILITY.COMMA_TO_TABLE(v_InitialString, v_TabLen, v_Table);
    DBMS_OUTPUT.PUT_LINE('Table (length = ' || v_TabLen || '):');
    FOR v_Count IN 1..v_Table.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_Table(v_Count));
    END LOOP;
 /*  
    v_InitialString := '1,2,3';
    DBMS_OUTPUT.PUT_LINE('Initial string2: ' || v_InitialString);
    DBMS_UTILITY.COMMA_TO_TABLE(v_InitialString, v_TabLen, v_Table);   -- the line cause error
    DBMS_OUTPUT.PUT_LINE('Table (length = ' || v_TabLen || '):');
    FOR v_Count IN 1..v_Table.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_Table(v_Count));
    END LOOP;
 */
 END;
0
The following code correctly develops the needed concatenation. I need to learn how to order the concatenation.

In this test case, I need to order, within the player_concat  field, the yanks first, (if there is a yanks value), then the birds(if there is a birds value), and then the stros.   And of course, the actual data has thousands of records, so I need to leave nothing to chance,.

Any and all pointers & suggestions appreciated!!


create table players
(
  pid        number(19),
  player_id  number(19),
  team_id    number(19),
  league     varchar2(9 byte)
);

create table teams
(
  tid   number(19),
  team  varchar2(50 byte)
);

Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (1, 9, 4, 'AL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (2, 9, 7, 'NL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (3, 9, 2, 'AL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (4, 7, 2, 'AL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (5, 7, 4, 'AL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (6, 4, 7, 'NL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (7, 5, 2, 'AL');
Insert into PLAYERS
   (PID, PLAYER_ID, TEAM_ID, LEAGUE)
 Values
   (8, 6, 4, 'AL');
COMMIT;

insert into teams
   (tid, team)
 values
   (2, 'birds');
insert into teams
   (tid, team)
 values
   (4, 'yanks');
insert into teams
   (tid, team)
 

Open in new window

0
I have a plsql unit which loads the table. It's a simple insert statement but only difference is the select is on a pipelined function. It looks as follows:

begin
insert /*+ append */ into TARGET_TAB
select
*
from
table(PIPELINED_FN(cursor(select /*+ parallel(s) */ * from SOURCE_TAB s)));
commit;
end;
/

This is working as intended. I see the function in action where the input records are processed in parllel slave processes and the output is streamed as soon as the records are processed.

Only issue is all the records are committed into target after all the rows are returned from the function

 How to commit the rows immediately  after the records are available from parallel processes?
0
Good Afternoon

I am using an Oracle Form (10g) and I would like to return multiple records in a field rather than just returning one row.  I tried Bulk Collection but I received a client side error.
0
Hi,

I am not clearly understanding the difference between Inter and Intra UoM converaions. I have read oracle doc and some brief examples but still nto very clear. Can someone please explain this in more details.

Thanks
Minzee
0
I'm running Oracle 9i and on Windows 2003 Server.  This is a legacy application that was setup by an old administrator and I need to access to recover the data to move.  I found the command for sqlplus and to use /nolog but SQLplus requires an admin and password.  If I have physical access to the server how to I reset the Oracle Admin login?  Or which login do i need to try to reset to be able to access features like the Database, exporting, backup and so on.  It seems they locked every place to access, Oracle enterprise console, SQLPlus, and the Database itself.  I assume once I get into enterprise console I can just reset the DB password myself if we even need to.  I'm just trying to get if off this server before it crashes.
0
I have a lengthy query that creates a table then fills it with about 20 INSERT statements from various other sources.

When I edit the query at times all the various pieces of the query open.  Is it possible to collapse the drill in with a single key stroke or mouse click?

mlmcc
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

We are trying to connect to an older Oracle 8i database from SQL 2014 64 bit  running on Windows 2012 64 bit. We were able to connect from a 32 bit sql 2005 server. I do not have the 10g 64 bit client which i believe i need.   Questions are , what can i do to connect the database ? is there some other files i need? is there a 10g 64 bit client anywhere ? Oracle does not have them on there public site.
0
hello,

first time here so hopefully someone can help.
I have two tables in one table it has a column with a number of digits ie 1 to 34...
in the other table is a column in numbers that differ in various lengths.

without doing case statements for all 34 number is there another way to say if the column in table 1 has 4 and table 2 has 3 digits can you format the number to look like 4 digits

another example
column in table 1 has 10 and table 2 has 8 digits format the number to look like 10 digits with leading zeros.

Many thanks

Mac
0
Hi Experts,

I have a below table creation statement.
   CREATE TABLE INF_DDA
   (	"TXNKEY" NUMBER(14,0) NOT NULL ENABLE,
		"GROUPID" VARCHAR2(8 BYTE), 
		"ACCTIDENTIFIER" NUMBER(18,0) NOT NULL ENABLE, 
		"REFNUMBER" VARCHAR2(50 BYTE), 
		"MODIFIEDDATE" DATE,
		"COMPLETEDDATE" DATE,
		CONSTRAINT "PK_INF_TRANXIDKEY" PRIMARY KEY ("TXNKEY"), 
		CONSTRAINT "UNQ_INF_DDA" UNIQUE ("TXNKEY", "GROUPID", "ACCTIDENTIFIER", "REFNUMBER")
   );

Open in new window



NOW I WANT TO ADD INDEXES FOR THE PrimaryKey and Unique Keys
 Do i need to add indexes or oracle will do implicitly for both p.key and unique keys?
 
 Why because after i created table i could see the indexes tab(oracle sql developer) these indexes are added.
 Now i have confusion like
 primarykey by default index will be added
 how unique keys also indexes are added?

THANKS,
0
Hello Experts,

I have a view which is working fine in one instance with same table structure but in other instance it is failing with ORA-01830  Error .
A view is created on top of another view.
0
Hi gurus,

Had a discussion with Oracle folks and he said that Oracle Golden Gate is a DR solution, where as I don't agree.

Bringing it here, so can see, what others have to say, obviously along with justification.

Regards
MK
0
Hello,

I am trying to parse a JSON format data that is stored in oracle CLOB data column. There are multiple/nested objects in the JSON data so I need a query to automatically get all the object/parent name and the value related. Please see attached is the sample example of the JSON data  and the expected output.

I tried the below query but it only gives the value and not the object/Key name.

SELECT jt.*
FROM   JSON_DOCUMENT_lob a,
       JSON_TABLE(json_msg, '$."Additional Statement No".*'
         COLUMNS ( 
           "Attribute/Parent" varchar2(32000) PATH '$.parent()',
           "Value" varchar2(32000) PATH '$'
       )) "JT";

Open in new window


Please assist.

Thank you
SampleData.txt
Book2.xlsx
0
Hi:

 How can I view the full SQL definition of a table in Orqacle SQL Developer? It seems to have some char length restriction when looking at the SQL tab of its definition.
0
Hi,

I have a date field in query in Oracle and I am having trouble pulling out the quarter.  Here is the code:
Select --URA_DOC_ITEM.prod_num,
--URA_PROD.SKU, 
URA_DOC_ITEM.CAT_OBJ_NAME, URA_DOC_ITEM.PROD_GRP_ID,
URA_DOC_ITEM.member_id_updated, URA_DOC_ITEM.date_updated,
  fn.FLD_VALUE,
  fd.FLD_VALUE,
    URA_DOC.STRUCT_DOC_TYPE DOC_TYPE,
 MAX(trunc(URA_DOC_ITEM.start_date)) STARTDT,  

  MAX(URA_DOC_ITEM.BASE_PRICE) URA,
 max( amp.BASE_PRICE) as amp,
 max( bp.BASE_PRICE) as BP,
 max( amp.BASE_PRICE)  - max( bp.BASE_PRICE) as CALCURA--,
 --max( amp.BASE_PRICE)  - max( bp.BASE_PRICE)/ CWAC.base_price as DISC

 
 
from    mmasw_cnt.MN_STRUCTURED_DOC URA_DOC,
        mmasw_cnt.MN_STRUCT_LINE_ITEM URA_DOC_ITEM,
         MMASW_PRD.MN_ITEM URA_PROD,
	  mmasw_prd.mn_Cat_Map m

Open in new window


I'd like to represent the line
MAX(trunc(URA_DOC_ITEM.start_date)) STARTDT   as YYYYQ, but I cannot get it to work without an error.

I have used EXTRACT and TRUNC but to no avail. Any ideas?

-wasmithpfs
0
hi Experts,

created master.sql file added below sql files:

@C:\script\xxx\aa.sql(file having one insert query)
@C:\script\xxx\bb.sql(file having one insert query)

when i execute the master.sql no insertions happened

some one suggest me how to do
0
Free Tool: Subnet Calculator
LVL 10
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Fact: I have access to an Oracle database/schema. One field consists of data joined together with a slash between each cost center.

Each field consists of data cost centers of varying length/depth

"/Production/Room 1/Machine 1"
or
"/Productiion2/Room2/Machine2/thingamajig3/detailx"
or
" " (i.e. an empty field i.e. null
or
"/Car2"
etc

I would like to break this into several columns fields. How do I fix this in the Oracle database?
0
HI EXPERTS,


XMLS:

<?xml version="1.0" encoding="UTF-8"?>
<parameters>
<flag>update</flag>
</parameters>

<?xml version="1.0" encoding="UTF-8"?>
<parameters>
<flag>update2</flag>
</parameters>

<?xml version="1.0" encoding="UTF-8"?>
<parameters>
<flag>update3</flag>
</parameters>

select viewid, pfxml,extract(xmltype(pfxml),'/parameters/flag/text()') as flag from inf_views where VIEWDESC like '%Report%';

o/p:
id and flag value i am getting for all 3 mentioned xmls
i

now i want to get only xml that containse update2 value
<flag>update2</flag>
0
Hi!

I have studied this example on how to use the decode-function on  https://www.techonthenet.com/oracle/functions/decode.php

I (almost) understand the example but I don´t understand why, they only place the ABS-function on the right side of the subtraction:

DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

I get the same result when I do like this (other database then in the example from techonthenet) but maybe, it´s likely to be wrong with other data:

abs(sysdate - hire_date) - ABS(sysdate - hire_date)

or

(sysdate - hire_date) - (sysdate - hire_date)
0
I am using an Oracle database, I couldn't select anything except Microsoft SQL as a topic.

I created a table with all records that matched a certain criteria. The table contains a customer number and a field with either Y or N along with other fields. This table can contain the same customer number several times. I am looking to select only a single customer number that never had a Y.
WD.csv
0
CREATE TABLE MIPP2
(
  TI_ID          VARCHAR2(12 BYTE),
  PR_ID          VARCHAR2(12 BYTE),
  SERIAL_NUMBER  NUMBER(8)
);

CREATE TABLE PRODLIC3
(
  P_ID    VARCHAR2(12 BYTE),
  P_CODE  VARCHAR2(12 BYTE),
  CMP_ID  NUMBER(8)
);

CREATE TABLE PROD_PERMIT4
(
  PRD_ID            NUMBER(8),
  CMP_ID            NUMBER(8),
  PERMIT_STATUS_ID  NUMBER(8)
);

CREATE TABLE TIP_STATUS5
(
  STATUS_ID  NUMBER(8),
  STATUS     VARCHAR2(12 BYTE)
);

CREATE TABLE TRACK_ITEM1
(
  TRACK_ITEM_ID  NUMBER(8),
  CMP_ID         NUMBER(8)
);

Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('11', '8', 452);
Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('12', '96', NULL);
Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('13', '9', NULL);
Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('11', '4', 19);
Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('11', '9', 999);

Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('8', '66', 5);
Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('96', '55', 6);
Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('90', '45', NULL);
Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('4', '54', 5);
Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('9', '45', 5);

Insert into PROD_PERMIT4
   (PRD_ID, CMP_ID, PERMIT_STATUS_ID)
 Values
   (8, 5, 1);
Insert into PROD_PERMIT4
   (PRD_ID, CMP_ID, PERMIT_STATUS_ID)
 Values
   (96, 6, 2);
Insert into 

Open in new window

0
Listing this anew in hopes that if I ask a better question, I may get a helpful response from the resident experts.  

I need to use listagg and concatenation to create a field that begins with the track_item_id, adds any and all p_code, add any and all serial_number, and then places the status for each track_item_id,  p_code / serial_number combination.  

There are thousands of records, the example attempts to bring the logic definition to the barest possible groupings.

 To get there, take a cmp_id from the track_item1 table, add a product id from the mipp2 table.  See if those two fields define a record in a third prodlic3 table…  If so, list the p_code and serial_number, then pick up the status_id from there and use it to determine the status coming from pm_product_permit_status table..

Insert the status value into the current concatenation....placing the status after the serial number .. If there is no serial number, add a comma in front of the status

Product status
The prod_permit4 table carries a perrmit_status_id and the tip_status5 table carries the coded status (done--notdone--ignored--etc.)

There will be one only status for each combination. I have tried adding to the concatenation, but find it time I ask for help in putting together the SQL to get it right.

Any and all pointers and suggestion will be greatly appreciated……..
-----------------------------
        •   For each product, take cmp_id from track_item1 table and one of the PRD_ID …
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.