Error 904 sqlstate 42000

Can below scenario of duplicate alias lead to an error when executed from JDBC or hibernate: SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier select * From table_master VW LEFT O...

Can below scenario of duplicate alias lead to an error when executed from JDBC or hibernate:

SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

select * From table_master VW
LEFT OUTER JOIN TABLE(test_func(1, 300)) vw 
ON VW.table_key = vw.function_key

Facing this in production only. It works fine in test environment.

Daniel's user avatar

Daniel

10.3k12 gold badges42 silver badges81 bronze badges

asked Sep 2, 2016 at 17:16

sam_evang's user avatar

5

In my case the DB field didn’t exist, and it was returning
SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

After Creating the field, of course it worked,

Double check those fields and make sure they are matching…

Hope it may give you a clue to find the issue

answered Aug 13, 2019 at 23:24

Davel_AG's user avatar

In my case I missed @ManyToOne(fetch = FetchType.EAGER) on that @JoinColumn

answered Oct 7, 2019 at 12:40

kolle_86's user avatar

kolle_86kolle_86

3572 silver badges7 bronze badges

Verify that the user with which you are executing the query has the necessary grants for the test_func() function.

answered Oct 14, 2021 at 17:43

JoseJC's user avatar

JoseJCJoseJC

7281 gold badge9 silver badges20 bronze badges

Источник

Содержание

  1. SQL Error [904] [42000]: ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier on Oracle 9.2.0.8 Database #10647
  2. Comments
  3. 904 : 42000 : java.sql.SQLException: ORA-00904: «EMPLOYE_STATUS_NK»: invali
  4. Comments
  5. SQL error -904 accessing : [IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904
  6. QlikView App Dev
  7. SQLSTATE 42000 (Ошибка 102) Неверный синтаксис рядом с ‘)’

SQL Error [904] [42000]: ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier on Oracle 9.2.0.8 Database #10647

I Just download the lastest EA 7.3.0 version, connect to a 9.2.0.8 Oracle database, try to monitor the Blocking Sessions and it show up:

SQL Error [904] [42000]: ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier
Error : 904, Position : 445, Sql = , Error Msg = ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier

SELECT
wsession.sid waiting_session,
wsession.serial# serial,
wsession.logon_time,
wsession.blocking_session_status,
wsession.event,
wsession.username waiting_user,
wprocess.pid wait_pid,
nvl(obj.object_name, ‘-‘) oname,
nvl(obj.owner, ‘-‘) owner,
wsession.row_wait_block# row_lock,
wsession.blocking_session holding_session,
hprocess.pid hold_pid,
hsession.username holding_user
FROM
v$session wsession
JOIN v$session hsession ON
wsession.blocking_session = hsession.sid
JOIN v$process wprocess ON
wprocess.addr = wsession.paddr
JOIN v$process hprocess ON
hprocess.addr = hsession.paddr
LEFT JOIN dba_objects obj ON
obj.object_id = wsession.row_wait_obj#
WHERE
wsession.blocking_session IS NOT NULL
UNION
SELECT
wsession.sid waiting_session,
wsession.serial# serial,
wsession.logon_time,
wsession.blocking_session_status,
wsession.event,
wsession.username waiting_user,
wprocess.pid wait_pid,
nvl(obj.object_name, ‘-‘) oname,
nvl(obj.owner, ‘-‘) owner,
wsession.row_wait_block# row_lock,
nvl(wsession.blocking_session, 0) holding_session,
nvl(hprocess.pid, 0) hold_pid,
nvl(hsession.username, ‘-‘) holding_user
FROM
v$session wsession
LEFT JOIN v$session hsession ON
wsession.blocking_session = hsession.sid
JOIN v$process wprocess ON
wprocess.addr = wsession.paddr
LEFT JOIN v$process hprocess ON
hprocess.addr = hsession.paddr
LEFT JOIN dba_objects obj ON
obj.object_id = wsession.row_wait_obj#
WHERE
wsession.sid IN (
SELECT
blocking_session
FROM
v$session)

ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier

The text was updated successfully, but these errors were encountered:

Источник

904 : 42000 : java.sql.SQLException: ORA-00904: «EMPLOYE_STATUS_NK»: invali

I am new to ODI. I have been getting this error *904 : 42000 : java.sql.SQLException: ORA-00904: «EMPLOYEE_STATUS_NK»: invalid identifier *. I am using an interface to move data from staging to target datastore. Both staging and target are oracle tables. In my target table I have three columns. First once references an oracle sequence and being exec in targer as sequence is created in target. Seond column is a natural key column and is also defind as a key. Third column is simple just showing desc of emplyee. I am using LKM Oracle to Oracle (for staging table) and IKM Oracle Incremental Update for target datastore.

I will apprecciate If someone can help me with this.

Following are complete error details

ava.sql.SQLException: ORA-00904: «EMPLOYEE_STATUS_NK»: invalid identifier

at java.lang.Thread.run(Unknown Source)

Edited by: Greenwich on Mar 15, 2010 11:00 AM

Edited by: Greenwich on Mar 15, 2010 5:52 PM

Well this looks like that one of your columns or tables are not in the respective database.

Please review the query (step) on which the process has failed , and check if the column EMPLOYE_STATUS_NK is available in the target or source.

Thanks for qucik reply. First column is only present in targer so executing it in target as it’s a reference to an Oracle Sequence. Third column employee_description is mapped to a table in stagin.

I confirmed ; column EMPLOYE_STATUS_NK is present in target database and mapped with primary key of source table. I am executing on Source. This column is also marked as key in target datastore. It still give same error.

quicker way to resolve this will be if you can share the SQL in this step where you are getting this error.

Some points to check —

if it’s a seq number make sure it runs on «target» , it should be «check not null» and of course the Sequence is present in the database (here in target).

I closely followed the sql as you mentioned and figured out why I was getting this error. I had renamed a column in the target database. I added an «_» in column name. ODI had intially created a error table for this table, however ot again tried to create a new table but with a warning that table ALREADY EXISTS.
This table still had old table defination and would give an error «invalid and name of column.

Источник

SQL error -904 accessing : [IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904

we are importing a Basis queue from SAPKB620053 to SAPKB620063 but the importing job dead (RC 12) with the following reason:

Start import R3TRFUGRVSCAN_RFC .

sap_dext called with msgnr 4:

key: FUVSCAN_RFC_INIT DT0002

SQL error -904 accessing : [IBM][CLI Driver][DB2] DSNT408I SQLCODE = -904, ER

RESOURCE 00000304, AND RESOURCE NAME SX40XAAA.DOKCLU .X’01815E’ ‘.X’03’

DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 102 13172746 0 13813475 -1010298874 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X’00000066′ X’00C9000A’ X’00000000′ X’00D2C6E3′ X’C3C81006′ X’00000000′ SQL DIAGNOSTIC

End date and time : 20071204172624

Ended with return code: ===> 12 > Mandatory: Your SAP system runs on DB2 V7. If it runs on DB2 V6 or

V8, this note does not apply.

For note 565490: we have checked the environment and update the

For note 355213: the parameters LOCKMAX = 1.000.000 and NUMKUSR =

2.097.152 (The maximum Value) >> The system parameter NUMLKTS sets the

default for the tablespace attribute LOCKMAX. . If the number of locks

on a particular tablespace exceeds the LOCKMAX value, these locks will

be replaced by a single tablespace-scope lock (note that the LOCKMAX is

enforced on a per thread, per tablespace basis).

We have the following parameters:

NUMKUSR = 2.097.152 (The maximum Value)

LOCKMAX = 1000000 for the TS

DSNT501I -DGQF DSNILMCL RESOURCE UNAVAILABLE 015

NAME SX40XAAA.DOKCLU .X’01815D’ ‘.X’17

Anybody can help me to go on with the import queue?

Источник

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

  • Qlik Community
  • :
  • All Forums
  • :
  • QlikView App Dev
  • :
  • SQL##f — SqlState: S0022, ErrorCode: 904, ErrorMsg.
  • Subscribe to RSS Feed
  • Mark Topic as New
  • Mark Topic as Read
  • Float this Topic for Current User
  • Bookmark
  • Subscribe
  • Mute
  • Printer Friendly Page
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Report Inappropriate Content

I am trying to load the below script but getting the error message as

» SQL##f — SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: «YEAR»: invalid identifier».

script : LET vIncrementalExpression = ‘Where a.customer not in (»A»,»B»,»C») and YEAR (salhist.dated) >= Year (GETDATE ()) — 3’;

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Report Inappropriate Content

I got the answer. when you query YEAR (INVOCIEDATE) is not valid in oracle. so i tried EXTRACT (YEAR from INVOICEDATE) and its working fine.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Report Inappropriate Content

Please post the script snippet where you use this variable.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Report Inappropriate Content

Hi, below is the script.

LET vQvdExists = if(FileSize(‘$(vQvdFile)’) > 0, -1, 0);

IF $(vQvdExists) THEN

LOAD max(INVOICEDATE) as maxdate //?? Set the name of the date or datetime field

FROM $(vQvdFile) (qvd);

LET vMaxdate = Date(Peek(‘maxdate’,0,’maxdateTab’),’YYYY-MM-DD’);

LET vLast30date = Date(Peek(‘maxdate’,0,’maxdateTab’)-30,’YYYY-MM-DD’);

IF IsNull(vMaxdate) then

LET vIncrementalExpression = ‘Where CUSTOMERCODE not in (»A»,»B»,»C») and INVOICEDATE >=’ & Chr(39)& ‘$(vLast30date)’ &Chr(39) &

Источник

SQLSTATE 42000 (Ошибка 102) Неверный синтаксис рядом с ‘)’

Общая настройка

Это странный вопрос, и я сожалею об этом. Я действительно потратил некоторое время на поиск и анализ нашего кода. У нас есть работа, вызывающая хранимую процедуру, которая выбирает некоторые данные, а затем вызывает другие хранимые процедуры (некоторые имена извлекаются с помощью операторов select, поскольку они могут меняться). Он вызывает около 20 различных этих хранимых процедур примерно в 10 000 раз (суммирует различные вызовы), просто изменяя параметры. Эти хранимые процедуры извлекают некоторые данные, а затем вставляют данные в нашу базу данных.

Версия SQL-Server

Это отлично работало в Microsoft SQL Server 2005, но с тех пор, как мы перешли на SQL-Server 2012 (11.0.3000.0), и эта проблема, похоже, началась с тех пор, или мы просто не знали об этом раньше.

