Skip to Main Content
Forums

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NLS settings corrected, but does not work (SQLcl)

2881746Jan 26 2018 — edited Jan 26 2018

Hi all,

I have some difficulties to change language from default to ENGLISH.

Oracle registry NLS_LANG is ENGLISH,

Windows NLS_LANG variable is ENGLISH too.

login.sql script corrected accordingly: System.setProperty("user.lang","en");

All tests show en:us and [american_america.WE8ISO8859P15], but message language is still not ENGLISH, see log file below please.

If I try to change it by JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US'  -- it is accepted, but connect is not possible: "Error Message = Locale not recognized"

Any idea?

Best regards,

Sergey

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

C:\Local\DB\Tickets\180123>sql.bat

C:\Local\DB\Tickets\180123>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8'

C:\Local\DB\Tickets\180123>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=WE8MSWIN1252'

C:\Local\DB\Tickets\180123>set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US'

C:\Local\DB\Tickets\180123>C:\Progs\sqlcl\bin\sql.exe test/test@puma02:1521/DB1STA.sixt.de

Picked up JAVA_TOOL_OPTIONS: '-Duser.language=en -Duser.region=US'

SQLcl: Release 17.4.0 Production on Tue Jan 23 14:14:56 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

  USER          = test

  URL           = jdbc:oracle:oci8:@puma02:1521/DB1STA.sixt.de

  Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020

  USER          = test

  URL           = jdbc:oracle:thin:@puma02:1521/DB1STA.sixt.de

  Error Message = Locale not recognized

Username? (RETRYING) ('test/*********@puma02:1521/DB1STA.sixt.de'?)

  USER          = test

  URL           = jdbc:oracle:oci8:@puma02:1521/DB1STA.sixt.de

  Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020

  USER          = test

  URL           = jdbc:oracle:thin:@puma02:1521/DB1STA.sixt.de

  Error Message = Locale not recognized

Username? (RETRYING) ('test/*********@puma02:1521/DB1STA.sixt.de'?)

-- ==== without language settings it is connected, but wuth default system language:

C:\Local\DB\Tickets\180123>sql.bat

C:\Local\DB\Tickets\180123>C:\Progs\sqlcl\bin\sql.exe test/test@puma02:1521/DB1STA.sixt.de

SQLcl: Release 17.4.0 Production auf Di Jan 23 14:28:02 2018

Copyright (c) 1982, 2018, Oracle. All rights reserved. Alle Rechte vorbehalten.

Last Successful login time: Di Jan 23 2018 14:28:07 +01:00

Verbunden mit:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

login.sql wurde in CWD gefunden. Der DB-Zugriff ist f++r login.sql eingeschr+ñnkt.

Passen Sie SQLPATH an, um den Pfad f++r eine umfassende Funktionalit+ñt einzuschlie+ƒen.

en

us

TEST @ puma02:1521/DB1STA.sixt.de >

TEST @ db1sta >alias ls=SELECT object_name FROM user_objects;

TEST @ db1sta >ls

Keine Zeilen gew+ñhlt

TEST @ db1sta >show java

Java Detail

-----------

java.home= C:\Program Files\Java\jre1.8.0_161

java.vendor= Oracle Corporation

java.vendor.url= http://java.oracle.com/

java.version= 1.8.0_161

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------

os.arch= amd64

os.name= Windows 10

os.version= 10.0

path.separator= ;

file.separator= \

line.separator=

user.dir= C:\Local\Scripts\SQLCL

user.home= C:\Users\S3414

user.name= S3414

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------

Classpath=

C:\Progs\sqlcl\bin\sql.exe;C:\Progs\sqlcl\bin\..\lib\.;C:\Progs\sqlcl\bin\..\lib\..;C:\Progs\sqlcl\bin\..\lib\antlr-runtime.jar;C:\Progs\sqlcl\bin\..\lib\commons-codec.jar;C:\Progs\sqlcl\bin

\..\lib\c

ommons-logging.jar;C:\Progs\sqlcl\bin\..\lib\httpclient.jar;C:\Progs\sqlcl\bin\..\lib\httpcore.jar;C:\Progs\sqlcl\bin\..\lib\httpmime.jar;C:\Progs\sqlcl\bin\..\lib\jackson-annotations.jar;C:

