Error pls 00103

Warning: Procedure created with compilation errors — PLS-00103

Hello my ODC friends ,

In the process of simulating a fast refresh MView on schema TEST on Linux of Oracle 11.2.0.4.0 I encounter this weird error PLS-00103.

The following is my entire code,

Could you help me find out where is this error? Thanks in advance.

Best Answer

your job action parameter is wrong. you have forgotten double single quotes:

instead of: job_action => ‘begin dbms_mview.refresh(‘mv_t’,’f’); end;’

it should be : job_action => ‘begin dbms_mview.refresh(»mv_t»,»f»); end;’

Answers

your job action parameter is wrong. you have forgotten double single quotes:

instead of: job_action => ‘begin dbms_mview.refresh(‘mv_t’,’f’); end;’

it should be : job_action => ‘begin dbms_mview.refresh(»mv_t»,»f»); end;’

begin dbms_mview.refresh(‘mv_t’,’f’); end;

Hello Mustafa KALAYCI ,

Yep, your suggestion is pretty good. FYI I modify my job_action parameter on Oracle system package «DBMS_SCHEDULER.create_job». This time my procedure has been created successfully.

In fact I’m more wondering whether this root cause is the content of parameter job_action has already used a couple of single quotation marks so Oracle System Procedure dbms_mview.refresh‘s parameters (get back to the second place) have to use a couple of double quotation marks.

I can assume such a scenario (if I don’t use the previous mentioned Oracle Scheduler to regularly refresh my MView ‘mv_t’) — just manually refresh my materialized view. Then using a couple of singe quotation marks is okay.

If I add an extra parameter name before the value of parameter it looks like more clear and elegant.

Fortunately version 11.2 of oracle official online docs — https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_mview.htm#ARPLS67203 helps me. It told me that I should use parameter name list rather than tab.

Indeed, the package procedure dbms_mview.refresh is overloaded, so depending on how you call it, with just parameter position or parameter naming («=>») will depend on which procedure actually gets executed. You can see this if you describe dbms_mview. For example:

Which of course you gleaned anyway by making good use of the official Oracle documentation for your version db.

Источник

How to Resolve PLS-00103: Encountered the symbol

PLS-00103

I cannot cover all error patterns of PL-00103 in this post, here are some cases that encounter PLS-00103.

1. Missing IS

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «BEGIN».

SQL> set serveroutput on;
SQL> create or replace procedure p1
2 begin
3 dbms_output.put_line(‘Procedure 1’);
4 end;
5 /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

LINE/COL ERROR
——— ——————————————————————
2/1 PLS-00103: Encountered the symbol «BEGIN» when expecting one of
the following:
( ; is with default authid as cluster compress order using
compiled wrapped external deterministic parallel_enable
pipelined result_cache accessible rewrite
The symbol «is» was substituted for «BEGIN» to continue .

Solution

The keyword IS is expected before BEGIN as explained above.

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6 /

SQL> show errors
No errors.

2. Missing END

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «end-of-file».

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors
Errors for PROCEDURE P1:

LINE/COL ERROR
——— ——————————————————————
4/38 PLS-00103: Encountered the symbol «end-of-file» when expecting
one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with

Solution

In this case, the keyword END is expected before the symbol / (slash).

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6 /

SQL> show errors
No errors.

3. Missing Slash

When we tried to compile two procedures in a session, we got PLS-00103: Encountered the symbol «CREATE».

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6
7 create or replace procedure p2
8 is
9 begin
10 dbms_output.put_line(‘Procedure 2’);
11 end;
12 /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

This is because every programming unit is an independent one, we should use symbol / (slash) in SQL*Plus to compile them separately.

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6 /

SQL> create or replace procedure p2
2 is
3 begin
4 dbms_output.put_line(‘Procedure 2’);
5 end;
6 /

4. EXECUTE IMMEDIATE

In an anonymous PL/SQL block, we use EXECUTE IMMEDIATE .

SQL> begin
2 execute immediate ‘select nvl(first_name, ‘NO_VALUE’) from employees’ ;
3 end;
4 /
execute immediate ‘select nvl(first_name, ‘NO_VALUE’) from employees’;
*
ERROR at line 2:
ORA-06550: line 2, column 46:
PLS-00103: Encountered the symbol «NO_VALUE» when expecting one of the following:
* & = — + ; at in is mod remainder not rem return
returning <> or != or

= >= and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol «* was inserted before «NO_VALUE» to continue.

Solution

For EXECUTE IMMEDIATE statement, you should use extra single quotes to escape original single quotes in the statement like this.

SQL> begin
2 execute immediate ‘select nvl(first_name, »NO_VALUE») from employees’ ;
3 end;
4 /

Источник

Error: PLS-00103: Encountered the symbol «EXCEPTION» when expecting one of the following: and Encoun

I tried to write a procedure like following :

create or replace procedure xx_test_item

( p_item IN varchar2)

where user_config_id = p_item

and attribute_category = ‘ITEM_RELATIONSHIPS’;

from mtl_system_items_b msib, xxcz_config_attributes xca

where msib.segment1 = xca.attribute_1;

if (l_attribute_1 = p_item)

dbms_output.put_line(‘Main Item and Related item is same :’||l_attribute_1);

(l_attribute_1 IS NULL)

dbms_output.put_line(‘Not a valid item. ‘);

dbms_output.put_line(‘Items not matching :’||l_attribute_1);

WHEN no_data_found THEN

dbms_output.put_line(‘No such item’);

WHEN others THEN

Compilation errors for PROCEDURE APPS.XX_TEST_ITEM

Error: PLS-00103: Encountered the symbol «EXCEPTION» when expecting one of the following:

( begin case declare end exit for goto if loop mod null

pragma raise return select update while with

Best Answer

ELSE IF is valid PL/SQL, but it’s a new, nested IF statement, and requires its own, nested END IF statement.

Also, lose the EXCEPTION section; it’s not doing anything except hiding useful details about any error that might occur.

Answers

ELSE IF is valid PL/SQL, but it’s a new, nested IF statement, and requires its own, nested END IF statement.

Also, lose the EXCEPTION section; it’s not doing anything except hiding useful details about any error that might occur.

. and please PLEASE PLEASE learn how to use exception handlers properly. In your case — get rid of them completely; all they do is hide errors.

Just a beginner.. but , thanks for the advice ; I will learn more about exceptions.

I just found abou ELSIF .

But , thanks to you for the help ..

Also there is no requirement for brackets around IF conditions (because we have THEN) so they are just clutter and the compiler will ignore them.

It there is a run-time error and you don’t have a EXCEPTION section, then Oracle stops execution of the procedure, and displays an error message including the exact place where it found the error, and a specific message identifying what error it detected.

If you write an EXCEPTION section, you get only what you write, so make sure what you write is better than what Oracle gives you by default.

Sometimes, the line numbers in Oracle’s default error messages are wrong. (For example, if you have mis-matched single quotes, the error may not be apparent until several lines after the actual mistake.) But usually, the line numbers are right. Your EXCEPTION section doesn’t give any hint about where the error occurred.

Sometimes, the Oracle error messages aren’t all that helpful. (For example «Missing right parenthesis» usually means «unspecified syntax error».) But usually. the Oracle error messages identify the problem clearly and correctly. Your EXCEPTION section may not display anything at all; your front end may not be displaying dbms_output messages. Assuming that it is, yout ‘No such item’ message is as helpful as Oracle’s default NO_DATA_FOUND, but no better, and for all other errors, all you get is ‘Error’, which isn’t helpful t all.

Источник

PLS-00103: Encountered the symbol «Create»

I have created a package in Oracle sql developer to communicate with LDAP; which has two procedures and one function.

Two Procedures — One for establishing a connection and another for closing the connection

Function — To search a particular string in LDAP and return 1 if the string is exists else it will return 0.

Apart from this, I have a query in which I used to call the function. ie the output of the query will retrieve a set of records and one of the data (string) in a column will go as an input to the function. The function will search for the string in the LDAP and return the number. If the return value is 1 we will display it as ‘Active’ in our query. The query will run multiple times based on the user selection.

The reason for the creation of package is to keep the LDAP connection alive. If I use it as a single function each time (for each string) it has to establish a connection with LDAP, search the string and closing the connection and it badly affects the performance.

Now my issue is, I’m getting the error: Error(6,14): PLS-00103: Encountered the symbol «create» (seems to be issue occurs in sql developer)

I have tried lot and searched lot but nothing resolved the issue. Also tried by including slash (/) after the end; as suggessted in many forums, but no use. I’m getting the same error.

I have posted the package below. It would be great if some one help me to resolve it. The aim of the package is to keep the LDAP connection alive.

Thanks in advance.

CREATE OR REPLACE
PACKAGE GET_LDAP AS

PROCEDURE GET_LDAP_CON (l_retval PLS_INTEGER, l_session DBMS_LDAP.session);
FUNCTION GET_LDAP_SEARCH (loginname VARCHAR2, l_attrs DBMS_LDAP.string_collection, l_count NUMBER) RETURN NUMBER;
PROCEDURE GET_LDAP_DISCON (l_retval PLS_INTEGER);
END GET_LDAP;

CREATE OR REPLACE
PACKAGE BODY GET_LDAP AS

ldapLogon:=’FALSE’;
l_ldap_host VARCHAR2(256) := »;
l_ldap_port VARCHAR2(256) := »;
l_ldap_user VARCHAR2(256) := »;
l_ldap_passwd VARCHAR2(256) := »;
l_ldap_base VARCHAR2(256) := »;
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collection;
l_message DBMS_LDAP.message;
l_filter varchar2(35):=’ =’|| loginname;
l_count NUMBER:=0;

— code for procedure GET_LDAP_CON

PROCEDURE GET_LDAP_CON (l_session, l_retval) is
BEGIN

Источник

Error [PLS-00103: Encountered the symbol «CREATE»] using PACKAGE

Hi guys!
When I compile this Package, i get this Error:
PLS-00103: Encountered the symbol «CREATE»

How can I solve this problem?
The code compiled is bellow:

CREATE OR REPLACE
PACKAGE CAMPO_PACK AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE DeleteCode(pCod NUMBER);

END CAMPO_PACK;
—- body —-
CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS

PROCEDURE DeleteCode(pCod NUMBER) AS
BEGIN
DELETE FROM campo
WHERE cod = pcod;
END DeleteCode;

END CAMPO_PACK;

Thanks for help,
Anderson

Edited by: user8723300 on 13/08/2009 17:03

Edited by: user8723300 on 13/08/2009 17:04

Best Answer

I don’t know that tool very well.

The package header and body are two separate objects and must be compiled separately. I suspect that the package body is somehow getting included in the package header and SQL Developer is trying to compile the whole thing at once. You need to figure out how to compile the header first, then the body. Your code compiles when I use SQL Plus. I had to create the table first, then I included a slash (/) after the package header and another one after the body. The slash tells sql plus to execute the buffer (in this case, compile the object).
If I remove the slash after the package header, sql plus tries to compile the whole thing at once and I get the same error you’re getting:

Answers

SQLPLUS is confused by blank lines within a SQL statement.

CUT & PASTE whole session so we can see exactly what you did & how Oracle responded

CREATE OR REPLACE
PACKAGE CAMPO_PACK AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE DeleteCode(pCod NUMBER);

END CAMPO_PACK;
/ — try with this

—- body —-
CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS

PROCEDURE DeleteCode(pCod NUMBER) AS
BEGIN
DELETE FROM campo
WHERE cod = pcod;
END DeleteCode;

END CAMPO_PACK;
/— try with this

Hi!
Taking out the blank lines I ‘ve got the same eror messsage.

Compiling the code above, I’ve got the following message:
PLS-00103: Encountered the symbol «/»

Any other alternative?

13:27:11 SQL> CREATE OR REPLACE
13:27:19 2 PACKAGE CAMPO_PACK AS
13:27:19 3
13:27:19 4 TYPE T_CURSOR IS REF CURSOR;
13:27:19 5
13:27:19 6 PROCEDURE DeleteCode(pCod NUMBER);
13:27:19 7
13:27:19 8 END CAMPO_PACK;
13:27:20 9 /

Elapsed: 00:00:00.60
13:27:24 SQL> CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS
13:27:43 2
13:27:43 3 PROCEDURE DeleteCode(pCod NUMBER) AS
13:27:43 4 BEGIN
13:27:43 5 DELETE FROM campo
13:27:43 6 WHERE cod = pcod;
13:27:43 7 END DeleteCode;
13:27:43 8
13:27:43 9 END CAMPO_PACK;
13:27:44 10 /

Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.12
13:27:44 SQL> show errors
Errors for PACKAGE BODY CAMPO_PACK:

LINE/COL ERROR
——— ——————————————————————
5/1 PL/SQL: SQL Statement ignored
5/13 PL/SQL: ORA-00942: table or view does not exist
13:27:57 SQL>

Does this error seem something related to database permission or role?
Can this problem be a problem related to environment configuration?
I’ ve enabled every permissions like this:

CONNECT
RESOURCE
DBA

CREATE DATABASE LINK
CREATE MATERIALIZED VIEW
CREATE PROCEDURE
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

Forgive me but I’ve justs tarted to use Oracle today!

What tool are you using? SQL Plus? Something else?

If SQL Plus, post (cut and paste) the entire session exactly as you see it.

I’m using Oracle SQL Developer 1.5.5 (1.5.5.59.69).
How can I get the session in it?

I don’t know that tool very well.

The package header and body are two separate objects and must be compiled separately. I suspect that the package body is somehow getting included in the package header and SQL Developer is trying to compile the whole thing at once. You need to figure out how to compile the header first, then the body. Your code compiles when I use SQL Plus. I had to create the table first, then I included a slash (/) after the package header and another one after the body. The slash tells sql plus to execute the buffer (in this case, compile the object).
If I remove the slash after the package header, sql plus tries to compile the whole thing at once and I get the same error you’re getting:

if you don’t have permission it will say ORA-01031: insufficient privileges

you problem is that spec and body are getting compile together. «/» will separate spec and body

was this helpful?

Great!
Your solutions runs perfectally!

The problem is i was using false script datas.

The problems is the sequence bellow is not being recognized and it is into PACKAGE BODY:
SELECT CAMPO_SEQ.CURRVAL INTO pNewID FROM DUAL;

Let me put the whole script joining the execution:

SQL> CREATE OR REPLACE PACKAGE CAMPO_PACK AS
2
3 TYPE T_CURSOR IS REF CURSOR;
4
5 PROCEDURE InserirCampo(pNewID OUT NUMBER, pCod_Requisicao NUMBER, pCod_Ti
poCampo NUMBER, pCampo VARCHAR2, pDataInicial TIMESTAMP, pDataFinal TIMESTAMP, p
Cod_Cidade NUMBER,
6 pLogradouro VARCHAR2, pNumero VARCHAR2, pComplemento VARCHAR2, pBairro VA
RCHAR2, pCEP VARCHAR2, pProtocoloRelacionado VARCHAR2);
7 PROCEDURE GetCamposByRequisicaoID(pCod_Requisicao NUMBER, IO_CURSOR OUT
T_CURSOR);
8 PROCEDURE DeletarByRequisicaoID(pCod_Requisicao NUMBER);
9
10 END CAMPO_PACK;
11 /

SQL> CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS
2
3 PROCEDURE InserirCampo(pNewID OUT NUMBER, pCod_Requisicao NUMBER, pCod_Ti
poCampo NUMBER, pCampo VARCHAR2, pDataInicial TIMESTAMP, pDataFinal TIMESTAMP, p
Cod_Cidade NUMBER,
4 pLogradouro VARCHAR2, pNumero VARCHAR2, pComplemento VARCHAR2, pBairro VA
RCHAR2, pCEP VARCHAR2, pProtocoloRelacionado VARCHAR2) AS
5 BEGIN
6 INSERT INTO Campo (Cod_Requisicao, Cod_TipoCampo, Campo, DataInicial, D
ataFinal, cod_cidade, Logradouro, Numero, Complemento, Bairro, CEP, ProtocoloRel
acionado)
7 VALUES
8 (pCod_Requisicao, pCod_TipoCampo, pCampo, pDataInicial, pDataFinal, pco
d_cidade, pLogradouro, pNumero, pComplemento, pBairro, pCEP, pProtocoloRelaciona
do);
9
*10 SELECT CAMPO_SEQ.CURRVAL INTO pNewID FROM DUAL;*
11 END InserirCampo;
12
13 PROCEDURE GetCamposByRequisicaoID(pCod_Requisicao NUMBER, IO_CURSOR OUT
T_CURSOR)
14 IS
15 V_CURSOR T_CURSOR;
16 BEGIN
17 OPEN V_CURSOR FOR
18 SELECT Cam.Cod_Campo, Cam.Cod_Requisicao, Cam.Cod_TipoCampo, C
am.Campo, Cam.DataInicial, Cam.DataFinal, Cid.Cod_Cidade, Cid.NomeCidade, Cid.Co
d_UF, UF.Descricao UFDescricao,
19 Cam.Logradouro, Cam.Numero, Cam.Complemento, Cam.Bairro, Ca
m.CEP, Cam.ProtocoloRelacionado
20 FROM Campo Cam
21 LEFT JOIN Cidade Cid ON Cid.Cod_Cidade = Cam.Cod_Cidade
22 LEFT JOIN UF ON UF.Cod_UF = Cid.Cod_UF
23 WHERE Cod_Requisicao = pCod_Requisicao;
24 IO_CURSOR := V_CURSOR;
25 END GetCamposByRequisicaoID;
26
27 PROCEDURE DeletarByRequisicaoID(pCod_Requisicao NUMBER) AS
28 BEGIN
29 DELETE FROM campo
30 WHERE cod_requisicao = pcod_requisicao;
31 END DeletarByRequisicaoID;
32
33 END CAMPO_PACK;
34 /

Warning: Package body created with compilation errors.

SQL> show err
Erros para PACKAGE BODY CAMPO_PACK:

LINE/COL ERROR
——— ——————————————————————
10/5 PL/SQL: SQL Statement ignored
10/12 PL/SQL: ORA-02289: Sequence does not exist.
SQL>

Источник

I cannot cover all error patterns of PL-00103 in this post, here are some cases that encounter PLS-00103.

1. Missing IS
2. Missing END
3. Missing Slash
4. EXECUTE IMMEDIATE

1. Missing IS

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «BEGIN».

SQL> set serveroutput on;
SQL> create or replace procedure p1
  2  begin
  3    dbms_output.put_line('Procedure 1');
  4  end;
  5  /

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

Solution

The keyword IS is expected before BEGIN as explained above.

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6  /

2. Missing END

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «end-of-file».

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  /

Let’s see the error.

SQL> show errors
Errors for PROCEDURE P1:

Solution

In this case, the keyword END is expected before the symbol / (slash).

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6  /

3. Missing Slash

When we tried to compile two procedures in a session, we got PLS-00103: Encountered the symbol «CREATE».

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6
  7  create or replace procedure p2
  8  is
  9  begin
 10    dbms_output.put_line('Procedure 2');
 11  end;
 12  /

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

This is because every programming unit is an independent one, we should use symbol / (slash) in SQL*Plus to compile them separately.

SQL> create or replace procedure p1
  2  is
  3  begin
  4    dbms_output.put_line('Procedure 1');
  5  end;
  6  /

4. EXECUTE IMMEDIATE

In an anonymous PL/SQL block, we use EXECUTE IMMEDIATE.

SQL> begin
  2    execute immediate 'select nvl(first_name, 'NO_VALUE') from employees';
  3  end;
  4  /
  execute immediate 'select nvl(first_name, 'NO_VALUE') from employees';
                                             *
ERROR at line 2:
ORA-06550: line 2, column 46:
PLS-00103: Encountered the symbol "NO_VALUE" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol "* was inserted before "NO_VALUE" to continue.

Solution

For EXECUTE IMMEDIATE statement, you should use extra single quotes to escape original single quotes in the statement like this.

SQL> begin
  2    execute immediate 'select nvl(first_name, ''NO_VALUE'') from employees';
  3  end;
  4  /

How to Resolve PLS-00103: Encountered the symbol

PLS-00103

I cannot cover all error patterns of PL-00103 in this post, here are some cases that encounter PLS-00103.

1. Missing IS

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «BEGIN».

SQL> set serveroutput on;
SQL> create or replace procedure p1
2 begin
3 dbms_output.put_line(‘Procedure 1’);
4 end;
5 /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

LINE/COL ERROR
——— ——————————————————————
2/1 PLS-00103: Encountered the symbol «BEGIN» when expecting one of
the following:
( ; is with default authid as cluster compress order using
compiled wrapped external deterministic parallel_enable
pipelined result_cache accessible rewrite
The symbol «is» was substituted for «BEGIN» to continue .

Solution

The keyword IS is expected before BEGIN as explained above.

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6 /

SQL> show errors
No errors.

2. Missing END

You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol «end-of-file».

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors
Errors for PROCEDURE P1:

LINE/COL ERROR
——— ——————————————————————
4/38 PLS-00103: Encountered the symbol «end-of-file» when expecting
one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with

Solution

In this case, the keyword END is expected before the symbol / (slash).

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6 /

SQL> show errors
No errors.

3. Missing Slash

When we tried to compile two procedures in a session, we got PLS-00103: Encountered the symbol «CREATE».

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6
7 create or replace procedure p2
8 is
9 begin
10 dbms_output.put_line(‘Procedure 2’);
11 end;
12 /

Warning: Procedure created with compilation errors.

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

This is because every programming unit is an independent one, we should use symbol / (slash) in SQL*Plus to compile them separately.

SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line(‘Procedure 1’);
5 end;
6 /

SQL> create or replace procedure p2
2 is
3 begin
4 dbms_output.put_line(‘Procedure 2’);
5 end;
6 /

4. EXECUTE IMMEDIATE

In an anonymous PL/SQL block, we use EXECUTE IMMEDIATE .

SQL> begin
2 execute immediate ‘select nvl(first_name, ‘NO_VALUE’) from employees’ ;
3 end;
4 /
execute immediate ‘select nvl(first_name, ‘NO_VALUE’) from employees’;
*
ERROR at line 2:
ORA-06550: line 2, column 46:
PLS-00103: Encountered the symbol «NO_VALUE» when expecting one of the following:
* & = — + ; at in is mod remainder not rem return
returning <> or != or

= >= and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol «* was inserted before «NO_VALUE» to continue.

Solution

For EXECUTE IMMEDIATE statement, you should use extra single quotes to escape original single quotes in the statement like this.

SQL> begin
2 execute immediate ‘select nvl(first_name, »NO_VALUE») from employees’ ;
3 end;
4 /

Источник

PLS-00103: столкнулся с символом «конец файла» при ожидании одного из следующих:;

Я Запускаю следующий скрипт —

и получил ошибку —

4 ответов

в блоке PL / SQL оператор select должен иметь into статья:

в PL / SQL вы не можете просто выбрать некоторые данные. Куда должен пойти результат?

удалить BEGIN и END и запустите SELECT с помощью SQL * plus или другого инструмента, который может запустить инструкцию SQL и представить результат где-нибудь.

использовать SELECT department_name INTO dep_name чтобы поместить результат в переменную PL/SQL (работает, только если ваш SELECT возвращает одну строку)

использовать SELECT department_name BULK COLLECT INTO dep_name_table поставить результат в таблицу PL / SQL (работает для нескольких строк)

или, может быть, вы можете описать, чего вы пытаетесь достичь и в какой среде вы хотите запустить код SQL или PL/SQL.

PLS-00103 всегда означает, что компилятор бросил, потому что мы сделали синтаксическую ошибку. Было бы очень аккуратно, если бы текст сообщения сказал: You have made a syntax error, please check your code но увы это не так.

документация Oracle является всеобъемлющей и онлайн. Вы можете найти раздел об интеграции SQL-запросов в PL / SQL здесь. Я призываю вас прочитать его, чтобы предотвратить ваш следующий вопрос. Потому что, как только вы исправили простой синтаксис bloomer вы собираетесь ударить TOO_MANY_ROWS (предполагая, что у вас есть более одного отдела).

чтобы избежать проблемы too_many_rows, вы можете использовать курсор, что-то вроде этого (я не тестировал это, но вдоль этих строк )

Это поместит первое значение, которое он найдет в таблице, в v_department. Используйте предложение ORDER BY, чтобы убедиться, что возвращаемая строка будет той, которая вам нужна, предполагая возможность 2 разных значений.

Источник

PLS-00103 error after declare in oracle procedure

I have a simple stored proc. which got me PLS-00103 error. Try to search a solution, but not found.

24/12 PLS-00103: Encountered the symbol «LOOP» when expecting one of
the following:
:= . ( @ % ; not null range default character

If move BEGIN after declare: error will be:
9/1 PLS-00103: Encountered the symbol «DECLARE» when expecting one
of the following:
begin function package pragma procedure subtype type use
form
current cursor external language
The symbol «begin» was substituted for «DECLARE» to continue.

156/4 PLS-00103: Encountered the symbol «end-of-file» when expecting
one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with

Answers

DECLARE sections are only valid in two contexts:

1. Triggers
2. The local declaration of variables within a nested block

It still not working after remove DECLARE.

SQL> show error
Errors for PROCEDURE EC_NRB_SCHEMA.PROC_CHNB_PURGE_ALL_NED:

Works for me:
You obviously haven’t removed the correct bit or something.

It also looks like you’re trying to insert into your associative array. you can’t do that. They aren’t tables and SQL doesn’t know about them, so standard DML operations will not work on them. If you do actually have a table called stage_item_status in your schema, why call your associative array by the same name?!

What is it you’re ultimately trying to do? Update a table somewhere? Insert data somewhere? Pull data out for a report? What?

I really appreciate your kind help on this.

You notes are correct.
The table or array is not recognized. so i got error below.
Basically, I am trying to convert some proc from Syb to Oracle without touching too much logic change.
The proc tries to get a batch of pkg_nbr/id, then delete all related record in the other tables based on the nbr/id collected. again get another batch do the same thing. (I don’t know why previous people do that.) They used to do it in sybase in temp table.They want to keep it.(I don’t care.)
I have to create a pl/sql table to do that.

SQL> show error
Errors for PROCEDURE EC_NRB_SCHEMA.PROC_CHNB_PURGE_ALL_NED:

LINE/COL ERROR
——— ——————————————————————
25/6 PL/SQL: SQL Statement ignored
25/18 PL/SQL: ORA-00942: table or view does not exist
61/11 PL/SQL: SQL Statement ignored
64/27 PL/SQL: ORA-00942: table or view does not exist
69/11 PL/SQL: SQL Statement ignored
72/27 PL/SQL: ORA-00942: table or view does not exist
77/11 PL/SQL: SQL Statement ignored
80/27 PL/SQL: ORA-00942: table or view does not exist
85/11 PL/SQL: SQL Statement ignored
88/27 PL/SQL: ORA-00942: table or view does not exist
93/11 PL/SQL: SQL Statement ignored

So, rather than using an array, you could use a Global Temp Table and effectively mimic what you do in sybase, or you could simply do several deletes:
That would be my preferred solution.

Or you could just create a cursor to collect the data that in sybase you would put in the temp table, and then loop through that to delete your data. This is not the preferred method (nor is copying the sybase way of doing things by using the GTT!) as it is row-by-row (aka slow by slow).

I don’t know much about Sybase, but based on what you are showing it must be a different beast.

To me it sounds like a really bad idea, to try and do a «one-to-one» mapping of a Sybase approach into Oracle.

Why the use of temp tables (Oracle do have those, you know) or collections for that matters. Not knowing what you are trying to do, just do it. In SQL that is.

And some details:
Don’t (Not that it matters) use DISTINCT when using UNION. UNION will make everything distinct itself.

this is considered wrong. Sysdate is already of type DATE and DATE — NUMBER is DATE in Oracle. And don’t CAST, use TO_NUMBER
So change into Unless, of course, decode_desc is already a NUMBER.

The best way to do this on Oracle would depend on a number of factors. Among the most important would be the number of rows returned by the query that gets the pkg_trkng_nbr, pkg_trkng_unique_id to be deleted, and how quickly it executes. If this query is relatively quick to run, then I would be extremely likely to just do a set of straight deltes in the procedure. Something alnog the lines of:
If the driving query is relatively expensive that is, takes significant time to run, then I would likely go with Boneist’s suggestion and use a global temporary table. You would create the GTT once in sql before creating the procedure, then use it over and over. A GTT will get emptied when when the session exits (with ON COMMIT PRESERVE ROWS), or when the session commits (with ON COMMIT DELETE ROWS which is the default behaviour). So, you procedure would look something like:
My third choice would be using pl/sql arrays as you are attmpting here. To do this it would need to look more like:
Note that in all cases, I removed the DISTINCt from your select statement. Using UNION implies a sort distinct of the two queries anyway, so distincting each one individually is an overhead you don’t need. I would likely use union all in any case to avoid the sort since there is no real harm in deleting a non-existent row.

Источник

Error [PLS-00103: Encountered the symbol «CREATE»] using PACKAGE

Hi guys!
When I compile this Package, i get this Error:
PLS-00103: Encountered the symbol «CREATE»

How can I solve this problem?
The code compiled is bellow:

CREATE OR REPLACE
PACKAGE CAMPO_PACK AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE DeleteCode(pCod NUMBER);

END CAMPO_PACK;
—- body —-
CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS

PROCEDURE DeleteCode(pCod NUMBER) AS
BEGIN
DELETE FROM campo
WHERE cod = pcod;
END DeleteCode;

END CAMPO_PACK;

Thanks for help,
Anderson

Edited by: user8723300 on 13/08/2009 17:03

Edited by: user8723300 on 13/08/2009 17:04

Best Answer

I don’t know that tool very well.

The package header and body are two separate objects and must be compiled separately. I suspect that the package body is somehow getting included in the package header and SQL Developer is trying to compile the whole thing at once. You need to figure out how to compile the header first, then the body. Your code compiles when I use SQL Plus. I had to create the table first, then I included a slash (/) after the package header and another one after the body. The slash tells sql plus to execute the buffer (in this case, compile the object).
If I remove the slash after the package header, sql plus tries to compile the whole thing at once and I get the same error you’re getting:

Answers

SQLPLUS is confused by blank lines within a SQL statement.

CUT & PASTE whole session so we can see exactly what you did & how Oracle responded

CREATE OR REPLACE
PACKAGE CAMPO_PACK AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE DeleteCode(pCod NUMBER);

END CAMPO_PACK;
/ — try with this

—- body —-
CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS

PROCEDURE DeleteCode(pCod NUMBER) AS
BEGIN
DELETE FROM campo
WHERE cod = pcod;
END DeleteCode;

END CAMPO_PACK;
/— try with this

Hi!
Taking out the blank lines I ‘ve got the same eror messsage.

Compiling the code above, I’ve got the following message:
PLS-00103: Encountered the symbol «/»

Any other alternative?

13:27:11 SQL> CREATE OR REPLACE
13:27:19 2 PACKAGE CAMPO_PACK AS
13:27:19 3
13:27:19 4 TYPE T_CURSOR IS REF CURSOR;
13:27:19 5
13:27:19 6 PROCEDURE DeleteCode(pCod NUMBER);
13:27:19 7
13:27:19 8 END CAMPO_PACK;
13:27:20 9 /

Elapsed: 00:00:00.60
13:27:24 SQL> CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS
13:27:43 2
13:27:43 3 PROCEDURE DeleteCode(pCod NUMBER) AS
13:27:43 4 BEGIN
13:27:43 5 DELETE FROM campo
13:27:43 6 WHERE cod = pcod;
13:27:43 7 END DeleteCode;
13:27:43 8
13:27:43 9 END CAMPO_PACK;
13:27:44 10 /

Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.12
13:27:44 SQL> show errors
Errors for PACKAGE BODY CAMPO_PACK:

LINE/COL ERROR
——— ——————————————————————
5/1 PL/SQL: SQL Statement ignored
5/13 PL/SQL: ORA-00942: table or view does not exist
13:27:57 SQL>

Does this error seem something related to database permission or role?
Can this problem be a problem related to environment configuration?
I’ ve enabled every permissions like this:

CONNECT
RESOURCE
DBA

CREATE DATABASE LINK
CREATE MATERIALIZED VIEW
CREATE PROCEDURE
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

Forgive me but I’ve justs tarted to use Oracle today!

What tool are you using? SQL Plus? Something else?

If SQL Plus, post (cut and paste) the entire session exactly as you see it.

I’m using Oracle SQL Developer 1.5.5 (1.5.5.59.69).
How can I get the session in it?

I don’t know that tool very well.

The package header and body are two separate objects and must be compiled separately. I suspect that the package body is somehow getting included in the package header and SQL Developer is trying to compile the whole thing at once. You need to figure out how to compile the header first, then the body. Your code compiles when I use SQL Plus. I had to create the table first, then I included a slash (/) after the package header and another one after the body. The slash tells sql plus to execute the buffer (in this case, compile the object).
If I remove the slash after the package header, sql plus tries to compile the whole thing at once and I get the same error you’re getting:

if you don’t have permission it will say ORA-01031: insufficient privileges

you problem is that spec and body are getting compile together. «/» will separate spec and body

was this helpful?

Great!
Your solutions runs perfectally!

The problem is i was using false script datas.

The problems is the sequence bellow is not being recognized and it is into PACKAGE BODY:
SELECT CAMPO_SEQ.CURRVAL INTO pNewID FROM DUAL;

Let me put the whole script joining the execution:

SQL> CREATE OR REPLACE PACKAGE CAMPO_PACK AS
2
3 TYPE T_CURSOR IS REF CURSOR;
4
5 PROCEDURE InserirCampo(pNewID OUT NUMBER, pCod_Requisicao NUMBER, pCod_Ti
poCampo NUMBER, pCampo VARCHAR2, pDataInicial TIMESTAMP, pDataFinal TIMESTAMP, p
Cod_Cidade NUMBER,
6 pLogradouro VARCHAR2, pNumero VARCHAR2, pComplemento VARCHAR2, pBairro VA
RCHAR2, pCEP VARCHAR2, pProtocoloRelacionado VARCHAR2);
7 PROCEDURE GetCamposByRequisicaoID(pCod_Requisicao NUMBER, IO_CURSOR OUT
T_CURSOR);
8 PROCEDURE DeletarByRequisicaoID(pCod_Requisicao NUMBER);
9
10 END CAMPO_PACK;
11 /

SQL> CREATE OR REPLACE PACKAGE BODY CAMPO_PACK AS
2
3 PROCEDURE InserirCampo(pNewID OUT NUMBER, pCod_Requisicao NUMBER, pCod_Ti
poCampo NUMBER, pCampo VARCHAR2, pDataInicial TIMESTAMP, pDataFinal TIMESTAMP, p
Cod_Cidade NUMBER,
4 pLogradouro VARCHAR2, pNumero VARCHAR2, pComplemento VARCHAR2, pBairro VA
RCHAR2, pCEP VARCHAR2, pProtocoloRelacionado VARCHAR2) AS
5 BEGIN
6 INSERT INTO Campo (Cod_Requisicao, Cod_TipoCampo, Campo, DataInicial, D
ataFinal, cod_cidade, Logradouro, Numero, Complemento, Bairro, CEP, ProtocoloRel
acionado)
7 VALUES
8 (pCod_Requisicao, pCod_TipoCampo, pCampo, pDataInicial, pDataFinal, pco
d_cidade, pLogradouro, pNumero, pComplemento, pBairro, pCEP, pProtocoloRelaciona
do);
9
*10 SELECT CAMPO_SEQ.CURRVAL INTO pNewID FROM DUAL;*
11 END InserirCampo;
12
13 PROCEDURE GetCamposByRequisicaoID(pCod_Requisicao NUMBER, IO_CURSOR OUT
T_CURSOR)
14 IS
15 V_CURSOR T_CURSOR;
16 BEGIN
17 OPEN V_CURSOR FOR
18 SELECT Cam.Cod_Campo, Cam.Cod_Requisicao, Cam.Cod_TipoCampo, C
am.Campo, Cam.DataInicial, Cam.DataFinal, Cid.Cod_Cidade, Cid.NomeCidade, Cid.Co
d_UF, UF.Descricao UFDescricao,
19 Cam.Logradouro, Cam.Numero, Cam.Complemento, Cam.Bairro, Ca
m.CEP, Cam.ProtocoloRelacionado
20 FROM Campo Cam
21 LEFT JOIN Cidade Cid ON Cid.Cod_Cidade = Cam.Cod_Cidade
22 LEFT JOIN UF ON UF.Cod_UF = Cid.Cod_UF
23 WHERE Cod_Requisicao = pCod_Requisicao;
24 IO_CURSOR := V_CURSOR;
25 END GetCamposByRequisicaoID;
26
27 PROCEDURE DeletarByRequisicaoID(pCod_Requisicao NUMBER) AS
28 BEGIN
29 DELETE FROM campo
30 WHERE cod_requisicao = pcod_requisicao;
31 END DeletarByRequisicaoID;
32
33 END CAMPO_PACK;
34 /

Warning: Package body created with compilation errors.

SQL> show err
Erros para PACKAGE BODY CAMPO_PACK:

LINE/COL ERROR
——— ——————————————————————
10/5 PL/SQL: SQL Statement ignored
10/12 PL/SQL: ORA-02289: Sequence does not exist.
SQL>

Источник

The PLS-00103: Encountered the symbol “END” when expecting one of the following: error occurs when the keyword “END” is encountered when the PL/SQL code is expected to have a valid literal value. When oracle scans the PL/SQL code while compiling, it detects the “END” symbol instead of the literal one. In most cases, the error occurs just before the “END” statement line. If you fix the error where the line number is displayed, the error PLS-00103: Encountered the symbol “END” when expecting one of the following: will be resolved.

The PLS-00103: Encountered the symbol “END” when expecting one of the following: error appears right before the “END” keyword. Oracle was expecting a literal in the code, but instead found the symbol “END.” The problem isn’t caused by the keyword “END”. The error is caused by a programming error just before the keyword “END”. This error can be found in procedures, triggers, functions, packages, and PL/SQL blocks

Exception

The exception would show the exact error as well as the line number where it happened. The keyword “END” appears in the error message. The error occurs right before the word “END”. In the exception stack trace, the expected keywords or literals will be mentioned.

Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Cause

Oracle was anticipating any of the literals in the code, but only saw the symbol “END”. The error is very often not in the keyword “END.” The error will appear right before the END keyword on the line. In the error stack trace, the expected literal will be identified. To fix this error, the expected literal should be added or corrected in the code.

Solution 1

The keywords “BEGIN” and “END” are used to start and end a block in Oracle. If there isn’t a statement in the BEGIN and END blocks, the code would throw an error. Between begin and end, at least one statement should be available. Delete the begin and end statements or add at least one statement in between if no statement is available.

declare
begin
end;

Exception

Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution

declare
begin
  dbms_output.put_line('yawin');
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 2

In most of cases, the error occurs in the line just before the END statement. Double check the list just before the END statement. If any error exist, correct it. The error stack trace will display some possible changes in the error line. Before the “END” keyword, Oracle expects one of the specified keywords or literals to be added.

declare
begin
 dbms_output.put_line('yawin')
end;

Exception

Error report -
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   := . ( % ;
The symbol ";" was substituted for "END" to continue.
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution

declare
begin
 dbms_output.put_line('yawin');
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 3

Oracle will throw an error if the keyword “END” is used in an unlikely location. Oracle encountered the symbol “END” as it was expecting some other keyword or literals in this instance. The “END” keyword should be added to close the block of statements begun with keyword “START”. The BEGIN and END keywords should be the same and in the same order.

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 begin
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
    
     dbms_output.put_line('yawin');
     end;
 end if;
end;

Exception

Error report -
ORA-06550: line 10, column 17:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national character
   nchar
The symbol "<an identifier>" was substituted for "." to continue.
ORA-06550: line 11, column 6:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:n%s"

Solution

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
    begin
     dbms_output.put_line('yawin');
     end;
 end if;
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 4

The error “PLS-00103: Encountered the symbol “END” when expecting one of the following:” would be thrown in the code if the BEGIN and END statements are missing. Before beginning a block of sentences, Oracle expects the BEGIN keyword. The END keyword is supposed to appear at the end of the statement block. The error would be thrown if the BEGIN and END statements are not present in the blocks.

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 begin
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
     dbms_output.put_line('yawin');
 end if;
end;

Exception

Error report -
ORA-06550: line 9, column 17:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national character
   nchar
The symbol "<an identifier>" was substituted for "." to continue.
ORA-06550: line 10, column 2:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:n%s"

Solution

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 begin
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
    begin
       dbms_output.put_line('yawin');
    end
 end if;
end;

Output

yawin
PL/SQL procedure successfully completed.

1. What is your version of Node.js? Run examples/version.js to find versions.
   nodeJs 6.10.3

2. What version of node-oracledb are you using?
   1.13.1

3. What is the version of your Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?
   InstantClient 12.2 (latest on your website)

4. What is the version of Oracle Database?
   Oracle 11Xe, installed by Docker (https://hub.docker.com/r/wnameless/oracle-xe-11g/)

5. What is your OS and version?
   Ubuntu 16.04

Hi,

I am trying to run some queries on Oracle 11 XE.

For some reasons, I have to create a table through an anonymous block, then create a sequence and create a trigger, obviously, this has to be done in only one query, so I have to chain the three parts.

I’ve done this in sql developper by using the «/», which allows doing multiline request (as sql plus supports), but when trying to run it with nodeoracledb, I have the following error :

In waterfall error cb: ==> Error: ORA-06550: line 1, column 310:
PLS-00103: Encountered the symbol "/"

I’ve tried to execute all queries inside execute immediate (which works for the connection.execute), but my trigger isn’t compiled at this time.

here’s the code I use :

/* Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved. */

/******************************************************************************
 *
 * You may not use the identified files except in compliance with the Apache
 * License, Version 2.0 (the "License.")
 *
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * NAME
 *   insert1.js
 *
 * DESCRIPTION
 *   Creates a table and inserts data.  Shows DDL and DML
 *
 *****************************************************************************/

var async = require('async');
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');

var doconnect = function(cb) {
  oracledb.getConnection(
    {
      user          : dbConfig.user,
      password      : dbConfig.password,
      connectString : dbConfig.connectString
    },
    cb);
};

var dorelease = function(conn) {
  conn.release(function (err) {
    if (err)
      console.error(err.message);
  });
};

var dolaunch = function (conn, cb) {
  conn.execute(
  "BEGIN EXECUTE IMMEDIATE 'CREATE TABLE Users (id NUMBER(*,0) NULL, username NVARCHAR2(255) NULL, createdAt TIMESTAMP WITH LOCAL TIME ZONE NOT NULL, updatedAt TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,CONSTRAINT PKUsersid PRIMARY KEY (id))'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END; n n/ n CREATE SEQUENCE seq_Users;",
    
    function(err, result)
    {
      if (err) {
		  console.log('Error !');
        return cb(err, conn);
      } else {
        console.dir(result);
        return cb(null, conn);
      }
    });
};

async.waterfall(
  [
	doconnect,
    dolaunch,
    dorelease
  ],
  function (err, conn) {
    if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
    if (conn)
      dorelease(conn);
  });