ошибка

Поэтому мы получаем эту ошибку каждый раз, когда мы ее выполняем:

Я знаю, что это очень мало информации, но наши скрипты довольно большие, и я хотел бы спросить, что некоторые из вас могли бы решить эту проблему.

Что я сделал до сих пор

Я просмотрел сценарии, сделал несколько сухих пробегов (b/c весь скрипт работает около часа… (это ночная работа)). Сухие трассы прекрасно работали. Также у нас почти нет открывающих скобок, и они всегда закрываются. Как только он извлекает данные, после часа работы он “сбой” с этой ошибкой…

Итак, что я сделал:

  • Сухой запуск (без фактической загрузки данных, но вызов большинства хранимых процедур) – хорошо, успех
  • Вызывается непосредственно основная процедура магазина (не как работа) – не работала ни
  • Прочтите код, искавшийся для ошибок в скобках – ok, ошибок в скобках не найдено
  • Работало с разными пользователями: db-admin, мой пользователь – не работал ни
  • Поиск через Google/Stackoverflow/littlbe бит через stackexchange в целом

Вопросов

Похоже, все, что должен сделать скрипт, сделано правильно и полностью, поэтому мы не понимаем, почему он не возвращает “успех” и бросает это сообщение об ошибке.

Я мог представить, что может быть поле, которое он извлекает, содержащий escape-символ… это имело бы смысл?

Могу ли я установить универсальную точку останова, что выполнение скрипта сломается, как только появится эта ошибка, и покажите мне, что данные вызывают эту ошибку… например, отладка кода в visual studio?

Поэтому мой главный вопрос: не могли бы вы дать мне подсказку/помочь как лучше подойти к этой ошибке? Что я должен делать?

работа

MY_STOREDPROCEDURE_MAIN

spStartMyNightlyJob

После этого он открывает около 15 различных хранимых процедур, в зависимости от того, какие задачи “определяются” каждый вечер.

Если я прокомментирую фактическое его выполнение

то он завершается без ошибок.

Спасибо уже за то, что посмотрел на мой вопрос 😉

Проблема заключается в вашей 2-й инструкции Insert. Замените свой старый.

Источник

Источник

Error Code: 904, SQL State: 42000] ORA-00904: «G».»NAME»: invalid

When I run the SQL below, I receive the following:

08:00:08 [SELECT — 0 row(s), 0.000 secs] [Error Code: 904, SQL State: 42000] ORA-00904: «G».»NAME»: invalid identifier

When I comment out the BOLD lines, it runs okay. I’ve tried fully qualifying the column with no success. I’m sure it’s a formatting issue.

Select
sr.schoolid,
sr.studentid, sr.coursenumber,
sr.REGISTREQ_ID,
(select
min(rr.ID||’-‘||rr.COURSEGROUP)
from
REGISTREQ rr
where
rr.grade_level = (select s.sched_nextyeargrade from students s where sr.studentid = s.id)
and rr.yearid = 2100
and rr.id not in (6426,6415)
and rr.COURSEGROUP in
(select
g.name
from
gen g
where
g.CAT = ‘coursegroups’
and instr(g.valuet, sr.coursenumber) > 0
and g.schoolid = sr.schoolid)
and ((g.name like ‘%ALT%’ and sr.GLOBALALTERNATECOURSE=1)
or (g.name not like ‘%ALT%’ and sr.GLOBALALTERNATECOURSE=0))
) as New_RegID
from
SCHEDULEREQUESTS sr
where
sr.schoolid = 703
and sr.yearid = 2100

Best Answer

That’s because the lines you’ve highligted are out of scope for the «g» alias which is part of the inner query.

You perhaps just want «name» without the «g.» prefix.

Источник

SQL Error [904] [42000]: ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier on Oracle 9.2.0.8 Database #10647

Comments

luiscarlosbolanhos commented Dec 9, 2020 •

I Just download the lastest EA 7.3.0 version, connect to a 9.2.0.8 Oracle database, try to monitor the Blocking Sessions and it show up:

SQL Error [904] [42000]: ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier
Error : 904, Position : 445, Sql = , Error Msg = ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier

SELECT
wsession.sid waiting_session,
wsession.serial# serial,
wsession.logon_time,
wsession.blocking_session_status,
wsession.event,
wsession.username waiting_user,
wprocess.pid wait_pid,
nvl(obj.object_name, ‘-‘) oname,
nvl(obj.owner, ‘-‘) owner,
wsession.row_wait_block# row_lock,
wsession.blocking_session holding_session,
hprocess.pid hold_pid,
hsession.username holding_user
FROM
v$session wsession
JOIN v$session hsession ON
wsession.blocking_session = hsession.sid
JOIN v$process wprocess ON
wprocess.addr = wsession.paddr
JOIN v$process hprocess ON
hprocess.addr = hsession.paddr
LEFT JOIN dba_objects obj ON
obj.object_id = wsession.row_wait_obj#
WHERE
wsession.blocking_session IS NOT NULL
UNION
SELECT
wsession.sid waiting_session,
wsession.serial# serial,
wsession.logon_time,
wsession.blocking_session_status,
wsession.event,
wsession.username waiting_user,
wprocess.pid wait_pid,
nvl(obj.object_name, ‘-‘) oname,
nvl(obj.owner, ‘-‘) owner,
wsession.row_wait_block# row_lock,
nvl(wsession.blocking_session, 0) holding_session,
nvl(hprocess.pid, 0) hold_pid,
nvl(hsession.username, ‘-‘) holding_user
FROM
v$session wsession
LEFT JOIN v$session hsession ON
wsession.blocking_session = hsession.sid
JOIN v$process wprocess ON
wprocess.addr = wsession.paddr
LEFT JOIN v$process hprocess ON
hprocess.addr = hsession.paddr
LEFT JOIN dba_objects obj ON
obj.object_id = wsession.row_wait_obj#
WHERE
wsession.sid IN (
SELECT
blocking_session
FROM
v$session)

ORA-00904: «WSESSION».»BLOCKING_SESSION»: invalid identifier

The text was updated successfully, but these errors were encountered:

Источник

904 : 42000 : java.sql.SQLException: ORA-00904: «EMPLOYE_STATUS_NK»: invali

I am new to ODI. I have been getting this error *904 : 42000 : java.sql.SQLException: ORA-00904: «EMPLOYEE_STATUS_NK»: invalid identifier *. I am using an interface to move data from staging to target datastore. Both staging and target are oracle tables. In my target table I have three columns. First once references an oracle sequence and being exec in targer as sequence is created in target. Seond column is a natural key column and is also defind as a key. Third column is simple just showing desc of emplyee. I am using LKM Oracle to Oracle (for staging table) and IKM Oracle Incremental Update for target datastore.

I will apprecciate If someone can help me with this.

Following are complete error details

ava.sql.SQLException: ORA-00904: «EMPLOYEE_STATUS_NK»: invalid identifier

at java.lang.Thread.run(Unknown Source)

Edited by: Greenwich on Mar 15, 2010 11:00 AM

Edited by: Greenwich on Mar 15, 2010 5:52 PM

Comments

Well this looks like that one of your columns or tables are not in the respective database.

Please review the query (step) on which the process has failed , and check if the column EMPLOYE_STATUS_NK is available in the target or source.

Thanks for qucik reply. First column is only present in targer so executing it in target as it’s a reference to an Oracle Sequence. Third column employee_description is mapped to a table in stagin.

I confirmed ; column EMPLOYE_STATUS_NK is present in target database and mapped with primary key of source table. I am executing on Source. This column is also marked as key in target datastore. It still give same error.

quicker way to resolve this will be if you can share the SQL in this step where you are getting this error.

Some points to check —

if it’s a seq number make sure it runs on «target» , it should be «check not null» and of course the Sequence is present in the database (here in target).

I closely followed the sql as you mentioned and figured out why I was getting this error. I had renamed a column in the target database. I added an «_» in column name. ODI had intially created a error table for this table, however ot again tried to create a new table but with a warning that table ALREADY EXISTS.
This table still had old table defination and would give an error «invalid and name of column.

Источник

SQL -904 Top Reasons to Get This Error

This is mainly occurred due to work space is not available in the DB2. In the real scenario, If we need to deal with multiple rows, there is enough work space is needed , else we get SQL Code-904. Resolution: (Source IBM Red book)

A new online updatable DSNZPARM, MAXTEMPS, has been added to DSN6SYSP. This DSNZPARM specifies the maximum amount of workfile storage that an agent can use and is specified in units of MBs or GBs. If the DSNZPARM value is specified as 0, then no limit is enforced, which was the default in previous releases.

  • When the total storage used by an agent exceeds the MAXTEMPS value that you specified, a Resource Unavailable message (DSNT501I) and a Resource Unavailable SQLCODE (-904) are issued.

Tthe activity that caused this condition terminates. The -904 message has a new reason code and resource name as shown in the below example.

New resource unavailable information

The MAXTEMPS DSNZPARM can protect your workfile from becoming exhausted by runaway queries and declared global temporary tables, however make sure you have enough 32 KB WORKFILE space allocated to avoid -904 resource unavailable failures for large declared global temporary tables (DGTT) since they cannot span workfiles.

Pertinent APARs on space reuse and performance for DGTT for INSERT/DELETE are PK62009, PK67301, and PK70060.

The current recommendation is to allocate workfiles with secondary extents for use by DGTT.

In-memory workfile support is provided when the final sort output data that would have been stored in a work file is less than the 4 KB or 32 KB pagesize of the selected work file. This means that small sorts are not written out to the work file, but the results are presented directly from the WORKFILE 4 KB or 32 KB buffer.

This usage of in-memory workfile support provides a performance enhancement. In one test measurement, we achieved a 10 to 30% CPU reduction for small sorts. This enhancement is not available for declared global temporary tables (both user-defined and used by DB2) as they are always written to the WORKFILE.

In-memory workfile support is expected to be of most benefit for online transactions with relatively short-running SQL calls in which the number of rows that are sorted can be small.

Источник

Как исправить ошибку: Ошибка SQL [904] [42000]: ORA-00904: «SPAREBOX»: недопустимый идентификатор

Я использую SQL и хочу вычислить сумму в следующем виде:

таблица LOG0055D со столбцом и значением:

при запуске возникает ошибка:

как исправить проблему? огромное спасибо

Очевидно, что это ошибка Oracle, так зачем отмечать 4 разные СУБД?

Вероятно, вам следует задать новый вопрос с соответствующими выборочными данными, желаемыми результатами и четким объяснением проблемы.

Вы не можете использовать столбцы с псевдонимами (полный ящик и запасной ящик) позже при выборе. Повторите вычисления, использованные для их создания, чтобы получить totalbox.

Итак, не могли бы вы изменить его или сделать так, как ожидалось?

У вас несколько ошибок в запросе. Один из них — это ссылка на псевдоним столбца в том же SELECT , где он определен. Второе — это отсутствие GROUP BY .

Итак, я предполагаю, что вы действительно хотите:

Обратите внимание, что я удалил L55.TOTAL_QUANTITY, L55.PACKING_STYLE, L55.QUANTITY_IN_FULL_CTN из SELECT . Эти значения используются в функциях агрегирования, поэтому редко имеет смысл включать их в GROUP BY .

это полезно для меня, как я могу получить целые числа, а не десятичные

@luongkhanh. . . Вы можете использовать cast() для преобразования результатов вычислений в нужный вам тип.

Источник

Источник

Can below scenario of duplicate alias lead to an error when executed from JDBC or hibernate:

SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

select * From table_master VW
LEFT OUTER JOIN TABLE(test_func(1, 300)) vw 
ON VW.table_key = vw.function_key

Facing this in production only. It works fine in test environment.

you’re assigning the vw alias twice? That can’t be right.
– sstan

ya.. it works as oracle don’t complain. But in production not sure why it gives error. Can some oracle schemas be case seneitive.
– sam_evang

I agree that I can get the query to work in Oracle 12c myself (maybe production is on a different version of Oracle?). But in my opinion, that should be the bug. I don’t think the query should be considered valid, it’s ambiguous. If I were you, I would just assign 2 distinct aliases, which is really the only sane thing to do here, and try again with that.
– sstan

What are the exact versions of the two servers (edit your question and add the output of select * from v$version)
– a_horse_with_no_name

2 Answers

In my case the DB field didn’t exist, and it was returning
SQL Error: 904, SQLState: 42000 ORA-00904: : invalid identifier

After Creating the field, of course it worked,

Double check those fields and make sure they are matching…

Hope it may give you a clue to find the issue

In my case I missed @ManyToOne(fetch = FetchType.EAGER) on that @JoinColumn

Источник

Я использую SQL и хочу вычислить сумму в следующем виде:

Таблица LOG0055D со столбцом и значением:

DELVRY_NO         ITM_NAME      TOTAL_QUANTITY       PACKING_STYLE      QUANTITY_IN_FULL_CTN
DVR20101900006    CHP-671R         61                   30                        60

Мой рецепт:

FULLBOX = TOTAL_QUANTITY/PACKING_STYLE (only get integer)
SPAREBOX = TOTAL_QUANTITY - QUANTITY_IN_FULL_CTN
=> TOTALBOX = FULLBOX + SPAREBOX

Мой запрос:

SELECT 
L55.DELVRY_NO
   , L55.ITM_NAME
   , L55.TOTAL_QUANTITY
   , L55.PACKING_STYLE
   , L55.QUANTITY_IN_FULL_CTN
   , SUM(L55.TOTAL_QUANTITY/L55.PACKING_STYLE) AS FULLBOX
   , SUM(L55.TOTAL_QUANTITY - L55.QUANTITY_IN_FULL_CTN) AS SPAREBOX
   , SUM(FULLBOX + SPAREBOX) AS TOTALBOX
FROM  LOG0055D L55

При запуске возникает ошибка:

SQL Error [904] [42000]: ORA-00904: "SPAREBOX": invalid identifier
SQL Error [937] [42000]: ORA-00937: not a single-group group function

Как исправить проблему? огромное спасибо

3 ответа

Лучший ответ

У вас несколько ошибок в запросе. Один из них — это ссылка на псевдоним столбца в том же SELECT, где он определен. Второй — отсутствие GROUP BY.

Итак, я предполагаю, что вы действительно хотите:

SELECT L55.DELVRY_NO, L55.ITM_NAME, 
       SUM(L55.TOTAL_QUANTITY / L55.PACKING_STYLE) AS FULLBOX,
       SUM(L55.TOTAL_QUANTITY - L55.QUANTITY_IN_FULL_CTN) AS SPAREBOX
       (SUM(L55.TOTAL_QUANTITY  /L55.PACKING_STYLE) +
        SUM(L55.TOTAL_QUANTITY - L55.QUANTITY_IN_FULL_CTN)
       ) AS TOTALBOX
FROM LOG0055D L55
GROUP BY L55.DELVRY_NO, L55.ITM_NAME;

Обратите внимание, что я удалил L55.TOTAL_QUANTITY, L55.PACKING_STYLE, L55.QUANTITY_IN_FULL_CTN из SELECT. Эти значения используются в функциях агрегирования, поэтому редко имеет смысл включать их в GROUP BY.


1

Gordon Linoff
4 Апр 2021 в 11:38

Вы можете использовать WITH (и, конечно, добавить GROUP BY в конце, чтобы избежать ошибки ORA-00937)

Я попытался воспроизвести структуру вашей таблицы на основе того, что вы опубликовали:

create table LOG0055D (DELVRY_NO varchar2(100),ITM_NAME varchar2(100),TOTAL_QUANTITY number, PACKING_STYLE number, QUANTITY_IN_FULL_CTN number);

insert into LOG0055D values ('DVR20101900006','CHP-671R',61,30,60);

commit;

SQL> select * from LOG0055D;

DELVRY_NO       ITM_NAME        TOTAL_QUANTITY PACKING_STYLE QUANTITY_IN_FULL_CTN
--------------- --------------- -------------- ------------- --------------------
DVR20101900006  CHP-671R                    61            30                   60

Затем я переписал ваш запрос, используя WITH и GROUP BY

WITH BOXCOUNT AS (                                                         
SELECT ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX
, ROUND(SUM(TOTAL_QUANTITY - QUANTITY_IN_FULL_CTN)) AS SPAREBOX 
FROM LOG0055D
)
SELECT
L55.DELVRY_NO
, L55.ITM_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, BC.FULLBOX
, BC.SPAREBOX
,SUM(BC.FULLBOX + BC.SPAREBOX) AS TOTALBOX
FROM LOG0055D L55, BOXCOUNT BC
GROUP BY 
L55.DELVRY_NO
, L55.ITM_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, BC.FULLBOX
, BC.SPAREBOX;

Поскольку вы написали, если я правильно понял, что вам нужно только целое число, я добавил ROUND к обоим SUM внутри WITH

Вот окончательный результат:

DELVRY_NO       ITM_NAME        TOTAL_QUANTITY PACKING_STYLE QUANTITY_IN_FULL_CTN    FULLBOX   SPAREBOX   TOTALBOX
--------------- --------------- -------------- ------------- -------------------- ---------- ---------- ----------
DVR20101900006  CHP-671R                    61            30                   60          2          1          3


1

Dharman
4 Апр 2021 в 14:08

Вы не можете использовать столбцы с псевдонимами (полный ящик и запасной ящик) позже при выборе. Повторите вычисления, использованные для их создания, чтобы получить totalbox.


0

P.Salmon
4 Апр 2021 в 09:48