\Progs\sql

cl\bin\..\lib\jackson-core.jar;C:\Progs\sqlcl\bin\..\lib\jackson-databind.jar;C:\Progs\sqlcl\bin\..\lib\javax.json.jar;C:\Progs\sqlcl\bin\..\lib\jline.jar;C:\Progs\sqlcl\bin\..\lib\jsch.jar;

C:\Progs\s

qlcl\bin\..\lib\ojdbc8.jar;C:\Progs\sqlcl\bin\..\lib\oracle.dbtools-common.jar;C:\Progs\sqlcl\bin\..\lib\oracle.dbtools.http.jar;C:\Progs\sqlcl\bin\..\lib\oracle.dbtools.jdbcrest.jar;C:\Prog

s\sqlcl\bi

n\..\lib\oracle.sqldeveloper.sqlcl.jar;C:\Progs\sqlcl\bin\..\lib\oraclepki.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-collation.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-mapping.jar;C:\Progs\sqlcl\bi

n\..\lib\o

rai18n-servlet.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-utility.jar;C:\Progs\sqlcl\bin\..\lib\orai18n.jar;C:\Progs\sqlcl\bin\..\lib\orajsoda.jar;C:\Progs\sqlcl\bin\..\lib\osdt_cert.jar;C:\Progs

\sqlcl\bin

\..\lib\osdt_core.jar;C:\Progs\sqlcl\bin\..\lib\stringtemplate.jar;C:\Progs\sqlcl\bin\..\lib\xdb6.jar;C:\Progs\sqlcl\bin\..\lib\xmlparserv2.jar;C:\Progs\sqlcl\bin\..\modules\oracle.xdk\xmlpa

rserv2.jar

;C:\Progs\sqlcl\bin\..\rdbms\jlib\xdb6.jar;C:\Progs\sqlcl\bin\..\jlib\orai18n-mapping.jar

;C:\Progs\sqlcl\bin\..\jlib\orai18n.jar;C:\Progs\sqlcl\bin\..\jdbc\lib\ojdbc6.jar;C:\Progs\sqlcl\bin\..\jdbc\lib\ojdbc7.jar;C:\Progs\sqlcl\bin\..\jdbc\lib\ojdbc8.jar;C:\Progs\sqlcl\bin\..\jl

ib\orai18

n-utility.jar

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- ==== login.sql:

set termout off

define gname=idle

column global_name new_value gname

select lower(user)||'@' ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,'.') dot from global_name);

set sqlprompt '&gname>'

set termout on

set linesize 200

set pagesize 100

column VALUE_COL_PLUS_SHOW_PARAM format A65

column NAME_COL_PLUS_SHOW_PARAM  format A40

set sqlprompt "_user '@' _connect_identifier >"

script

  var System  = Java.type("java.lang.System");

  System.setProperty("user.lang","en");

  System.setProperty("user.country","us");

  System.out.println( System.getProperty("user.lang"));

  System.out.println(  System.getProperty("user.country"));

/

-- ====

C:\Local\DB\Tickets\180126>sql.bat

C:\Local\DB\Tickets\180126>rem to change language

C:\Local\DB\Tickets\180126>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8'

C:\Local\DB\Tickets\180126>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US'

C:\Local\DB\Tickets\180126>rem set LANG=en_US.UTF-8

C:\Local\DB\Tickets\180126>rem to suppress extra empty lnes

C:\Local\DB\Tickets\180126>rem SET STD_ARGS=-Djava.awt.headless=true -Xss10M -Dfile.encoding=UTF-8

C:\Local\DB\Tickets\180126>C:\Progs\sqlcl\bin\sql.exe /nolog

SQLcl: Release 17.4.0 Production auf Fr Jan 26 12:48:11 2018

Copyright (c) 1982, 2018, Oracle. All rights reserved. Alle Rechte vorbehalten.

en  <<<<<<<<<<<<<<<<<<<<< !!!!!

us

@  >@.[%NLS_LANG%]

Fehler beim Start in Zeile: 1 in Befehl -

@.[%NLS_LANG%]

Fehlerbericht -

SP2-0310: +ûffnen von Datei nicht m+Âglich: ".[american_america.WE8ISO8859P15]"

@  >

C:\Local\DB\Tickets\180123>sql.batC:\Local\DB\Tickets\180123>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8'C:\Local\DB\Tickets\180123>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=WE8MSWIN1252'C:\Local\DB\Tickets\180123>set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US'
C:\Local\DB\Tickets\180123>C:\Progs\sqlcl\bin\sql.exe test/test@puma02:1521/DB1STA.sixt.dePicked up JAVA_TOOL_OPTIONS: '-Duser.language=en -Duser.region=US'
SQLcl: Release 17.4.0 Production on Tue Jan 23 14:14:56 2018
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
  USER          = test  URL           = jdbc:oracle:oci8:@puma02:1521/DB1STA.sixt.de  Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020  USER          = test  URL           = jdbc:oracle:thin:@puma02:1521/DB1STA.sixt.de  Error Message = Locale not recognizedUsername? (RETRYING) ('test/*********@puma02:1521/DB1STA.sixt.de'?)  USER          = test  URL           = jdbc:oracle:oci8:@puma02:1521/DB1STA.sixt.de  Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020  USER          = test  URL           = jdbc:oracle:thin:@puma02:1521/DB1STA.sixt.de  Error Message = Locale not recognizedUsername? (RETRYING) ('test/*********@puma02:1521/DB1STA.sixt.de'?)

-- ==== But without language settings it is connected, but wuth default system language:
C:\Local\DB\Tickets\180123>sql.batC:\Local\DB\Tickets\180123>C:\Progs\sqlcl\bin\sql.exe test/test@puma02:1521/DB1STA.sixt.de
SQLcl: Release 17.4.0 Production auf Di Jan 23 14:28:02 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved. Alle Rechte vorbehalten.
Last Successful login time: Di Jan 23 2018 14:28:07 +01:00
Verbunden mit:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Application Testing options
login.sql wurde in CWD gefunden. Der DB-Zugriff ist f++r login.sql eingeschr+ñnkt.Passen Sie SQLPATH an, um den Pfad f++r eine umfassende Funktionalit+ñt einzuschlie+ƒen.enus
TEST @ puma02:1521/DB1STA.sixt.de >
TEST @ db1sta >alias ls=SELECT object_name FROM user_objects;TEST @ db1sta >lsKeine Zeilen gew+ñhlt


TEST @ db1sta >show javaJava Detail-----------java.home= C:\Program Files\Java\jre1.8.0_161java.vendor= Oracle Corporationjava.vendor.url= http://java.oracle.com/java.version= 1.8.0_161--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------os.arch= amd64os.name= Windows 10os.version= 10.0path.separator= ;file.separator= \line.separator=
user.dir= C:\Local\Scripts\SQLCLuser.home= C:\Users\S3414user.name= S3414--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Classpath=C:\Progs\sqlcl\bin\sql.exe;C:\Progs\sqlcl\bin\..\lib\.;C:\Progs\sqlcl\bin\..\lib\..;C:\Progs\sqlcl\bin\..\lib\antlr-runtime.jar;C:\Progs\sqlcl\bin\..\lib\commons-codec.jar;C:\Progs\sqlcl\bin\..\lib\commons-logging.jar;C:\Progs\sqlcl\bin\..\lib\httpclient.jar;C:\Progs\sqlcl\bin\..\lib\httpcore.jar;C:\Progs\sqlcl\bin\..\lib\httpmime.jar;C:\Progs\sqlcl\bin\..\lib\jackson-annotations.jar;C:\Progs\sqlcl\bin\..\lib\jackson-core.jar;C:\Progs\sqlcl\bin\..\lib\jackson-databind.jar;C:\Progs\sqlcl\bin\..\lib\javax.json.jar;C:\Progs\sqlcl\bin\..\lib\jline.jar;C:\Progs\sqlcl\bin\..\lib\jsch.jar;C:\Progs\sqlcl\bin\..\lib\ojdbc8.jar;C:\Progs\sqlcl\bin\..\lib\oracle.dbtools-common.jar;C:\Progs\sqlcl\bin\..\lib\oracle.dbtools.http.jar;C:\Progs\sqlcl\bin\..\lib\oracle.dbtools.jdbcrest.jar;C:\Progs\sqlcl\bin\..\lib\oracle.sqldeveloper.sqlcl.jar;C:\Progs\sqlcl\bin\..\lib\oraclepki.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-collation.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-mapping.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-servlet.jar;C:\Progs\sqlcl\bin\..\lib\orai18n-utility.jar;C:\Progs\sqlcl\bin\..\lib\orai18n.jar;C:\Progs\sqlcl\bin\..\lib\orajsoda.jar;C:\Progs\sqlcl\bin\..\lib\osdt_cert.jar;C:\Progs\sqlcl\bin\..\lib\osdt_core.jar;C:\Progs\sqlcl\bin\..\lib\stringtemplate.jar;C:\Progs\sqlcl\bin\..\lib\xdb6.jar;C:\Progs\sqlcl\bin\..\lib\xmlparserv2.jar;C:\Progs\sqlcl\bin\..\modules\oracle.xdk\xmlparserv2.jar;C:\Progs\sqlcl\bin\..\rdbms\jlib\xdb6.jar;C:\Progs\sqlcl\bin\..\jlib\orai18n-mapping.jar;C:\Progs\sqlcl\bin\..\jlib\orai18n.jar;C:\Progs\sqlcl\bin\..\jdbc\lib\ojdbc6.jar;C:\Progs\sqlcl\bin\..\jdbc\lib\ojdbc7.jar;C:\Progs\sqlcl\bin\..\jdbc\lib\ojdbc8.jar;C:\Progs\sqlcl\bin\..\jlib\orai18n-utility.jar----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ==== login.sql:set termout offdefine gname=idlecolumn global_name new_value gnameselect lower(user)||'@' ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,'.') dot from global_name);set sqlprompt '&gname>'set termout onset linesize 200set pagesize 100column VALUE_COL_PLUS_SHOW_PARAM format A65column NAME_COL_PLUS_SHOW_PARAM  format A40
set sqlprompt "_user '@' _connect_identifier >"
script  var System  = Java.type("java.lang.System");  System.setProperty("user.lang","en");  System.setProperty("user.country","us");  System.out.println( System.getProperty("user.lang"));  System.out.println(  System.getProperty("user.country"));/
-- ====C:\Local\DB\Tickets\180126>sql.bat
C:\Local\DB\Tickets\180126>rem to change language
C:\Local\DB\Tickets\180126>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8'
C:\Local\DB\Tickets\180126>rem set JAVA_TOOL_OPTIONS='-Duser.language=en -Duser.region=US'
C:\Local\DB\Tickets\180126>rem set LANG=en_US.UTF-8
C:\Local\DB\Tickets\180126>rem to suppress extra empty lnes
C:\Local\DB\Tickets\180126>rem SET STD_ARGS=-Djava.awt.headless=true -Xss10M -Dfile.encoding=UTF-8
C:\Local\DB\Tickets\180126>C:\Progs\sqlcl\bin\sql.exe /nolog
SQLcl: Release 17.4.0 Production auf Fr Jan 26 12:48:11 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved. Alle Rechte vorbehalten.
en  <<<<<<<<<<<<<<<<<<<<< !!!!!us
@  >@.[%NLS_LANG%]
Fehler beim Start in Zeile: 1 in Befehl -@.[%NLS_LANG%]Fehlerbericht -SP2-0310: +ûffnen von Datei nicht m+Âglich: ".[american_america.WE8ISO8859P15]" @  >
This post has been answered by Gaz in Oz on Jan 26 2018
Jump to Answer

Comments

BluShadow
The majority of those commands are SQL*Plus commands. That means that they are specific to SQL*Plus and are not part of SQL or PL/SQL which is what you write your procedures/packages with.

Tell us more about what you are trying to achieve and we can advise the best way, because this may be that you put everything in a procedure and code the requirements to generate output or you put everything in a script and execute it via a job etc.
221158
You can include the select statement in a cursor within a database procedure and use dynamic SQL (execute immediate to execute the DDL).

Christopher Soza
Oracle BI DBA
Orix Consultancy Services Ltd
b: http://sozaman.blogspot.com
519811
The system setup is that from Oracle 10g database, data will be copied to locally deployed Oracle XE databases. In order to do that, tables in Oracle XE should be truncated. But before truncating the table, constraints should be disabled first.Then after inserting records from Oracle 10g, constraints shall be enabled again.

I hope you could help me with this one. Thanks in advance.
Marwim
Hello sidkaterin,

here is a package I use for development. Never give access to this package to a normal user!

Regards
Marcus
CREATE OR REPLACE PACKAGE  my_trunc
IS
PROCEDURE disable_triggers;
PROCEDURE enable_triggers;
PROCEDURE disable_constraints;
PROCEDURE enable_constraints;
PROCEDURE truncate_table(p_tabelle IN VARCHAR2);

END my_trunc;
/

CREATE OR REPLACE PACKAGE BODY my_trunc
IS

PROCEDURE disable_triggers
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN
    cur_name := dbms_sql.open_cursor;
    FOR c_trg IN(
        SELECT  'ALTER TRIGGER ' || trigger_name || ' DISABLE ' stmnt
        FROM    user_triggers
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_trg.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Disable Trigger: '||SUBSTR(SQLERRM, 1, 100));
END disable_triggers;

PROCEDURE enable_triggers
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN
    cur_name := dbms_sql.open_cursor;
    FOR c_trg IN (
        SELECT  'ALTER TRIGGER ' || trigger_name || ' ENABLE ' stmnt
        FROM    user_triggers
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_trg.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Enable Trigger: '||SUBSTR(SQLERRM, 1, 100)
                );
END enable_triggers;

PROCEDURE disable_constraints
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type = 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type != 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Disable Constraints: '||
                 SUBSTR(SQLERRM, 1, 100)
                );
END disable_constraints;

PROCEDURE enable_constraints
IS
    cur_name                INTEGER;
    rows_processed          INTEGER;
BEGIN

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type != 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

    cur_name := dbms_sql.open_cursor;
    FOR c_con IN (
        SELECT  'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ' ' stmnt
        FROM    user_constraints
        WHERE   constraint_type = 'R'
        )
    LOOP
        dbms_sql.parse (
                 cur_name
                ,c_con.stmnt
                ,dbms_sql.v7
                );
        rows_processed := dbms_sql.execute (cur_name);
    END LOOP;
    dbms_sql.close_cursor (cur_name);

EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Enable Constraints: '||
                 SUBSTR(SQLERRM, 1, 100)
                );
END enable_constraints;

PROCEDURE truncate_table
    (
     p_tabelle              IN VARCHAR2
    )
IS
    cur_name                INTEGER;
    v_statement             VARCHAR2(500);
    rows_processed          INTEGER;
BEGIN
    v_statement     := 'TRUNCATE TABLE ' || p_tabelle || ' ';
    cur_name        := dbms_sql.open_cursor;
    dbms_sql.parse (
             cur_name
           ,v_statement
            ,dbms_sql.v7
            );
    rows_processed  := dbms_sql.execute (cur_name);
    dbms_sql.close_cursor (cur_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_sql.close_cursor (cur_name);
        RAISE_APPLICATION_ERROR (
                 -20999
                ,'Truncate Table ' || p_tabelle || ': ' ||
                SUBSTR(SQLERRM, 1, 100)
                );
END truncate_table;

END my_trunc;
/
SHO ERR
Edited by: Marwim on 18.09.2008 07:33
Replaced lessThan greaterThan with != because using code-markup does not hinder the forum software from changing the characters.
519811
Thank you. I can review this and maybe would revise a few statements to fit my requirements.
247514
sidkaterin wrote:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql;
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
--and c.constraint_type IN ('P','R','U');
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
Or run your procedure here.
exec myprocedure ;
exit
/

however, there are other procedures that shall be done after disabling the constraints. I want to put them in one package. How do I call this from a procedure? When I tried to simply enclose it in a PROCEDURE format, the spool file command is not recognized. It returned error message. Hope anyone can help me with this. Thanks in advance.
1 - 6

Post Details

Added on Jan 26 2018
9 comments
3,090 views