Home » SQL & PL/SQL » SQL & PL/SQL » OE SAMPEL SCHEMA (ORACLE 12.2.0.1.0, window 10)
OE SAMPEL SCHEMA [message #671803] Thu, 20 September 2018 04:18 Go to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
I Want to install / add sample schema OE / Sh but it display error message.
i connect with user sys as sysdba and then alter session set container = pdborcl command.
As i download the sample schema or github, unzip and store all of those in "D:\app\OracleHomeUser\product\12.2.0\dbhome_1\demo\schema".

i write following code :
SQL> alter session set container = pdborcl;

Session altered.

SQL> @?/demo/schema/order_entry/oe_main.sql

specify password for OE as parameter 1:
Enter value for 1: oe

specify default tablespeace for OE as parameter 2:
Enter value for 2: users

specify temporary tablespace for OE as parameter 3:
Enter value for 3: temp

specify password for HR as parameter 4:
Enter value for 4: hr

specify password for SYS as parameter 5:
Enter value for 5: bilal

specify directory path for the data files as parameter 6:
Enter value for 6: c:/

writeable directory path for the log files as parameter 7:
Enter value for 7: c:/log

specify version as parameter 8:
Enter value for 8: v3

specify connect string as parameter 9:
Enter value for 9: pdborcl

and it display error like this
SP2-0606: Cannot create SPOOL file "c:/logoe_oc_v3.log"

User dropped.

old   1: CREATE USER oe IDENTIFIED BY &pass
new   1: CREATE USER oe IDENTIFIED BY oe

User created.

old   1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER oe DEFAULT TABLESPACE users QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER oe TEMPORARY TABLESPACE temp

User altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.

Grant succeeded.

ERROR:
ORA-28002: the password will expire within 7 days


Connected.

Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.

Session altered.


Session altered.

SP2-0310: unable to open file "__SUB__CWD__/order_entry/coe_v3.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/loe_v3.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/poe_v3.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/oc_main.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/oe_analz.sql"
Connected.

Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.

not spooling currently


as user is create but no data. please help me in this regards.
Re: OE SAMPEL SCHEMA [message #671804 is a reply to message #671803] Thu, 20 September 2018 04:33 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
If you look at the readme.md file, you will see the instruction to replace all occurrences of __SUB_CWD__ with whatever your working directory is. There are several files you have to edit.
Re: OE SAMPEL SCHEMA [message #671806 is a reply to message #671804] Thu, 20 September 2018 05:20 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
Dear Sir in read.md file, following informations are
 "Change all embedded paths to match your working directory

The installation scripts need your current directory embedded in
various locations.  Use a text editor or the following Perl script to
make the changes, replacing occurrences of the token `__SUB__CWD__`
with your current working directory, for example
`/home/oracle/db-sample-schemas`

```shell
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat 
```
"
.
to follow these instruction i replace all the __SUB__CWD__ with the path
D:\app\OracleHomeUser\product\12.2.0\dbhome_1\demo\schema
.

but still it display same error.
Re: OE SAMPEL SCHEMA [message #671807 is a reply to message #671806] Thu, 20 September 2018 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're getting the same error then you missed some instances if __SUB__CWD__.
It can't complain about it if it isn't there.
Re: OE SAMPEL SCHEMA [message #671809 is a reply to message #671806] Thu, 20 September 2018 05:25 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Why don't you show us the output like you did the first time?
Re: OE SAMPEL SCHEMA [message #671810 is a reply to message #671807] Thu, 20 September 2018 05:26 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
I made replacement in these file coe_v3.sql, loe_v3.sql, poe_v3.sql, oc_main.sql, oe_analz.sql, in any other file repalcement of __SUB__CWD__.

as i use replace all ...
Re: OE SAMPEL SCHEMA [message #671817 is a reply to message #671810] Thu, 20 September 2018 05:52 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
Now i make changes in oe_main, then it create table but still few error are there. like


DROP PACKAGE xdb.coe_configuration
*
ERROR at line 1:
ORA-04043: object COE_CONFIGURATION does not exist


DROP PACKAGE xdb.coe_namespaces
*
ERROR at line 1:
ORA-04043: object COE_NAMESPACES does not exist


DROP PACKAGE xdb.coe_dom_helper
*
ERROR at line 1:
ORA-04043: object COE_DOM_HELPER does not exist


DROP PACKAGE xdb.coe_utilities
*
ERROR at line 1:
ORA-04043: object COE_UTILITIES does not exist


DROP PACKAGE xdb.coe_tools
*
ERROR at line 1:
ORA-04043: object COE_TOOLS does not exist


DROP TRIGGER xdb.no_dml_operations_allowed
*
ERROR at line 1:
ORA-04080: trigger 'NO_DML_OPERATIONS_ALLOWED' does not exist


DROP VIEW    xdb.database_summary
*
ERROR at line 1:
ORA-00942: table or view does not exist



FROM            customers c, countries cr
                             *
ERROR at line 7:
ORA-00942: table or view does not exist



Function created.

  countries co,
  *
ERROR at line 29:
ORA-00942: table or view does not exist
Re: OE SAMPEL SCHEMA [message #671818 is a reply to message #671817] Thu, 20 September 2018 06:35 Go to previous messageGo to next message
EdStevens
Messages: 1249
Registered: September 2013
Senior Member
Bilal Khan wrote on Thu, 20 September 2018 05:52
Now i make changes in oe_main, then it create table but still few error are there. like


DROP PACKAGE xdb.coe_configuration
*
ERROR at line 1:
ORA-04043: object COE_CONFIGURATION does not exist

Most of the errors are as above. Tried to drop a package that wasn't there. No harm, no foul. I'd be willing to bet that if you look at the scripts themselves there will be comments telling you that ORA-04043 is an acceptable error. This is quite common in installation packages that 'defensively' start by deleting/dropping anything that they are going to create later.

Referring back to your original posting:

Quote:
specify directory path for the data files as parameter 6:
Enter value for 6: c:/
Do you really think putting the data files in the root directory of your C: drive is a wise choice?
Re: OE SAMPEL SCHEMA [message #671819 is a reply to message #671818] Thu, 20 September 2018 06:37 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
thanks dear. its just practice. thats why i did it. i will avoid it.
Re: OE SAMPEL SCHEMA [message #671821 is a reply to message #671818] Thu, 20 September 2018 06:40 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I thought the same, Ed, but I'd also expect to see each drop with a "<object_type> created." message.
Re: OE SAMPEL SCHEMA [message #671822 is a reply to message #671819] Thu, 20 September 2018 06:40 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
i applied same procedure for SH Schema, but it create tables without number any records. following are efforts.
SQL> alter session set container = pdborcl;

Session altered.

SQL>  @?/demo/schema/sales_history/sh_main.sql

specify password for SH as parameter 1:
Enter value for 1: sh

specify default tablespace for SH as parameter 2:
Enter value for 2: users

specify temporary tablespace for SH as parameter 3:
Enter value for 3: emp

specify password for SYS as parameter 4:
Enter value for 4: bilal

specify directory path for the data files as parameter 5:
Enter value for 5: $ORACLE_HOME/demo/schemas/sales_history/

writeable directory path for the log files as parameter 6:
Enter value for 6: $ORACLE_HOME/demo/schemas/sales_history/

specify version as parameter 7:
Enter value for 7: v3

specify connect string as parameter 8:
Enter value for 8: pdborcl
but it display this

SP2-0606: Cannot create SPOOL file "$ORACLE_HOME/demo/schemas/sales_history/sh_v3.log"

Session altered.


User dropped.

old   1: CREATE USER sh IDENTIFIED BY &pass
new   1: CREATE USER sh IDENTIFIED BY sh

User created.

old   1: ALTER USER sh DEFAULT TABLESPACE &tbs
new   1: ALTER USER sh DEFAULT TABLESPACE users
old   2:  QUOTA UNLIMITED ON &tbs
new   2:  QUOTA UNLIMITED ON users

User altered.

old   1: ALTER USER sh TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER sh TEMPORARY TABLESPACE emp
ALTER USER sh TEMPORARY TABLESPACE emp
*
ERROR at line 1:
ORA-00959: tablespace 'EMP' does not exist



Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.

Grant succeeded.

old   1: CREATE OR REPLACE DIRECTORY data_file_dir AS '&data_dir'
new   1: CREATE OR REPLACE DIRECTORY data_file_dir AS '$ORACLE_HOME/demo/schemas/sales_history/'

Directory created.

old   1: CREATE OR REPLACE DIRECTORY log_file_dir AS '&log_dir'
new   1: CREATE OR REPLACE DIRECTORY log_file_dir AS '$ORACLE_HOME/demo/schemas/sales_history/'

Directory created.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.

Session altered.


Session altered.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Creating constraints ...

Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


specify password for SH as parameter 1:

specify path for data files as parameter 2:

specify path for log files as parameter 3:

specify version as parameter 4:

specify connect string as parameter 5:

Looking for indexes that could slow down load ...

no rows selected


loading TIMES using:
$ORACLE_HOME/demo/schemas/sales_history/time_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/time_v3.dat
$ORACLE_HOME/demo/schemas/sales_history/time_v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:56 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/time_v3.log)


loading COUNTRIES using:
$ORACLE_HOME/demo/schemas/sales_history/coun_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/coun_v3.dat
$ORACLE_HOME/demo/schemas/sales_history/coun_v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:56 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/coun_v3.log)


loading CUSTOMERS using:
$ORACLE_HOME/demo/schemas/sales_history/cust_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/cust1v3.dat
$ORACLE_HOME/demo/schemas/sales_history/cust1v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/cust1v3.log)


loading PRODUCTS  using:
$ORACLE_HOME/demo/schemas/sales_history/prod_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/prod1v3.dat
$ORACLE_HOME/demo/schemas/sales_history/prod1v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/prod1v3.log)


loading PROMOTIONS  using:
$ORACLE_HOME/demo/schemas/sales_history/prom_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/prom1v3.dat
$ORACLE_HOME/demo/schemas/sales_history/prom1v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/prom1v3.log)


loading CHANNELS using:
$ORACLE_HOME/demo/schemas/sales_history/chan_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/chan_v3.dat
$ORACLE_HOME/demo/schemas/sales_history/chan_v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/chan_v3.log)


loading SALES  using:
$ORACLE_HOME/demo/schemas/sales_history/sale_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/sale1v3.dat
$ORACLE_HOME/demo/schemas/sales_history/sale1v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/sale1v3.log)


loading COSTS using external table


Table created.

INSERT /*+ append */ INTO costs
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file sale1v3.dat in DATA_FILE_DIR not found



loading additonal SALES using:
$ORACLE_HOME/demo/schemas/sales_history/dmsal_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/dmsal_v3.dat
$ORACLE_HOME/demo/schemas/sales_history/dmsal_v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/dmsal_v3.log)


loading SUPPLEMENTARY DEMOGRAPHICS using:
$ORACLE_HOME/demo/schemas/sales_history/dem_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/dem1v3.dat
$ORACLE_HOME/demo/schemas/sales_history/dem1v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/dem1v3.log)


Commit complete.


Enabling constraints ...

Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Table altered.


Creating additional indexes ...

Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Create dimensions ...

Dimension created.


Commit complete.


Dimension created.


Dimension created.


Dimension created.


Dimension created.

Creating MVs as tables ...


View created.


Table created.


Table created.


Index created.


Index created.


Index created.


Index created.

Creating materialized views ...


Materialized view created.


Materialized view created.


Creating comments ...

Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


gathering statistics ...
BEGIN dbms_stats.gather_table_stats(          ownname          => 'SH'                     ,          tabname          => 'SALES_TRANSACTIONS_EXT' ,          partname         => NULL                     ,          estimate_percent => NULL                     ,          block_sample     => TRUE                     ,          method_opt       => 'FOR ALL COLUMNS SIZE 1' ,          degree           => NULL                     ,          granularity      => 'ALL'                    ,          cascade          => TRUE                     ,          stattab          => NULL                     ,          statid           => NULL                     ,          statown          => NULL                     ); END;

*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 35428
ORA-06512: at "SYS.DBMS_STATS", line 33883
ORA-06512: at "SYS.DBMS_STATS", line 28173
ORA-29400: data cartridge error
KUP-04040: file sale1v3.dat in DATA_FILE_DIR not found
ORA-06512: at "SYS.DBMS_SQL", line 1735
ORA-06512: at "SYS.DBMS_STATS", line 28090
ORA-06512: at "SYS.DBMS_STATS", line 33632
ORA-06512: at "SYS.DBMS_STATS", line 35304
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1



PL/SQL procedure successfully completed.

as table are created but no records is there;


TABLE_NAME                                                                                                                       SUM(NUM_ROWS)
-------------------------------------------------------------------------------------------------------------------------------- -------------
CAL_MONTH_SALES_MV                                                                                                                           0
CHANNELS                                                                                                                                     0
COSTS                                                                                                                                        0
COUNTRIES                                                                                                                                    0
CUSTOMERS                                                                                                                                    0
FWEEK_PSCAT_SALES_MV                                                                                                                         0
PRODUCTS                                                                                                                                     0
PROMOTIONS                                                                                                                                   0
SALES                                                                                                                                        0
SALES_TRANSACTIONS_EXT
SUPPLEMENTARY_DEMOGRAPHICS                                                                                                                   0
TIMES                                                                                                                                        0

its not clear to me how to resolve this issue,
Re: OE SAMPEL SCHEMA [message #671823 is a reply to message #671819] Thu, 20 September 2018 06:42 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Also I know that the operating system will resolve "/" characters to "\" ones for you but it hurts my eyes seeing forward slashes with Windows commands.
Re: OE SAMPEL SCHEMA [message #671825 is a reply to message #671822] Thu, 20 September 2018 06:48 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Bilal Khan wrote on Thu, 20 September 2018 04:40

gathering statistics ...
BEGIN dbms_stats.gather_table_stats( ownname => 'SH' , tabname => 'SALES_TRANSACTIONS_EXT' , partname => NULL , estimate_percent => NULL , block_sample => TRUE , method_opt => 'FOR ALL COLUMNS SIZE 1' , degree => NULL , granularity => 'ALL' , cascade => TRUE , stattab => NULL , statid => NULL , statown => NULL ); END;

*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 35428
ORA-06512: at "SYS.DBMS_STATS", line 33883
ORA-06512: at "SYS.DBMS_STATS", line 28173
ORA-29400: data cartridge error
KUP-04040: file sale1v3.dat in DATA_FILE_DIR not found
ORA-06512: at "SYS.DBMS_SQL", line 1735
ORA-06512: at "SYS.DBMS_STATS", line 28090
ORA-06512: at "SYS.DBMS_STATS", line 33632
ORA-06512: at "SYS.DBMS_STATS", line 35304
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1



PL/SQL procedure successfully completed.
[/code]

as table are created but no records is there;


TABLE_NAME                                                                                                                       SUM(NUM_ROWS)
-------------------------------------------------------------------------------------------------------------------------------- -------------
CAL_MONTH_SALES_MV                                                                                                                           0
CHANNELS                                                                                                                                     0
COSTS                                                                                                                                        0
COUNTRIES                                                                                                                                    0
CUSTOMERS                                                                                                                                    0
FWEEK_PSCAT_SALES_MV                                                                                                                         0
PRODUCTS                                                                                                                                     0
PROMOTIONS                                                                                                                                   0
SALES                                                                                                                                        0
SALES_TRANSACTIONS_EXT
SUPPLEMENTARY_DEMOGRAPHICS                                                                                                                   0
TIMES                                                                                                                                        0

its not clear to me how to resolve this issue,
NUM_ROWS get populated only after successfully gathering current statistics
Re: OE SAMPEL SCHEMA [message #671826 is a reply to message #671822] Thu, 20 September 2018 06:52 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
SP2-0606: Cannot create SPOOL file "$ORACLE_HOME/demo/schemas/sales_history/sh_v3.log
Windows may well resolve your forwards slashes, but SQL*Plus won't. Neither will $ORACLE_HOME mean anything. Use:
SPOOL %ORACLE_HOME\demo\schemas\sales_history\sh_v3.log
Re: OE SAMPEL SCHEMA [message #671827 is a reply to message #671822] Thu, 20 September 2018 06:53 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
What does the following query return?
SQL> select directory_path from dba_directories where directory_name='DATA_FILE_DIR';
Re: OE SAMPEL SCHEMA [message #671828 is a reply to message #671827] Thu, 20 September 2018 07:00 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
SQL> select directory_path from dba_directories where directory_name='DATA_FILE_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
pdborcl

1 row selected.
Re: OE SAMPEL SCHEMA [message #671829 is a reply to message #671822] Thu, 20 September 2018 07:05 Go to previous messageGo to next message
EdStevens
Messages: 1249
Registered: September 2013
Senior Member
Bilal Khan wrote on Thu, 20 September 2018 06:40
i applied same procedure for SH Schema, but it create tables without number any records. following are efforts.
SQL> alter session set container = pdborcl;

Session altered.

SQL>  @?/demo/schema/sales_history/sh_main.sql

specify password for SH as parameter 1:
Enter value for 1: sh

<snip>

specify directory path for the data files as parameter 5:
Enter value for 5: $ORACLE_HOME/demo/schemas/sales_history/
You are on Windows, right? So why are you specifying a direcotry with *nix syntax? Not only are the 'slashes' the wrong (which Windows MAY correct) but Windows will not know how to interpret '$ORACLE_HOME'. The script won't catch the problem here, because it is simply using what you provide to build a CREATE DIRECTORY command. That command simply creates a db directory object to act as an alias to the actual OS directory, and does not actually check if the specified OS directory is valid.

Quote:
writeable directory path for the log files as parameter 6:
Enter value for 6: $ORACLE_HOME/demo/schemas/sales_history/

specify version as parameter 7:
Enter value for 7: v3

specify connect string as parameter 8:
Enter value for 8: pdborcl
but it display this

SP2-0606: Cannot create SPOOL file "$ORACLE_HOME/demo/schemas/sales_history/sh_v3.log"
And the reason it cannot create the spool file is because windows doesn't know what to do with $ORACLE_HOME. The windows syntax would be %ORACLE_HOME%.

Quote:
old   1: ALTER USER sh TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER sh TEMPORARY TABLESPACE emp
ALTER USER sh TEMPORARY TABLESPACE emp
*
ERROR at line 1:
ORA-00959: tablespace 'EMP' does not exist
Tablespace doesn't exist because it couldn't create it using *nix specifications on windows.
<snip>

Quote:
old   1: CREATE OR REPLACE DIRECTORY data_file_dir AS '&data_dir'
new   1: CREATE OR REPLACE DIRECTORY data_file_dir AS '$ORACLE_HOME/demo/schemas/sales_history/'

Directory created.

old   1: CREATE OR REPLACE DIRECTORY log_file_dir AS '&log_dir'
new   1: CREATE OR REPLACE DIRECTORY log_file_dir AS '$ORACLE_HOME/demo/schemas/sales_history/'

Directory created.
Again, the directory object in the db is created, but it references an invalid, non-existent OS directory.

Quote:
loading TIMES using:
$ORACLE_HOME/demo/schemas/sales_history/time_v3.ctl
$ORACLE_HOME/demo/schemas/sales_history/time_v3.dat
$ORACLE_HOME/demo/schemas/sales_history/time_v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 16:33:56 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file ($ORACLE_HOME/demo/schemas/sales_history/time_v3.log)
Couldn't find the file because there is no such directory.
Need I continue?
Re: OE SAMPEL SCHEMA [message #671838 is a reply to message #671829] Thu, 20 September 2018 09:14 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
i could not resolve yet, still same issue.
Re: OE SAMPEL SCHEMA [message #671839 is a reply to message #671838] Thu, 20 September 2018 09:18 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Until you're ready to understand the difference between Windows and Unix, you're not going to.

"/" should be "\".

"$ORACLE_HOME" should be "%ORACLE_HOME%".

Re: OE SAMPEL SCHEMA [message #671840 is a reply to message #671838] Thu, 20 September 2018 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Bilal Khan wrote on Thu, 20 September 2018 07:14
i could not resolve yet, still same issue.
When problem exists between keyboard and chair, our ability to correct the issue is limited.
When you provide incorrect inputs to scripts, then errors should be expected.

You provided NO actionable detail above.
Your words above are as useful as my words below.

My car won't go.
Tell me how to make my car go.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Re: OE SAMPEL SCHEMA [message #671850 is a reply to message #671840] Thu, 20 September 2018 09:43 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
Dear I make this
SQL> @?/demo/schema/sales_history/sh_main.sql

specify password for SH as parameter 1:
Enter value for 1: sh

specify default tablespace for SH as parameter 2:
Enter value for 2: users

specify temporary tablespace for SH as parameter 3:
Enter value for 3: temp

specify password for SYS as parameter 4:
Enter value for 4: bilal

specify directory path for the data files as parameter 5:
Enter value for 5: %ORACLE_HOME\demo\schemas\sales_history\

writeable directory path for the log files as parameter 6:
Enter value for 6: %ORACLE_HOME\demo\schemas\sales_history\

specify version as parameter 7:
Enter value for 7: v3

specify connect string as parameter 8:
Enter value for 8: pdborcl

but now it display these error

Table created.

INSERT /*+ append */ INTO costs
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file sale1v3.dat in DATA_FILE_DIR not found



loading additonal SALES using:
%ORACLE_HOME\demo\schemas\sales_history\dmsal_v3.ctl
%ORACLE_HOME\demo\schemas\sales_history\dmsal_v3.dat
%ORACLE_HOME\demo\schemas\sales_history\dmsal_v3.log

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 20 19:36:47 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-522: lfiopn failed for file (%ORACLE_HOME\demo\schemas\sales_history\dmsal_v3.log)


loading SUPPLEMENTARY DEMOGRAPHICS using:
%ORACLE_HOME\demo\schemas\sales_history\dem_v3.ctl
%ORACLE_HOME\demo\schemas\sales_history\dem1v3.dat
%ORACLE_HOME\demo\schemas\sales_history\dem1v3.log

and
gathering statistics ...
BEGIN dbms_stats.gather_table_stats(          ownname          => 'SH'                     ,          tabname          => 'SALES_TRANSACTIONS_EXT' ,          partname         => NULL                     ,          estimate_percent => NULL                     ,          block_sample     => TRUE                     ,          method_opt       => 'FOR ALL COLUMNS SIZE 1' ,          degree           => NULL                     ,          granularity      => 'ALL'                    ,          cascade          => TRUE                     ,          stattab          => NULL                     ,          statid           => NULL                     ,          statown          => NULL                     ); END;

*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 35428
ORA-06512: at "SYS.DBMS_STATS", line 33883
ORA-06512: at "SYS.DBMS_STATS", line 28173
ORA-29400: data cartridge error
KUP-04040: file sale1v3.dat in DATA_FILE_DIR not found
ORA-06512: at "SYS.DBMS_SQL", line 1735
ORA-06512: at "SYS.DBMS_STATS", line 28090
ORA-06512: at "SYS.DBMS_STATS", line 33632
ORA-06512: at "SYS.DBMS_STATS", line 35304
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1

Re: OE SAMPEL SCHEMA [message #671851 is a reply to message #671850] Thu, 20 September 2018 09:46 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
You need to learn how to use Windows. You have already been told how to use an environment variable. Read gazzag's note again.

[Updated on: Thu, 20 September 2018 09:47]

Report message to a moderator

Re: OE SAMPEL SCHEMA [message #671853 is a reply to message #671850] Thu, 20 September 2018 09:48 Go to previous message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Which part of below do you NOT understand?
>"$ORACLE_HOME" should be "%ORACLE_HOME%".
Do you notice any difference between above & below?
>Enter value for 6: %ORACLE_HOME\demo\schemas\sales_history\

TWO "%" are required & NOT just one as you provided!

%ORACLE_HOME%
Previous Topic: Help w/ REGEXP_SUBSTR
Next Topic: SQL code syntax Understanding
Goto Forum:
  


Current Time: Wed Sep 23 01:21:38 CDT 2020