Источник

  • How to return an error code of SQL * Plus of PL/SQL procedure

    Hello

    I want to be able to pass a variable back to the Unix script, and I have the following code

    ******************

    sqlplus/myuser mypass < < EOF
    WHENEVER SQLERROR EXIT 41.
    WHENEVER OSERROR EXIT 81;
    execute pkonp.run_onp;
    output
    EXPRESSIONS OF FOLKLORE

    ERRORCODE = $?

    If [$ERRORCODE! = 0] #test for successful onp
    then
    #Notify failure of the administrator of the ONP.
    echo ‘Fail ONP to ‘date’ n’
    FI

    ******************
    However, I am unable to get my PL/SQL to pass an error code to SQL * more.

    How to catch an error in PL/SQL and pass the SQL error code *, so that SQL * Plus can then pass the error to the Unix Shell script?

    Thank you very much.

    Yes — in a nut shell, you need to convert Oracle codes exception to the process of Linux/Unix/Windows exit codes as the number of exception Oracle is physically too large to fit into an integer to a byte that is used by the operating system for the exit codes.

    The best place to do this would be something like a SQL * more script as is the process real o/s which should only return exit code of the kernel.

  • ORA-00904: «I.» «» PROCESS_FLAG «: invalid identifier — I is a var slider

    create or replace PROCEDURE validate_data_proc
    IS

    CURSOR c1
    IS
    SELECT itemname, ICT, msib mtl_system_items_b item_conv_tab_sv itemname1 msib.segment1 icts.item_name
    where icts.item_name! = msib.segment1;
    CURSOR c2
    IS
    SELECT TIC. Nom_element itemname, icts.ORGANIZATION_ID org, msib.segment1 itemname1, msib.organization_id the item_conv_tab_sv ICT, msib mtl_system_items_b org1
    where icts.organization_id = msib.organization_id and icts.item_name! = msib.segment1;

    BEGIN

    I’m IN c1
    LOOP
    -Select i.item_name from i in
    If (c1% notfound) then
    FOR r IN c2
    LOOP
    If (c2% notfound) then

    update of HCCI process_flag = 3 set item_conv_tab_sv where item_conv_tab_sv.item_name = r.item_name;
    on the other

    update of item_conv_tab_sv HCCI set process_flag = 1 where item_conv_tab_sv.item_name = r.item_name;
    end if;
    end loop;
    on the other
    update of HCCI process_flag = 3 set item_conv_tab_sv where item_conv_tab_sv.item_name = i.item_name;
    end if;
    COMMIT;
    end loop;

    END validate_data_proc;

    I use a cursor nested which I create that err
    I get the foll error message,

    Error (23.30): PL/SQL: statement ignored
    Error (23,116): PL/SQL: ORA-00904: ‘R ‘. «» Nom_element «: invalid identifier
    Error (23,118): PLS-00302: component ‘Nom_élément’ must be declared
    Error (26,32): PL/SQL: statement ignored
    Error (26,109): PL/SQL: ORA-00904: ‘R ‘. «» PROCESS_FLAG «: invalid identifier
    Error (26,111): PLS-00302: component ‘PROCESS_FLAG’ must be declared
    Error (30,31): PL/SQL: statement ignored
    Error (30,108): PL/SQL: ORA-00904: «I.» «» PROCESS_FLAG «: invalid identifier
    Error (30,110): PLS-00302: component ‘PROCESS_FLAG’ must be declared
    Error (26,118): PL/SQL: ORA-00904: ‘R ‘. «» Nom_element «: invalid identifier
    Error (26,120): PLS-00302: component ‘Nom_élément’ must be declared
    Error (30,117): PL/SQL: ORA-00904: «I.» «» Nom_element «: invalid identifier
    Error (30,119): PLS-00302: component ‘Nom_élément’ must be declared

    someone… Help, please…

    Find a difference:

    CURSOR c1
    IS
    SELECT icts.item_name itemname , msib.segment1 itemname1 of the item_conv_tab_sv ICT, mtl_system_items_b msib
    where icts.item_name! = msib.segment1;
    CURSOR c2
    IS
    SELECT TIC. Nom_element itemname, icts.ORGANIZATION_ID org, msib.segment1 itemname1, msib.organization_id item_conv_tab_sv cts, mtl_system_items_b msib org1
    where icts.organization_id = msib.organization_id and icts.item_name! = msib.segment1;

    And:

    update of HCCI process_flag = 3 set item_conv_tab_sv where item_conv_tab_sv.item_name = rnom_element;
    Update item_conv_tab_sv set process_flag icss = 3 where item_conv_tab_sv.item_name = i.nom_element;

    But even in this case, your code is wrong for CURSOR loop retrieves a line and stops automatically when there is no lines. Therefore, there is no sense to check notfound % c1 inside the loop for the SLIDER — this will be always FALSE. If your interior for CURSOR LOOP (C2) will never run.

    SY.

  • Need help to resolve the error — below the SQL statement to execute cannot be

    Below is my CO as, which creates a callable statement.

    try {}
    OAApplicationModule oaapplicationmodule = (webBean) pageContext.getApplicationModule;
    OADBTransactionImpl t = (OADBTransactionImpl) oaapplicationmodule.getOADBTransaction ();
    OracleCallableStatement proc = (OracleCallableStatement) t.createCallableStatement (lquery,-1);
    proc. Execute();
    t.Commit ();

    }
    catch (SQLException sqlexception)
    {
    throw OAException.wrapperException (sqlexception);
    }

    After the execution of the page, get the below error… (Please find below stack error)

    I have referred the development guide but did not get something useful.

    Please give me clues about the same.

    Raghu cordially

    — Error Stack ———————————————————

    Error page

    Details of the exception.

    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: Houston-27123: SQL error in the preparation of the call statement. Statement: null
    at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:888)
    at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
    at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
    at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2637)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
    in OA. jspService(OA.jsp:40)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
    to EDU.oswego.cs.dl.util.concurrent.PooledExecutor$ Worker.run (PooledExecutor.java:803)
    at java.lang.Thread.run(Thread.java:534)
    # # 0 in detail
    java.sql.SQLException: the SQL statement to execute cannot be empty or null
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
    at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:1138)
    at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:1054)
    at oracle.jbo.server.DBTransactionImpl.createCallableStatement(DBTransactionImpl.java:3033)
    at cisco.oracle.apps.xxchr.element.server.webui.XXCHRElementSetSearchCO.processFormRequest(XXCHRElementSetSearchCO.java:343)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:799)
    at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
    at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1118)
    at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
    at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
    at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
    at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
    at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
    at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2633)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
    in OA. jspService(OA.jsp:40)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
    to EDU.oswego.cs.dl.util.concurrent.PooledExecutor$ Worker.run (PooledExecutor.java:803)
    at java.lang.Thread.run(Thread.java:534)
    java.sql.SQLException: the SQL statement to execute cannot be empty or null
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
    at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:1138)
    at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:1054)
    at oracle.jbo.server.DBTransactionImpl.createCallableStatement(DBTransactionImpl.java:3033)
    at cisco.oracle.apps.xxchr.element.server.webui.XXCHRElementSetSearchCO.processFormRequest(XXCHRElementSetSearchCO.java:343)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:799)
    at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
    at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1118)
    at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
    at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
    at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:995)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:961)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:816)
    at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
    at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
    at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2633)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
    in OA. jspService(OA.jsp:40)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
    to EDU.oswego.cs.dl.util.concurrent.PooledExecutor$ Worker.run (PooledExecutor.java:803)
    at java.lang.Thread.run(Thread.java:534)

    Published by: Rambeau on Oct 14, 2010 02:58

    check the code stuck by me again

    Connection Conn = pageContext.getApplicationModule (webBean) .getOADBTransaction () .getJdbcConnection ();

    Connection conn = oaapplicationmodule.getOADBTransaction().getJdbcConnection(); //Right one

    Thank you
    -Anil

  • RMAN — 06004:ORACLE error in the recovery catalog. ORA-00904: invalid…

    Hello all,.

    One of our backups RMAN fails with the following error message. Any suggestions would be greatly appreciated.

    ==================================================================================================

    From backup 2008-12-30 22:03:47
    using channel ORA_DISK_1
    RMAN-00571: ===========================================================
    RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =.
    RMAN-00571: ===========================================================
    RMAN-03002: failure of the backup command to 2008-12-30 22:03:47
    RMAN-06004: the recovery catalog database ORACLE error: ORA-00904: invalid identifier

    RMAN >
    RMAN > #BACKUP # ARCHIVELOG delete all ‘% d_bkp_al_ % t_Set % s_Piece %p’ entry FORMAT;
    2 >
    3 > # DELETE ARCHIVELOG until ‘ SYSDATE-7 «;
    4 >
    5 > # check if the database can be restored
    6 > RESTORE DATABASE # VALIDATE;
    7 >
    8 > # check if controlfile can be restored
    9 > #RESTORE # CONTROLFILE to ‘ / backups/admin/custpr/custpr_bkp_cntlfile.ctl’ VALIDATE;
    10 >
    11 > # check if archivelogs for these past two weeks can be restored
    12 > # RESTORE ARCHIVELOG FROM TIME ‘ SYSDATE-7’ VALIDATE ‘;
    13 >
    14 > #-check all backups on backup media are intact
    15 > # CROSSCHECK BACKUP OF DATABASE;
    16 >
    17 > #-display based on a list of files that need to be stored on the retention
    18 > policy #. For this case study, the files that do not have at least 1 backups
    19 > # will be reported.
    20 > REPORT NEED backup.
    RMAN retention policy apply to the order
    RMAN retention policy is set to 7 days recovery window
    Report of the files whose recovery needs more 7 days of archived newspapers
    Days of files name
    —- —— ——————————————————
    RMAN-00571: ===========================================================
    RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =.
    RMAN-00571: ===========================================================
    RMAN-03002: failure of command report at 2008-12-30 22:03:48
    RMAN-06004: the recovery catalog database ORACLE error: ORA-00904: invalid identifier

    RMAN >
    RMAN > #-remove unnecessary backups. This command deletes the backups based on the
    2 > retention policy of #.
    3 > # commented DELETE OBSOLETE — TSM not configured to remove on 68
    4 > #DELETE # OBSOLETE;
    5 >
    6 > #-complete list of the existing backups
    7 > LIST BACKUP;
    RMAN-00571: ===========================================================
    RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =.
    RMAN-00571: ===========================================================
    RMAN-03002: failure of the list command at 2008-12-30 22:03:49
    RMAN-06004: the recovery catalog database ORACLE error: ORA-00904: invalid identifier

    RMAN >
    RMAN > # end of file.
    2 > * end-of-file *.

    RMAN >

    Published by: ORA_UMAIR on December 31, 2008 07:51

    No help is possible.
    You publish a file of newspaper only, and can not see the command that failed.
    Also you include, contrary to the recommendation in the post on Forums label, the complete version number of 4-digit.
    This is important because they either has an error in your script that does not find RMAN or you hit a bug.

    ——
    Sybrand Bakker
    Senior Oracle DBA

  • Impdp: Look to the right, but always an ORA-39087: directory name invalid

    It is quite mysterious, as much as I did it several more times without problems.

    I created the C:OracleImportTest directory on the file system of the server, created a matching object in the directory DP_DIR in Oracle and granted my user read and write access to it. I’ve been staring at it and try different things all morning, but I always get the following results:

    C:test>impdp cgernon/**** DIRECTORY=dp_dir SERVICE_NAME=test1 DUMPFILE=export.dmp LOGFILE=import.log

Import: Release 11.1.0.6.0 - Production on Friday, 27 May, 2011 12:01:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Release 11.1.0.6.0 - Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DP_DIR is invalid

    «But Mercurial!», you say, ‘ surely you screwed something up to the implementation of the directory and either it does not exist or you do not have access to it! I’d be the first to agree that is the most likely cause of the problem, but by making the following queries to check a second time leaves me in a State of complete confusion:

    select * from all_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
----- -------------- -------------------
SYS   DP_DIR         C:OracleImportTest                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    select * from user_tab_privs where table_name = 'DP_DIR';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY 
------- ----- ---------- ------- --------- --------- --------- 
CGERNON SYS   DP_DIR     SYS     READ      NO        NO        
CGERNON SYS   DP_DIR     SYS     WRITE     NO        NO       

    Anyone know of any other conditions that can cause this error, or see what I’m missing here? I’m completely mystified. Thank you!

    Hello

    If you do not use a RAC database, I’m not sure that you use the SERVICE_NAME parameter.

    The SERVICE_NAME is used to specify the Instance (in a CAR) that you want to use for importing:

    http://download.Oracle.com/docs/CD/E11882_01/server.112/e16536/dp_import.htm#CIHHFIDE

    Otherwise, you can specify the complete connection string or set the ORACLE_SID environment variable.

    Hope this helps.
    Best regards
    Jean Valentine

  • Error in the SQL statement when executing ETL of DAC

    Hi all

    I have installed and configured the 7.9.6.3 biapps, then I run the full load of the CRM — loyalty in DAC topic. And I got the error task failed, I check the Session to the Informatica server log files.

    view of $ SEBL_VERT_811.DATAWAREHOUSE. SDE_SBL_Vert_811_Adaptor.SDE_GeographyDimension_Business.log

    DIRECTOR > use VAR_27028 replace value [0] for the variable defined by the workflow/worklet user: [$passInStatus].
    DIRECTOR > VAR_27028 use override the value [DataWarehouse] session parameter: [$DBConnection_OLAP].
    DIRECTOR > VAR_27028 use override the value [SEBL_VERT_811] for the session parameter: [$DBConnection_OLTP].
    DIRECTOR > VAR_27028 use override the value [SEBL_VERT_811.DATAWAREHOUSE. SDE_SBL_Vert_811_Adaptor.SDE_GeographyDimension_Business.log] for the session parameter: [$PMSessionLogFile].
    DIRECTOR > VAR_27028 use override value [1] to parameter mapping: [MPLT_LOAD_W_GEO_DS. [DATASOURCE_NUM_ID$ $].
    DIRECTOR > VAR_27028 use override value for the parameter mapping]: [$$ Hint1].
    DIRECTOR > VAR_27028 use override value for the parameter mapping]: [$$ council2].
    DIRECTOR > session initialization of TM_6014 [SDE_GeographyDimension_Business] to [my Jul 25 17:29:47 2011].
    DIRECTOR > name of the repository TM_6683: [Oracle_BI_DW_Base]
    DIRECTOR > TM_6684 server name: [Oracle_BI_DW_Server]
    DIRECTOR > TM_6686 folder: [SDE_SBL_Vert_811_Adaptor]
    DIRECTOR > Workflow TM_6685: [SDE_GeographyDimension_Business] run the Instance name: Id series []: [260]
    DIRECTOR > mapping TM_6101 name: SDE_GeographyDimension_Business [version 1].
    DIRECTOR > TM_6963 pre 85 Timestamp compatibility is enabled
    DIRECTOR > the TM_6964 Date of the Session format is [HH24:MI:SS DD/MM/YYYY]
    DIRECTOR > TM_6827 [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Storage] will be used as the storage of session directory [SDE_GeographyDimension_Business].
    DIRECTOR > CMN_1802 Session recovery cache initialization is complete.
    DIRECTOR > configuration using [DisableDB2BulkMode, Yes] TM_6708 property
    DIRECTOR > configuration using TM_6708 [ServerPort, 6325] property
    DIRECTOR > configuration using [overrideMpltVarWithMapVar Yes] TM_6708 property
    DIRECTOR > configuration using TM_6708 [SiebelUnicodeDB,[email protected] [email protected]] property

    DIRECTOR > TM_6703 Session [SDE_GeographyDimension_Business] is headed by 64-bit integration Service [node01_hkhgc01dvapp01], [version9.0.1 HotFix2], build [1111].
    MANAGER > PETL_24058 Running score of the Group [1].
    MANAGER > initialization of engine PETL_24000 of parallel Pipeline.
    MANAGER > PETL_24001 parallel Pipeline engine running.
    MANAGER > session initialization PETL_24003 running.
    MAPPING > CMN_1569 Server Mode: [UNICODE]
    MAPPING > code page of the server CMN_1570: [Unicode UTF-8 encoding]
    MAPPING > TM_6151 the session to the sort order is [binary].
    MAPPING > TM_6185 warning. Code page validation is disabled in this session.
    MAPPING > treatment of low accuracy using TM_6156.
    MAPPING > retry TM_6180 blocking logic will not apply.
    MAPPING > TM_6187 Session focused on the target validation interval is [10000].
    MAPPING > TM_6307 DTM error log disabled.
    MAPPING > TE_7022 TShmWriter: initialized
    MAPPING > DBG_21075 connection to the database [ANBDEV], [dwhadmin] users
    MAPPING > Search CMN_1716 [MPLT_LOAD_W_GEO_DS. LKP_W_LST_OF_VAL_G] uses the connection to database [relational: DataWarehouse] in [UTF-8 encoding Unicode] code page
    MAPPING > Search CMN_1716 [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS] uses the connection to database [relational: DataWarehouse] in [UTF-8 encoding Unicode] code page
    MAPPING > DBG_21694 AGG_COUNTRY_CITY_ZIPCODE [0] Partition: size = [1048576] Index cache, data cache size = [2097152]
    MAPPING > TE_7212 increasing [Cache of Index] size of transformation [AGG_COUNTRY_CITY_ZIPCODE] of [1048576] to [2402304].
    MAPPING > TE_7212 increasing [Cache data] size of transformation [AGG_COUNTRY_CITY_ZIPCODE] of [2097152] to [2097528].
    MAPPING > TE_7029 aggregate information: create the new Index and data files
    MAPPING > TE_7034 aggregate information: Index file is [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.idx]
    MAPPING > information aggregated TE_7035: data file is [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.dat]
    MAPPING > TM_6007 DTM initialized successfully for the session [SDE_GeographyDimension_Business]
    DIRECTOR > PETL_24033 all the DTM connection information: [< NO >].
    MANAGER > PETL_24004 from the tasks before the session. : (My Jul 25 17:29:47 2011)
    MANAGER > task PETL_24027 before the session completed successfully. : (My Jul 25 17:29:47 2011)
    DIRECTOR > PETL_24006 from data movement.
    MAPPING > Total TM_6660 Buffer Pool size is 36000000 bytes and block size is 128000 bytes.
    LKPDP_2 > search for DBG_21097 Transformation [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS]: default sql to create the cache of research: SELECT CITY, COUNTRY, POSTAL code, STATE_PROV OF W_GEO_DS of the ORDER BY CITY, COUNTRY, POSTAL code, STATE_PROV

    LKPDP_1 > search for DBG_21312 Transformation [MPLT_LOAD_W_GEO_DS. LKP_W_LST_OF_VAL_G]: search replace sql to create the cache: SELECT W_LST_OF_VAL_G.VAL AS VAL, W_LST_OF_VAL_G.R_TYPE AS R_TYPE FROM W_LST_OF_VAL_G
    WHERE
    W_LST_OF_VAL_G.R_TYPE LIKE ‘% ETL’ ORDER BY R_TYPE, VAL

    LKPDP_1 > TE_7212 increasing [Cache of Index] size of transformation [MPLT_LOAD_W_GEO_DS. LKP_W_LST_OF_VAL_G] of [1048576] to [1050000].
    LKPDP_2 > TE_7212 increasing [Cache of Index] size of transformation [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS] of [20000000] to [20006400].
    LKPDP_2 > TE_7212 increasing [Cache data] size of transformation [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS] of [20000000] to [20004864].
    READER_1_1_1 > DBG_21438 Reader: Source is [ANSDEV], [SIEBEL] users
    READER_1_1_1 > code page Source of BLKR_16051 database connection [SEBL_VERT_811]: [Unicode UTF-8 encoding]
    READER_1_1_1 > BLKR_16003 initialization completed successfully.
    WRITER_1_ * _1 > WRT_8146 author: target’s database [ANBDEV], user [dwhadmin], loose [on] mode
    WRITER_1_ * _1 > WRT_8106 WARNING! Session Mode Bulk — recovery is not guaranteed.
    WRITER_1_ * _1 > code page target database connection [Data Warehouse] WRT_8221: [Unicode UTF-8 encoding]
    WRITER_1_ * _1 > target WRT_8124 W_GEO_DS of Table: SQL INSERT statement:
    INSERT INTO W_GEO_DS(CITY,CONTINENT,COUNTRY,COUNTY,STATE_PROV,ZIPCODE,DATASOURCE_NUM_ID,X_CUSTOM) VALUES (?,?,?,?,?,?,?,?)
    WRITER_1_ * _1 > WRT_8020 No. column that is marked as the primary key for the table [W_GEO_DS]. Updates not supported.
    WRITER_1_ * _1 > connection WRT_8270 #1 target group consists of target (s) [W_GEO_DS]
    WRITER_1_ * _1 > WRT_8003 writer initialization complete.
    READER_1_1_1 > BLKR_16007 player run began.
    WRITER_1_ * _1 > WRT_8005 writer run began.
    WRITER_1_ * _1 > WRT_8158

    START SUPPORT SESSION *.

    Startup load time: my Jul 25 17:29:47 2011

    Target table:

    W_GEO_DS

    READER_1_1_1 > RR_4029 SQ [SQ_S_ADDR_ORG] User Instance specified SQL query [SELECT DISTINCT
    S_ADDR_ORG. CITY,
    S_ADDR_ORG. COUNTRIES,
    S_ADDR_ORG. COUNTY,
    S_ADDR_ORG. PROVINCE,
    S_ADDR_ORG. STATE,
    S_ADDR_ORG. Zip code
    ‘0’ AS X_CUSTOM
    Of
    V_ADDR_ORG, S_ADDR_ORG
    ]
    READER_1_1_1 > RR_4049 SQL query sent to the database: (my Jul 25 17:29:47 2011)
    READER_1_1_1 > CMN_1761 Timestamp event: [lun 25 juil 17:29: 47 2011]
    READER_1_1_1 > RR_4035 SQL Error]
    ORA-00942: table or view does not exist

    Database driver error…
    Function name: run
    Stmt SQL: SELECT DISTINCT
    S_ADDR_ORG. CITY,
    S_ADDR_ORG. COUNTRIES,
    S_ADDR_ORG. COUNTY,
    S_ADDR_ORG. PROVINCE,
    S_ADDR_ORG. STATE,
    S_ADDR_ORG. Zip code
    ‘0’ AS X_CUSTOM
    Of
    V_ADDR_ORG, S_ADDR_ORG

    Fatal error Oracle
    Database driver error…
    Function name: run
    Stmt SQL: SELECT DISTINCT
    S_ADDR_ORG. CITY,
    S_ADDR_ORG. COUNTRIES,
    S_ADDR_ORG. COUNTY,
    S_ADDR_ORG. PROVINCE,
    S_ADDR_ORG. STATE,
    S_ADDR_ORG. Zip code
    ‘0’ AS X_CUSTOM
    Of
    V_ADDR_ORG, S_ADDR_ORG

    [Error fatal Oracle].
    READER_1_1_1 > CMN_1761 Timestamp event: [lun 25 juil 17:29: 47 2011]
    READER_1_1_1 > BLKR_16004 ERROR: prepare failed.
    WRITER_1_ * _1 > WRT_8333 roll back all the targets due to the fatal error of session.
    WRITER_1_ * _1 > rollback WRT_8325 Final, executed for the target [W_GEO_DS] at end of load
    WRITER_1_ * _1 > WRT_8035 of full load time: my Jul 25 17:29:47 2011

    SUMMARY OF THE LOAD
    ============

    WRT_8036 target: W_GEO_DS (Instance name: [W_GEO_DS])
    WRT_8044 responsible for this target data no.

    WRITER_1__1 > WRT_8043 * END LOAD SESSION *.
    MANAGER > PETL_24031
    PERFORMANCE INFORMATION FOR TGT SUPPORT ORDER [1] GROUP, SIMULTANEOUS GAME [1] *.
    Thread [READER_1_1_1] created [stage play] point score [SQ_S_ADDR_ORG] is complete. Running time total was enough for significant statistics.
    [TRANSF_1_1_1] thread created for [the scene of transformation] partition has made to the point [SQ_S_ADDR_ORG]. Running time total was enough for significant statistics.
    [TRANSF_1_2_1] thread created for [the scene of transformation] partition has made to the point [AGG_COUNTRY_CITY_ZIPCODE]. Running time total was enough for significant statistics.
    Thread [WRITER_1_ * _1] created for [the scene of writing] partition has made to the point [W_GEO_DS]. Running time total was enough for significant statistics.

    MAPPING > CMN_1791 size which would take [0] groups total lines of entry for [AGG_COUNTRY_CITY_ZIPCODE], in memory, it is [0] index cache bytes
    MAPPING > CMN_1790 cached data size that would [0] groups total lines of entry for [AGG_COUNTRY_CITY_ZIPCODE], in memory, bytes [0]
    MAPPING > CMN_1793 index cache size which would hold [0] lines in the table to search for [MPLT_LOAD_W_GEO_DS. LKP_W_LST_OF_VAL_G], in memory, is bytes [0]
    MAPPING > CMN_1792 cached data size that would [0] lines in the table to search for [MPLT_LOAD_W_GEO_DS. LKP_W_LST_OF_VAL_G], in memory, is bytes [0]
    MAPPING > CMN_1793 index cache size which would hold [0] lines in the table to search for [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS], in memory, is bytes [0]
    MAPPING > CMN_1792 cached data size that would [0] lines in the table to search for [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS], in memory, is bytes [0]
    MANAGER > PETL_24005 from tasks after the session. : (My Jul 25 17:29:47 2011)
    MANAGER > task of PETL_24029 after the session completed successfully. : (My Jul 25 17:29:47 2011)
    MAPPING > cache TE_7216 deleting files [PMLKUP14527_524289_0_260L64] for processing [MPLT_LOAD_W_GEO_DS. LKP_W_LST_OF_VAL_G].
    MAPPING > cache TE_7216 deleting files [PMLKUP14527_524293_0_260L64] for processing [MPLT_LOAD_W_GEO_DS. LKP_W_GEO_DS].
    MAPPING > TM_6018 the session completed with errors of processing row [0].
    MANAGER > TE_7216 deleting files cache [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.idx] for [AGG_COUNTRY_CITY_ZIPCODE] transformation.
    MANAGER > TE_7216 deleting files cache [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.dat] for [AGG_COUNTRY_CITY_ZIPCODE] transformation.
    MANAGER > parallel PETL_24002 engine Pipeline completed.
    DIRECTOR > Session PETL_24013 run duly filled with failure.
    DIRECTOR > TM_6022

    PLENARY OF THE LOAD
    ================================================

    DIRECTOR > TM_6252 Source load summary.
    DIRECTOR > Table CMN_1740: [SQ_S_ADDR_ORG] (name of the Instance: [SQ_S_ADDR_ORG])
    Output [0] lines, affected lines [0], applied [0] lines, rejected lines [0]
    DIRECTOR > TM_6253 Target Load summary.
    DIRECTOR > Table CMN_1740: [W_GEO_DS] (name of the Instance: [W_GEO_DS])
    Output [0] lines, affected lines [0], applied [0] lines, rejected lines [0]
    DIRECTOR > TM_6023
    ===================================================

    DIRECTOR > TM_6020 Session [SDE_GeographyDimension_Business] to [my Jul 25 17:29:48 2011].

    After reviewing the log, I found the select statement fails, the SQL below was wrong:

    SELECT DISTINCT
    S_ADDR_ORG. CITY,
    S_ADDR_ORG. COUNTRIES,
    S_ADDR_ORG. COUNTY,
    S_ADDR_ORG. PROVINCE,
    S_ADDR_ORG. STATE,
    S_ADDR_ORG. Zip code
    ‘0’ AS X_CUSTOM
    Of
    V_ADDR_ORG, S_ADDR_ORG

    There is no V_ADDR_ORG but the table S_ADDR_ORG in the transaction of Siebel database. So I don’t know why he build this sql when data transfer to OBAW. It is the fate of bi box app.

    Experts! How could I solve this problem? Please help, thanks a lot!

    Best regards
    Ryan

    Ryan,

    Yes, you missed to create views for the source tables. both in the design of dac > tables tab, select any table and clieck right on it and then click capture of change scripts > generate scripts from view. It will ask you if it can generate a script for all tables. so it will generate the script from view for you. now run the whole esript in the source database. then your problem will be solved.

    If this answers your question. do my correct answer.

    Thank you
    Jay.

  • The rights of the applicant, change of context (PL/SQL-&gt; SQL-&gt; PL/SQL) and ORA-00904

    Hello

    I’m doing a validation test of principle with regard to the rights of the applicant. I created 2 diagrams of Test (DEFINER1 and INVOKER1) for the same thing. When I call a procedure in the Invoker1 Definer1 and the procedure has, in turn, a SQL statement inside that a PL/SQL function calls, so he get ORA-00904 «» is not a valid identifier. All procedures/packages in the scheme of Definer1 are created as the rights of the applicant. Simplified script below for the same, kindly let me know if I make a mistake, it’s the expected behavior, or miss me something.

    set line 10000

    Set feedback off

    ——————————————————————————————————————————

    QUICK problem: error ORA-00904: «object name»: invalid identifier

    CALLS while the use of the appellant’s rights, procedure call PL/SQL that has a SQL that calls a PL/SQL function.

    ——————————————————————————————————————————

    ——————————————————————————————————————————

    GUEST

    GUEST

    ——————————————————————————————————————————

    Creating patterns DEFINER1 and INVOKER1 QUICK…

    GUEST

    ——————————————————————————————————————————

    CREATE DEFINER1 USER IDENTIFIED BY DEFINER1

    TEMPORARY TEMP TABLESPACE

    /

    GRANT CREATE SESSION TO DEFINER1

    /

    GRANT CREATE PROCEDURE FOR DEFINER1

    /

    CREATE INVOKER1 USER IDENTIFIED BY INVOKER1

    TEMPORARY TEMP TABLESPACE

    /

    GRANT CREATE SESSION TO INVOKER1

    /

    GRANT CREATE PROCEDURE FOR INVOKER1

    /

    ——————————————————————————————————————————

    GUEST will now connect to the DEFINER1 schema…

    GUEST

    CONNECT DEFINER1/DEFINER1

    ——————————————————————————————————————————

    Creating QUICK package (with rights of Summoners) with 2 functions simple (one with PRAGMA RESTRICT_REFERENCES another without him.)…

    GUEST

    ——————————————————————————————————————————

    CREATE or REPLACE PACKAGE global_pkg

    AUTHID CURRENT_USER

    AS

    FUNCTION add2 (NUMBER of p1

    NUMBER of p2

    )

    RETURN NUMBER;

    FUNCTION add2_rr (NUMBER of p1

    NUMBER of p2

    )

    RETURN NUMBER;

    PRAGMA RESTRICT_REFERENCES (add2_rr, WNDS, WNPS, NSOR, RNP);

    END global_pkg;

    /

    —————————————————————

    CREATE OR REPLACE PACKAGE BODY GLOBAL_PKG

    AS

    —————————————————————

    FUNCTION add2 (NUMBER of p1

    NUMBER of p2

    )

    RETURN NUMBER

    IS

    BEGIN

    RETURN (p1 + p2);

    END add2;

    —————————————————————

    FUNCTION add2_rr (NUMBER of p1

    NUMBER of p2

    )

    RETURN NUMBER

    IS

    BEGIN

    RETURN (p1 + p2);

    END add2_rr;

    —————————————————————

    END global_pkg;

    /

    ——————————————————————————————————————————

    ENCOURAGE creating stand-alone simple function (with rights of Summoners)…

    GUEST

    ——————————————————————————————————————————

    FUNCTION to CREATE or REPLACE add2 (NUMBER of p1

    NUMBER of p2

    )

    RETURN NUMBER

    AUTHID CURRENT_USER

    IS

    BEGIN

    RETURN (p1 + p2);

    END ADD2;

    /

    ——————————————————————————————————————————

    PROMPT creation of procedure (with rights of Summoners) who calls the Package and functions…

    GUEST

    ——————————————————————————————————————————

    CREATE OR REPLACE PROCEDURE pr_tester

    AUTHID CURRENT_USER

    IS

    l_num NUMBER (2);

    BEGIN

    dbms_output.put_line (1 + 1 = ‘ | global_pkg.add2_rr (1,1));

    BEGIN

    SELECT global_pkg.add2_rr (1.1)

    IN l_num

    FROM DUAL;

    dbms_output.put_line (‘worked..’);

    EXCEPTION

    WHILE OTHERS

    THEN

    dbms_output.put_line (‘global_pkg.add2_rr wo’ |) SQLERRM);

    END;

    dbms_output.put_line (1 + 1 = ‘ | global_pkg.) ADD2 (1,1));

    BEGIN

    SELECT global_pkg.add2 (1.1)

    IN l_num

    FROM DUAL;

    dbms_output.put_line (‘worked..’);

    EXCEPTION

    WHILE OTHERS

    THEN

    dbms_output.put_line (‘global_pkg.add2 wo’ |) SQLERRM);

    END;

    dbms_output.put_line (1 + 1 = ‘ |) ADD2 (1,1));

    BEGIN

    SELECT add2 (1.1)

    IN l_num

    FROM DUAL;

    dbms_output.put_line (‘worked..’);

    EXCEPTION

    WHILE OTHERS

    THEN

    dbms_output.put_line (‘add2 wo’ |) SQLERRM);

    END;

    END;

    /

    ——————————————————————————————————————————

    Grant QUICK overlooking the procedure to INVOKER1 scheme…

    GUEST

    ——————————————————————————————————————————

    GRANT EXECUTE ON pr_tester TO invoker1

    /

    ——————————————————————————————————————————

    Test QUICK 1: Define schema works…

    GUEST

    ——————————————————————————————————————————

    SET SERVEROUTPUT ON

    BEGIN

    pr_tester;

    END;

    /

    ——————————————————————————————————————————

    GUEST

    GUEST

    GUEST

    GUEST will now connect to the INVOKER1 schema…

    GUEST

    CONNECT INVOKER1/INVOKER1

    ——————————————————————————————————————————

    Test FAST 2: Fails with ORA — 00904 define schema

    ——————————————————————————————————————————

    SET SERVEROUTPUT ON

    BEGIN

    definer1.pr_tester;

    END;

    /

    ——————————————————————————————————————————

    GUEST

    GUEST

    GUEST

    INVITE to drop the two schemas, enter the password for the sys

    ——————————————————————————————————————————

    CONNECT SYS AS SYSDBA

    DROP USER CASCADE DEFINER1

    /

    DROP USER INVOKER1

    /

    ——————————————————————————————————————————

    GUEST

    GUEST has dropped to users…

    ——————————————————————————————————————————

    Thank you

    Marie.

    Basically, you know the answer…

    Why you call procedure Tester pr_tester with the name of schema prefix (definer1.pr_tester) during his call of the schema of the invoker?

    Now… Imagine the following query runs under the scheme of the appellant:

    SELECT global_pkg.add2_rr (1.1)

    IN l_num

    FROM DUAL;

    the schema of the invoker ‘knows’ global_pkg?

    HTH

  • ORA-00600: internal error code, arguments: ORA-06512: at «SYS.» DBMS_STATS.

    Hello

    I had an error in my alert.log. The case of the patch?

    My OS: linux x86_64 — Redhat 5.5
    DBMS: 10.2.0.5 — acronym instance — data files

    ERROR:
    ~ ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [15735], [2176], [2152], [], [], [], [], [] ~ ORA-06512: at «SYS.] [[DBMS_STATS», line 21275 ~], [] [], [], [], [], []]
    TRACK:
    [ORA-00600: internal error code, arguments: [15735], [2176], [2152] [] [] [], [],]
    For this session current SQL statement:
    Select / * + parallel(t,32) parallel_index(t,32) cursor_sharing_exact use_weak_name_resl dynamic_sampling dbms_stats (0) no_monitoring * / count (*), count (distinct «ALIENACAO»), sum (sys_op_opnsize («ALIENACAO»)), substrb (dump (min («ALIENACAO»), 16.0, 32), 1, 120), substrb (dump (max («ALIENACAO»), 16.0, 32), 1, 120), count (distinct «PARCELA»), sum (sys_op_opnsize («PARCELA»)), substrb (dump (min («PARCELA»), 16.0, 32), 1, 120) , substrb (dump (max («PARCELA») 16.0, 32), 1, 120), count (distinct «NUM_PAGAMENTO»), sum (sys_op_opnsize («NUM_PAGAMENTO»)), substrb (dump (min («NUM_PAGAMENTO»), 16.0, 32), 1, 120), substrb (dump (max («NUM_PAGAMENTO»), 16.0, 32), 1, 120), count (distinct «DATA_VENCIMENTO»), substrb (dump (min («DATA_VENCIMENTO»), 16.0, 32), 1, 120), substrb (dump (max («DATA_VENCIMENTO»), 16.0, 32), 1, 120), count (distinct «DATA_PAGAMENTO») ,substrb(dump(min(«DATA_PAGAMENTO»),16,0,32),1,120),substrb(dump(max(«DATA_PAGAMENTO»),16,0,32) ,1,120),count(«VALOR_TOTAL_PAGO_10»),count(distinct «VALOR_TOTAL_PAGO_10»),sum(sys_op_opnsize(«VALOR_TOTAL_PAGO_10»)),substrb(dump(min(«VALOR_TOTAL_PAGO_10»),16,0,32),1,120),substrb(dump(max(«VALOR_TOTAL_PAGO_10»),16,0,32),1,120),count(«VALOR_TAXA_40»),count(distinct «VALOR_TAXA_40»),sum(sys_op_opnsize(«VALOR_TAXA_40»)) ,substrb(dump(min(«VALOR_TAXA_40»),16,0,32) ,1,120),substrb(dump(max(«VALOR_TAXA_40»),16,0,32),1,120),count(«VALOR_MULTA_60»),count(distinct «VALOR_MULTA_60»),sum(sys_op_opnsize(«VALOR_MULTA_60»)),substrb(dump(min(«VALOR_MULTA_60»),16,0,32),1,120),substrb(dump(max(«VALOR_MULTA_60»),16,0,32),1,120),count(«VALOR_MULTA_CORRECAO_170»),count(distinct «VALOR_MULTA_CORRECAO_170»),sum(sys_op_opnsize(«VALOR_MULTA_CORRECAO_170»)) ,substrb(dump(min(«VALOR_MULTA_CORRECAO_170») ,16,0,32),1,120),substrb(dump(max(«VALOR_MULTA_CORRECAO_170»),16,0,32),1,120),count(«SOMA_MULTA_60_170»),count(distinct «SOMA_MULTA_60_170»),sum(sys_op_opnsize(«SOMA_MULTA_60_170»)),substrb(dump(min(«SOMA_MULTA_60_170»),16,0,32),1,120),substrb(dump(max(«SOMA_MULTA_60_170»),16,0,32),1,120),count(«VALOR_CORRECAO_EXTRA_90»),count(distinct «VALOR_CORRECAO_EXTRA_90») , sum (sys_op_opnsize («VALOR_CORRECAO_EXTRA_90»)), substrb (dump (min («VALOR_CORRECAO_EXTRA_90»), 16.0, 32), 1, 120), substrb (dump (max («VALOR_CORRECAO_EXTRA_90»), 16.0, 32), 1, 120), count («VALOR_RESIDUO_80»), count (distinct «VALOR_RESIDUO_80»), sum (sys_op_opnsize («VALOR_RESIDUO_80»)), substrb (dump (min («VALOR_RESIDUO_80»), 16.0, 32), 1, 120), substrb (dump (max («VALOR_RESIDUO_80»), 16.0, 32), 1, 120), count («VALOR_SEGURO_50») , count (distinct «VALOR_SEGURO_50»), sum (sys_op_opnsize («VALOR_SEGURO_50»)), substrb (dump (min («VALOR_SEGURO_50»), 16.0, 32), 1, 120), substrb (dump (max («VALOR_SEGURO_50»), 16.0, 32), 1, 120), count («VALOR_MORA_70»), count (distinct «VALOR_MORA_70»), sum (sys_op_opnsize («VALOR_MORA_70»)), substrb (dump (min («VALOR_MORA_70»), 16.0, 32), 1, 120), substrb (dump (max («VALOR_MORA_70»), 16.0, 32), 1, 120), count («VALOR_MORA_CORRECAO_160») , count (distinct «VALOR_MORA_CORRECAO_160»), sum (sys_op_opnsize («VALOR_MORA_CORRECAO_160»)), substrb (dump (min («VALOR_MORA_CORRECAO_160»), 16.0, 32), 1, 120), substrb (dump (max («VALOR_MORA_CORRECAO_160»), 16.0, 32), 1, 120), count («SOMA_MORA_70_160»), count (distinct «SOMA_MORA_70_160»), sum (sys_op_opnsize («SOMA_MORA_70_160»)), substrb (dump (min («SOMA_MORA_70_160»), 16.0, 32), 1, 120), substrb (dump (max(«SOMA_MORA_70_160») ,16,0,32),1,120),count(«VALOR_AMORTIZACAO_CAP_30»),count(distinct «VALOR_AMORTIZACAO_CAP_30») ,sum(sys_op_opnsize(«VALOR_AMORTIZACAO_CAP_30»)),substrb(dump(min(«VALOR_AMORTIZACAO_CAP_30»),16,0,32),1,120),substrb(dump(max(«VALOR_AMORTIZACAO_CAP_30»),16,0,32),1,120),count(«VALOR_AMORTIZACAO_JTP_100»),count(distinct «VALOR_AMORTIZACAO_JTP_100»),sum(sys_op_opnsize(«VALOR_AMORTIZACAO_JTP_100»)),substrb(dump(min(«VALOR_AMORTIZACAO_JTP_100») (((, 16,0,32),1,120),substrb(dump(max(«VALOR_AMORTIZACAO_JTP_100») 16.0, 32), 1, 120) of «FGAI». «‘ VALOR_PAG_EXPORTA_SGH ‘ sample (1.3989586355) t
    -PL/SQL call stack-
    the line object
    serial number of handle
    body 0x11e3be750 1216 package SYS. DBMS_SYS_SQL
    body 0x11e3cc878 334 package SYS. DBMS_SQL
    body 0x11e6c2728 11791 package SYS. DBMS_STATS
    body 0x11e6c2728 13987 package SYS. DBMS_STATS
    body 0x11e6c2728 14454 package SYS. DBMS_STATS
    body 0x11e6c2728 14925 package SYS. DBMS_STATS
    package body 21201 0x11e6c2728 SYS. DBMS_STATS

    Thank you!

    Published by: user13521923 on 02/10/2011 04:42

    Hello

    Refer to Metalink Doc ID 455202.1

    HTH
    Anand

  • Search and apply reception error: ORA-00904: «ON_ACCT_PO_NUM»: not valid

    Hello

    We get an error when you try to apply revenues, ORA-00904: «ON_ACCT_PO_NUM»: invalid identifier. Found some Metalink:

    Find and apply functionality Arxrwmai filled with ORA-00904: error «ON_ACCT_PO_NUM» [388202.1 ID]
    ARXRWMAI: ORA-00904: «On_acct_po_num»: invalid identifier, applying the reception [564612.1 ID]
    ARP_PROCESS_APPLICATION is not valid — PLS-00302: component ‘ON_ACCT_CUST_ID’ must be declared [577194.1 ID]

    Basically all suggest that the columns (IE ON_ACCT_PO_NUM) are missing in AR_RECEIVABLE_APPLICATIONS. Check these tables and columns are actually there, however, I ran SQL > @$AR_TOP/patch/115/sql/arvrrapp.sql (the version is 115.26.15104.3). And re-tested, always a problem. Audit to verify what patches have been applied recently that could cause this.

    RDBMS: 11.2.0.1.0
    Oracle Applications: 11.5.10.2

    Thank you
    -Steve

    Hi steve;

    Check use select * ad_bugs and check patch that applied using the applied date column

    * PS: patch 5359197 is exist on your server that is mentioned on the research and apply feature Arxrwmai ends with ORA-00904: error «ON_ACCT_PO_NUM» [388202.1 ID] *.

    Respect of
    HELIOS

  • Error with PL/SQL package

    Hello

    I wrote a package specification given below:

    create or replace PACKAGE BILL_PACKAGE AS
    storeId varchar2 (5);
    startDate varchar2 (10);
    FUNCTION boolean RETURN F_Bill (str_id IN tel_tr_ltm_bl_py.id_str_rt%TYPE,ws_id IN tel_tr_ltm_bl_py.id_ws%TYPE,v_date IN tel_tr_ltm_bl_py.dc_dy_bsn%TYPE);
    END BILLPAYPACKAGE;

    I wrote the body of package also. Now when I call the F_Bill function, I get this error:

    PLS-00201: identifier ‘ BILL_PACKAGE. STARTDATE’ must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 3, column 1:
    PLS-00201: identifier ‘ BILL_PACKAGE. STOREID’ must be declared
    ORA-06550: line 3, column 1:
    PL/SQL: Statement ignored

    This same package works well on another local data base.

    What could be the reason for this?

    What is an access problem? In db, which we get an error, we use a user «ConUser» to connect to a schema ‘b_owner ‘.

    How can we verify this if it is a question of privilege.

    Thank you!

    >
    …

    This same package works well on another local data base.

    What could be the reason for this?

    What is an access problem? In db, which we get an error, we use a user «ConUser» to connect to a schema ‘b_owner ‘.

    How can we verify this if it is a question of privilege.

    The ‘ConUSer’ user needs to execute privileges for the package.
    Also, it depends on what he calls the package or the variables in it.

    You can check the privs with

    select * from all_tab_privs where table_name = 'BILL_PACKAGE';

    This must be run as a user ConUser.

    You may also have a problem with name resolution. The package name is not BILL_PACKAGE. It’s ‘b_owner. BILL_PACKAGE «.
    If you omit the schema name, then the current user is tried. So if you call BILL_PACKAGE, then this translates into «ConUSer.BILL_PACKAGE».

    You can change this by creating a synonym as

    create synonym bill_package for b_owner.bill_package;

  • I am trying to connect to a site web I have bee on several times and get an error code 10061

    I received an error code 10061 he States, «when the server gateway or proxy contacted the connection was refused, this is usually to try to connect to a service that is not active on the top stream server.

    Hello

    1. which version of the Windows operating system is installed on your computer?

    2. which site you are trying to connect to?

    3. what web browser do you use? (Internet Explorer, Firefox, etc.)

    4. what version of IE are you using?

    I suggest you to refer to the following Microsoft article and check if it helps.

    «Connection Refused» Message connecting to IIS server: http://support.microsoft.com/kb/272494

    Hope the information is useful.

  • The Service Pack installation failed with the 0x8e5e03fe error code.

    Hi all

    I’m not able to install Windows Server 2008 R2 SP1, when I install SP1, its going very well up to 90%, after that its giving the below error.

    ‘ 1. the Service Pack installation failed with the error 0x8e5e03fe. «.

    2 «changes in an update (Service Pack for Microsoft Windows (KB976932)) failed during the installation of the Service Pack.

    Identity: Package_for_KB976932 ~ 31bf3856ad364e35 ~ amd64 ~ ~ 6.1.1.17514

    Error code: 0x8e5e03fe

    State: 7 «

    I took the following steps to resolve. but it’s not helping me.

    1. I CA anti-virus, I disabled the Agent Client of CA.

    «2. I installed Windows analysis tool, but I did ‘ t get any error, under the errorlog for your reference.»
    =================================
    Preparation of control system was updated.
    Binary version 6.1.7601.21645
    Package Version 11.0
    2011-04-16 15:49

    Checking Windows Service Packages

    Checking of manifests package and catalogs

    Checking package Watchlist

    Watchlist checking

    Checking packages

    Control components store

    Summary:
    Seconds executed: 753
    No error found.

    3. I ran this commond «»dism / online/Remove-Package /packagename:Package_for_KB976932~31bf3856ad364e35~amd64~~6.1.1.17514 « «

    Please help me

    Concerning

    Ganga

    Hi Ganga,

    Your question of Windows 7 is more complex than what is generally answered in the Microsoft Answers forums. It is better suited for the Windows Server Forums. Please post your question in the Windows Server forum.

    http://social.technet.Microsoft.com/forums/en-us/category/WindowsServer/

  • error code 0x800b010e high cotext used run microsoft fix it programs

    Attach it to the usual run files and folders or codex falls down, is back with 0x800b010e and high context error codes.

    Hello

    Including the exact name of the troubleshooting, you tried to run when the issue has been met?

    Provide the event logs to better understand the issue.

    See the links below for more information.

    Procedure to view and manage event logs in Event Viewer in Windows XP

    http://support.Microsoft.com/kb/308427

    Using the event viewer

    http://www.Microsoft.com/resources/documentation/Windows/XP/all/proddocs/en-us/snap_event_viewer.mspx?mfr=true

    Method 1:

    Runt the SFC scan as this problem may occur due to corrupted system files.

    Reference:

    Description of Windows XP and Windows Server 2003 System File Checker (Sfc.exe)

    http://support.Microsoft.com/kb/310747

    Method 2:

    Clean boot

    How to configure Windows XP to start in a «clean boot» State

    http://support.Microsoft.com/kb/310353

    Note:

    How to configure Windows to use a Normal startup state

    After you have used the boot is a way to solve your problem, you can follow these steps to configure Windows XP to start normally.

    1. Click Startand then click run.
    2. Type msconfigand click OK.
      The System Configuration utility dialog box appears.
    3. Click the general tab, click Normal Startup — load all device drivers and services, and then click OK.
    4. When you are prompted, click restart to restart the computer

  • CD player does not. Not changed anything on computer. says will not start error code 10.

    I have Windows vista on my computer. I tried to play a cd and burn also. The drive does not at all. The light comes on, it runs for a second or two and then stops. I checked the updates for the drive and there is not. It is coming up with an error code 10. States, it does not start. I tried to find a solution for this and found nothing. HELP PLEASE?

    Hello

    Description of errors related to the 10 Code that generates the Device Manager in Windows on computers
    http://support.Microsoft.com/kb/943104/

    Step 1: Please do all the same underneath if you did some before as is often total
    a process that solves the problem.

    Try this — Panel — Device Manager — CD/DVD — double click on the device — driver tab.
    Click on update drivers (this will probably do nothing) — RIGHT click ON the drive — uninstall.
    RESTART this will refresh the default driver stack. Even if the reader does not appear to continue
    below.

    Then, work your way through these — don’t forget the drive might be bad, could be a coward
    cable or slight corrosion on the contacts (usually for a laptop) and other issues.

    Your CD or DVD drive is missing or is not recognized by Windows or other programs
    http://support.microsoft.com/kb/314060 — a Mr Fixit

    Try this fix manually if the Fixit 314060 does not work
    http://www.pchell.com/hardware/cd_drive_error_code_39.shtml

    Your CD or DVD drive is missing or is not recognized by Windows or other programs-
    a Mr Fixit
    http://support.Microsoft.com/kb/982116

    The CD drive or the DVD drive does not work as expected on a computer that you upgraded to
    for Windows Vista
    http://support.Microsoft.com/kb/929461

    When you insert a CD or a DVD, Windows Vista may not recognize the disc
    http://support.Microsoft.com/kb/939052

    Your CD or DVD drive cannot read or write media — A Mr Fixit
    http://support.Microsoft.com/GP/cd_dvd_drive_problems

    CD/DVD drive does not appear in Windows Vista, or you receive this error in Windows
    Vista installation after booting from the DVD (AHCI)
    http://support.Microsoft.com/kb/952951
    Drive CD — R or CD — RW Drive is not recognized as a recordable device
    http://support.Microsoft.com/kb/316529/

    Hardware devices not detected or not working — A Mr Fixit
    http://support.Microsoft.com/GP/hardware_device_problems

    Another possibility is that the cables are loose. Remove ALL power, then make sure that the cables in both
    ends. Remove and replace, do not just tight. For laptops, you can often clean power and
    contacts data with a pencil eraser.

    Some DVD players do not use the Windows default drivers so check with the manufacturer of system and
    manufacturer of device to see if there is a firmware or drivers for your drive if necessary.

    ===============================

    Step 2: You have disc problems as the CD/DVD is actually 4 discs in 1 case (CD & DVD
    Burn and CD and DVD read). So it is not unusual for 1 or 2 operational so that other parts
    do it right.

    Did you follow the Troubleshooting Guide for the reader who still does not work? There are
    the entries in registry that the troubleshooter does not solve and those who «might» be the cause.

    Check with your Maker system and a device for the two possible firmware updates and
    the correct registry entries for your car.

    Here are the keys that I of course are those in question — for the subkeys of the CD/DVD drive
    as there will be other subkeys in these keys. Do not forget to ask specific keys involved as well as
    the parameters.

    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetEnumIDE

    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetHardware Profiles001SystemCurrentControlSetEnumIDE

    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlClass {4D36E965-E325-11CE-BFC1-08002BE10318}

    ————————————————————————

    You can probably find more info here and maybe even the exact registry for your CD/DVD settings
    drive from someone with the same model.

    Forums — a lot of expert real help
    http://Club.myce.com/

    CD/DVD units
    http://www.myce.com/storage/

    Use DevManView to locate the CD/DVD in the registry (be careful and do a prior Restore Point)
    nothing change) — find the DevManView device and then make a right click on it free in RegEdit.

    DevManView — free — an alternative to the standard Windows Device Manager, which displays all the
    devices and their properties in flat table, instead of the tree viewer
    http://www.NirSoft.NET/utils/device_manager_view.html

    I hope this helps.

    Rob Brown — Microsoft MVP<- profile=»» -=»» windows=»» expert=»» -=»» consumer=»» :=»» bicycle=»» -=»» mark=»» twain=»» said=»» it=»»>

    • Источник

    Понравилась статья? Поделить с друзьями:

    Читайте также:

  • Error 900 lexmark
  • Error 9 iphone 6s plus
  • Error 9 g код блокируется во время тревоги или толчкового режима
  • Error 9 failed to open the file cisco
  • Error 887a0001 sea of thieves

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии